跳到主要内容

WINDOW_FUNCTION

描述

窗口函数(也称为分析函数)是一类特殊的内置函数,它能在保留原始行的基础上进行计算。与聚合函数不同,窗口函数:

  • 在特定窗口范围内处理数据,而不是按 GROUP BY 分组
  • 为结果集的每一行计算一个值
  • 可以在 SELECT 列表中添加额外的列
  • 在查询处理中最后执行(在 JOIN、WHERE、GROUP BY 之后)

窗口函数常用于金融和科学计算领域,用于分析趋势、计算离群值和数据分桶等场景。

语法

function(<args>) OVER(
[PARTITION BY <expr> [, <expr> ...]]
[ORDER BY <expr> [ASC | DESC] [, <expr> [ASC | DESC] ...]]
[<window_clause>]
)

参数

参数说明
<args>窗口函数的输入参数,具体参数根据所使用的函数而定
<function>支持的函数包括: AVG(), COUNT(), DENSE_RANK(), FIRST_VALUE(), LAG(), LAST_VALUE(), LEAD(), MAX(), MIN(), RANK(), ROW_NUMBER(), SUM()
<partition_by>类似于 GROUP BY,按指定列对数据进行分组
<order_by>定义窗口内数据的排序方式
<window_clause>定义窗口范围,语法为:ROWS BETWEEN [ { m | UNBOUNDED } PRECEDING | CURRENT ROW] [ AND [CURRENT ROW | { UNBOUNDED | n } FOLLOWING] ]

返回值

返回与输入表达式相同的数据类型。

举例

  1. 假设我们有如下的股票数据,股票代码是 JDR,closing price 是每天的收盘价。
create table stock_ticker (stock_symbol string, closing_price decimal(8,2), closing_date timestamp);    
...load some data...
select * from stock_ticker order by stock_symbol, closing_date
 | stock_symbol | closing_price | closing_date        |
|--------------|---------------|---------------------|
| JDR | 12.86 | 2014-10-02 00:00:00 |
| JDR | 12.89 | 2014-10-03 00:00:00 |
| JDR | 12.94 | 2014-10-04 00:00:00 |
| JDR | 12.55 | 2014-10-05 00:00:00 |
| JDR | 14.03 | 2014-10-06 00:00:00 |
| JDR | 14.75 | 2014-10-07 00:00:00 |
| JDR | 13.98 | 2014-10-08 00:00:00 |
  1. 这个查询使用分析函数产生 moving_average 这一列,它的值是3天的股票均价,即前一天、当前以及后一天三天的均价。第一天没有前一天的值,最后一天没有后一天的值,所以这两行只计算了两天的均值。这里 Partition By 没有起到作用,因为所有的数据都是 JDR 的数据,但如果还有其他股票信息,Partition By 会保证分析函数值作用在本 Partition 之内。
select stock_symbol, closing_date, closing_price,    
avg(closing_price) over (partition by stock_symbol order by closing_date
rows between 1 preceding and 1 following) as moving_average
from stock_ticker;
 | stock_symbol | closing_date        | closing_price | moving_average |
|--------------|---------------------|---------------|----------------|
| JDR | 2014-10-02 00:00:00 | 12.86 | 12.87 |
| JDR | 2014-10-03 00:00:00 | 12.89 | 12.89 |
| JDR | 2014-10-04 00:00:00 | 12.94 | 12.79 |
| JDR | 2014-10-05 00:00:00 | 12.55 | 13.17 |
| JDR | 2014-10-06 00:00:00 | 14.03 | 13.77 |
| JDR | 2014-10-07 00:00:00 | 14.75 | 14.25 |
| JDR | 2014-10-08 00:00:00 | 13.98 | 14.36 |