hive的开窗函数

Hive中的开窗函数（Window Functions）

一、引言

Hive作为一种数据仓库工具，在处理大规模数据集时提供了丰富的分析功能。其中，开窗函数（Window Functions）是一种强大的分析工具，允许我们在一组行上执行计算，这组行与当前查询的行在某种程度上相关。它们常用于计算移动平均值、排名等复杂操作。

二、基本概念

1. 窗口：窗口是一组按某种顺序排列的行的集合，这些行与当前行有某种关系（如前后N行）。
2. 分区：在窗口函数中，可以将数据划分为多个分区，每个分区独立进行窗口计算。
3. 排序：窗口内的行通常按照某个列的值进行排序。
4. 框架：定义了窗口内哪些行将被用于计算，例如“前一行到后两行”。

三、常用开窗函数

1. ROW_NUMBER()：为每一行分配一个唯一的序号，从1开始。

   SELECT column1, column2, ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_num FROM table_name;

2. RANK()：类似于ROW_NUMBER()，但如果有相同的值，则它们会获得相同的排名，并且后续排名会跳过。

   SELECT column1, column2, RANK() OVER (PARTITION BY column1 ORDER BY column2 DESC) AS rank FROM table_name;

3. DENSE_RANK()：类似于RANK()，但不会跳过后续排名。

   SELECT column1, column2, DENSE_RANK() OVER (PARTITION BY column1 ORDER BY column2 DESC) AS dense_rank FROM table_name;

4. NTILE(n)：将结果集分成大致相等的n个桶，并为每行分配一个桶号。

   SELECT column1, column2, NTILE(4) OVER (ORDER BY column2) AS quartile FROM table_name;

5. LAG() 和 LEAD()：访问前一行或后一行的值。

   SELECT column1, column2, LAG(column2, 1) OVER (ORDER BY column2) AS prev_value, LEAD(column2, 1) OVER (ORDER BY column2) AS next_value FROM table_name;

6. FIRST_VALUE() 和 LAST_VALUE()：返回窗口中第一行或最后一行的值。

   SELECT column1, column2, FIRST_VALUE(column2) OVER (PARTITION BY column1 ORDER BY column2 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_val, LAST_VALUE(column2) OVER (PARTITION BY column1 ORDER BY column2 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_val FROM table_name;

7. SUM(), AVG(), MIN(), MAX()：作为窗口函数使用时，可以计算窗口内数据的聚合值。

   SELECT column1, column2, SUM(column2) OVER (PARTITION BY column1 ORDER BY column2 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS running_sum FROM table_name;

四、窗口定义子句

• OVER()：指定窗口的定义，包括分区和排序规则。
  • PARTITION BY：将数据分为不同的分区，每个分区独立计算。
  • ORDER BY：指定窗口内行的排序方式。
  • ROWS BETWEEN ... AND ... 或 RANGE BETWEEN ... AND ...：定义窗口框架。

五、示例

假设我们有一个名为sales的表，包含以下字段：id, product, sale_date, amount。我们希望计算每个产品的销售额累计总和。

SELECT product, sale_date, amount, SUM(amount) OVER (PARTITION BY product ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales FROM sales;

在这个例子中，我们使用SUM()作为窗口函数，通过PARTITION BY product将数据按产品分组，并通过ORDER BY sale_date对每组数据进行排序。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示窗口框架是从分区的第一行到当前行。

六、总结

Hive的开窗函数提供了一种灵活而强大的方式来执行复杂的分析和计算任务。通过合理使用这些函数，我们可以轻松实现诸如排名、累计总和等操作，从而更好地洞察数据。