SQL Server中按ID2统计两类行数并过滤的查询优化咨询

优化思路 #

你的原方案通过CTE关联后用AVG取全量行数的写法不够合理（本质是利用重复值的平均值等于原值），可以通过预统计全量ID2行数再关联，或直接用窗口/子查询获取全量计数，简化逻辑同时保证准确性。

方案一：预统计全量ID2行数 + 过滤后聚合 #

WITH ID2TotalCounts AS (
    -- 一次性计算每个ID2在主表中的全量总行数
    SELECT ID2, COUNT(*) AS count_of_all_rows
    FROM MyTable
    GROUP BY ID2
)
SELECT
    t.ID2,
    MIN(t.col1) AS col1_min,
    MAX(t.col1) AS col1_max,
    AVG(t.col1) AS col1_avg,
    -- 统计过滤后数据中condition=1的行数
    SUM(CASE WHEN t.condition = 1 THEN 1 ELSE 0 END) AS count_of_rows_with_condition,
    tc.count_of_all_rows
FROM MyTable t
-- 用INNER JOIN替代IN子查询，性能更稳定
INNER JOIN ListOfID1sIWant l ON t.ID1 = l.ID1
INNER JOIN ID2TotalCounts tc ON t.ID2 = tc.ID2
GROUP BY t.ID2, tc.count_of_all_rows

方案二：窗口函数 + 子查询直接获取全量计数 #

如果不想用CTE，也可以在SELECT中用子查询直接获取对应ID2的全量行数：

SELECT DISTINCT
    t.ID2,
    MIN(t.col1) OVER (PARTITION BY t.ID2) AS col1_min,
    MAX(t.col1) OVER (PARTITION BY t.ID2) AS col1_max,
    AVG(t.col1) OVER (PARTITION BY t.ID2) AS col1_avg,
    SUM(CASE WHEN t.condition = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY t.ID2) AS count_of_rows_with_condition,
    -- 子查询直接获取该ID2在主表的全量总行数
    (SELECT COUNT(*) FROM MyTable mt WHERE mt.ID2 = t.ID2) AS count_of_all_rows
FROM MyTable t
INNER JOIN ListOfID1sIWant l ON t.ID1 = l.ID1

关键优化点 #

1. 替换原方案中AVG(count_of_all_rows)的不合理写法，直接获取准确的全量计数
2. 用INNER JOIN替代IN子查询，在数据量较大时SQL Server的查询优化器更容易生成高效执行计划
3. 两种方案都避免了原方案中关联后重复计算的冗余逻辑，代码可读性更强

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