基于移动日期窗口的按类别高效去重计数方案求助

核心优化思路 #

先通过去重压缩数据基数，再利用覆盖索引+定向范围查询替代全表扫描的子查询，避免重复计算，大幅提升效率。

前置步骤：去重压缩数据 #

4亿行原始数据中，同一用户在同一天同一消费类别（或类别+年龄组）的多条交易对统计唯一用户数无意义，先去重可大幅降低后续计算量：

场景1：按SpendCategory统计每日过去30天唯一用户数 #

1. 生成去重临时表并创建覆盖索引 #

-- 提取唯一的用户-消费类别-交易日期组合
SELECT DISTINCT TxnDate, UserID, SpendCategory
INTO #distinct_txns
FROM 你的主表名; -- 替换为实际的CTE或临时表名称

-- 创建覆盖索引，让查询无需回表，直接从索引获取数据
CREATE NONCLUSTERED INDEX IX_distinct_txns_cat_date_user 
ON #distinct_txns (SpendCategory, TxnDate) 
INCLUDE (UserID);

2. 高效计算移动窗口唯一用户数 #

利用CROSS APPLY结合索引的范围扫描，针对每个日期-类别组合精准定位窗口内的用户：

SELECT
    cal.TxnDate,
    cal.SpendCategory,
    -- 若业务允许近似值，SQL Server 2019+可改用APPROX_COUNT_DISTINCT(UserID)，速度提升数倍
    COUNT(DISTINCT d.UserID) AS rolling_30d_unique_users
FROM (
    -- 生成所有需要统计的日期-类别组合（基于去重后的数据，避免冗余）
    SELECT DISTINCT TxnDate, SpendCategory FROM #distinct_txns
) cal
CROSS APPLY (
    -- 仅获取当前日期-类别下，过去30天内的唯一用户
    SELECT UserID
    FROM #distinct_txns t
    WHERE t.SpendCategory = cal.SpendCategory
      AND t.TxnDate BETWEEN DATEADD(DAY, -29, cal.TxnDate) AND cal.TxnDate
    GROUP BY UserID
) d
GROUP BY cal.TxnDate, cal.SpendCategory
ORDER BY cal.SpendCategory, cal.TxnDate;

场景2：按SpendCategory+AgeGroup组合统计 #

1. 生成去重临时表并创建覆盖索引 #

-- 提取唯一的用户-消费类别-年龄组-交易日期组合
SELECT DISTINCT TxnDate, UserID, SpendCategory, AgeGroup
INTO #distinct_txns_age
FROM 你的主表名;

-- 创建适配组合维度的覆盖索引
CREATE NONCLUSTERED INDEX IX_distinct_txns_cat_age_date_user 
ON #distinct_txns_age (SpendCategory, AgeGroup, TxnDate) 
INCLUDE (UserID);

2. 计算移动窗口唯一用户数 #

SELECT
    cal.TxnDate,
    cal.SpendCategory,
    cal.AgeGroup,
    COUNT(DISTINCT d.UserID) AS rolling_30d_unique_users
FROM (
    SELECT DISTINCT TxnDate, SpendCategory, AgeGroup FROM #distinct_txns_age
) cal
CROSS APPLY (
    SELECT UserID
    FROM #distinct_txns_age t
    WHERE t.SpendCategory = cal.SpendCategory
      AND t.AgeGroup = cal.AgeGroup
      AND t.TxnDate BETWEEN DATEADD(DAY, -29, cal.TxnDate) AND cal.TxnDate
    GROUP BY UserID
) d
GROUP BY cal.TxnDate, cal.SpendCategory, cal.AgeGroup
ORDER BY cal.SpendCategory, cal.AgeGroup, cal.TxnDate;

额外优化建议 #

• 优先处理临时表而非CTE：CTE每次引用都会重新计算，转成临时表并建索引后，后续查询仅扫描索引，效率显著提升。
• 启用近似统计（可选）：若业务允许1-2%的误差，使用APPROX_COUNT_DISTINCT替代COUNT(DISTINCT)，大数据量下速度可提升5-10倍。
• 分区表适配（若可行）：若主表是按TxnDate分区的物理表，窗口查询会自动限定扫描分区，进一步减少IO开销。

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