hive开窗函数大汇总-案例|代码+结果

---

1. ROW_NUMBER(): 为每一行分配一个唯一的整数值，根据指定的排序顺序进行排序。

2. RANK(): 计算每一行在排序结果中的排名，如果存在相同的值，则会跳过相应的排名。

3. DENSE_RANK(): 计算每一行在排序结果中的排名，如果存在相同的值，则不会跳过相应的排名。

4. NTILE(n): 将排序结果划分为n个近似相等的桶，并为每个行分配一个桶编号。

5. LAG(col, offset, default): 返回当前行之前指定偏移量的行的值。可以指定默认值作为偏移量超出范围时的返回值。

6. LEAD(col, offset, default): 返回当前行之后指定偏移量的行的值。可以指定默认值作为偏移量超出范围时的返回值。

7. FIRST_VALUE(col): 返回分组内的第一个行的值。

8. LAST_VALUE(col): 返回分组内的最后一个行的值。

9. AVG(col) OVER (partition by expr): 计算分组内每个行的平均值。

10. SUM(col) OVER (partition by expr): 计算分组内每个行的总和。

1.ROW_NUMBER()

为每一行分配一个唯一的整数值，根据指定的排序顺序进行排序。

查询示例：

SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_number
FROM students;

结果示例：

|   name   | score | row_number |
|----------|-------|------------|
|   Bob    |  92   |     1      |
|  David   |  92   |     2      |
|   Eve    |  88   |     3      |
|  Alice   |  85   |     4      |
| Charlie  |  78   |     5      |

2.RANK()

• 计算每一行在排序结果中的排名，如果存在相同的值，则会跳过相应的排名。

查询示例：

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

结果示例：

|   name   | score | rank |
|----------|-------|------|
|   Bob    |  92   |  1   |
|  David   |  92   |  1   |
|   Eve    |  88   |  3   |
|  Alice   |  85   |  4   |
| Charlie  |  78   |  5   |

3.DENSE_RANK()

• 计算每一行在排序结果中的排名，如果存在相同的值，则不会跳过相应的排名。

查询示例：

SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

结果示例：

|   name   | score | dense_rank |
|----------|-------|------------|
|   Bob    |  92   |     1      |
|  David   |  92   |     1      |
|   Eve    |  88   |     2      |
|  Alice   |  85   |     3      |
| Charlie  |  78   |     4      |

4.NTILE(n)

• 将排序结果划分为n个近似相等的桶，并为每个行分配一个桶编号。

查询示例：

SELECT name, score, NTILE(4) OVER (ORDER BY score DESC) AS bucket
FROM students;

结果示例：

|   name   | score | bucket |
|----------|-------|--------|
|   Bob    |  92   |   1    |
|  David   |  92   |   1    |
|   Eve    |  88   |   2    |
|  Alice   |  85   |   3    |
| Charlie  |  78   |   4    |

5.LAG(col, offset, default)

• 返回当前行之前指定偏移量的行的值。可以指定默认值作为偏移量超出范围时的返回值。

查询示例：

SELECT name, score, LAG(score, 1, 0) OVER (ORDER BY name) AS previous_score
FROM students;

结果示例：

|   name   | score | previous_score |
|----------|-------|----------------|
|  Alice   |  85   |       0        |
|   Bob    |  92   |       85       |
| Charlie  |  78   |       92       |
|  David   |  92   |       78       |
|   Eve    |  88   |       92       |

在上述查询中，LAG(score, 1, 0) OVER (ORDER BY name)函数将返回每个学生的上一个学生的得分。如果没有上一个学生，则返回默认值0。

6.LEAD(col, offset, default)

• 返回当前行之后指定偏移量的行的值。可以指定默认值作为偏移量超出范围时的返回值。

查询：

SELECT name, score, LEAD(score, 1, 0) OVER (ORDER BY score DESC) AS next_score
FROM students;

结果：

|   name   | score | next_score |
|----------|-------|------------|
|   Bob    |  92   |     92     |
|  David   |  92   |     88     |
|   Eve    |  88   |     85     |
|  Alice   |  85   |     78     |
| Charlie  |  78   |     0      |

在上述查询中，LEAD(score, 1, 0)函数将返回每个学生的下一个学生的得分。如果没有下一个学生，则返回默认值0。

7.FIRST_VALUE(col)

• 返回分组内的第一个行的值。

查询：

SELECT name, score, FIRST_VALUE(score) OVER (PARTITION BY class_id ORDER BY score DESC) AS highest_score
FROM students;

结果：

|   name   | score | highest_score |
|----------|-------|---------------|
|  Alice   |  85   |      92       |
|   Bob    |  92   |      92       |
| Charlie  |  78   |      88       |
|  David   |  92   |      92       |
|   Eve    |  88   |      92       |

在上述查询中，FIRST_VALUE(score) OVER (PARTITION BY class_id ORDER BY score DESC)函数将返回每个班级中得分最高的学生的得分。

8.LAST_VALUE(col)

• 返回分组内的最后一个行的值。

查询：

SELECT name, score, LAST_VALUE(score) OVER (PARTITION BY class_id ORDER BY score ASC) AS lowest_score
FROM students;

结果：

|   name   | score | lowest_score |
|----------|-------|--------------|
|  Alice   |  85   |      78      |
|   Bob    |  92   |      78      |
| Charlie  |  78   |      78      |
|  David   |  92   |      78      |
|   Eve    |  88   |      78      |

在上述查询中，LAST_VALUE(score) OVER (PARTITION BY class_id ORDER BY score ASC)函数将返回每个班级中得分最低的学生的得分。

9.AVG(col) OVER (PARTITION BY expr)

• 计算分组内每个行的平均值。

查询示例：

SELECT name, score, AVG(score) OVER (PARTITION BY class_id) AS avg_score
FROM students;

结果示例：

|   name   | score | avg_score |
|----------|-------|-----------|
|  Alice   |  85   |   87.5    |
|   Bob    |  92   |   87.5    |
| Charlie  |  78   |   83.0    |
|  David   |  92   |   83.0    |
|   Eve    |  88   |   83.0    |

在上述查询中，AVG(score) OVER (PARTITION BY class_id)函数将计算每个班级内学生成绩的平均值。

10.SUM(col) OVER (PARTITION BY expr)

• 计算分组内每个行的总和。

查询示例：

SELECT name, score, SUM(score) OVER (PARTITION BY class_id) AS total_score
FROM students;

结果示例：

|   name   | score | total_score |
|----------|-------|-------------|
|  Alice   |  85   |    348      |
|   Bob    |  92   |    348      |
| Charlie  |  78   |    249      |
|  David   |  92   |    249      |
|   Eve    |  88   |    249      |

在上述查询中，SUM(score) OVER (PARTITION BY class_id)函数将计算每个班级内学生成绩的总和。