列转行案列:现在有这样一份数据:
exercise_topn.txt
需求:
求出每种爱好中,年龄最大的两个人(爱好,年龄,姓名)注意思考一个问题:如果某个爱好中的第二大年龄
有多个相同的怎么办?
其中需要注意的是:每一条记录中的爱好有多个值,以"-"分隔
id,姓名,年龄,爱好
id,name,age,favors
1,huangxiaoming,45,a-c-d-f
2,huangzitao,36,b-c-d-e
3,huanglei,41,c-d-e
4,liushishi,22,a-d-e
5,liudehua,39,e-f-d
6,liuyifei,35,a-d-e
思路总结:
1.先把爱好拆分成一条一条的数据,行转列我们可以用explode() + lateral view
2.按照爱好进行分组年龄进行排序取rownum<=2 我们可以用rownum over()
实现
1.select a.id,a.name,a.age, favor_view as favor from exercise_topn a
alter view explode(split(a.favors,“-”)) favor_view as favor;
转换后的数据:
| 1 | huangxiaoming | 45 | a |
| 1 | huangxiaoming | 45 | c |
| 1 | huangxiaoming | 45 | d |
| 1 | huangxiaoming | 45 | f |
| 2 | huangzitao | 36 | b |
| 2 | huangzitao | 36 | c |
| 2 | huangzitao | 36 | d |
| 2 | huangzitao | 36 | e |
| 3 | huanglei | 41 | c |
| 3 | huanglei | 41 | d |
| 3 | huanglei | 41 | e |
SELECT c.id, c.name, c.age, c.favor
FROM (
SELECT b.id, b.name, b.age, b.favor
, row_number() OVER (PARTITION BY b.favor ORDER BY b.age DESC) AS rank
FROM (
SELECT a.id AS id, a.name AS name, a.age AS age, favor_view.favor
FROM exercise_topn a
LATERAL VIEW explode(split(a.favors, '-')) favor_view AS favor
) b
) c
WHERE c.rank <= 2;
RANK()
生成数据项在分组中的排名,排名相等会在名次中留下空位(比如 1 2 2 4)
DENSE_RANK()
生成数据项在分组中的排名,排名相等会在名次中不会留下空位(比如1,2,2,3)
2.行转列案列
所有数学课程成绩 大于 语文课程成绩的学生的学号
数据结构如下:
id,sid,course,score
1,1,yuwen,43
2,1,shuxue,55
3,2,yuwen,77
4,2,shuxue,88
5,3,yuwen,98
6,3,shuxue,65
7,3,yingyu,80
解决方案:
1.行列转换
SELECT sid
, max(CASE course
WHEN 'yuwen' THEN score
ELSE 0
END) AS yuwen
, max(CASE course
WHEN 'shuxue' THEN score
ELSE 0
END) AS shuxue
, max(CASE course
WHEN 'yingyu' THEN score
ELSE 0
END) AS yingyu
FROM exercise_course
GROUP BY sid;
得到的数据结果为:
+------+--------+---------+---------+
| sid | yuwen | shuxue | yingyu |
+------+--------+---------+---------+
| 3 | 98 | 65 | 80 |
| 1 | 43 | 55 | 0 |
| 2 | 77 | 88 | 0 |
+------+--------+---------+---------+
2.加过滤条件
SELECT aa.sid
FROM (
SELECT sid
, max(CASE course
WHEN 'yuwen' THEN score
ELSE 0
END) AS yuwen
, max(CASE course
WHEN 'shuxue' THEN score
ELSE 0
END) AS shuxue
, max(CASE course
WHEN 'yingyu' THEN score
ELSE 0
END) AS yingyu
FROM exercise_course
GROUP BY sid
) aa
WHERE aa.shuxue > aa.yuwen;