PostgreSQL分组聚合子集实现：基于半年不活跃规则计算用户忠诚度奖金余额

针对你的需求——仅累加用户最后一次半年不活跃（lag = '.5y'::interval）之后的交易金额，若不存在该间隔的交易则累加所有交易——可以通过CTE（公共表达式）+ 分组过滤的方式实现，具体步骤如下：

核心思路 #

1. 先定位每个用户最后一次触发"半年不活跃"规则的交易序号（ord）；
2. 针对每个用户，仅统计序号大于等于该序号的交易金额；若用户从未触发过该规则，则统计所有交易。

完整SQL查询语句 #

WITH user_last_inactive AS (
    -- 第一步：找到每个用户最后一次出现半年不活跃间隔的交易序号
    SELECT 
        "user",
        MAX(ord) AS last_inactive_ord
    FROM transactions
    WHERE lag = '.5y'::interval
    GROUP BY "user"
)
SELECT 
    t."user",
    SUM(t.amount) AS "sum(amount)"
FROM transactions t
LEFT JOIN user_last_inactive uli 
    ON t."user" = uli."user"
WHERE 
    -- 过滤条件：要么没有半年不活跃记录，要么交易序号在最后一次不活跃之后
    (uli.last_inactive_ord IS NULL OR t.ord >= uli.last_inactive_ord)
GROUP BY t."user"
ORDER BY t."user";

代码逻辑解释 #

1. CTE user_last_inactive：分组统计每个用户最后一次出现lag = '.5y'::interval的交易序号ord，这是我们需要保留交易的起始点；
2. 主查询：将交易表与CTE左连接，确保没有半年不活跃记录的用户也被包含；通过WHERE条件过滤出有效交易，最后按用户分组求和。

执行结果验证 #

运行上述查询后，会得到你期望的结果：

user | sum(amount)
------+-------------
    1 |          30
    2 |          30
    3 |          10

内容的提问来源于stack exchange，提问作者demon.mhm