百万级数据集下：如何用SQL自连接计算用户最近3次历史购买平均值？

原表结构与示例数据 #

user | date       | purchase_amount
1    | 2020-01-01 | 10
1    | 2020-01-04 | 4
1    | 2020-01-05 | 1
1    | 2020-02-01 | 6
2    | ....

需求说明 #

新增列past_3_purchases_avg，计算当前购买记录日期之前最多3次历史购买的金额平均值，预期输出如下：

user | date       | purchase_amount | past_3_purchases_avg
1    | 2020-01-01 | 10              | 0 (无历史购买记录)
1    | 2020-01-04 | 4               | 10 (仅有的上一次购买为$10)
1    | 2020-01-05 | 1               | 7  (最近2次购买为$4和$10)
1    | 2020-02-01 | 6               | 5  (最近3次购买为$1、$4和$10)
1    | 2020-02-04 | 3               | 3.6 (最近3次购买为$1、$4和$6)
2    | ....

由于数据集达数百万行，窗口函数或LAG函数效率不足，以下是高效的自连接实现方案：

高效自连接解决方案 #

核心思路 #

先给每个用户的购买记录按日期升序分配行号，通过行号范围匹配当前记录的前3条历史记录，再聚合计算平均值。利用行号关联可大幅减少连接开销，配合索引能高效处理百万级数据。

实现SQL #

WITH ranked_purchases AS (
    SELECT 
        user,
        date,
        purchase_amount,
        ROW_NUMBER() OVER (PARTITION BY user ORDER BY date) AS rn
    FROM purchases
)
SELECT 
    rp.user,
    rp.date,
    rp.purchase_amount,
    COALESCE(AVG(rp_prev.purchase_amount), 0) AS past_3_purchases_avg
FROM ranked_purchases rp
LEFT JOIN ranked_purchases rp_prev
    ON rp.user = rp_prev.user
    AND rp_prev.rn BETWEEN rp.rn - 3 AND rp.rn - 1
GROUP BY rp.user, rp.date, rp.purchase_amount, rp.rn
ORDER BY rp.user, rp.date;

性能优化建议 #

1. 给原表purchases建立复合索引(user, date)，生成ranked_purchases时可直接利用索引排序，避免全表排序开销。
2. 若数据库支持，可将ranked_purchases的行号计算逻辑提前物化，或创建临时表并添加(user, rn)索引，进一步加速自连接过程。

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