构建数据：
create table if not exists tmp.luoxiaoxin9 as
select 'zhangsan' as name
,'80' as english
,'87' as math
,'90' as chinese
union all
select 'lisi' as name
,'78' as english
,'91' as math
,'88' as chinese
union all
select 'wangwu' as name
,'56' as english
,'85' as math
,'84' as chinese
union all
select 'wangwu' as name
,'93' as english
,'84' as math
,'84' as chinese
列聚合：
create table if not exists tmp.luoxiaoxin10 as
select concat_ws(',',name,english,math,chinese) as cw
,array(name,english,math,chinese) as ar
from tmp.luoxiaoxin9
行聚合：
create table if not exists tmp.luoxiaoxin11 as
select name
,concat_ws('|',collect_list(english))
,concat_ws('|',collect_list(math))
,concat_ws('|',collect_list(chinese))
,concat_ws('|',collect_set(chinese))
,concat_ws('|',collect_set(math))
,concat_ws('|',sort_array(collect_list(english)))
,concat_ws('|',sort_array(collect_list(math)))
from tmp.luoxiaoxin9
group by name
行拆分：
select cw
,ex
from tmp.luoxiaoxin10
lateral VIEWexplode(split(cw,',')) ex AS ex;
demo1:完成下图的转化
with row_line_row as (
select -- 100,历史累计a20,本月累计a8,本周累计a5,上周累计
concat_ws('a',
concat_ws(',', collect_list(histroy_cnt)),
concat_ws(',', collect_list(mon_cnt)),
concat_ws(',', collect_list(week_cnt)),
concat_ws(',', collect_list(last_week_cnt))
) cw_cl_cw from (
select '100' histroy_cnt
,'20' mon_cnt
,'8' week_cnt
,'5' last_week_cnt
union all
select '历史累计' histroy_cnt
,'本月累计' mon_cnt
,'本周累计' week_cnt
,'上周累计' last_week_cnt
) t
group by '1'
)
select
-- 再使用内层分隔符,下标索引个数是内层个数
split(es,',')[0] tag
,split(es,',')[1] cnt
from row_line_row
lateral VIEWexplode(split(cw_cl_cw,'a')) es AS es; -- 先使用外层分隔符
demo2
with row_line_row as (
select concat_ws('a',
concat_ws(',', '本月值1', mon_cnt1, mon_rat1),
concat_ws(',', '本月值2', mon_cnt2, mon_rat2)
) cw_cl_cw
from ( select '100' mon_cnt1
,'300' mon_cnt2
,'82%' mon_rat1
,'78%' mon_rat2
) t
)
select-- 再使用内层分隔符,下标索引个数是内层个数
split(es,',')[0] `指标名称`
,split(es,',')[1] `月累计`
,split(es,',')[2] `环比`
from row_line_row
lateral VIEWexplode(split(cw_cl_cw,'a')) es AS es
以前我是这么行转列的，看了某位同学的文章有所顿悟
行转列：
select id
,max(case when class='语文' then source else null end) as a
,max(case when class='数学' then source else null end) as b
,max(case when class='英语' then source else null end) as c
from table6
group by id
列转行：
select id
,'语文'
,max(yuwen)
from table7
group by id
union all
select id
,'数学'
,max(shuxue)
from table7
group by id
union all
select id
,'英语'
,max(yingyu)
from table7
group by id
排序拓展
排序：
select name
,concat_ws(",",sort_array(collect_set(math) over(distribute by name))) c
,concat_ws(",",sort_array(collect_set(math) over(partition by name))) c1
,concat_ws(",",sort_array(collect_set(math) over(partition by name order by math asc))) c2
,concat_ws(",",sort_array(collect_set(math) over(partition by name order by math desc))) c3
from tmp.luoxiaoxin9;
SELECT memberid
,regexp_replace(concat_ws('-',
sort_array(collect_set(concat_ws(':'
,cast(legcount as string)
,airways
)
)
)
),
'\d:',''
) hs
,concat_ws('-',
sort_array(collect_set(concat_ws(':',
cast(legcount as string)
,airways
)
)
)
)
from (select 1 as memberid
,'A' as airways
,2 as legcount
union ALL
select 1 as memberid
,'B' as airways
,3 as legcount
union ALL
select 2 as memberid
,'C' as airways
,4 as legcount
union ALL
select 2 as memberid
,'D' as airways
,1 as legcount
union ALL
select 2 as memberid
,'D' as airways
,1 as legcount
) as t
group by memberid
