题目:编写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作为默认值。