主表筛选后如何高效获取关联子表的聚合统计数据？

有两张呈一对多关系的表，表结构和测试数据如下：

CREATE TABLE main 
(
    id INTEGER,
    filter_c TEXT
);

INSERT INTO main (id, filter_c) 
VALUES (1, 'data1'),
       (2, 'data2');
 
CREATE TABLE feature 
(
    main_id INTEGER,
    mark_c TEXT
);

INSERT INTO feature (main_id, mark_c) 
VALUES (1, 'mark1'),
       (1, 'mark1'),
       (1, 'mark2'),
       (1, null);

表规模分别为7k和2m条记录，预计会增长3-4倍，已创建索引。

原本的全表聚合统计查询执行时间约7秒，但实际只需从main表筛选最多10条记录，获取对应统计数据：

SELECT 
    main_id, SUM(amount) AS total, 
    mark_c AS best, MAX(marked) AS goods, 
    SUM(marked) - MAX(marked) AS bads
FROM 
    (SELECT 
         main_id, mark_c,
         COUNT(*) AS amount, --包含Null
         COUNT(mark_c) AS marked --排除Null
     FROM 
         feature
     GROUP BY 
         main_id, mark_c)
GROUP BY 
    main_id

尝试用子查询关联，但无法返回多个字段：

SELECT 
    id, filter_c,
    (SELECT 
         main_id, 
         SUM(amount) AS total, mark_c AS best, 
         MAX(marked) AS goods, SUM(marked) - MAX(marked) AS bads
     FROM  
         (SELECT 
              main_id, mark_c,
              COUNT(*) AS amount, --包含Null
              COUNT(mark_c) AS marked --排除Null
          FROM 
              feature
          WHERE 
              main_id = id --预筛选
          GROUP BY 
              main_id, mark_c)
     GROUP BY 
         main_id)
FROM 
    main
WHERE 
    filter_c = 'data1'    

需要找到仅针对所需记录收集统计数据的方法。

解决方案1：先筛选目标main记录，再关联聚合 #

先从main表筛选出目标id范围，再限定feature的查询范围进行聚合，最后二次分组得到统计结果：

SELECT 
    m.id, m.filter_c,
    f_stats.total,
    f_stats.best,
    f_stats.goods,
    f_stats.bads
FROM main m
JOIN (
    SELECT 
        main_id,
        SUM(amount) AS total,
        -- 取marked值最大的mark_c，若有多个可按需求调整排序规则
        FIRST_VALUE(mark_c) OVER (PARTITION BY main_id ORDER BY marked DESC) AS best,
        MAX(marked) AS goods,
        SUM(marked) - MAX(marked) AS bads
    FROM (
        SELECT 
            main_id,
            mark_c,
            COUNT(*) AS amount,
            COUNT(mark_c) AS marked
        FROM feature
        -- 仅处理筛选后的main_id，缩小数据范围
        WHERE main_id IN (SELECT id FROM main WHERE filter_c = 'data1')
        GROUP BY main_id, mark_c
    ) sub
    GROUP BY main_id
) f_stats ON m.id = f_stats.main_id
WHERE m.filter_c = 'data1'

这种方式避免全表扫描feature，利用main_id索引快速定位目标数据，适合数据量较大的场景。

解决方案2：使用LATERAL JOIN/APPLY（数据库专属语法） #

如果使用PostgreSQL、SQL Server等支持该语法的数据库，可以针对每条筛选后的main记录单独计算对应feature的统计，逻辑更直观：

PostgreSQL版本 #

SELECT 
    m.id, m.filter_c,
    f_stats.total,
    f_stats.best,
    f_stats.goods,
    f_stats.bads
FROM main m
LEFT JOIN LATERAL (
    SELECT 
        SUM(amount) AS total,
        FIRST_VALUE(mark_c) OVER (ORDER BY marked DESC) AS best,
        MAX(marked) AS goods,
        SUM(marked) - MAX(marked) AS bads
    FROM (
        SELECT 
            mark_c,
            COUNT(*) AS amount,
            COUNT(mark_c) AS marked
        FROM feature f
        WHERE f.main_id = m.id
        GROUP BY mark_c
    ) sub
) f_stats ON true
WHERE m.filter_c = 'data1'

SQL Server版本 #

将LATERAL替换为OUTER APPLY（保留无对应feature的main记录）或CROSS APPLY（仅保留有对应feature的main记录）即可。

这种方式会对每条筛选出的main记录单独查询，最大化利用feature(main_id)索引，尤其适合仅需少量main记录的场景。

解决方案3：用窗口函数简化聚合层级 #

通过窗口函数直接在一次分组后计算全局统计，减少子查询层级：

SELECT DISTINCT
    m.id, m.filter_c,
    SUM(f.amount) OVER (PARTITION BY f.main_id) AS total,
    FIRST_VALUE(f.mark_c) OVER (PARTITION BY f.main_id ORDER BY f.marked DESC) AS best,
    MAX(f.marked) OVER (PARTITION BY f.main_id) AS goods,
    SUM(f.marked) OVER (PARTITION BY f.main_id) - MAX(f.marked) OVER (PARTITION BY f.main_id) AS bads
FROM main m
JOIN (
    SELECT 
        main_id,
        mark_c,
        COUNT(*) AS amount,
        COUNT(mark_c) AS marked
    FROM feature
    WHERE main_id IN (SELECT id FROM main WHERE filter_c = 'data1')
    GROUP BY main_id, mark_c
) f ON m.id = f.main_id
WHERE m.filter_c = 'data1'

用窗口函数一次性计算出每个main_id的统计值，再通过DISTINCT去重，减少分组操作的开销。

性能优化建议 #

• 给feature表的main_id字段创建覆盖索引，包含mark_c字段，例如PostgreSQL：CREATE INDEX idx_feature_mainid_markc ON feature(main_id) INCLUDE(mark_c);，这样聚合查询无需回表，直接从索引获取数据。
• 如果筛选条件固定，可将筛选后的main_id存入临时表，再关联feature查询，进一步优化执行计划。

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