数据长这样
张三,大唐双龙传
李四,天下无贼
张三,神探狄仁杰
李四,霸王别姬
李四,霸王别姬
王五,机器人总动员
王五,放牛班的春天
王五,盗梦空间
多行转一行
使用cancat_ws(等同wm_concat )、collect_list、collect_set、sort_array
这些函数分别是哪个数据库的,分别有什么区别稍后补上。
-- group_concat
-- 使用group_concat,mysql
SELECT locus, GROUP_CONCAT(id) FROM info
WHERE locus IN('AB086827','AF040764') GROUP BY locus
-- wm_concat
-- 相对应的是Oracle独有的wm_concat,一般搭配group by 使用。阿里中台亦可用
-- concat_ws
-- 使用给定的分隔符将多个输入字符串列连接到一个字符串列中
-- 这个方法在阿里中台亲测可用
-- 只不过注意ent_name, id1 两个都必须出现在group by之后
select ent_name, id1, concat_ws(',',collect_list(cons_no)) as cons_no
from power_data_day
GROUP BY ent_name, id1;
-- concat_ws的另一个用法是将hive的list或者array数据拼成string类型。hive中直接用cast转换list类型数据会报错。concat_ws(";",array_data) as
-- collect_list
---使用逗号分隔,但是有中括号[]
select username, collect_list(video_name)
from t_visit_video
group by username ;
-- collect_set功能:聚合函数,返回去除重复元素后的集合
select username, id, collect_set(video_name)
from t_visit_video
where username is not null
group by username , id ;
-- sort_array功能:集合函数,对输入数组元素按升序或降序排序。空元素将按升序放在返回数组的开头,或按降序放在返回数组的末尾。
collect_set函数的搭配使用见下
https://blog.csdn.net/weixin_43597208/article/details/117450304
多行转一列
数据如图:
pref_name, sex, population
AA, 1, 100
AA, 2, 104
变成下面:
pref_name, M, F
AA, 100, 104
详细数据减附图1
select
pref_name,
sum( case when sex='1' then population else 0 end ) as cnt_M,
sum( case when sex='0' then population else 0 end ) as cnt_F
from table1
group by pref_name
多列转一列
table_a和table_b,在下面的代码中其实是同一张表的不同筛选条件而已,甚至可以是同一张表。如此即可实现同一张表的不同的列,转成一列
select col1, col3 from table_a
union all
select col1, col2 from table_b
一行拆成多行
-- trans_array
-- 阿里中台
-- explode
-- 对数据格式有要求explode() takes an array or a map as a parameter
select explode(subs) as sub from employees ;
-- 如果是逗号分割的字符串等,使用下面代码
select app from specter.userapp_list
lateral view explode(split(applist,',')) r1 AS app;
一行拆成多列
M列转换为N行
stack函数
附录
附图1