test

行转列：collect_set和collect_list简单应用

假设有表格t，表示学生迟到信息

用列转行在1行查看学生的所有迟到信息

1
2
3
4
5
6
7

select
    t.name,
    concat_ws(",",collect_set(date)) as date_detail_set,
    concat_ws(",",collect_list(date)) as date_detail_list
from t 
group by
    t.name

输出结果

name	date_detail_set	date_detail_list
张三	20181013,20181012	20181013,20181012
李四	20181012,20181014	20181012,20181014

输出结果有序展示

根据上面例子，新组成列是无序展示的，如果需要计算特定日期迟到的学生，由于展示顺序的差异，上述方法也就不能实现这个场景了
解决方案：先对数据排序，用collect_list对排序后的结果进行转化，代码如下

1
2
3
4
5
6
7
8
9
10
11

select
    t1.name,
    concat_ws(",",collect_set(date)) as date_detail_set,
    concat_set(",",collect_list(date)) as date_detail_list
from 
(select *
from t 
order by
     name,date) t1
group by
    t1.name

输出结果

name	date_detail_set	date_detail_list
张三	20181013,20181012	20181012,20181013
李四	20181012,20181014	20181012,20181014

列转行

假设有表t1，表样如上述输出结果，现在需要将其展开为表t
函数：LATERAL VIEW explode，代码如下

1
2
3
4
5
6
7

select 
	name,
	date_detail_set,
	date
from t  
where LATERAL VIEW explode(split(date_detail_set,",")) result as date
group by name,date_detail,date

Welcome to Hexo! This is your very first post. Check documentation for more info. If you get any problems when using Hexo, you can find the answer in troubleshooting or you can ask me on GitHub.

Quick Start

Create a new post

1

$ hexo new "My New Post"

More info: Writing

Run server

1

$ hexo server

More info: Server

Generate static files

1

$ hexo generate

More info: Generating

Deploy to remote sites

1

$ hexo deploy

More info: Deployment