Блог

Главная - Вопросы по программированию - SQL для получения общего непогашенного баланса, соответствующего критериям вывода

SQL для получения общего непогашенного баланса, соответствующего критериям вывода

#sql #postgresql #aggregate-functions

#sql #postgresql #агрегатные функции

Вопрос:

У меня есть таблица платежей, в которой есть идентификатор пользователя и сумма. Минимальный вывод средств пользователями составляет $ 5,00, и им может быть выплачено любое количество платежей. Их общий баланс нигде не сохраняется (чтобы упростить его и избежать проблем с синхронизацией), вместо этого он определяется путем выполнения запроса типа:

 SELECT SUM(amount) 
  FROM payment 
 WHERE user_id = 5;

Я хочу узнать общую сумму всех остатков, которые соответствуют требованию вывода средств. Я хотел бы сделать что-то вроде:

   SELECT SUM(SUM(amount)) 
    FROM payment 
   WHERE sum(amount) >= 5.00
GROUP BY user_id;

К сожалению, я получаю сообщение об ошибке

ОШИБКА: агрегаты не разрешены в предложении WHERE

Как я могу написать этот запрос? Я использую postgres 8.4.

Часть 2:

Я не уверен, возможно ли это вообще, но в эту таблицу платежей также включены платежи, сделанные пользователем: таблица платежей включает сумму, from_user_id и to_user_id. Это система учета с одной записью. Было бы здорово, если бы я мог также вычесть платежи, произведенные пользователем. Псевдо-sql является:

 SELECT (sum of all amounts) 
 WHERE (sum(amount where to_user_id = 5) - sum(amount where from_user_id = 5)) > 5.00

Ответ №1:

Вам нужно использовать предложение HAVING

Никогда не слышал, чтобы кто-нибудь пытался выполнить вложенную агрегацию. Я предлагаю вам выполнить внутренний бит отдельно, либо с with статментом, либо используя что-то вроде

 select sum(foo) from (select sum(amount) as foo ...having ...)

Я полагаю, что вы хотите что-то подобное для своей второй части, хотя я могу сомневаться.

 with a as(
  select sum(amount) as credit, from_user from payment group by from_user
), b as (
  select sum(amount) as debit, to_user from payment group by to_user
)
-- glossing over any null values with coalesce:
select to_user, coalesce(credit,0) - coalesce(debit,0) -- coalesce users perhaps?
from a outer join b on from_user = to_user
where coalesce(credit,0) - coalesce(debit,0) > limit -- or vice-versa

Комментарии:

1. Спасибо, это дает мне часть этого. Я все еще не могу суммировать (sum(сумма)) — в нем говорится, что вы не можете вложить агрегацию. Есть ли способ выполнить эту часть?

2. Я никогда не слышал, чтобы кто-нибудь пытался выполнить вложенную агрегацию. Я предлагаю вам выполнить внутренний бит отдельно, либо с with статментом, либо с чем-то вроде select sum(foo) from (select sum(amount) as foo ...having ...)

3. Отлично, спасибо. Есть идеи, как выполнить часть 2? Я понятия не имею, как разделить две суммы (amount) (одна сумма для to_user минус сумма для from_user)

4. Спасибо, это работает — одно исправление: это должно быть внешнее объединение, иначе у пользователей, которые никогда не отправляли деньги, не было бы баланса, но в остальном это здорово.

Ответ №2:

Вы захотите использовать предложение HAVING.

Ниже приведены все пользователи, у которых баланс превышает 5,00. Затем вы могли бы выполнить цикл на своем языке программирования, чтобы получить общий баланс. SELECT sum(amount) FROM payment GROUP BY user_id HAVING sum(amount) >= 5.00 ;

Я не могу придумать способ объединить все это в одну эффективную инструкцию SQL, но я продолжу думать.

Я также не думаю, что у меня есть ответ на вашу часть 2, я приношу извинения.

Ответ №3:

Часть 1: Агрегированные данные и HAVING предложение

В SQL WHERE предложение не поддерживает использование агрегированного значения (т. Е. MIN, MAX, SUM, COUNT и т.д.), Которого нет в подзапросе или производной таблице / встроенном представлении. Такие случаи могут быть обработаны только в HAVING предложении:

   SELECT SUM(p.amount) 
    FROM PAYMENT p
GROUP BY p.to_user_id
  HAVING SUM(p.amount) >= 5.00

Часть 2:

Поскольку пользователь может получать платежи только на свою учетную запись, или наоборот — вам необходимо получить список пользователей для внешнего присоединения к:

    SELECT COALESCE(x.debit, 0) - COALESCE(y.credit, 0) AS balance
     FROM (SELECT a.from_user_id AS user
             FROM PAYMENT a
           UNION 
           SELECT b.to_user_id
             FROM PAYMENT b) z
LEFT JOIN (SELECT t.to_user_id,
                  SUM(t.payment) AS debit
             FROM PAYMENT t
         GROUP BY t.to_user_id) x ON x.to_user_id = z.user
LEFT JOIN (SELECT t.from_user_id,
                  SUM(t.payment) AS credit
             FROM PAYMENT t
         GROUP BY t.from_user_id) y ON y.from_user_id = z.user
    WHERE COALESCE(x.debit, 0) - COALESCE(y.credit, 0) >= 5.00
 GROUP BY z.user