PostgreSQL统计用户订单数及score变更次数的SQL实现

解决方案 #

你可以通过LAG()窗口函数标记评分变更记录，再对同一用户的变更标记求和得到score_changed，修改后的SQL如下：

SELECT
    s.order_date,
    s.customer_ID,
    s.order_ID,
    s.score,
    SUM(CASE 
        WHEN LAG(s.score) OVER (PARTITION BY s.customer_ID ORDER BY s.order_date) IS DISTINCT FROM s.score 
        THEN 1 
        ELSE 0 
    END) OVER (PARTITION BY s.customer_ID) AS score_changed,
    ROW_NUMBER() OVER (PARTITION BY s.customer_ID ORDER BY s.order_date) AS orders_per_customer
FROM sales s
GROUP BY 1,2,3,4
ORDER BY 1,2,3,5;

逻辑说明

1. 先用LAG(s.score) OVER (PARTITION BY s.customer_ID ORDER BY s.order_date)获取同一用户按订单时间排序的上一条评分记录
2. 通过CASE判断当前评分和上一条评分是否不同，不同则记1，相同记0
3. 用SUM() OVER (PARTITION BY s.customer_ID)对同一用户的所有标记求和，因为窗口没有加ORDER BY，所以会统计该用户所有订单的总变更次数，同一用户所有行的取值完全一致
4. 这里用IS DISTINCT FROM是为了兼容score为NULL的场景，如果你的业务中score不会为NULL，换成!=也可以

结果验证

计算后各用户的score_changed取值符合预期：

• user_01：评分从1→5→4，共变更2次，取值为2
• user_02：仅1笔订单，无变更，取值为0
• user_03：评分从3→2，共变更1次，取值为1
• user_04：仅1笔订单，无变更，取值为0
• user_05：3笔订单评分均为1，无变更，取值为0
• user_06：仅1笔订单，无变更，取值为0

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