如何优化千万级数据下判断客户历史购买同款产品的SQL查询性能

原SQL性能差的核心原因 #

你当前使用的自关联写法会触发笛卡尔积效应：每个订单行需要和对应客户所有历史订单行做关联匹配，时间复杂度为O(N²)，千万级数据下关联后的数据量会膨胀到数亿甚至数十亿行，必然会触发超时。除此之外该写法还会产生重复的订单行，需要额外做去重处理，进一步拖慢查询速度。

最优优化方案：使用窗口函数替代自关联 #

窗口函数的时间复杂度为O(N)，仅需扫描一次全表即可完成计算，性能提升可以达到数十倍以上，正确写法如下：

select 
    order_date,
    customer_id,
    order_id,
    product_id,
    case 
        when lag(order_date) over(partition by customer_id, product_id order by order_date) is not null
        then 'Y'
        else 'N'
    end as prev_purchase_sts
from 你的订单表名

逻辑说明：按customer_id（客户ID）和product_id（商品ID）分组，同一组内按订单日期排序，lag函数取当前行的上一行的订单日期，只要存在上一行就说明该客户此前买过同款商品，标记为Y，否则标记为N。如果需要排除同日期的其他订单，可以调整排序规则为order by order_date, order_id即可。

额外性能提升建议 #

• 给订单表创建联合覆盖索引：(customer_id, product_id, order_date) include (order_id)，可以让窗口函数计算时直接走索引不需要回表查询，进一步提升查询速度
• 如果是Hive/Spark SQL等分布式计算引擎，可以适当调整shuffle分区数，避免数据倾斜导致的长尾问题

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