hive开窗函数边界

### Hive 开窗函数边界处理方式 在 Hive 中，开窗函数的边界可以通过 `ROWS BETWEEN` 和 `RANGE BETWEEN` 来定义窗口范围。这些关键字允许用户根据业务需求精确控制窗口的起始和结束位置。以下是关于边界处理的具体使用方法： #### 1. 关键字含义 - **CURRENT ROW**：表示当前行。 - **UNBOUNDED PRECEDING**：表示从窗口的第一行开始（无上界）。 - **UNBOUNDED FOLLOWING**：表示到窗口的最后一行结束（无下界）。 - **PRECEDING**：向前指定行数。 - **FOLLOWING**：向后指定行数。 - **[num] PRECEDING**：表示当前行之前的 `[num]` 行。 - **[num] FOLLOWING**：表示当前行之后的 `[num]` 行。 #### 2. 语法结构 开窗函数的边界范围可以通过以下语法定义： ```sql (ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING) ``` #### 3. 常见边界设置 - **从第一行到当前行**： ```sql SUM(A) OVER (PARTITION BY B ORDER BY C ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ``` 这种方式适用于需要计算累积值的场景[^4]。 - **从当前行到最后一行**： ```sql SUM(A) OVER (PARTITION BY B ORDER BY C ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) ``` - **固定窗口大小**： - 包括当前行及其前两行： ```sql SUM(A) OVER (PARTITION BY B ORDER BY C ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ``` - 包括当前行及其前后各一行（共三行）： ```sql SUM(A) OVER (PARTITION BY B ORDER BY C ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ``` - **默认窗口范围**： - 当未指定窗口从句时，默认为 `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`[^3]。 - 如果同时缺少 `ORDER BY` 和窗口从句，则默认为 `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`。 #### 4. 示例代码 以下是一个完整的示例，展示如何使用不同的边界条件： ```sql -- 示例数据 CREATE TABLE sales ( user_name STRING, month_id INT, sale_amt DECIMAL(10, 2) ); INSERT INTO sales VALUES ('Alice', 1, 100.0), ('Alice', 2, 200.0), ('Alice', 3, 300.0), ('Bob', 1, 50.0), ('Bob', 2, 75.0), ('Bob', 3, 100.0); -- 查询示例 SELECT user_name, month_id, sale_amt, -- 累积求和（从第一行到当前行） SUM(sale_amt) OVER (PARTITION BY user_name ORDER BY month_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum, -- 固定窗口（当前行及其前两行） AVG(sale_amt) OVER (PARTITION BY user_name ORDER BY month_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_last_3_months FROM sales; ``` #### 5. `ROWS` 和 `RANGE` 的区别 - **ROWS**：基于物理行数进行窗口划分。 - **RANGE**：基于排序字段的值进行窗口划分。如果排序字段存在重复值，则这些行会被视为同一逻辑行[^2]。 例如： ```sql -- 使用 ROWS SUM(A) OVER (PARTITION BY B ORDER BY C ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) -- 使用 RANGE SUM(A) OVER (PARTITION BY B ORDER BY C RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) ``` 在 `RANGE` 模式下，如果 `C` 的值相同，则这些行会被合并为一个逻辑行参与计算。 --- ###