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()

1. SELECT
2.   sales_date,
3.   region,
4.   amount,
5.   ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_by_amount
6. FROM
7.   sales;

复制代码

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

1. SELECT
2.   sales_date,
3.   region,
4.   amount,
5.   RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_by_amount
6. FROM
7.   sales;

复制代码

与ROW_NUMBER()类似，但如果有贩卖额雷同的记录，它们将得到雷同的排名，并且排名之间会留下空位。
DENSE_RANK()

1. SELECT
2.   sales_date,
3.   region,
4.   amount,
5.   DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS dense_rank_by_amount
6. FROM
7.   sales;

复制代码

与RANK()类似，但如果有贩卖额雷同的记录，它们将得到雷同的排名，但排名之间不会留下空位。
NTILE(n)

1. SELECT
2.   sales_date,
3.   region,
4.   amount,
5.   NTILE(4) OVER (PARTITION BY region ORDER BY amount DESC) AS quartile
6. FROM
7.   sales;

复制代码

这个查询将每个地区的贩卖记录按贩卖额降序分列，并将它们分成4个桶（四分位数），每桶包含大约雷同数目的记录。
分布函数

PERCENT_RANK()

1. SELECT
2.   sales_date,
3.   region,
4.   amount,
5.   PERCENT_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS percent_rank_by_amount
6. FROM
7.   sales;

复制代码

盘算每个地区内贩卖额的百分比排名。
CUME_DIST()

1. SELECT
2.   sales_date,
3.   region,
4.   amount,
5.   CUME_DIST() OVER (PARTITION BY region ORDER BY amount DESC) AS cume_dist_by_amount
6. FROM
7.   sales;

复制代码

盘算每个地区内小于等于当前贩卖额的记录占总记录数的比例。
前后函数

LAG(col, n)

1. SELECT
2.   sales_date,
3.   region,
4.   amount,
5.   LAG(amount, 1) OVER (PARTITION BY region ORDER BY sales_date) AS previous_amount
6. FROM
7.   sales;

复制代码

返回当前记录按贩卖日期排序后，前一条记录的贩卖额。
LEAD(col, n)

1. SELECT
2.   sales_date,
3.   region,
4.   amount,
5.   LEAD(amount, 1) OVER (PARTITION BY region ORDER BY sales_date) AS next_amount
6. FROM
7.   sales;

复制代码

返回当前记录按贩卖日期排序后，下一条记录的贩卖额。
头尾函数

FIRST_VALUE()

1. SELECT
2.   sales_date,
3.   region,
4.   amount,
5.   FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY sales_date) AS first_amount
6. FROM
7.   sales;

复制代码

返回每个地区内按贩卖日期排序后的第一条记录的贩卖额。
LAST_VALUE()

1. SELECT
2.   sales_date,
3.   region,
4.   amount,
5.   LAST_VALUE(amount) OVER (PARTITION BY region ORDER BY sales_date) AS last_amount
6. FROM
7.   sales;

复制代码

返回每个地区内按贩卖日期排序后的末了一条记录的贩卖额。
聚合函数+窗口函数

SUM(col) OVER(…)

1. SELECT
2.   sales_date,
3.   region,
4.   amount,
5.   SUM(amount) OVER (PARTITION BY region ORDER BY sales_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales
6. FROM
7.   sales;

复制代码

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。