您可以首先使用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 |

这个方法怎么样？

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;

首先，必须使用返回所有月份（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 |

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

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

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