如何在ClickHouse中实现类似R pivot_wider、pandas pivot的行转列功能？

针对你提到的key不固定、每月动态变化的场景，以下提供两种可直接落地的实现方案，适配不同使用场景：

方案1：基于Map聚合的轻量方案（适配Hive/Spark SQL/Presto等支持复杂类型的引擎） #

该方案不需要预先枚举所有key值，也无需修改SQL主体逻辑，适配key每月变化的场景，也是你提到的基于集合类的实现思路：

1. 先按公共维度event_id、date、event_name分组，将同组的key和value聚合为一个Map结构
2. 按需从Map中取出对应字段值，不存在的key会自动返回NULL，不需要额外做缺失值处理

示例代码（Spark SQL）：

WITH event_attr_map AS (
    SELECT 
        event_id,
        date,
        event_name,
        -- 聚合同事件所有属性为key-value结构
        map_from_entries(collect_list(struct(key, value))) AS attr_map
    FROM 你的原表名称
    GROUP BY event_id, date, event_name
)
SELECT 
    event_id,
    date,
    event_name,
    attr_map['session_id'] AS session_id,
    attr_map['network'] AS network,
    attr_map['screen_id'] AS screen_id,
    attr_map['any_var'] AS any_var
    -- 后续新增key仅需要在此处新增一行取数逻辑即可
FROM event_attr_map

如果使用的是不支持map_from_entries的低版本Hive，可替换为字符串拼接转Map的逻辑：

WITH event_attr_map AS (
    SELECT 
        event_id,
        date,
        event_name,
        str_to_map(concat_ws(',', collect_list(concat(key, ':', value))), ',', ':') AS attr_map
    FROM 你的原表名称
    GROUP BY event_id, date, event_name
)
SELECT * FROM event_attr_map

方案2：动态Pivot方案（适配所有支持动态SQL的引擎，可自动生成全部列） #

如果需要直接生成包含所有key的完整宽表，不需要手动写每列的取数逻辑，可以用动态SQL自动枚举当前所有key值实现行列转换：
示例代码（MySQL语法，其他引擎仅需调整动态SQL拼接逻辑）：

-- 第一步：查询当前所有去重的key值，拼接为pivot的入参格式
SET @all_keys = (SELECT GROUP_CONCAT(DISTINCT CONCAT("'", key, "'")) FROM 你的原表名称);

-- 第二步：拼接完整的pivot SQL并执行
SET @exec_sql = CONCAT("
SELECT * 
FROM 你的原表名称
PIVOT (
    MAX(value) 
    FOR key IN (", @all_keys, ")
) AS final_wide_table
");

PREPARE stmt FROM @exec_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

注意事项 #

• 两种方案对100列左右的宽表都有很好的性能表现，无额外性能损耗
• 聚合函数使用MAX/MIN均可，同一个事件同一个key仅对应一个value，不会影响最终结果
• 如果使用Spark等大数据引擎，可通过Scala/Python代码先拉取key列表，再拼接执行SQL，逻辑和上述动态SQL一致

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