Clickhouse中基于type和subtype实现单列转多列的方法

开发者阿泽

2026-6-18

ClickHouse行转列消除NULL值方案

原始数据

date	value	type	subtype
2021-01-01	1	A	x
2021-01-01	2	A	y
2021-01-01	3	B	x
2021-01-01	4	B	y
2021-01-02	5	A	x
2021-01-02	6	A	y
2021-01-02	7	B	x
2021-01-02	8	B	y

目标格式

date	Ax	Ay	Bx	By
2021-01-01	1	2	3	4
2021-01-02	5	6	7	8

问题重现

使用以下查询时，返回结果包含大量NULL值，每条记录仅单个字段有值：

SELECT
    date,
    case when type = 'A' and subtype = 'x' then value end Ax,
    case when type = 'A' and subtype = 'y' then value end Ay,
    case when type = 'B' and subtype = 'x' then value end Bx,
    case when type = 'B' and subtype = 'y' then value end By
FROM my_table

解决方案

通过聚合函数+分组将同一日期的行合并，过滤NULL值。由于每个date + type + subtype组合仅存在一条记录，使用max()或sum()聚合均可：

SELECT
    date,
    max(case when type = 'A' and subtype = 'x' then value end) AS Ax,
    max(case when type = 'A' and subtype = 'y' then value end) AS Ay,
    max(case when type = 'B' and subtype = 'x' then value end) AS Bx,
    max(case when type = 'B' and subtype = 'y' then value end) AS By
FROM my_table
GROUP BY date