如何高效生成level1 question与level2 question列？大数据量优化需求

针对500万行数据下子查询耗时过长的问题，推荐使用窗口函数实现单次扫描计算，彻底解决关联查询的性能瓶颈。

核心方案（带明确分组键） #

假设你的表存在用于划分组的字段（如group_id，同一组内的行共享Level1/Level2 Question），直接用窗口函数聚合：

SELECT
    -- 保留原表所有需要的字段
    id, level, type, question, other_columns,
    -- 提取当前组内符合条件的Level1 Question
    MAX(CASE WHEN level = 1 AND type = 'Group' THEN question END) OVER (PARTITION BY group_id) AS `level1 question`,
    -- 提取当前组内符合条件的Level2 Question
    MAX(CASE WHEN level = 2 AND type = 'Group' THEN question END) OVER (PARTITION BY group_id) AS `level2 question`
FROM your_table;

无明确分组键的场景（按行顺序分组） #

如果没有固定分组键，而是按行的顺序划分组（如Level1 Group行之后的所有行属于该组，直到下一个Level1 Group行），先用累积窗口函数标记分组：

WITH grouped_data AS (
    SELECT
        id, level, type, question, other_columns,
        -- 每遇到Level1 Group行，生成新分组ID
        SUM(CASE WHEN level = 1 AND type = 'Group' THEN 1 ELSE 0 END) OVER (ORDER BY id) AS group_id
    FROM your_table
)
SELECT
    id, level, type, question, other_columns,
    MAX(CASE WHEN level = 1 AND type = 'Group' THEN question END) OVER (PARTITION BY group_id) AS `level1 question`,
    MAX(CASE WHEN level = 2 AND type = 'Group' THEN question END) OVER (PARTITION BY group_id) AS `level2 question`
FROM grouped_data;

性能优化补充 #

• 给PARTITION BY和ORDER BY用到的字段（如group_id、id）创建索引，能进一步加速窗口函数的计算
• 该方案支持MySQL 8.0+、PostgreSQL、SQL Server等主流数据库，无需复杂子查询关联，500万行数据通常能在数分钟内完成计算

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