如何在SQL Server中为每个订单计算客户近一年历史订单数

• 需识别高用量客户：针对每个订单，统计该客户在当前订单时间点过去一年内的订单数量（不含当前订单本身）
• 核心数据字段：customer_id（客户唯一标识）、order_id（订单唯一标识）、order_dttm（订单时间戳）
• 实现要求：SQL Server环境下自动化处理，主查询不执行聚合操作，优先采用关联参考表的方式，适配大规模数据集且每日刷新的场景

此前尝试子查询关联方式，但结果不符合预期：

SELECT *
,HU_CUSTOMER_YOY
FROM data
LEFT JOIN (SELECT MAX(ORDER_ID) AS ORDER_ID
          ,COUNT(CUSTOMER_ID) AS HU_CUSTOMER_YOY
          FROM data AS CUS_HU
          WHERE ORDER_DTTM > DATEADD(YEAR, -1, ORDER_DTTM)
          GROUP BY CUS_HU.CUSTOMER_ID) 
          CUSHU on CUSHU.ORDER_ID = data.ORDER_ID

存在的问题：

• 仅在客户的最新订单上返回统计数值
• 统计的是该客户所有历史订单，而非当前订单时间点过去一年的订单数

可以采用自关联+预聚合子查询的方式，主查询保持非聚合逻辑，同时适配大数据场景：

SELECT 
    d.customer_id,
    d.order_id,
    COALESCE(hu.HU_count, 0) AS HU_count,
    d.order_dttm
FROM data d
LEFT JOIN (
    -- 预计算每个订单对应的过去一年历史订单数
    SELECT 
        d1.customer_id,
        d1.order_id,
        COUNT(d2.order_id) AS HU_count
    FROM data d1
    INNER JOIN data d2 
        ON d1.customer_id = d2.customer_id
        AND d2.order_dttm >= DATEADD(YEAR, -1, d1.order_dttm)
        AND d2.order_dttm < d1.order_dttm -- 排除当前订单本身
    GROUP BY d1.customer_id, d1.order_id
) hu 
    ON d.customer_id = hu.customer_id 
    AND d.order_id = hu.order_id
ORDER BY d.customer_id, d.order_dttm;

方案说明 #

1. 自关联逻辑：将表自身关联，d1作为当前订单表，d2作为该客户的历史订单表
2. 时间区间控制：通过d2.order_dttm >= DATEADD(YEAR, -1, d1.order_dttm)和d2.order_dttm < d1.order_dttm精准锁定当前订单生成前一年的历史订单
3. 空值处理：用COALESCE将无历史订单时的NULL替换为0，匹配预期结果格式
4. 性能优化建议：针对大规模数据集，建立复合索引提升查询效率：

CREATE NONCLUSTERED INDEX IX_data_customer_orderdttm ON data (customer_id, order_dttm) INCLUDE (order_id);

该索引可大幅降低自关联时的数据扫描量，适配每日刷新的业务场景

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