ClickHouse行转列问题：未知列名与列数时如何输出指定聚合结果

核心思路 #

ClickHouse没有原生支持的动态PIVOT语法，因为查询列需要在执行前确定，所以我们通过动态拼接SQL的方式实现：先查询出所有不重复的type枚举值，自动生成对应聚合逻辑的查询语句，再执行该语句得到结果。

实现步骤 #

方案1：分步执行（兼容所有版本） #

1. 执行以下语句生成目标查询SQL：

SELECT concat('SELECT fecha AS date, ', 
       arrayStringConcat(
           groupArray(
               concat(
                   'uniq(if(type = ''', type, ''', Field1, null)) AS Type_', type, '_uniq_values_Field1, ',
                   'sum(if(type = ''', type, ''', Field2, 0)) AS Type_', type, '_sum_Field2'
               )
           ), ', '
       ), ' FROM myTable GROUP BY fecha ORDER BY fecha') AS query_sql
FROM (SELECT DISTINCT type FROM myTable ORDER BY type);

2. 运行上一步得到的拼接好的SQL即可获得预期输出，你的示例数据生成的SQL如下：

SELECT 
    fecha AS date,
    uniq(if(type = 'A', Field1, null)) AS Type_A_uniq_values_Field1,
    sum(if(type = 'A', Field2, 0)) AS Type_A_sum_Field2,
    uniq(if(type = 'B', Field1, null)) AS Type_B_uniq_values_Field1,
    sum(if(type = 'B', Field2, 0)) AS Type_B_sum_Field2,
    uniq(if(type = 'C', Field1, null)) AS Type_C_uniq_values_Field1,
    sum(if(type = 'C', Field2, 0)) AS Type_C_sum_Field2
FROM myTable
GROUP BY fecha
ORDER BY fecha

方案2：一步执行（支持ClickHouse 21.9及以上版本） #

可以使用EXECUTE IMMEDIATE语法直接执行动态生成的SQL，无需手动复制：

EXECUTE IMMEDIATE
SELECT concat('SELECT fecha AS date, ', 
       arrayStringConcat(
           groupArray(
               concat(
                   'uniq(if(type = ''', type, ''', Field1, null)) AS Type_', type, '_uniq_values_Field1, ',
                   'sum(if(type = ''', type, ''', Field2, 0)) AS Type_', type, '_sum_Field2'
               )
           ), ', '
       ), ' FROM myTable GROUP BY fecha ORDER BY fecha')
FROM (SELECT DISTINCT type FROM myTable ORDER BY type);

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