ClickHouse-SQL练习一_综合

一、需求

表一经过sql语句输出为表二

-- 表一：
─uid─┬─gender─┬─agegroup─┬─favor─┐
│   1 │ M      │ 90后     │ sm    │
│   2 │ M      │ 70后     │ sj    │
│   3 │ M      │ 90后     │ ms    │
│   4 │ F      │ 80后     │ sj    │
│   5 │ F      │ 90后     │ ms    │
└─────┴────────┴──────────┴───────┘-- 表二：
┌─tag──────┬─tag_value─┬─uid─────┐
│ agegroup │ 70后      │ [2]     │
│ agegroup │ 80后      │ [4]     │
│ agegroup │ 90后      │ [1,3,5] │
│ favor    │ ms        │ [3,5]   │
│ favor    │ sj        │ [2,4]   │
│ favor    │ sm        │ [1]     │
│ gender   │ F         │ [4,5]   │
│ gender   │ M         │ [1,2,3] │
└──────────┴───────────┴─────────┘

二、分析

表一到表二是一眼看不出来的，可以一步一步来，用反推来

1、uid展开（列转行）

列转行：将一列中复杂的array或者map结构拆分成多行

把表二的uid展开如下

标签	标签值	用户
年龄段	90后	1
年龄段	90后	3
年龄段	90后	5
年龄段	80后	4
年龄段	70后	2
性别	男	1
性别	男	2
性别	男	3
性别	女	4
性别	女	5
偏好	数码	1
偏好	美食	3
偏好	美食	5
偏好	书籍	2
偏好	书籍	4

排序看起来可能更好理解

标签	标签值	用户
年龄段	90后	1
性别	男	1
偏好	数码	1
年龄段	70后	2
性别	男	2
偏好	书籍	2
年龄段	90后	3
性别	男	3
偏好	美食	3
年龄段	80后	4
性别	女	4
偏好	书籍	4
年龄段	90后	5
性别	女	5
偏好	美食	5

2、聚合

统计每个uid，聚合起来如下

用户	标签值
1	[(年龄段,90后),(性别，男),(偏好，数码)]
2	[(年龄段,70后),(性别，男),(偏好，书籍)]
3	[(年龄段,90后),(性别，男),(偏好，美食)]
4	[(年龄段,80后),(性别，女),(偏好，书籍)]
5	[(年龄段,90后),(性别，女),(偏好，美食)]

三、结果步骤

1、查询

查询出数据

select
agegroup,
gender,
favor,
uid
from user_tag_merge
;┌─agegroup─┬─gender─┬─favor─┬─uid─┐
│ 90后     │ M      │ sm    │   1 │
│ 70后     │ M      │ sj    │   2 │
│ 90后     │ M      │ ms    │   3 │
│ 80后     │ F      │ sj    │   4 │
│ 90后     │ F      │ ms    │   5 │
└──────────┴────────┴───────┴─────┘

2、组合成元组

每个值前面，补上字段名,用**()组合成元组**

select
('agegroup', agegroup ), ('gender',gender ),('favor',favor ),uid 
from user_tag_merge
;┌─tuple('agegroup', agegroup)─┬─tuple('gender', gender)─┬─tuple('favor', favor)─┬─uid─┐
│ ('agegroup','90后')         │ ('gender','M')          │ ('favor','sm')        │   1 │
│ ('agegroup','70后')         │ ('gender','M')          │ ('favor','sj')        │   2 │
│ ('agegroup','90后')         │ ('gender','M')          │ ('favor','ms')        │   3 │
│ ('agegroup','80后')         │ ('gender','F')          │ ('favor','sj')        │   4 │
│ ('agegroup','90后')         │ ('gender','F')          │ ('favor','ms')        │   5 │
└─────────────────────────────┴─────────────────────────┴───────────────────────┴─────┘

3、拼接成数组

在clickhouse中直接用[]来表示数组

select
[('agegroup', agegroup  ),('gender',gender ),('favor',favor )]  tag_code_value,uid 
from user_tag_merge
;┌─tag_code_value──────────────────────────────────────┬─uid─┐
│ [('agegroup','90后'),('gender','M'),('favor','sm')] │   1 │
│ [('agegroup','70后'),('gender','M'),('favor','sj')] │   2 │
│ [('agegroup','90后'),('gender','M'),('favor','ms')] │   3 │
│ [('agegroup','80后'),('gender','F'),('favor','sj')] │   4 │
│ [('agegroup','90后'),('gender','F'),('favor','ms')] │   5 │
└─────────────────────────────────────────────────────┴─────┘

4、炸开

用arrayJoin炸开，类似于hive中的explode

SELECT
arrayJoin
([('agegroup', agegroup), ('gender', gender), ('favor', favor)])
AS tag_code_value,uid
FROM user_tag_merge
;┌─tag_code_value──────┬─uid─┐
│ ('agegroup','90后') │   1 │
│ ('gender','M')      │   1 │
│ ('favor','sm')      │   1 │
│ ('agegroup','70后') │   2 │
│ ('gender','M')      │   2 │
│ ('favor','sj')      │   2 │
│ ('agegroup','90后') │   3 │
│ ('gender','M')      │   3 │
│ ('favor','ms')      │   3 │
│ ('agegroup','80后') │   4 │
│ ('gender','F')      │   4 │
│ ('favor','sj')      │   4 │
│ ('agegroup','90后') │   5 │
│ ('gender','F')      │   5 │
│ ('favor','ms')      │   5 │
└─────────────────────┴─────┘

5、拆分

把元组中的字段名和字段值拆开

SELECT
tag_code_value.1 AS tag_code,
tag_code_value.2 AS tag_value,
uid
FROM 
(
SELECT
arrayJoin([('agegroup', agegroup), ('gender', gender), ('favor', favor)])
AS tag_code_value, uid   
FROM user_tag_merge
) AS tv
;┌─tag_code─┬─tag_value─┬─uid─┐
│ agegroup │ 90后      │   1 │
│ gender   │ M         │   1 │
│ favor    │ sm        │   1 │
│ agegroup │ 70后      │   2 │
│ gender   │ M         │   2 │
│ favor    │ sj        │   2 │
│ agegroup │ 90后      │   3 │
│ gender   │ M         │   3 │
│ favor    │ ms        │   3 │
│ agegroup │ 80后      │   4 │
│ gender   │ F         │   4 │
│ favor    │ sj        │   4 │
│ agegroup │ 90后      │   5 │
│ gender   │ F         │   5 │
│ favor    │ ms        │   5 │
└──────────┴───────────┴─────┘

6、聚合

用前两个字段作为维度聚合uid

SELECT
tag_code_value.1 AS tag_code,
tag_code_value.2 AS tag_value,
groupArray(uid) as us
FROM 
(
SELECT
arrayJoin([('agegroup', agegroup), ('gender', gender), ('favor', favor)])
AS tag_code_value, uid   
FROM user_tag_merge
) AS tv
group by
tag_code_value.1,
tag_code_value.2
;┌─tag_code─┬─tag_value─┬─us──────┐
│ gender   │ F         │ [4,5]   │
│ agegroup │ 70后      │ [2]     │
│ agegroup │ 90后      │ [1,3,5] │
│ agegroup │ 80后      │ [4]     │
│ gender   │ M         │ [1,2,3] │
│ favor    │ ms        │ [3,5]   │
│ favor    │ sj        │ [2,4]   │
│ favor    │ sm        │ [1]     │
└──────────┴───────────┴─────────┘

四、扩展-转换为Bitmap表

在最后一步上把groupArray 替换成groupBitmapState

SELECT
tag_code_value.1 AS tag_code,
tag_code_value.2 AS tag_value,
groupBitmapState(uid) as us
FROM 
(
SELECT
arrayJoin([('agegroup', agegroup), ('gender', gender), ('favor', favor)])
AS tag_code_value, uid   
FROM user_tag_merge
) AS tv
group by
tag_code_value.1,
tag_code_value.2
;┌─tag_code─┬─tag_value─┬─us─┐
│ gender   │ F         │    │
│ agegroup │ 70后      │    │
│ agegroup │ 90后      │    │
│ agegroup │ 80后      │    │
│ gender   │ M         │    │
│ favor    │ ms        │    │
│ favor    │ sj        │    │
│ favor    │ sm        │    │
└──────────┴───────────┴────┘

注意：这里聚合成bitmap的列没有显示是正常的，因为bitmap的结构本身无法用正常文本显示