相關(guān)函數(shù)請參考官方網(wǎng)站：
https://clickhouse.tech/docs/zh/sql-reference/functions/date-time-functions/

行列轉(zhuǎn)置一般由由行轉(zhuǎn)為列，或者由列轉(zhuǎn)為行。

CREATE TABLE datasets.t_city
(
    `province` String, 
    `city` String, 
    `createtime` DateTime, 
    `city_level` Int8
)
ENGINE = MergeTree()
ORDER BY province
SETTINGS index_granularity = 8192；
 
 
 
insert into t_city values('Hubei','Wuhan',now(),2),('Hubei','Xiangyang',now(),3),('Shanghai','Shanghai',now(),1),('Guangdong','Guangzhou',now(),1),('Guangdong','Shenzhen',now(),1),('Guangdong','DOngguan',now(),2),('Guangdong','Zhuhai',now(),3);
 
 
Clickhouse> select * from t_city;
 
SELECT *
FROM t_city
 
┌─province──┬─city──────┬──────────createtime─┬─city_level─┐
│ Guangdong │ Guangzhou │ 2020-07-07 14:02:53 │          1 │
│ Guangdong │ Shenzhen  │ 2020-07-07 14:02:53 │          1 │
│ Guangdong │ DOngguan  │ 2020-07-07 14:02:53 │          2 │
│ Guangdong │ Zhuhai    │ 2020-07-07 14:02:53 │          3 │
│ Hubei     │ Wuhan     │ 2020-07-07 14:02:53 │          2 │
│ Hubei     │ Xiangyang │ 2020-07-07 14:02:53 │          3 │
│ Shanghai  │ Shanghai  │ 2020-07-07 14:02:53 │          1 │
└───────────┴───────────┴─────────────────────┴────────────┘
 
7 rows in set. Elapsed: 0.002 sec. 
列轉(zhuǎn)行：

查詢每個省份用戶的城市：

Clickhouse> select province, groupArray(city) from t_city group by province;
 
SELECT 
    province, 
    groupArray(city)
FROM t_city
GROUP BY province
 
┌─province──┬─groupArray(city)─────────────────────────────┐
│ Shanghai  │ ['Shanghai']                                 │
│ Hubei     │ ['Wuhan','Xiangyang']                        │
│ Guangdong │ ['Guangzhou','Shenzhen','Dongguan','Zhuhai'] │
└───────────┴──────────────────────────────────────────────┘
 
 
 
 
插入一條重復(fù)的記錄：
insert into t_city values('Hubei','Wuhan',now(),2);
 
可以看到Hubei有一個重復(fù)的wuhan
 
Clickhouse> select province, groupArray(city) from t_city group by province;
 
SELECT 
    province, 
    groupArray(city)
FROM t_city
GROUP BY province
 
┌─province──┬─groupArray(city)─────────────────────────────┐
│ Shanghai  │ ['Shanghai']                                 │
│ Hubei     │ ['Wuhan','Xiangyang','Wuhan']                │
│ Guangdong │ ['Guangzhou','Shenzhen','Dongguan','Zhuhai'] │
└───────────┴──────────────────────────────────────────────┘
 
3 rows in set. Elapsed: 0.002 sec. 
 
可以使用函數(shù)groupUniqArray進行去重：
 
Clickhouse> select province, groupUniqArray(city) from t_city group by province;
 
SELECT 
    province, 
    groupUniqArray(city)
FROM t_city
GROUP BY province
 
┌─province──┬─groupUniqArray(city)─────────────────────────┐
│ Shanghai  │ ['Shanghai']                                 │
│ Hubei     │ ['Wuhan','Xiangyang']                        │
│ Guangdong │ ['Zhuhai','Dongguan','Guangzhou','Shenzhen'] │
└───────────┴──────────────────────────────────────────────┘
 
3 rows in set. Elapsed: 0.003 sec. 
字符串拆分：
 
Clickhouse> select splitByChar('#','Hubei_Wuhan#Hubei_Xiangyang#Guangdong_Shenzhen') col;
 
SELECT splitByChar('#', 'Hubei_Wuhan#Hubei_Xiangyang#Guangdong_Shenzhen') AS col
 
┌─col────────────────────────────────────────────────────┐
│ ['Hubei_Wuhan','Hubei_Xiangyang','Guangdong_Shenzhen'] │
└────────────────────────────────────────────────────────┘
 
1 rows in set. Elapsed: 0.002 sec. 
可以使用ArrayJoin 函數(shù)將一行轉(zhuǎn)為行：

Clickhouse> select arrayJoin(splitByChar('#', 'Hubei_Wuhan#Hubei_Xiangyang#Guangdong_Shenzhen')) col;
 
SELECT arrayJoin(splitByChar('#', 'Hubei_Wuhan#Hubei_Xiangyang#Guangdong_Shenzhen')) AS col
 
┌─col────────────────┐
│ Hubei_Wuhan        │
│ Hubei_Xiangyang    │
│ Guangdong_Shenzhen │
└────────────────────┘
 
3 rows in set. Elapsed: 0.002 sec. 
 
 
 
實際示例：
 
SELECT 
    province, 
    groupArray(city)
FROM t_city
GROUP BY province
 
┌─province──┬─groupArray(city)─────────────────────────────┐
│ Shanghai  │ ['Shanghai']                                 │
│ Hubei     │ ['Wuhan','Xiangyang','Wuhan']                │
│ Guangdong │ ['Guangzhou','Shenzhen','Dongguan','Zhuhai'] │
└───────────┴──────────────────────────────────────────────┘
 
3 rows in set. Elapsed: 0.003 sec. 
 
Clickhouse> SELECT     province,     arrayJoin(groupArray(city)) new_city FROM t_city GROUP BY province;
 
SELECT 
    province, 
    arrayJoin(groupArray(city)) AS new_city
FROM t_city
GROUP BY province
 
┌─province──┬─new_city──┐
│ Shanghai  │ Shanghai  │
│ Hubei     │ Wuhan     │
│ Hubei     │ Xiangyang │
│ Hubei     │ Wuhan     │
│ Guangdong │ Guangzhou │
│ Guangdong │ Shenzhen  │
│ Guangdong │ Dongguan  │
│ Guangdong │ Zhuhai    │
└───────────┴───────────┘
 
8 rows in set. Elapsed: 0.003 sec. 
結(jié)論：在clickhouse中主要使用兩個函數(shù)groupArray 和ArrayJoin。

　　

總結(jié)

以上是生活随笔為你收集整理的clickhouse 行列转换的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。