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

2024-07-25 15:48:19 浏览数 (1)

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

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

建表语句
代码语言:javascript复制
drop table stu;
create table stu( 
Stu_no int, 
class string, 
score int 
)
row format delimited 
fields terminated by 't' 
;
数据: stu表
代码语言:javascript复制
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
导入本地数据语句并忽略第一行
代码语言:javascript复制
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了

案例需求结果示例
代码语言:javascript复制
 -------- --------- -------- ----- ---------- -- 
| 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语句实现每班前三名,分数一样不并列,同时求出前三名按名次排序的一次的分差:

代码语言:javascript复制
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作为默认值。

0 人点赞