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