按收入和年份分组用户：Group_3连续两年分组SQL报错求助

报错原因 #

不是不能在CASE语句里用LAG函数，而是SQL语法禁止在聚合函数（如你代码中的SUM）内部嵌套窗口函数。这是因为聚合函数先对数据做分组汇总，窗口函数则在分组完成后处理行级数据，两者执行顺序不同，因此无法在聚合函数的参数中嵌套窗口函数。

原代码逻辑问题 #

你要实现的Group_3是“连续两年订单收入均超过2000的用户”，但原代码逻辑完全错误——试图在SUM的CASE判断中用LAG检测年份连续性，既违反语法规则，也无法实现需求。正确思路是先按用户+年份统计年收入，再用窗口函数识别连续达标的情况，最后结合其他分组条件完成用户划分。

正确实现代码 #

WITH yearly_revenue AS (
    -- 统计每个用户每年的总收入
    SELECT 
        email_id,
        EXTRACT(YEAR FROM order_creation_date) AS order_year,
        SUM(revenue) AS annual_revenue
    FROM sql_test
    GROUP BY email_id, EXTRACT(YEAR FROM order_creation_date)
),
user_continuous AS (
    -- 标记用户是否存在连续两年收入超2000的情况
    SELECT 
        email_id,
        MAX(CASE 
            WHEN annual_revenue > 2000 
                 AND LAG(annual_revenue) OVER(PARTITION BY email_id ORDER BY order_year) > 2000
            THEN 1 
            ELSE 0 
        END) AS has_continuous_years
    FROM yearly_revenue
    GROUP BY email_id
),
user_summary AS (
    -- 计算原需求中需要的各类汇总数据
    SELECT 
        email_id,
        COUNT(*) AS num_of_orders,
        SUM(revenue) AS total_money_spent,
        SUM(CASE WHEN order_creation_date BETWEEN '2016-01-01' AND '2016-12-31' THEN revenue END) AS rev_2016,
        SUM(CASE WHEN order_creation_date BETWEEN '2015-01-01' AND '2016-12-31' THEN revenue END) AS rev_2015_2016
    FROM sql_test
    GROUP BY email_id
)
-- 最终按优先级进行用户分组
SELECT 
    s.email_id,
    s.num_of_orders,
    s.total_money_spent,
    CASE
        WHEN s.rev_2016 > 2000 THEN 'Group_1'
        WHEN s.rev_2015_2016 > 2000 THEN 'Group_2'
        WHEN u.has_continuous_years = 1 THEN 'Group_3'
        WHEN s.rev_2015_2016 < 2000 THEN 'Group_4'
        WHEN s.rev_2015_2016 = 0 THEN 'Group_5'
    END AS user_group
FROM user_summary s
LEFT JOIN user_continuous u ON s.email_id = u.email_id
ORDER BY s.num_of_orders DESC;

代码逻辑说明 #

1. yearly_revenue：按用户和年份分组，计算每个用户每年的总收入，这是判断连续达标的基础数据。
2. user_continuous：用LAG窗口函数获取用户上一年的收入，判断是否存在连续两年收入均超2000的情况，用MAX函数标记符合条件的用户（只要有一次连续达标就算符合Group_3）。
3. user_summary：提前计算原需求中需要的各类汇总数据，避免重复计算，提升效率。
4. 最终关联所有CTE，按照你设定的分组优先级（Group_1优先级最高，依次递减）完成用户分组。

注意事项 #

• CASE语句的顺序直接影响分组结果，要确保优先级符合你的业务逻辑（比如用户同时符合Group_1和Group_3时，会被分到Group_1）。
• 该代码支持识别任意连续两年的达标情况，不限于2015-2016年的范围。

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