如何在 ClickHouse 中实现行列转换

x33g5p2x  于2021-03-10 转载在 ClickHouse  
字(2.8k)|赞(0)|评价(0)|浏览(5093)

当我们在进行数据分析时,时常会遇到行转列、列转行的查询需求。今天就来聊一聊如何在 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.

相关文章

微信公众号

最新文章

更多