多INNER JOIN转置列查询性能优化求助

表结构 #

CREATE TABLE "holes" (
    "tournament"    INTEGER,
    "year"  INTEGER,
    "course"    INTEGER,
    "round" INTEGER,
    "hole"  INTEGER,
    "stimp" INTEGER,
);

示例数据 #

33  2016    895 1   1   12
33  2016    895 1   2   18
33  2016    895 1   3   15
33  2016    895 1   4   11
33  2016    895 1   5   18
33  2016    895 1   6   28
33  2016    895 1   7   21
33  2016    895 1   8   14
33  2016    895 1   9   10
33  2016    895 1   10  11
33  2016    895 1   11   12
33  2016    895 1   12   18
33  2016    895 1   13   15
33  2016    895 1   14   11
33  2016    895 1   15   18
33  2016    895 1   16   28 
33  2016    895 1   17   21
33  2016    895 1   18   14 

原始慢查询 #

最初使用多次自连接实现转置，但执行速度极慢：

SELECT h.tournament, h.year, h.course, h.round, 
hole1.stimp AS "hole 1", 
hole2.stimp AS "hole 2",
hole3.stimp AS "hole 3",
hole4.stimp AS "hole 4", 
hole5.stimp AS "hole 5",
hole6.stimp AS "hole 6", 
hole7.stimp AS "hole 7",
hole8.stimp AS "hole 8", 
hole9.stimp AS "hole 9", 
hole10.stimp AS "hole 10",
hole11.stimp AS "hole 11",
hole12.stimp AS "hole 12", 
hole13.stimp AS "hole 13",
hole14.stimp AS "hole 14", 
hole15.stimp AS "hole 15", 
hole16.stimp AS "hole 16", 
hole17.stimp AS "hole 17", 
hole18.stimp AS "hole 18"
FROM holes h
INNER JOIN holes hole1
ON hole1.course = h.hole
AND hole1.hole = '1'
INNER JOIN holes hole2
ON hole2.course = h.hole
AND hole2.hole = '2'
INNER JOIN holes hole3
ON hole3.course = h.hole
AND hole3.hole = '3'
INNER JOIN holes hole4
ON hole4.course = h.hole
AND hole4.hole = '4'
INNER JOIN holes hole5
ON hole5.course = h.hole
AND hole5.hole = '5'
INNER JOIN holes hole6
ON hole6.course = h.hole
AND hole6.hole = '6'
INNER JOIN holes hole7
ON hole7.course = h.hole
AND hole7.hole = '7'
INNER JOIN holes hole8
ON hole8.course = h.hole
AND hole8.hole = '8'
INNER JOIN holes hole9
ON hole9.course = h.hole
AND hole9.hole = '9'
INNER JOIN holes hole10
ON hole10.course = h.hole
AND hole10.hole = '10'
INNER JOIN holes hole11
ON hole11.course = h.hole
AND hole11.hole = '11'
INNER JOIN holes hole12
ON hole12.course = h.hole
AND hole12.hole = '12'
INNER JOIN holes hole13
ON hole13.course = h.hole
AND hole13.hole = '13'
INNER JOIN holes hole14
ON hole14.course = h.hole
AND hole14.hole = '14'
INNER JOIN holes hole15
ON hole15.course = h.hole
AND hole15.hole = '15'
INNER JOIN holes hole16
ON hole16.course = h.hole
AND hole16.hole = '16'
INNER JOIN holes hole17
ON hole17.course = h.hole
AND hole17.hole = '17'
INNER JOIN holes hole18
ON hole18.course = h.hole
AND hole18.hole = '18'
GROUP BY h.tournament, h.year, h.course, h.round

优化后的查询 #

@Parfait提供的优化方案，将多次自连接改为CASE聚合，但原方案使用MAX时因空值问题无法正常显示，已替换为MIN：

SELECT h.tournament, h.year, h.course, h.round, 
    MIN(CASE WHEN h2.hole = '1' THEN h2.stimp END) AS "hole 1", 
    MIN(CASE WHEN h2.hole = '2' THEN h2.stimp END) AS "hole 2",
    MIN(CASE WHEN h2.hole = '3' THEN h2.stimp END) AS "hole 3",
    MIN(CASE WHEN h2.hole = '4' THEN h2.stimp END) AS "hole 4",
    MIN(CASE WHEN h2.hole = '5' THEN h2.stimp END) AS "hole 5",
    MIN(CASE WHEN h2.hole = '6' THEN h2.stimp END) AS "hole 6",
    MIN(CASE WHEN h2.hole = '7' THEN h2.stimp END) AS "hole 7",
    MIN(CASE WHEN h2.hole = '8' THEN h2.stimp END) AS "hole 8",
    MIN(CASE WHEN h2.hole = '9' THEN h2.stimp END) AS "hole 9",
    MIN(CASE WHEN h2.hole = '10' THEN h2.stimp END) AS "hole 10",
    MIN(CASE WHEN h2.hole = '11' THEN h2.stimp END) AS "hole 11",
    MIN(CASE WHEN h2.hole = '12' THEN h2.stimp END) AS "hole 12",
    MIN(CASE WHEN h2.hole = '13' THEN h2.stimp END) AS "hole 13",
    MIN(CASE WHEN h2.hole = '14' THEN h2.stimp END) AS "hole 14", 
    MIN(CASE WHEN h2.hole = '15' THEN h2.stimp END) AS "hole 15",
    MIN(CASE WHEN h2.hole = '16' THEN h2.stimp END) AS "hole 16",
    MIN(CASE WHEN h2.hole = '17' THEN h2.stimp END) AS "hole 17",
    MIN(CASE WHEN h2.hole = '18' THEN h2.stimp END) AS "hole 18"
FROM holes h
INNER JOIN holes h2
   ON h2.course = h.hole
GROUP BY h.tournament, h.year, h.course, h.round

当前问题 #

替换MAX为MIN后解决了空值显示问题，但仍需进一步优化查询性能和逻辑正确性。

进一步优化指导 #

1. 修正自连接逻辑错误 #

原优化查询中的自连接条件h2.course = h.hole逻辑完全错误，这会把h表的球洞号和h2表的球场ID关联，导致数据匹配混乱。正确的关联条件应该是匹配同一赛事、年份、球场、轮次的记录：

INNER JOIN holes h2
   ON h2.tournament = h.tournament
   AND h2.year = h.year
   AND h2.course = h.course
   AND h2.round = h.round

2. 去掉多余的自连接，直接分组聚合 #

实际上不需要自连接，直接对原表按tournament, year, course, round分组，用CASE聚合即可，这会大幅提升性能：

SELECT 
    tournament, 
    year, 
    course, 
    round, 
    MIN(CASE WHEN hole = 1 THEN stimp END) AS "hole 1",
    MIN(CASE WHEN hole = 2 THEN stimp END) AS "hole 2",
    MIN(CASE WHEN hole = 3 THEN stimp END) AS "hole 3",
    MIN(CASE WHEN hole = 4 THEN stimp END) AS "hole 4",
    MIN(CASE WHEN hole = 5 THEN stimp END) AS "hole 5",
    MIN(CASE WHEN hole = 6 THEN stimp END) AS "hole 6",
    MIN(CASE WHEN hole = 7 THEN stimp END) AS "hole 7",
    MIN(CASE WHEN hole = 8 THEN stimp END) AS "hole 8",
    MIN(CASE WHEN hole = 9 THEN stimp END) AS "hole 9",
    MIN(CASE WHEN hole = 10 THEN stimp END) AS "hole 10",
    MIN(CASE WHEN hole = 11 THEN stimp END) AS "hole 11",
    MIN(CASE WHEN hole = 12 THEN stimp END) AS "hole 12",
    MIN(CASE WHEN hole = 13 THEN stimp END) AS "hole 13",
    MIN(CASE WHEN hole = 14 THEN stimp END) AS "hole 14",
    MIN(CASE WHEN hole = 15 THEN stimp END) AS "hole 15",
    MIN(CASE WHEN hole = 16 THEN stimp END) AS "hole 16",
    MIN(CASE WHEN hole = 17 THEN stimp END) AS "hole 17",
    MIN(CASE WHEN hole = 18 THEN stimp END) AS "hole 18"
FROM holes
GROUP BY tournament, year, course, round;

3. 空值处理优化 #

如果需要将空值替换为特定值（比如0或'无数据'），可以使用COALESCE函数：

COALESCE(MIN(CASE WHEN hole = 1 THEN stimp END), 0) AS "hole 1"

4. 添加复合索引提升性能 #

给holes表创建复合索引，覆盖分组和查询所需的字段，大幅加快分组聚合速度：

CREATE INDEX idx_holes_pivot ON holes(tournament, year, course, round, hole, stimp);

5. MIN/MAX的选择说明 #

如果每个(tournament, year, course, round, hole)组合只有一条记录，MIN和MAX的结果完全一致；如果存在重复记录，MIN取最小值，MAX取最大值，可根据业务需求选择。若要确保取任意唯一值，也可使用ANY_VALUE（MySQL）或FIRST_VALUE（窗口函数）。

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