如何优化父表关联子表12个月数据的行转列查询？

问题背景 #

需要从父表parents关联按年月存储的子表parentmonthly，将过去12个月的子表数据合并为单行。现有查询可运行但希望优化效率，子表的period字段格式为年份*100+月份（例如2022年7月对应202207），查询时已知结束月份（对应原查询的bnsm12），需往前推11个月获取数据。

原查询的问题 #

原查询通过12次LEFT JOIN关联子表，每次关联一个月份的数据，这种方式会增加表关联的开销，当子表数据量较大时，性能会明显下降。

优化方案：条件聚合（CASE WHEN + GROUP BY） #

这种方式只需要一次关联子表，通过条件聚合将多行数据转换为单行，逻辑更简洁，性能更优。

示例SQL

SELECT
    bn.id,
    bn.accountno,
    -- 依次取出过去12个月的period和balance
    MAX(CASE WHEN pm.period = 202105 THEN pm.period END) AS period_01,
    MAX(CASE WHEN pm.period = 202105 THEN pm.balance END) AS balance_01,
    MAX(CASE WHEN pm.period = 202106 THEN pm.period END) AS period_02,
    MAX(CASE WHEN pm.period = 202106 THEN pm.balance END) AS balance_02,
    MAX(CASE WHEN pm.period = 202107 THEN pm.period END) AS period_03,
    MAX(CASE WHEN pm.period = 202107 THEN pm.balance END) AS balance_03,
    MAX(CASE WHEN pm.period = 202108 THEN pm.period END) AS period_04,
    MAX(CASE WHEN pm.period = 202108 THEN pm.balance END) AS balance_04,
    MAX(CASE WHEN pm.period = 202109 THEN pm.period END) AS period_05,
    MAX(CASE WHEN pm.period = 202109 THEN pm.balance END) AS balance_05,
    MAX(CASE WHEN pm.period = 202110 THEN pm.period END) AS period_06,
    MAX(CASE WHEN pm.period = 202110 THEN pm.balance END) AS balance_06,
    MAX(CASE WHEN pm.period = 202111 THEN pm.period END) AS period_07,
    MAX(CASE WHEN pm.period = 202111 THEN pm.balance END) AS balance_07,
    MAX(CASE WHEN pm.period = 202112 THEN pm.period END) AS period_08,
    MAX(CASE WHEN pm.period = 202112 THEN pm.balance END) AS balance_08,
    MAX(CASE WHEN pm.period = 202201 THEN pm.period END) AS period_09,
    MAX(CASE WHEN pm.period = 202201 THEN pm.balance END) AS balance_09,
    MAX(CASE WHEN pm.period = 202202 THEN pm.period END) AS period_10,
    MAX(CASE WHEN pm.period = 202202 THEN pm.balance END) AS balance_10,
    MAX(CASE WHEN pm.period = 202203 THEN pm.period END) AS period_11,
    MAX(CASE WHEN pm.period = 202203 THEN pm.balance END) AS balance_11,
    MAX(CASE WHEN pm.period = 202204 THEN pm.period END) AS period_12,
    MAX(CASE WHEN pm.period = 202204 THEN pm.balance END) AS balance_12
FROM parents AS bn
LEFT JOIN parentmonthly AS pm 
    ON pm.parentId = bn.id 
    AND pm.period BETWEEN 202105 AND 202204 -- 限定时间范围，减少扫描数据量
WHERE bn.id BETWEEN 16620 AND 16650
GROUP BY bn.id, bn.accountno;

方案优势

1. 减少关联开销：仅需一次LEFT JOIN，避免了12次关联带来的性能损耗
2. 提前过滤数据：通过pm.period BETWEEN条件提前筛选出目标12个月的数据，减少参与聚合的数据量
3. 兼容性强：CASE WHEN + GROUP BY的写法支持几乎所有关系型数据库，无需依赖特定数据库的PIVOT语法

额外优化建议 #

• 为子表parentmonthly建立复合索引(parentId, period)，可以大幅提升关联和过滤时的数据查找速度
• 如果需要频繁切换结束月份，可以考虑用脚本或存储过程动态生成CASE语句，避免手动修改SQL

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