开始之前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_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
实现过程先计算排名,再计算分差。