Hive的窗口函数及其操作

Hive支持窗口函数（Window Functions），这些函数允许用户执行在一组相关记录上的计算，这组记录被称为窗口。窗口函数是OLAP（联机分析处理）类型查询的重要组成部分，因为它们允许用户对数据的子集（即窗口）执行聚合计算，而不是在整个结果集上。

Hive中的窗口函数通常与OVER()子句一起使用，以定义窗口的范围和如何对数据进行分区。以下是一些Hive支持的窗口函数及其操作：

1. ROW_NUMBER()

ROW_NUMBER()函数为结果集的每一行分配一个唯一的序号。序号是根据OVER()子句中定义的排序顺序生成的。

示例：

SELECT product_id, sales_amount, ROW_NUMBER() OVER (ORDER BY sales_amount DESC) as rank 
FROM sales;

2. RANK() 和 DENSE_RANK()

RANK()和DENSE_RANK()函数为结果集的每一行分配一个排名。与ROW_NUMBER()不同，这两个函数在遇到相同的值时会有不同的处理方式。

• RANK()：当遇到相同的值时，会跳过下一个序号。
• DENSE_RANK()：当遇到相同的值时，不会跳过下一个序号。

示例：

SELECT product_id, sales_amount, RANK() OVER (ORDER BY sales_amount DESC) as rank 
FROM sales; 


SELECT product_id, sales_amount, DENSE_RANK() OVER (ORDER BY sales_amount DESC) as dense_rank 
FROM sales;

3. LEAD() 和 LAG()

LEAD()和LAG()函数用于访问结果集中的前一行或后一行的值。它们经常用于比较或计算连续行之间的差异。

• LEAD(column_name, offset, default)：返回指定偏移量的后续行的列值。
• LAG(column_name, offset, default)：返回指定偏移量的前一行的列值。

示例：

	SELECT product_id, sales_amount, 

	LAG(sales_amount, 1, 0) OVER (ORDER BY sales_date) as prev_sales_amount, 

	LEAD(sales_amount, 1, 0) OVER (ORDER BY sales_date) as next_sales_amount 

	FROM sales;

4. SUM(), AVG(), MIN(), MAX() 等聚合函数的窗口版本

Hive还支持聚合函数的窗口版本，这些函数可以在窗口范围内计算聚合值。

示例：

SELECT product_id, sales_date, sales_amount, 
SUM(sales_amount) OVER (PARTITION BY product_id ORDER BY sales_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_sales 
FROM sales;

在这个示例中，SUM()函数计算了每个产品从最早日期到当前日期的累积销售额。

5. PARTITION BY 和 ORDER BY

在OVER()子句中，PARTITION BY用于定义窗口的分区，而ORDER BY用于定义窗口内行的顺序。PARTITION BY允许你为每个分区独立地计算窗口函数。

6. 窗口帧规范（Window Frame Specification）

窗口帧规范定义了窗口的起始和结束边界。例如，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW定义了一个从分区第一行到当前行的滑动窗口。其他可能的值包括UNBOUNDED FOLLOWING、CURRENT ROW、特定的行数偏移量等。

7. NTILE()

NTILE()函数将有序分区中的行分发到指定数量的近似相等的组中，并为每一行分配一个组号。

示例：

SELECT product_id, sales_amount, NTILE(4) OVER (ORDER BY sales_amount DESC) as quartile 
FROM sales;

在这个示例中，NTILE(4)将产品按照销售额降序分为四个组（四分位数）。