postgresql sql获取年末余额

dl5txlt9  于 2022-12-29  发布在  PostgreSQL
关注(0)|答案(5)|浏览(173)

我有一个一年的交易表,其中的金额表示借方交易,如果值为负或贷方交易值为正。
现在,在给定的月份中,如果借记记录的数量小于3,或者如果一个月的借记总数小于100,那么我要收取5的费用。
我想在postgre中为此构建SQL查询:

select sum(amount), count(1), date_part('month', date) as month from transactions where amount < 0 group by month;

我能够得到每月的记录水平,我坚持如何进一步进行,并得到结果。

7gcisfzg

7gcisfzg1#

您可以首先使用generate_series()生成月份系列,然后将其与事务上的聚合查询连接,最后在外部查询中实现业务逻辑:

select sum(t.balance) 
    - 5 * count(*) filter(where coalesce(t.cnt, 0) < 3 or coalesce(t.debit, 0) < 100) as balance
from generate_series(date '2020-01-01', date '2020-12-01', '1 month') as d(dt)
left join (
    select date_trunc('month', date) as dt, count(*) cnt, sum(amount) as balance,
        sum(-amount) filter(where amount < 0) as debit
    from transactions t 
    group by date_trunc('month', date)
) t on t.dt = d.dt

Demo on DB Fiddle

| balance |
| ------: |
|    2746 |
4ktjp1zp

4ktjp1zp2#

这个方法怎么样?

SELECT
  SUM(
    CASE 
      WHEN usage.amount_s > 100
        OR usage.event_c > 3
        THEN 0 
      ELSE 5
    END
  ) AS YEAR_FEE
FROM (SELECT 1 AS month UNION
      SELECT 2 UNION
      SELECT 3 UNION
      SELECT 4 UNION
      SELECT 5 UNION
      SELECT 6 UNION
      SELECT 7 UNION
      SELECT 8 UNION
      SELECT 9 UNION
      SELECT 10 UNION
      SELECT 11 UNION
      SELECT 12
) months
  LEFT OUTER JOIN
 (
  SELECT 
    sum(amount) AS amount_s,
    count(1) event_c,
    date_part('month', date) AS month
  FROM transactions
  WHERE amount < 0
  GROUP BY month
) usage ON months.month = usage.month;
czq61nw1

czq61nw13#

首先,必须使用返回所有月份(1-12)的结果集,并使用LEFT连接将其连接到表。
然后聚合以获得每个月的金额总和,并使用条件聚合从满足条件的月份中减去5。
最后使用SUM()窗口函数对每个月的结果求和:

SELECT DISTINCT SUM(
         COALESCE(SUM(t.Amount), 0) -
         CASE 
           WHEN SUM((t.Amount < 0)::int) < 3 
             OR SUM(CASE WHEN t.Amount < 0 THEN -t.Amount ELSE 0 END) < 100 THEN 5 
           ELSE 0  
         END
       ) OVER () total
FROM generate_series(1, 12, 1) m(month) LEFT JOIN transactions t 
ON m.month = date_part('month', t.date) AND date_part('year', t.date) = 2020
GROUP BY m.month

请参见demo
结果:

> | total |
> | ----: |
> |  2746 |
mqxuamgl

mqxuamgl4#

我想你可以使用汉文条款。

Select ( sum(a.total) - (12- count(b.cnt ))*5 ) as result From  
(Select sum(amount) as total , 'A' as name from  transactions  ) as a  left join  
(Select  count(amount) as cnt  , 'A' as name
From transactions 
where amount <0 
group by month(date) 
having not(count(amount) <3 or  sum(amount) >-100) ) as b 
on a.name = b.name
avwztpqn

avwztpqn5#

选择
sum(amount)- 5*(12-(选择count(*)from(选择月份、count(amount)、sum(amount)from交易,其中金额〈0,按月份分组,具有Count(amount)〉=3和Sum(amount)〈=-100)))作为交易的余额;

相关问题