SQL 开窗函数简介

原创
04/08 16:58
阅读数 587

SQL 开窗函数简介

原文地址 | 译文地址

窗口函数对一组行进行操作,并为基础查询中的每一行返回一个值。 术语窗口描述了函数在其上进行操作的行集。 窗口函数使用窗口中各行的值来计算返回的值。 在查询中使用窗口函数时,请使用OVER()子句定义窗口。 OVER()子句(窗口定义)将窗口函数与其他分析和报告函数区分开。 一个查询可以包含具有相同或不同窗口定义的多个窗口函数。 OVER()子句具有以下功能:

  • 定义窗口分区以形成行组。 (PARTITION BY子句)
  • 对分区内的行进行排序。 (ORDER BY子句) 例如,以下查询使用AVG()窗口函数来计算第一季度员工的平均销售额:
  select emp_name, dealer_id, sales, avg(sales) over() as avgsales from q1_sales;
   +-----------------+------------+--------+-----------+
   |    emp_name     | dealer_id  | sales  | avgsales  |
   +-----------------+------------+--------+-----------+
   | Beverly Lang    | 2          | 16233  | 13631     |
   | Kameko French   | 2          | 16233  | 13631     |
   | Ursa George     | 3          | 15427  | 13631     |
   | Ferris Brown    | 1          | 19745  | 13631     |
   | Noel Meyer      | 1          | 19745  | 13631     |
   | Abel Kim        | 3          | 12369  | 13631     |
   | Raphael Hull    | 1          | 8227   | 13631     |
   | Jack Salazar    | 1          | 9710   | 13631     |
   | May Stout       | 3          | 9308   | 13631     |
   | Haviva Montoya  | 2          | 9308   | 13631     |
   +-----------------+------------+--------+-----------+
   10 rows selected (0.213 seconds)

AVG()窗口函数对窗口中定义的行进行操作,并为每行返回一个值。 为了进行比较,您可以使用AVG()函数作为标准集合函数来运行查询:

   select avg(sales) as avgsales from q1_sales;
   +-----------+
   | avgsales  |
   +-----------+
   | 13630.5   |
   +-----------+
   1 row selected (0.131 seconds)

查询将返回指定列中所有值的平均值的一行,而不是返回每一行的值。 您还可以在查询中包括可选的PARTITION BY和ORDER BY子句。 PARTITION BY子句将窗口细分为多个分区。 ORDER BY子句定义结果集每个分区内的行的逻辑顺序。 窗口函数将应用于每个分区内的行,并根据顺序规范进行排序。 以下查询将AVG()窗口函数与PARTITION BY子句一起使用,以确定第一季度每个经销商的平均汽车销售量:

   select emp_name, dealer_id, sales, avg(sales) over (partition by dealer_id) as avgsales from q1_sales;
   +-----------------+------------+--------+-----------+
   |    emp_name     | dealer_id  | sales  | avgsales  |
   +-----------------+------------+--------+-----------+
   | Ferris Brown    | 1          | 19745  | 14357     |
   | Noel Meyer      | 1          | 19745  | 14357     |
   | Raphael Hull    | 1          | 8227   | 14357     |
   | Jack Salazar    | 1          | 9710   | 14357     |
   | Beverly Lang    | 2          | 16233  | 13925     |
   | Kameko French   | 2          | 16233  | 13925     |
   | Haviva Montoya  | 2          | 9308   | 13925     |
   | Ursa George     | 3          | 15427  | 12368     |
   | Abel Kim        | 3          | 12369  | 12368     |
   | May Stout       | 3          | 9308   | 12368     |
   +-----------------+------------+--------+-----------+
   10 rows selected (0.215 seconds)  

以下查询使用AVERAGE()和ROWNUM()窗口函数确定第一季度每个经销商的平均汽车销量,并为分区中的每一行分配一个行号:

   select dealer_id, sales, emp_name,row_number() over (partition by dealer_id order by sales) as `row`,avg(sales) over (partition by dealer_id) as avgsales from q1_sales;
   +------------+--------+-----------------+------+---------------+
   | dealer_id  | sales  |    emp_name     | row  |      avgsales |
   +------------+--------+-----------------+------+---------------+
   | 1          | 8227   | Raphael Hull    | 1    | 14356         |
   | 1          | 9710   | Jack Salazar    | 2    | 14356         |
   | 1          | 19745  | Ferris Brown    | 3    | 14356         |
   | 1          | 19745  | Noel Meyer      | 4    | 14356         |
   | 2          | 9308   | Haviva Montoya  | 1    | 13924         |
   | 2          | 16233  | Beverly Lang    | 2    | 13924         |
   | 2          | 16233  | Kameko French   | 3    | 13924         |
   | 3          | 9308   | May Stout       | 1    | 12368         |
   | 3          | 12369  | Abel Kim        | 2    | 12368         |
   | 3          | 15427  | Ursa George     | 3    | 12368         |
   +------------+--------+-----------------+------+---------------+
   10 rows selected (0.37 seconds)  

窗口函数的类型

当前,Drill支持以下值,聚合和排名窗口功能:

求值

  • FIRST_VALUE()
  • LAG()
  • LAST_VALUE()
  • LEAD()

聚合

  • AVG()
  • COUNT()
  • MAX()
  • MIN()
  • SUM()

排行

  • CUME_DIST()
  • DENSE_RANK()
  • NTILE()
  • PERCENT_RANK()
  • RANK()
  • ROW_NUMBER()

语法

window_function (expression) OVER (
[ PARTITION BY expr_list ]
[ ORDER BY order_list ][ frame_clause ] )  

其中function是所描述的函数之一,例如AVG(),而expr_list为:

expression | column_name [, expr_list ]

order_list是:

expression | column_name [ASC | DESC] [ NULLS { FIRST | LAST } ] [, order_list ]

可选的frame_clause是以下frame之一:

{ RANGE | ROWS } frame_start
{ RANGE | ROWS } BETWEEN frame_start AND frame_end  

其中frame_start是以下选择之一:

UNBOUNDED PRECEDING  
CURRENT ROW  

而frame_end是以下选择之一:

   CURRENT ROW  
   UNBOUNDED FOLLOWING  

提示: “ frame_end”选项不能早于“ frame_start”选项出现,并且如果未明确包含,则默认为“ CURRENT ROW”。

论据(Arguments)

window_function 与OVER子句一起使用的以下任何函数可提供窗口规范:

  • AVG()
  • COUNT()
  • CUME_DIST()
  • DENSE_RANK()
  • FIRST_VALUE()
  • LAG()
  • LAST_VALUE()
  • LEAD()
  • MAX()
  • MIN()
  • NTILE()
  • PERCENT_RANK()
  • RANK()
  • ROW_NUMBER()
  • SUM()

OVER()
OVER()是必填子句,用于定义查询结果集中的窗口。 OVER()是SELECT的子集,也是聚合定义的一部分。 窗口函数为窗口中的每一行计算一个值。

PARTITION BY expr_list
PARTITION BY是一个可选子句,可将数据细分为多个分区。 包含partition子句将查询结果集划分为多个分区,并且window函数分别应用于每个分区。 重新开始每个分区的计算。 如果不包括partition子句,则该函数将对整个表或文件进行计算。

ORDER BY order_list
ORDER BY子句定义结果集每个分区内的行的逻辑顺序。 如果未指定PARTITION BY,则ORDER BY使用整个表。 ORDER BY对于聚合窗口函数是可选的,对于排名函数是必需的。 该ORDER BY子句与OVER子句之外使用的ORDER BY子句无关。 窗口函数将应用于根据排序列表规范排序的每个分区内的行。 排序列表中需要列标识符或计算结果为列标识符的表达式。 您也可以使用常量代替列名。 NULL被视为自己的组,在ASC中排在最后,在DESC中排在第一。 ASC是默认的排序顺序。

column_name
要被分区或排序的列的名称。 ASC | DESC指定升序或降序的排序顺序。

frame_clause
对于在frame而不是整个分区上运行的窗口函数,frame_clause指定创建窗口框架的行组。 frame_clause支持以下框架:

  • RANGE UNBOUNDED PRECEDING
  • RANGE BETWEEN CURRENT ROW AND CURRENT ROW
  • [RANGE | ROWS] BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • [RANGE | ROWS] BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 默认frame是“RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,”,与“RANGE UNBOUNDED PRECEDING.”相同。 该frame将frame设置为从分区的开始到当前行的最后一个对等点的所有行,这是由ORDER BY子句指定的。 排序不唯一时,frame还包括扎带。

以下定界符定义frame:

  • UNBOUNDED PRECEDING frame从分区的第一行开始。
  • UNBOUNDED FOLLOWING 对于ROW和RANGE模式,该frame都以分区的最后一行结尾。
  • CURRENT ROW 在ROWS模式下,CURRENT ROW表示该帧从当前行开始或结束。 在RANGE模式下,CURRENT ROW表示帧以ORDER BY顺序中当前行的第一个或最后一个对等点开始或结束。

使用说明

  • 您只能在查询的SELECT列表和ORDER BY子句中使用窗口函数。
  • 窗口函数在ORDER BY之前。
  • 在WHERE,GROUP BY和HAVING子句之后进行钻取处理窗口函数。
  • 在聚合集函数之后包含OVER()子句会将函数转换为聚合窗口函数。
  • 您可以使用窗口函数来聚合窗口框架中任意数量的行。
  • 如果要在FLATTEN子句返回的结果集上运行窗口函数,请在子查询中使用FLATTEN。 例如:
select x, y, a, sum(x) over() from  ( select x , y, flatten(z) as a from `complex.json`);
展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部