PostgreSQL按年度区间与营收分组用户的SQL查询问题求助

需求明确 #

• Group 1：总营收>2000，交易时间属于2015年（2016-12-31往前推1年的完整年度区间）
• Group 2：总营收>2000，交易时间属于2014年（2016-12-31往前推2年的完整年度区间）

常见错误原因 #

你的CASE WHEN失效，大概率是这两个问题：

1. 未按「用户+年度」聚合，导致单条交易记录的日期判断覆盖了年度总营收的逻辑
2. 日期区间条件写得不严谨，只写了起始日期没写结束日期，或者条件顺序颠倒导致逻辑覆盖

正确实现代码 #

假设你的数据表字段为user_id（用户ID）、revenue（单笔营收）、transaction_date（交易日期），替换成你实际的字段名即可：

方式1：先聚合再分组（推荐，逻辑清晰） #

SELECT 
    user_id,
    total_revenue,
    CASE 
        WHEN transaction_year = 2015 THEN 'Group 1'
        WHEN transaction_year = 2014 THEN 'Group 2'
        ELSE 'Other'
    END AS user_group
FROM (
    -- 子查询先按用户+年度计算总营收，过滤掉营收不达标组
    SELECT 
        user_id,
        EXTRACT(YEAR FROM transaction_date)::INT AS transaction_year,
        SUM(revenue) AS total_revenue
    FROM sql_test.public.user_revenue -- 替换成你的实际表名
    WHERE transaction_date BETWEEN '2014-01-01' AND '2015-12-31'
    GROUP BY user_id, transaction_year
    HAVING SUM(revenue) > 2000
) AS annual_user_revenue;

方式2：直接在CASE中判断区间 #

SELECT 
    user_id,
    SUM(revenue) AS total_revenue,
    CASE 
        WHEN SUM(revenue) > 2000 
             AND transaction_date >= '2015-01-01' 
             AND transaction_date < '2016-01-01' THEN 'Group 1'
        WHEN SUM(revenue) > 2000 
             AND transaction_date >= '2014-01-01' 
             AND transaction_date < '2015-01-01' THEN 'Group 2'
        ELSE 'Other'
    END AS user_group
FROM sql_test.public.user_revenue -- 替换成你的实际表名
GROUP BY user_id, transaction_date
HAVING SUM(revenue) > 2000;

关键注意事项 #

• 用transaction_date < '2016-01-01'替代<= '2015-12-31'，能兼容带时分秒的datetime类型数据，避免遗漏2015-12-31 23:59:59的记录
• 必须按user_id + transaction_year（或日期区间）分组，否则会把同一个用户跨年度的营收混在一起计算
• CASE WHEN的条件顺序要从近到远（先2015再2014），防止逻辑覆盖

内容的提问来源于stack exchange，提问作者Christopher