如何在 ClickHouse 中实现行列转换

Nauu

发布于 2020-06-29 11:12:38

13.7K0

发布于 2020-06-29 11:12:38

文章被收录于专栏：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.

对于这类查询，你还有其他思路吗，欢迎与我交流

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2020-06-18，如有侵权请联系 cloudcommunity@tencent.com 删除

自动驾驶