SQL的多行合并与拆分

数据分析ivy

一枚喜欢写笔记的数据分析师

1.多行合并为一行

1.1.Hive-SQL：collect_set和collect_list

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

date	name
20220822	张三
20220823	张三
20220810	李四
20220811	李四

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

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
张三	20220823,20220822	20220823,20220822
李四	20220810,20220811	20220810,20220811

输出结果有序展示

先对数据排序，用collect_list对排序后的结果进行转化，代码如下

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

输出结果

name	date_detail_set	date_detail_list
张三	20220823,20220822	20220822,20220823
李四	20220810,20220811	20220810,20220811

1.2.PostgreSQL：string_agg()

-- 模板：string_agg(字段 order by 排序字段)
-- 测试数据准备
create table tmp_0824 as 
select '20220822' as date,'张三' as name 
union all select '20220823' as date,'张三' as name 
union all select '20220810' as date,'李四' as name 
union all select '20220811' as date,'李四' as name 
-- 多行合并为一行
select 
	,string_agg(date||'；'  )  as date_set
	,string_agg(date||'；' order by date )  as date_list 
from tmp_0824
group by 
;

2.一行拆分为多行

2.1.Hive-SQL：LATERAL VIEW explode

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

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

2.2.PostgreSQL：unnest(),string_to_array()组合

-- 由于数据源末位有“,”，所以拆分后有空的行，需要限制下；一般正常情况不需要删除
-- 模板：unnest(string_to_array(需要拆分的字段, '拆分符号')) as date 
-- 案例：
select 
(select 
	,date_set