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 )