Hive-函数-窗口函数

常用窗口函数

Hive中常用的窗口函数（也称为开窗函数）丰富多样，这些函数能够在进行数据分析时提供强大的支持，特别适用于需要对分组数据进行复杂计算和排序的场景。以下是Hive中常用的一些窗口函数及其简要说明：

序号函数

• **ROW_NUMBER()**：为窗口内的每一行生成一个唯一的序号，序号从1开始，按照指定的排序顺序递增。
• **RANK()**：为窗口内的每一行生成一个排名，排名相同的行会获得相同的排名，且排名之间会留下空位。
• **DENSE_RANK()**：与RANK()类似，但排名相同的行会获得相同的排名，且排名之间不会留下空位。
• **NTILE(n)**：将窗口内的行分成n个桶，并为每行分配一个桶号。桶的分配尽量均匀，如果无法均匀分配，则优先分配较小编号的桶。

分布函数

• **PERCENT_RANK()**：返回窗口内当前行的百分比排名。
• **CUME_DIST()**：计算窗口内小于等于当前值的行数占总行数的比例，通常用于计算累积分布。

前后函数

• **LAG(col, n)**：返回窗口内当前行前面第n行的值，如果不存在则返回NULL。
• **LEAD(col, n)**：返回窗口内当前行后面第n行的值，如果不存在则返回NULL。

头尾函数

• **FIRST_VALUE()**：返回窗口内的第一个值。
• **LAST_VALUE()**：返回窗口内的最后一个值。

聚合函数+窗口函数联合

Hive还支持将常用的聚合函数（如SUM、AVG、MAX、MIN、COUNT）与窗口函数结合使用，以实现更复杂的计算。例如：

• **SUM(col) OVER(…)**：计算窗口内指定列的总和。
• **AVG(col) OVER(…)**：计算窗口内指定列的平均值。
• **MAX(col) OVER(…)**：计算窗口内指定列的最大值。
• **MIN(col) OVER(…)**：计算窗口内指定列的最小值。
• **COUNT(col) OVER(…)**：计算窗口内指定列中非NULL值的数量。

注意事项

• 窗口函数必须与OVER()子句一起使用，通过OVER()子句可以指定窗口的分区（PARTITION BY）、排序（ORDER BY）以及窗口的大小和位置（ROWS/RANGE BETWEEN…AND…）。
• 窗口函数在处理数据时，会为窗口内的每一行都执行计算，而不是将多行数据聚合成一行。
• 窗口函数兼具GROUP BY子句的分组功能以及ORDER BY子句的排序功能，但PARTITION BY子句并不具备GROUP BY子句的汇总功能。

使用示例

当然可以，以下是为每个提到的窗口函数提供的Hive SQL示例。请注意，这些示例假设我们有一个名为

sales

的表，其中包含

sales_date

（销售日期）、

region

（地区）和

amount

（销售额）等字段。

序号函数

ROW_NUMBER()

SELECT
  sales_date,
  region,
  amount,
  ROW_NUMBER()OVER(PARTITIONBY region ORDERBY amount DESC)AS rank_by_amount
FROM
  sales;

这个查询将为每个地区（

region

）内的销售记录按销售额（

amount

）降序排列，并为每条记录分配一个唯一的序号（

rank_by_amount

）。

RANK()

SELECT
  sales_date,
  region,
  amount,
  RANK()OVER(PARTITIONBY region ORDERBY amount DESC)AS rank_by_amount
FROM
  sales;

与

ROW_NUMBER()

类似，但如果有销售额相同的记录，它们将获得相同的排名，并且排名之间会留下空位。

DENSE_RANK()

SELECT
  sales_date,
  region,
  amount,
  DENSE_RANK()OVER(PARTITIONBY region ORDERBY amount DESC)AS dense_rank_by_amount
FROM
  sales;

与

RANK()

类似，但如果有销售额相同的记录，它们将获得相同的排名，但排名之间不会留下空位。

NTILE(n)

SELECT
  sales_date,
  region,
  amount,
  NTILE(4)OVER(PARTITIONBY region ORDERBY amount DESC)AS quartile
FROM
  sales;

这个查询将每个地区的销售记录按销售额降序排列，并将它们分成4个桶（四分位数），每桶包含大约相同数量的记录。

分布函数

PERCENT_RANK()

SELECT
  sales_date,
  region,
  amount,
  PERCENT_RANK()OVER(PARTITIONBY region ORDERBY amount DESC)AS percent_rank_by_amount
FROM
  sales;

计算每个地区内销售额的百分比排名。

CUME_DIST()

SELECT
  sales_date,
  region,
  amount,
  CUME_DIST()OVER(PARTITIONBY region ORDERBY amount DESC)AS cume_dist_by_amount
FROM
  sales;

计算每个地区内小于等于当前销售额的记录占总记录数的比例。

前后函数

LAG(col, n)

SELECT
  sales_date,
  region,
  amount,
  LAG(amount,1)OVER(PARTITIONBY region ORDERBY sales_date)AS previous_amount
FROM
  sales;

返回当前记录按销售日期排序后，前一条记录的销售额。

LEAD(col, n)

SELECT
  sales_date,
  region,
  amount,
  LEAD(amount,1)OVER(PARTITIONBY region ORDERBY sales_date)AS next_amount
FROM
  sales;

返回当前记录按销售日期排序后，下一条记录的销售额。

头尾函数

FIRST_VALUE()

SELECT
  sales_date,
  region,
  amount,
  FIRST_VALUE(amount)OVER(PARTITIONBY region ORDERBY sales_date)AS first_amount
FROM
  sales;

返回每个地区内按销售日期排序后的第一条记录的销售额。

LAST_VALUE()

SELECT
  sales_date,
  region,
  amount,
  LAST_VALUE(amount)OVER(PARTITIONBY region ORDERBY sales_date)AS last_amount
FROM
  sales;

返回每个地区内按销售日期排序后的最后一条记录的销售额。

聚合函数+窗口函数

SUM(col) OVER(…)

SELECT
  sales_date,
  region,
  amount,SUM(amount)OVER(PARTITIONBY region ORDERBY sales_date ROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)AS cumulative_sales
FROM
  sales;

计算每个地区内，从分区开始到当前行的累计销售额。