HiveSQL练习题-rank(),dense_rank(),row_number(),lag()开窗计算排名和分差

题目：编写sql语句实现每班前三名，分数一样不并列，同时求出前三名按名次排序的一次的分差：

开始之前hive打开本地模式
set hive.exec.mode.local.auto=true;

建表语句

drop table stu;
create table stu( 
Stu_no int, 
class string, 
score int 
)
row format delimited 
fields terminated by '\t' 
;

数据： stu表

Stu_no class score 
1	1901	90
2	1901	90
3	1901	83
4	1901	60
5	1902	66
6	1902	23
7	1902	99
8	1902	67
9	1902	87

导入本地数据语句并忽略第一行

CREATE TABLE temp_stu (
  Stu_no INT,
  class STRING,
  score INT
)row format delimited 
fields terminated by '\t' 
;

LOAD DATA LOCAL INPATH '/opt/app/hive/stu.txt' INTO TABLE temp_stu;
-- 创建临时表将空数据删除
INSERT INTO TABLE stu
SELECT * FROM temp_stu WHERE stu_no IS NOT NULL;

DROP TABLE temp_stu;

select * from stu;
TRUNCATE TABLE stu; 

ps：这里我写的麻烦了，其实只要打开stu.txt，删掉第一行就ok了

案例需求结果示例

+--------+---------+--------+-----+----------+--+
| class  | stu_no  | score  | rn  | rn_diff  |
+--------+---------+--------+-----+----------+--+
| 1901   | 2       | 90     | 1   | 90       |
| 1901   | 1       | 90     | 2   | 0        |
| 1901   | 3       | 83     | 3   | -7       |
| 1902   | 7       | 99     | 1   | 99       |
| 1902   | 9       | 87     | 2   | -12      |
| 1902   | 8       | 67     | 3   | -20      |
+--------+---------+--------+-----+----------+--+

实现代码及分析过程

– 题目：编写sql语句实现每班前三名，分数一样不并列，同时求出前三名按名次排序的一次的分差：

SELECT   
 	class, stu_no, score, rn,
	score - LAG(score, 1, 0) OVER (PARTITION BY class ORDER BY rn) AS rn_diff
from(	
		SELECT class, stu_no, score, rn
		FROM (
		    SELECT class, stu_no, score, 
		        row_number() OVER (PARTITION BY class ORDER BY score DESC) AS rn
		    FROM stu
		) t1
		WHERE rn <= 3
)t2

实现过程先计算排名，再计算分差。

• rank和row开窗函数
  如果希望在出现相同分数时跳过相同的排名并继续递增，可以使用 dense_rank() 函数代替 rank() 函数。dense_rank() 函数会为相同分数的记录分配连续递增的排名，而不会跳过。
  如果希望在连续递增的排名中避免重复的排名，可以使用 row_number() 函数。row_number() 函数会为每条记录分配唯一递增的排名，并且不会跳过排名。
• lag开窗
  使用了LAG函数来获取上一个排名的分数，然后计算当前排名的分差。对于每个班级内的记录，通过按排名顺序排序，并使用LAG函数来获取前一个排名的分数，然后用当前分数减去前一个分数得到分差。
  在LAG函数中，参数的含义如下：
  第一个参数（score）：指定要获取前一个值的列或表达式。在这种情况下，我们希望获取前一个排名的分数，因此使用的是score列。
  第二个参数（1）：指定要返回的前N个偏移量。在这种情况下，我们只需要返回前一个偏移量，因此使用的是1。
  第三个参数（0）：指定在没有前一个值时要返回的默认值。在这种情况下，我们希望在没有前一个值时返回0。
  所以，LAG(score, 1, 0)表示获取score列的前一个值（前一个排名的分数），如果没有前一个值，则返回0作为默认值。