如何计算sql中1个字段中每个条件的总和？

zynd9foi 于 2021-07-29 发布在 Java

关注(0)|答案(3)|浏览(476)

我又回来了哈哈，我正在努力计算以下内容：
找出有多少用户在上面有余额£2000在最近30天内至少有一次，所以应该是贷记借记来获得每个用户的余额。
我已经附上了数据库

我尝试了以下方法，基本上是自连接，但是输出缺少值。

SELECT user_id, (credit_amount - debit_amount) AS balance
FROM (SELECT A.user_id, A.type, B.type, A.amount AS debit_amount, B.amount AS credit_amount
      FROM public.transaction A, public.transaction B
      WHERE A.user_id = B.user_id
      AND a.type LIKE 'debit'
      AND b.type LIKE 'credit'
      AND A.created_at >= CURRENT_DATE - INTERVAL '30 days'
      AND A.created_at <= CURRENT_DATE) AS table_1
WHERE (credit_amount - debit_amount) > 2000
;

但是，由于在时间间隔内没有信用卡，用户\u id 3将被跳过&某些值将丢失。。任何帮助都很好，谢谢。

sql postgresql window-functions Date pivot

来源：https://stackoverflow.com/questions/62287639/how-to-calculate-sum-for-each-criteria-in-1-field-in-sql

3条答案

按热度按时间

lztngnrs1#

找出有多少用户在上面有余额£在过去的30天里至少有一次，
您可以使用窗口函数来计算每个用户在此期间的运行余额。然后，您只需统计运行平衡曾经超过阈值的不同用户：

select count(distinct user_id)  no_users
from (
    select 
        user_id,
        sum(case when type = 'credit' then amount else -amount end) 
            over(partition by user_id order by created_at) balance
    from transaction
    where created_at >= current_date - interval '30' day and created_at < current_date
) t
where balance > 2000

赞(0）回复(0）举报 2021-07-29

chhkpiq42#

使用条件聚合：

select user_id,
       (sum(amount) filter (where type = 'credit') -
        coalesce(sum(amount) filter (where type = 'debit'), 0)
       )
from public.transaction t
where t.created_at >= CURRENT_DATE - INTERVAL '30 days' and
      t.created_at < CURRENT_DATE
group by user_id;

赞(0）回复(0）举报 2021-07-29

e5nszbig3#

SELECT user_id, 
   c.credit_amount - b.debit_amount AS balance
FROM public.transaction a

JOIN (SELECT 
    user_id, type, amount AS debit_amount, 
  FROM public.transaction 
  where a.type LIKE 'debit') b on a.user_id = b.user_id

JOIN (SELECT
  user_id, type, amount AS credit_amount
  FROM public.transaction 
  where type LIKE 'credit') c on a.user_id = c.user_id

WHERE a.created_at >= CURRENT_DATE - INTERVAL '30 days'
AND a.created_at <= CURRENT_DATE) AS table_1
AND (c.credit_amount - b.debit_amount) > 2000
GROUP BY a.user_id;

赞(0）回复(0）举报 2021-07-29

我来回答

如何计算sql中1个字段中每个条件的总和？

3条答案

相关问题

热门标签

最新问答