SQL Server如何跨多表统计用户符合条件的唯一活跃日期数

核心逻辑拆解 #

统计需求可以拆成3个必做步骤，避免走弯路：

1. 把分散在B/C/D/E/F五张产品表的活跃记录做纵向合并，绝对不要用多表横向JOIN的方式处理，避免笛卡尔积导致数据膨胀
2. 关联用户维度表A，过滤掉活跃日期晚于用户min_date的无效记录
3. 按用户分组后对日期做去重计数，保证跨产品的同一日期只计数1次

方案1：UNION ALL 合并后直接聚合（优先推荐，适配90%以上场景） #

这个方案逻辑最直观，性能开销最低，是常规数据量下的最优选择。注意用UNION ALL而非UNION，后者会对全量数据做全局排序去重，平白增加30%以上的计算开销，我们只需要在最终计数阶段按用户+日期维度去重即可。

-- 仅返回有符合条件活跃记录的用户
SELECT
    a.username,
    COUNT(DISTINCT t.active_date) AS unique_active_days
FROM A a
INNER JOIN (
    SELECT username, date AS active_date FROM B
    UNION ALL
    SELECT username, date FROM C
    UNION ALL
    SELECT username, date FROM D
    UNION ALL
    SELECT username, date FROM E
    UNION ALL
    SELECT username, date FROM F
) t ON a.username = t.username
WHERE t.active_date <= a.min_date
GROUP BY a.username;

如果需要保留A表中所有用户（无符合条件活跃记录时活跃天数显示为0），将INNER JOIN改为LEFT JOIN，同时把日期过滤条件移到JOIN的ON子句中，避免误过滤无活跃记录的用户：

SELECT
    a.username,
    COUNT(DISTINCT t.active_date) AS unique_active_days
FROM A a
LEFT JOIN (
    SELECT username, date AS active_date FROM B
    UNION ALL SELECT username, date FROM C
    UNION ALL SELECT username, date FROM D
    UNION ALL SELECT username, date FROM E
    UNION ALL SELECT username, date FROM F
) t ON a.username = t.username AND t.active_date <= a.min_date
GROUP BY a.username;

性能优化建议 #

• 给每张产品表创建(username, date)的联合非聚集索引，给A表创建username的索引，能把查询速度提升数倍
• 如果产品表的date字段带时分秒，先转成纯日期类型再计算：CAST(date AS DATE) AS active_date，避免同一日期不同时间被判定为不同天

方案2：预去重后关联（超大数据量场景优化用） #

如果5张产品表的总数据量超过千万，且单用户单日跨多产品活跃的重复记录占比超过30%，可以先对合并后的全量活跃数据按username + date做一次预分组去重，减少后续关联和计数的计算量：

SELECT
    a.username,
    COUNT(t.active_date) AS unique_active_days
FROM A a
LEFT JOIN (
    SELECT username, active_date
    FROM (
        SELECT username, CAST(date AS DATE) AS active_date FROM B
        UNION ALL SELECT username, CAST(date AS DATE) FROM C
        UNION ALL SELECT username, CAST(date AS DATE) FROM D
        UNION ALL SELECT username, CAST(date AS DATE) FROM E
        UNION ALL SELECT username, CAST(date AS DATE) FROM F
    ) all_active
    GROUP BY username, active_date
) t ON a.username = t.username AND t.active_date <= a.min_date
GROUP BY a.username;

常见避坑点 #

• 绝对不要用多表横向JOIN产品表的写法：比如依次LEFT JOIN B/C/D/E/F五张表再做计数，这种写法会产生严重的笛卡尔积膨胀——如果某用户同一天在5张表都有活跃记录，JOIN后会产生32行重复数据，不仅查询极慢，计数结果也会完全错误
• 不要滥用UNION替代UNION ALL：除非你明确需要全量数据去重，否则UNION的排序去重操作会带来不必要的性能损耗
• 注意日期类型的隐式转换：如果A表的min_date和产品表的date类型不一致（比如一个是DATE一个是DATETIME），建议显式转成相同类型再比较，避免索引失效

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