如何查找用户第n次购买记录并按所在城市汇总对应消费总额

实现思路 #

• 先给每个用户的购买记录按购买时间升序排序，标记出每一条是该用户的第几次购买
• 筛选出所有用户标记为第3次的购买记录
• 关联用户表拿到对应城市信息，关联商品表拿到对应商品的价格
• 按城市维度分组，汇总所有第3次购买的商品总价

参考SQL代码 #

WITH ranked_purchases AS (
    SELECT
        user_id,
        product_id,
        -- 按用户ID分区，购买时间升序排序，标记购买次序
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY time ASC) AS purchase_order
    FROM purchases
)
SELECT
    users.city AS user_city,
    SUM(products.price) AS sum_of_3-rd_purchases
FROM ranked_purchases
-- 仅保留第三次购买记录
WHERE purchase_order = 3
-- 关联获取用户城市
LEFT JOIN users ON ranked_purchases.user_id = users.id
-- 关联获取商品价格
LEFT JOIN products ON ranked_purchases.product_id = products.id
-- 按城市分组汇总
GROUP BY users.city;

注意事项 #

如果存在同一个用户同一时间下单多件商品的场景，可根据业务计数规则调整窗口函数：

• 同一时间多笔订单算作不同次序：保持使用ROW_NUMBER()
• 同一时间多笔订单算作同一次序，后续次序跳过重复值：替换为RANK()
• 同一时间多笔订单算作同一次序，后续次序不跳过重复值：替换为DENSE_RANK()

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