Hive窗口边界ROWS BETWEEN 和 RANGE BETWEEN使用示例

Hive窗口边界ROWS BETWEEN 和 RANGE BETWEEN使用示例

在Hive中，窗口函数的 ROWS BETWEEN 和 RANGE BETWEEN 用于定义窗口的边界，确定窗口函数操作的行范围或值范围。以下是对这两种窗口边界的具体应用示例：

1. 创建名为student_scores的表，包括id（学生ID）、name（姓名）、score（分数）、group_name（组名）字段。

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. 查询student_scores表中的学生ID、姓名、分数、组名，并使用窗口函数计算每个组内每个学生的平均分数，包括该学生和后一名学生的成绩。

SELECT id,
       name,
       score,
       group_name,
       AVG(score)
           OVER (PARTITION BY group_name ORDER BY score DESC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS avg_score
FROM student_scores;

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

3. 查询student_scores表中的学生ID、姓名、分数、组名，并使用窗口函数计算每个组内每个学生的平均分数，包括该学生和组内所有成绩比他低的学生的成绩。

SELECT id,
       name,
       score,
       group_name,
       AVG(score)
           OVER (PARTITION BY group_name ORDER BY score DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS avg_score
FROM student_scores;

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

---

以下是对这两种窗口边界的详细解释：

1. ROWS BETWEEN：
   • ROWS BETWEEN <start> AND <end> 语法以行数为单位指定窗口的范围。
   • <start> 和 <end> 可以是以下类型之一：
   • UNBOUNDED PRECEDING ：表示窗口的起始边界是窗口中第一行之前的无限行。
   • CURRENT ROW ：表示当前行。
   • <n> PRECEDING ：表示当前行之前的n行。
   • <n> FOLLOWING ：表示当前行之后的n行。
   • 通过 ROWS BETWEEN 可以定义窗口函数操作的具体行范围，例如计算当前行与前后相邻行的平均值。
2. RANGE BETWEEN：
   • RANGE BETWEEN <start> AND <end> 语法以值范围为单位指定窗口的范围。
   • <start> 和 <end> 可以是以下类型之一：
   • UNBOUNDED PRECEDING ：表示窗口的起始边界是窗口中最小值之前的无限范围。
   • CURRENT ROW ：表示当前行。
   • <value> PRECEDING ：表示当前行值之前的指定值范围。
   • <value> FOLLOWING ：表示当前行值之后的指定值范围。
   • 通过 RANGE BETWEEN 可以定义窗口函数操作的值范围，例如计算当前行与最小值到当前行的值范围内的平均值。

总而言之， ROWS BETWEEN 和 RANGE BETWEEN 提供了不同的窗口边界定义方式， ROWS BETWEEN 基于行数，而 RANGE BETWEEN 基于值范围。根据具体需求和数据分析目的，选择合适的窗口边界定义方式来执行窗口函数操作。