Presto SQL如何实现行转列：将行不同取值转为多列并按主键聚合

问题原因 #

当前查询生成多行的核心原因是：子查询中每一行仅对应一个conversion_behavior_index，因此四个自定义列每次只有一个有值、其余三个为空，外层distinct仅能去重完全相同的行，无法将分散在多行的有效值合并到同一行。

最优解决方案 #

无需使用join，直接对计算出的四个列做聚合即可，Presto的聚合函数会自动忽略空值，正好可以把分散在多行的有效值合并为一行：

SELECT
    message_variation_id,
    MAX(first_cv) AS first_cv,
    MAX(second_cv) AS second_cv,
    MAX(third_cv) AS third_cv,
    MAX(fourth_cv) AS fourth_cv
FROM (
    SELECT
        message_variation_id,
        IF(bcc.conversion_behavior_index = 0, bcc.conversion_behavior) first_cv,
        IF(bcc.conversion_behavior_index = 1, bcc.conversion_behavior) second_cv,
        IF(bcc.conversion_behavior_index = 2, bcc.conversion_behavior) third_cv,
        IF(bcc.conversion_behavior_index = 3, bcc.conversion_behavior) fourth_cv
    FROM braze_currents.campaigns_conversion_partitioned bcc
    WHERE message_variation_id = '9617b279-f5bd-452d-abca-3263cf7e4651'
) t
GROUP BY message_variation_id

方案说明 #

• 不需要在内层加distinct，外层GROUP BY会自动按message_variation_id分组
• 用MAX/MIN都可以，只要每个conversion_behavior_index对应唯一的conversion_behavior，聚合后会自动取出对应非空值，没有值的列会自然返回NULL，符合允许third_cv/fourth_cv为空的需求
• 性能比join方案好很多，仅需一次表扫描+分组聚合，不需要额外的关联开销

拓展写法 #

如果后续conversion_behavior_index的取值不固定为0-3，也可以用Presto的map_agg函数先把键值对聚合为map，再按key提取，写法更简洁易扩展：

SELECT
    message_variation_id,
    cv_map[0] AS first_cv,
    cv_map[1] AS second_cv,
    cv_map[2] AS third_cv,
    cv_map[3] AS fourth_cv
FROM (
    SELECT
        message_variation_id,
        MAP_AGG(conversion_behavior_index, conversion_behavior) AS cv_map
    FROM braze_currents.campaigns_conversion_partitioned bcc
    WHERE message_variation_id = '9617b279-f5bd-452d-abca-3263cf7e4651'
    GROUP BY message_variation_id
) t

内容的提问来源于stack exchange，提问作者김명현