如何在 ClickHouse 中实现行列转换
当我们在进行数据分析时，时常会遇到行转列、列转行的查询需求。今天就来聊一聊如何在 CH 中实现这些查询。
**行转列*首先来看行转列，准备一张测试表：
CREATE TABLE test_x
(
id UInt64,
type String
)ENGINE = MergeTree()
ORDER BY id
接着写入数据:
INSERT INTO TABLE test_x
WITH(
SELECT ['A','B','C','D']
)AS dict
SELECT number,dict[number%4+1],number FROM numbers(100000)
原表数据如下所示:
ch7.nauu.com :) SELECT /* FROM test_x LIMIT 10;
SELECT /
FROM test_x
LIMIT 10
┌─id─┬─type─┬─val─┐
│ 0 │ A │ 0 │
│ 1 │ B │ 1 │
│ 2 │ C │ 2 │
│ 3 │ D │ 3 │
│ 4 │ A │ 4 │
│ 5 │ B │ 5 │
│ 6 │ C │ 6 │
│ 7 │ D │ 7 │
│ 8 │ A │ 8 │
│ 9 │ B │ 9 │
└────┴──────┴─────┘
10 rows in set. Elapsed: 0.010 sec.
现在进入正题，如果需要将行上的 type 值转为列字段，可以怎么实现呢?这里可以利用 CH 提供的**-If聚合函数。-If**是一种组合的聚合函数，其前缀可以是任意一个普通的聚合函数，例如：
sumIf(column，cond)
countIf(column，cond)
argMinIf(column，cond)
等等
其中，前缀是聚合函数的类型，column 是需要聚合的字段；而 cond 则是一个表达式，该聚合函数只会作用于符合条件范围内的数据。现在利用 **-If ** 实现行转列，例如将 type 中的 A、B、C、D 求 sum 值后转到列字段：
SELECT
sumIf(val, type = 'A') AS a,
sumIf(val, type = 'B') AS b,
sumIf(val, type = 'C') AS c,
sumIf(val, type = 'D') AS d
FROM test_x
┌──────────a─┬──────────b─┬──────────c─┬──────────d─┐
│ 1249950000 │ 1249975000 │ 1250000000 │ 1250025000 │
└────────────┴────────────┴────────────┴────────────┘
是不是很轻松呢？
**列转行*接下来继续看列转行，同样的，我们先准备测试表和测试数据：
CREATE TABLE test_y
(
id UInt64,
a String,
b String,
c String
)ENGINE = MergeTree()
ORDER BY id
INSERT INTO TABLE test_y SELECT number,concat('A',toString(number)),concat('B',toString(number)),concat('C',toString(number)) FROM numbers(10000000)
这张表的数据会是下面的样子:
ch7.nauu.com :) SELECT /* FROM test_y LIMIT 10;
SELECT /
FROM test_y
LIMIT 10
┌─id─┬─a──┬─b──┬─c──┐
│ 0 │ A0 │ B0 │ C0 │
│ 0 │ A0 │ B0 │ C0 │
│ 1 │ A1 │ B1 │ C1 │
│ 1 │ A1 │ B1 │ C1 │
│ 2 │ A2 │ B2 │ C2 │
│ 2 │ A2 │ B2 │ C2 │
│ 3 │ A3 │ B3 │ C3 │
│ 3 │ A3 │ B3 │ C3 │
│ 4 │ A4 │ B4 │ C4 │
│ 4 │ A4 │ B4 │ C4 │
└────┴────┴────┴────┘
10 rows in set. Elapsed: 0.006 sec.
现在，如果我们需要将 a、b、c 三列数据合并到一个新的列字段，可以怎么做呢？
首先能想到的方法是使用 UNION 子查询，例如：
SELECT id,a AS new_field FROM test_y
UNION ALL
SELECT id,b FROM test_y
UNION ALL
SELECT id,c FROM test_y
┌─id─┬─new_field─┐
│ 0 │ A0 │
│ 1 │ A1 │
│ 2 │ A2 │
│ 3 │ A3 │
│ 4 │ A4 │
└────┴───────────┘
┌─id─┬─new_field─┐
│ 0 │ B0 │
│ 1 │ B1 │
│ 2 │ B2 │
│ 3 │ B3 │
│ 4 │ B4 │
└────┴───────────┘
┌─id─┬─new_field─┐
│ 0 │ C0 │
│ 1 │ C1 │
│ 2 │ C2 │
│ 3 │ C3 │
│ 4 │ C4 │
└────┴───────────┘
15 rows in set. Elapsed: 0.008 sec.
这样，原来的三列数据被合并到了新字段 new_field。除了 UNION 之外，还可以怎么玩呢？我们还可以利用 arrayJoin 函数，其思路是，首先将 a、b、c 转为数组：
ch7.nauu.com :) SELECT arrayConcat(groupArray(a),groupArray(b),groupArray(c)) FROM test_y;
SELECT arrayConcat(groupArray(a), groupArray(b), groupArray(c))
FROM test_y
┌─arrayConcat(groupArray(a), groupArray(b), groupArray(c))─────────────────────┐
│ ['A0','A1','A2','A3','A4','B0','B1','B2','B3','B4','C0','C1','C2','C3','C4'] │
└──────────────────────────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.002 sec.
然后利用 arrayJoin 将数组转到行，所以最终的语句是:
ch7.nauu.com :) SELECT id,arrayJoin(arrayConcat(groupArray(a),groupArray(b),groupArray(c))) new_field FROM test_y GROUP BY id LIMIT 10;
SELECT
id,
arrayJoin(arrayConcat(groupArray(a), groupArray(b), groupArray(c))) AS new_field
FROM test_y
GROUP BY id
LIMIT 10
┌─id─┬─new_field─┐
│ 0 │ A0 │
│ 0 │ B0 │
│ 0 │ C0 │
│ 4 │ A4 │
│ 4 │ B4 │
│ 4 │ C4 │
│ 3 │ A3 │
│ 3 │ B3 │
│ 3 │ C3 │
│ 2 │ A2 │
└────┴───────────┘
10 rows in set. Elapsed: 0.016 sec.
版权说明 : 本文为转载文章, 版权归原作者所有 版权申明
原文链接 : https://mp.weixin.qq.com/s/b_zF2uBTjDRXwalWvdBbSQ
内容来源于网络，如有侵权，请联系作者删除！
