如何利用SQL窗口函数高效统计关联表的相关行数（优化大表关联计数性能）

嗨，我来帮你搞定这个大表计数的性能问题！你原来的查询之所以效率低，核心问题就是先全表统计所有documents数据，再和主表过滤后的结果关联——要是这两个documents表数据量很大，那全表扫描+分组的开销绝对是性能杀手。咱们的目标很明确：只给符合主表过滤条件的那些header，统计对应的documents行数，绝不做无用功。

核心优化思路：先过滤，再计数 #

不管用什么方法，第一步必须先把unique_headers里符合条件的行筛选出来，得到一个小的数据集，后续所有计数操作都只围绕这个小数据集的header id来做，这样就能避免全表扫描大的documents表。

方案一：带预过滤的子查询分组（最通用） #

这个方案逻辑直白，兼容性好，几乎所有SQL数据库都支持：

WITH filtered_headers AS (
    -- 先把符合条件的header筛选出来，这一步数据量很小
    SELECT header_a_id, header_b_id
    FROM unique_headers
    WHERE status = 'ACTIVE' 
      AND CreatedDate = '2025-01-01' 
      AND isDeleted = False
),
a_relevant_counts AS (
    -- 只统计和过滤后header相关的a表行数
    SELECT header_a_id, COUNT(*) AS a_count
    FROM documents_of_headers_a
    -- 只处理过滤后的header id，避免全表扫描
    WHERE header_a_id IN (SELECT header_a_id FROM filtered_headers)
    GROUP BY header_a_id
),
b_relevant_counts AS (
    -- 同理，只统计和过滤后header相关的b表行数
    SELECT header_b_id, COUNT(*) AS b_count
    FROM documents_of_headers_b
    WHERE header_b_id IN (SELECT header_b_id FROM filtered_headers)
    GROUP BY header_b_id
)
-- 最后关联结果，用COALESCE处理无匹配的情况（返回0而不是NULL）
SELECT 
    fh.header_a_id,
    fh.header_b_id,
    COALESCE(ac.a_count, 0) AS a_count,
    COALESCE(bc.b_count, 0) AS b_count
FROM filtered_headers fh
LEFT JOIN a_relevant_counts ac ON fh.header_a_id = ac.header_a_id
LEFT JOIN b_relevant_counts bc ON fh.header_b_id = bc.header_b_id;

这里的关键是，a_relevant_counts和b_relevant_counts只处理和过滤后header相关的行，不会扫描整个documents表，性能提升非常明显。

方案二：窗口函数+避免笛卡尔积（适合支持窗口函数的数据库） #

如果想用窗口函数，得注意一个坑：直接把两个documents表和主表关联会产生笛卡尔积（比如一个header对应3个a行和2个b行，关联后会生成6行数据），直接用窗口函数计数会重复统计。所以咱们可以分开关联，或者用DISTINCT配合窗口函数：

WITH filtered_headers AS (
    SELECT header_a_id, header_b_id
    FROM unique_headers
    WHERE status = 'ACTIVE' 
      AND CreatedDate = '2025-01-01' 
      AND isDeleted = False
)
SELECT DISTINCT
    fh.header_a_id,
    fh.header_b_id,
    -- 按header_a_id分组，统计对应的a表行数
    COUNT(a.header_a_id) OVER (PARTITION BY fh.header_a_id) AS a_count,
    -- 按header_b_id分组，统计对应的b表行数
    COUNT(b.header_b_id) OVER (PARTITION BY fh.header_b_id) AS b_count
FROM filtered_headers fh
-- 左关联a表，只关联过滤后header对应的行
LEFT JOIN documents_of_headers_a a ON fh.header_a_id = a.header_a_id
-- 左关联b表，同理
LEFT JOIN documents_of_headers_b b ON fh.header_b_id = b.header_b_id;

这里用DISTINCT来确保最终结果里每个header只出现一次，窗口函数则负责统计每个header对应的documents行数。不过要注意，如果你的两个documents表关联后产生的笛卡尔积行数特别多，这个方案的性能可能不如方案一，所以优先推荐方案一。

方案三：LATERAL JOIN（适合PostgreSQL、SQL Server等支持的数据库） #

如果你的数据库支持LATERAL JOIN（也叫交叉应用），这个方案会更灵活，数据库的优化器也能更好地处理：

WITH filtered_headers AS (
    SELECT header_a_id, header_b_id
    FROM unique_headers
    WHERE status = 'ACTIVE' 
      AND CreatedDate = '2025-01-01' 
      AND isDeleted = False
)
SELECT 
    fh.header_a_id,
    fh.header_b_id,
    -- 每个header单独统计对应的a表行数
    COALESCE(ac.a_count, 0) AS a_count,
    -- 每个header单独统计对应的b表行数
    COALESCE(bc.b_count, 0) AS b_count
FROM filtered_headers fh
-- 对每个header，查询对应的a表行数
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS a_count
    FROM documents_of_headers_a a
    WHERE a.header_a_id = fh.header_a_id
) ac ON true
-- 对每个header，查询对应的b表行数
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS b_count
    FROM documents_of_headers_b b
    WHERE b.header_b_id = fh.header_b_id
) bc ON true;

这个方案本质上和相关子查询类似，但写法更清晰，数据库可以优化成针对每个header id只查询一次documents表的相关行，性能表现非常稳定。

最后再划个重点 #

所有优化的核心都是缩小处理范围：先把主表的无关数据过滤掉，再只针对有用的header id去统计documents行数，绝对不要一开始就全表扫描大表。根据你的数据库类型和数据特征，选最适合的方案就行！

内容来源于stack exchange