hive经典案列--top N（行转列＼列转行）

列转行案列：现在有这样一份数据： 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() 生成数据项在分组中的排名，排名相等会在名次中留下空位（比如　１　２　２　４）

DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位（比如１，２，２，３）

２.行转列案列

所有数学课程成绩 大于 语文课程成绩的学生的学号

数据结构如下：

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;