SQL查询：计算客户首单与第二单消费金额的差值

核心思路 #

先给每个客户的订单按结账日期排序，单独提取首单、第二单的消费金额后计算差值即可，具体分三步实现：

• 第一步：计算每个订单（对应唯一cart_id）的总消费金额
• 第二步：给同个客户的所有订单按结账日期升序打序号，筛选出排序为1（首单）、2（第二单）的记录
• 第三步：关联同一客户的首单和第二单金额，计算差值

完整查询代码 #

WITH order_total AS (
    -- 计算每个购物车对应订单的总消费金额（单位：分，转元可除以100）
    SELECT 
        t1.customer_id,
        t1.cart_id,
        t1.date,
        SUM(t2.quantity * t2.price_per_unit_cents) AS order_amount_cents
    FROM 表1 t1
    JOIN 表2 t2 ON t1.cart_id = t2.cart_id
    GROUP BY t1.customer_id, t1.cart_id, t1.date
),
order_ranked AS (
    -- 给同客户的订单按结账日期排序
    SELECT 
        customer_id,
        order_amount_cents,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY date ASC) AS order_rank
    FROM order_total
)
-- 计算首单和第二单的差值，没有第二单的客户差值会显示为NULL
SELECT 
    customer_id,
    MAX(CASE WHEN order_rank = 1 THEN order_amount_cents END) AS first_order_amount_cents,
    MAX(CASE WHEN order_rank = 2 THEN order_amount_cents END) AS second_order_amount_cents,
    (MAX(CASE WHEN order_rank = 2 THEN order_amount_cents END) - MAX(CASE WHEN order_rank = 1 THEN order_amount_cents END)) AS amount_diff_cents
FROM order_ranked
WHERE order_rank <= 2
GROUP BY customer_id;

补充说明 #

• 如果需要把单位从分转成元，把order_amount_cents相关的字段都除以100即可
• 窗口函数ROW_NUMBER()会严格按日期排序，遇到同一客户同一天有多单的情况会按顺序生成1、2、3的序号；如果你需要把同一天的所有订单合并算成一单，可以把排序逻辑换成DENSE_RANK()，同时第一步分组的时候按customer_id和date聚合即可
• 最终结果里amount_diff_cents为正说明第二单比首单消费高，为负说明第二单比首单消费低

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