SQL从其他关联表返回统计总值的最优标准实践是什么

你之前用JOIN+GROUP BY统计出错，是因为直接关联Work、Quality两张明细表会产生笛卡尔积，导致统计行数被放大。性能最优、CPU占用最低的方案是先单独聚合两张明细表，再左关联到用户表，整个流程三张表仅需各扫描一次，远优于逐行计算的OUTER APPLY方案。

优化后代码 #

-- 预计算统计时间边界，避免重复计算
DECLARE @StatDateEnd DATE = DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6 + 7) -7;

SELECT 
    u.[userID],
    u.[Name],
    u.[Team],
    ISNULL(w.WorkTotal, 0) AS [Work],
    ISNULL(q.QualityFailTotal, 0) AS [Quality],
    -- 加空值处理避免除以0报错
    FORMAT(ISNULL(q.QualityFailTotal * 1.0 / NULLIF(w.WorkTotal, 0), 0), 'P') AS [%]
FROM [Users] u
LEFT JOIN (
    -- 先聚合工作记录表，每个用户仅返回1行统计结果
    SELECT 
        [UserID],
        COUNT(*) AS WorkTotal
    FROM [Work]
    WHERE [Date] < @StatDateEnd
    GROUP BY [UserID]
) w ON u.userID = w.UserID
LEFT JOIN (
    -- 先聚合考核记录表，每个用户仅返回1行统计结果
    SELECT 
        [UserID],
        COUNT(*) AS QualityFailTotal
    FROM [Quality]
    WHERE [Date] < @StatDateEnd AND [Result] = 'FAIL'
    GROUP BY [UserID]
) q ON u.userID = q.UserID

配套性能优化建议（进一步降低CPU/IO开销） #

• 给Work表创建联合覆盖索引：(UserID, Date)，聚合时直接走索引无需回表扫主数据
• 给Quality表创建联合覆盖索引：(UserID, Date, Result)，完全覆盖查询条件，无需回表

方案对比说明 #

• OUTER APPLY属于逐行关联计算，用户表有多少行就会分别查询Work、Quality表多少次，用户量过万后性能会出现明显下降
• 预聚合左关联方案三张表仅各扫描一次，不管用户量多少，明细表的扫描次数始终为1，CPU和IO开销都是最低的
• CTE写法和上述子查询预聚合的执行逻辑完全一致，性能没有差异，只是写法不同

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