如何无需自连接高效查询订单90天内的重复购买记录及保险情况？

现有存储客户交易信息的表，某一customer_id对应的记录如下：

需求：针对每个客户的每一条order_id，统计该订单bk_date之后90天内的重复购买次数，以及这些重复订单中是否存在附带保险的情况（例如order_id=1，90天内有3次重复购买，且其中存在带保险的订单）。理想输出如下：

已知仅获取下一条订单记录可使用LEAD窗口函数无需连接，但当前需求仅能想到通过自连接匹配90天内的订单，然而数据涉及数百万客户，自连接受内存限制不可行，求更高效的解决方案。

使用窗口函数的范围分区可以避免自连接，实现单次扫描数据完成统计，适合百万级大数据场景。核心思路是按客户分区、订单日期排序，针对每个订单，统计其日期之后90天内的后续订单数量及保险存在情况。

SQL示例（以PostgreSQL为例） #

SELECT
    order_id,
    bk_date,
    -- 统计当前订单日期后90天内的后续订单数量
    COUNT(*) OVER (
        PARTITION BY customer_id
        ORDER BY bk_date
        RANGE BETWEEN INTERVAL '1 day' FOLLOWING AND INTERVAL '90 days' FOLLOWING
    ) AS repeat_count,
    -- 判断后续订单是否存在带保险的记录（1表示存在，0表示不存在）
    COALESCE(MAX(booking_has_insurance_indicator) OVER (
        PARTITION BY customer_id
        ORDER BY bk_date
        RANGE BETWEEN INTERVAL '1 day' FOLLOWING AND INTERVAL '90 days' FOLLOWING
    ), 0) AS repeat_has_insurance_indicator
FROM customer_orders
ORDER BY order_id;

关键说明 #

1. 窗口范围定义：RANGE BETWEEN INTERVAL '1 day' FOLLOWING AND INTERVAL '90 days' FOLLOWING 指定统计当前订单日期之后1天到90天内的所有订单，排除当前订单本身。
2. 统计逻辑：
   • COUNT(*) 直接统计窗口内的订单数量，即重复购买次数。
   • MAX(booking_has_insurance_indicator) 取窗口内保险标识的最大值，若存在1则说明有带保险的订单；用COALESCE处理无后续订单时的NULL值，转为0。
3. 性能优势：窗口函数仅需对全表进行一次扫描，时间复杂度为O(n)，远低于自连接的O(n²)，可有效避免内存溢出问题。

不同数据库适配 #

• MySQL：需调整日期范围语法，例如将RANGE BETWEEN改为基于时间戳的范围，或使用变量结合窗口函数实现类似逻辑。
• BigQuery：支持RANGE BETWEEN的日期范围语法，可直接适配上述逻辑。

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