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 (PARTITION BY region ORDER BY amount DESC) AS rank_by_amount
FROM
  sales;

这个查询将为每个地区（region）内的销售记录按销售额（amount）降序排列，并为每条记录分配一个唯一的序号（rank_by_amount）。

RANK()

SELECT
  sales_date,
  region,
  amount,
  RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_by_amount
FROM
  sales;

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

DENSE_RANK()

SELECT
  sales_date,
  region,
  amount,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS dense_rank_by_amount
FROM
  sales;

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

NTILE(n)

SELECT
  sales_date,
  region,
  amount,
  NTILE(4) OVER (PARTITION BY region ORDER BY amount DESC) AS quartile
FROM
  sales;

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

分布函数

PERCENT_RANK()

SELECT
  sales_date,
  region,
  amount,
  PERCENT_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS percent_rank_by_amount
FROM
  sales;

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

CUME_DIST()

SELECT
  sales_date,
  region,
  amount,
  CUME_DIST() OVER (PARTITION BY region ORDER BY amount DESC) AS cume_dist_by_amount
FROM
  sales;

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

前后函数

LAG(col, n)

SELECT
  sales_date,
  region,
  amount,
  LAG(amount, 1) OVER (PARTITION BY region ORDER BY sales_date) AS previous_amount
FROM
  sales;

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

LEAD(col, n)

SELECT
  sales_date,
  region,
  amount,
  LEAD(amount, 1) OVER (PARTITION BY region ORDER BY sales_date) AS next_amount
FROM
  sales;

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

头尾函数

FIRST_VALUE()

SELECT
  sales_date,
  region,
  amount,
  FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY sales_date) AS first_amount
FROM
  sales;

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

LAST_VALUE()

SELECT
  sales_date,
  region,
  amount,
  LAST_VALUE(amount) OVER (PARTITION BY region ORDER BY sales_date) AS last_amount
FROM
  sales;

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

聚合函数+窗口函数

SUM(col) OVER(…)

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

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