sql行列转化

发表于 | 分类于

test

行转列：collect_set和collect_list简单应用

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

date name
20181012 张三
20181013 张三
20181014 李四
20181012 李四

用列转行在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