Hive中窗口函数的使用示例

Hive中窗口函数的使用示例，包括 ROW_NUMBER()、 RANK() 和 DENSE_RANK()

1. 创建包含学生成绩的表，并插入学生数据

CREATE TABLE student_scores
(
    id         INT,
    name       STRING,
    score      INT,
    group_name STRING
);
INSERT OVERWRITE TABLE student_scores
VALUES (1, 'Jack', 96, 'A组'),
       (2, 'John', 90, 'A组'),
       (5, 'Lucy', 97, 'A组'),
       (4, 'Caocao', 96, 'B组'),
       (3, 'Lvbu', 99, 'B组');

---

2. 使用ROW_NUMBER()窗口函数为学生分数按降序排名，并为每行分配唯一的行号

SELECT id, name, score, group_name, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM student_scores;

执行结果：
| id  | name    | score | group_name | row_num |
|-----|---------|-------|------------|---------|
| 3   | Lvbu    | 99    | B组       | 1       |
| 5   | Lucy    | 97    | A组       | 2       |
| 1   | Jack    | 96    | A组       | 3       |
| 4   | Caocao  | 96    | B组       | 4       |
| 2   | John    | 90    | A组       | 5       |

3. 使用RANK()窗口函数为学生分数按降序排名，并为相同分数的行分配相同的排名，但会跳过下一个排名

SELECT id, name, score, group_name, RANK() OVER (ORDER BY score DESC) AS rank_num
FROM student_scores;

执行结果：
| id  | name    | score | group_name | rank_num |
|-----|---------|-------|------------|----------|
| 3   | Lvbu    | 99    | B组       | 1        |
| 5   | Lucy    | 97    | A组       | 2        |
| 1   | Jack    | 96    | A组       | 3        |
| 4   | Caocao  | 96    | B组       | 3        |
| 2   | John    | 90    | A组       | 5        |

4. 使用DENSE_RANK()窗口函数为学生分数按降序排名，并为相同分数的行分配相同的排名，但不会跳过下一个排名

SELECT id, name, score, group_name, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_num
FROM student_scores;

执行结果：
| id  | name    | score | group_name | dense_rank_num |
|-----|---------|-------|------------|----------------|
| 3   | Lvbu    | 99    | B组       | 1              |
| 5   | Lucy    | 97    | A组       | 2              |
| 1   | Jack    | 96    | A组       | 3              |
| 4   | Caocao  | 96    | B组       | 3              |
| 2   | John    | 90    | A组       | 4              |

总结：以上代码实现了对学生成绩表进行分数排序并为每个学生分配相应的排名或行号。

• ROW_NUMBER() 函数为每行分配唯一的连续序号；
• RANK() 函数为相同分数的行分配相同的排名，但会跳过下一个排名；
• DENSE_RANK() 函数为相同分数的行分配相同的排名，但不会跳过下一个排名。