窗口函数

更新时间：2025-08-21

窗口函数

窗口函数是一类特殊的内置函数。和聚合函数类似，窗口函数也是对于多个输入行做计算得到一个数据值。不同的是，窗口函数是在一个特定的窗口内对输入数据做处理，而不是按照 group by 来分组计算。并且窗口函数的输入和输出行是一对一的关系，而不是多对一的关系。每个窗口内的数据可以用 over() 从句进行排序和分组。窗口函数会对结果集的每一行计算出一个单独的值，而不是每个 group by 分组计算一个值。这种灵活的方式允许用户在 select 从句中增加额外的列，给用户提供了更多的机会来对结果集进行重新组织和过滤。

窗口函数只能出现在 select 列表以及最外层的 order by 从句中。在查询过程中，窗口函数会在最后生效，就是说，在执行完join，where 和 group by 等操作之后再执行。窗口函数在金融和科学计算领域经常被使用到，用来分析趋势、计算离群值以及对大量数据进行分桶分析等。

窗口函数语法

SQL

1function(args) OVER(partition_by_clause order_by_clause [window_clause])

partition_by_clause:

Plain Text

1PARTITION BY expr [, expr ...]

order_by_clause:

Plain Text

1ORDER BY expr [ASC | DESC] [, expr [ASC | DESC] ...]

window_clause:

Plain Text

1ROWS BETWEEN [ { m | UNBOUNDED } PRECEDING | CURRENT ROW] [ AND [CURRENT ROW | { UNBOUNDED | n } FOLLOWING] ]
2RANGE BETWEEN [ {m | UNBOUNDED } PRECEDING | CURRENT ROW] [ AND [CURRENT ROW | { UNBOUNDED | n } FOLLOWING] ]

function

具体的窗口函数。目前支持的 Function 包括：
- AVG()
- COUNT()
- DENSE_RANK()
- FIRST_VALUE()
- LAG()
- LAST_VALUE()
- LEAD()
- MAX()
- MIN()
- RANK()
- ROW_NUMBER()
- SUM()
partition_by_clause

Partition By 从句和 Group By 类似，它把输入行按照指定的一列或多列分组，相同值的行会被分到一组。

每一组可以理解为一个窗口。如果不指定 Partition By 从句，则整个数据集为一组，即一个窗口。
order_by_clause

Order By 从句用于指定每一个分组内的数据排列顺序。数据集先按 Partition By 子句分组，每组内再排序，排序后按 Function 对每行进行计算。

与外层 Order By 的不同点是，OVER从句中的 Order By n（n是正整数） 相当于不做任何操作，而外层的 Order By n 表示按照第n列排序。

举例说明：
SQL
```
1SELECT   
2row_number() OVER (PARTITION BY class_id ORDER BY eventdate) AS id,   
3c1, c2, c3, c4   
4FROM events;
```
这个SQL会将 events 表中的数据行按 class_id 划分成多组后，每组内的数据按 eventdate 列排序。对排序后的每组内的数据，添加行号（1、2、3、4、...）。

window_clause

Window从句用来为窗口函数指定一个运算范围，以当前行为准，前后若干行作为窗口函数运算的对象。

Window从句支持的方法有：

AVG()
COUNT()
FIRST_VALUE()
LAST_VALUE()
SUM()
MAX()
MIN()

对于 MAX() 和 MIN(), window从句可以指定开始范围 UNBOUNDED PRECEDING

假设我们有如下的股票数据，股票代码是JDR，closing price是每天的收盘价。

SQL

1> create table stock_ticker (stock_symbol string, closing_price decimal(8,2), closing_date timestamp);
2 
3> ...load some data... 
4   
5> select * from stock_ticker order by stock_symbol, closing_date
6
7		 | stock_symbol | closing_price | closing_date        |
8		 |--------------|---------------|---------------------|
9		 | JDR          | 12.86         | 2014-10-02 00:00:00 |
10		 | JDR          | 12.89         | 2014-10-03 00:00:00 |
11		 | JDR          | 12.94         | 2014-10-04 00:00:00 |
12		 | JDR          | 12.55         | 2014-10-05 00:00:00 |
13		 | JDR          | 14.03         | 2014-10-06 00:00:00 |
14		 | JDR          | 14.75         | 2014-10-07 00:00:00 |
15		 | JDR          | 13.98         | 2014-10-08 00:00:00 |
16		 ```
17
18以下查询使用窗口函数产生 `moving_average` 这一列，它的值是3天的股票均价，即前一天、当前以及后一天三天的均价。
19
20第一天没有前一天的值，最后一天没有后一天的值，所以这两行只计算了两天的均值。
21
22这里 Partition By 没有起到作用，因为所有的数据都是JDR的数据，但如果还有其他股票信息，Partition By 会保证窗口函数值作用在本Partition之内。
23
24```sql
25> select stock_symbol, closing_date, closing_price,    
26  avg(closing_price) over (partition by stock_symbol order by closing_date    
27  rows between 1 preceding and 1 following) as moving_average    
28  from stock_ticker;
29
30		 | stock_symbol | closing_date        | closing_price | moving_average |
31		 |--------------|---------------------|---------------|----------------|
32		 | JDR          | 2014-10-02 00:00:00 | 12.86         | 12.87          |
33		 | JDR          | 2014-10-03 00:00:00 | 12.89         | 12.89          |
34		 | JDR          | 2014-10-04 00:00:00 | 12.94         | 12.79          |
35		 | JDR          | 2014-10-05 00:00:00 | 12.55         | 13.17          |
36		 | JDR          | 2014-10-06 00:00:00 | 14.03         | 13.77          |
37		 | JDR          | 2014-10-07 00:00:00 | 14.75         | 14.25          |
38		 | JDR          | 2014-10-08 00:00:00 | 13.98         | 14.36          |
39		 ```

Function 介绍

本节介绍PALO中可以用作窗口函数的方法。

AVG()

计算平均值。

语法：

SQL

1AVG([DISTINCT | ALL] *expression*) [OVER (*analytic_clause*)]

举例：

计算当前行和它前后各一行数据的x平均值

SQL

1> select x, property,    
2  avg(x) over
3  (partition by property order by x rows between 1 preceding and 1 following) as 'moving average'    
4  from int_t where property in ('odd','even');
5  
6 | x  | property | moving average |
7 |----|----------|----------------|
8 | 2  | even     | 3              |
9 | 4  | even     | 4              |
10 | 6  | even     | 6              |
11 | 8  | even     | 8              |
12 | 10 | even     | 9              |
13 | 1  | odd      | 2              |
14 | 3  | odd      | 3              |
15 | 5  | odd      | 5              |
16 | 7  | odd      | 7              |
17 | 9  | odd      | 8              |

该 SQL 首先会对数据集按照 property 分组，每组内按 x 列进行排序。之后对每组内的每行数据，计算其前一行，自身和后一行三行数据的平均值，作为该行的输出，写入 moving average 列。

COUNT()

统计个数。

语法：

SQL

1COUNT([DISTINCT | ALL] expression) [OVER (analytic_clause)]

举例：

计算从当前行到第一行x出现的次数。

Plain Text

1> select x, property,   
2  count(x) over   
3  (partition by property order by x rows between unbounded preceding and current row) as 'cumulative total'
4  from int_t where property in ('odd','even');
5  
6 | x  | property | cumulative count |
7 |----|----------|------------------|
8 | 2  | even     | 1                |
9 | 4  | even     | 2                |
10 | 6  | even     | 3                |
11 | 8  | even     | 4                |
12 | 10 | even     | 5                |
13 | 1  | odd      | 1                |
14 | 3  | odd      | 2                |
15 | 5  | odd      | 3                |
16 | 7  | odd      | 4                |
17 | 9  | odd      | 5                |

该 SQL 首先会对数据集按照 property 分组，每组内按 x 列进行排序。之后对每组内的每行数据，统计从自身算起之前所有行的行数，作为该行的输出，写入 cumulative count 列。

RANK()

RANK() 函数用来表示排名，与 DENSE_RANK()不同的是，如果出现了两个并列的值，RANK() 的第三个数就是 3，而不是 2。

语法：

SQL

1RANK() OVER(partition_by_clause order_by_clause)

举例：

根据x列进行排名

SQL

1> select x, y, rank() over(partition by x order by y) as rank from int_t;
2	
3| x | y | rank |
4|---|---|------|
5| 1 | 1 | 1    |
6| 1 | 2 | 2    |
7| 1 | 2 | 2    |
8| 2 | 1 | 1    |
9| 2 | 2 | 2    |
10| 2 | 3 | 3    |
11| 3 | 1 | 1    |
12| 3 | 1 | 1    |
13| 3 | 2 | 3    |

该 SQL 首先会对数据集按照 x 分组，每组内按 y 列进行排序。之后对每组内的每行数据，计算该行在组内的名次，写入 rank 列。

DENSE_RANK()

DENSE_RANK() 函数用来表示排名，与 RANK() 不同的是，DENSE_RANK() 不会出现空缺数字。比如，如果出现了两个并列的1，DENSE_RANK() 的第三个数仍然是2，而 RANK() 的第三个数是3。

语法：

SQL

1DENSE_RANK() OVER(partition_by_clause order_by_clause)

举例：

下例展示了按照property列分组对x列排名：

SQL

1> select x, y, dense_rank() over(partition by x order by y) as rank from int_t;
2
3| x | y | rank |
4|----|------|----------|
5| 1 | 1 | 1 |
6| 1 | 2 | 2 |
7| 1 | 2 | 2 |
8| 2 | 1 | 1 |
9| 2 | 2 | 2 |
10| 2 | 3 | 3 |
11| 3 | 1 | 1 |
12| 3 | 1 | 1 |
13| 3 | 2 | 2 |

该 SQL 首先会对数据集按照 x 分组，每组内按 y 列进行排序。之后对每组内的每行数据，计算该行在组内的名次，写入 rank 列。

FIRST_VALUE()

返回窗口范围内的第一个值。

语法：

Plain Text

1FIRST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])

举例：

我们有如下数据

Plain Text

1> select name, country, greeting from mail_merge;
2
3 | name    | country | greeting     |
4 |---------|---------|--------------|
5 | Pete    | USA     | Hello        |
6 | John    | USA     | Hi           |
7 | Boris   | Germany | Guten tag    |
8 | Michael | Germany | Guten morgen |
9 | Bjorn   | Sweden  | Hej          |
10 | Mats    | Sweden  | Tja          |

使用 FIRST_VALUE()，根据 country 分组，返回每个分组中第一个 greeting 的值：

SQL

1> select country, name,    
2 first_value(greeting) over (partition by country order by name, greeting) as greeting
3 from mail_merge;
4
5| country | name    | greeting  |
6|---------|---------|-----------|
7| Germany | Boris   | Guten tag |
8| Germany | Michael | Guten tag |
9| Sweden  | Bjorn   | Hej       |
10| Sweden  | Mats    | Hej       |
11| USA     | John    | Hi        |
12| USA     | Pete    | Hi        |

LAST_VALUE()

返回窗口范围内的最后一个值。与 FIRST_VALUE() 相反。

语法：

Plain Text

1LAST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])

使用 FIRST_VALUE() 举例中的数据：

SQL

1> select country, name,    
2  last_value(greeting)   
3  over (partition by country order by name, greeting) as greeting   
4  from mail_merge;
5  
6| country | name    | greeting     |
7|---------|---------|--------------|
8| Germany | Boris   | Guten morgen |
9| Germany | Michael | Guten morgen |
10| Sweden  | Bjorn   | Tja          |
11| Sweden  | Mats    | Tja          |
12| USA     | John    | Hello        |
13| USA     | Pete    | Hello        |

LAG()

用来计算当前行向前数若干行的值。

语法：

SQL

1LAG(expr, offset, default) OVER (partition_by_clause order_by_clause)

举例：

计算前一天的收盘价

SQL

1> select stock_symbol, closing_date, closing_price,    
2  lag(closing_price, 1, 0) over (partition by stock_symbol order by closing_date) as "yesterday closing"   
3  from stock_ticker   
4  order by closing_date;
5  
6| stock_symbol | closing_date        | closing_price | yesterday closing |
7|--------------|---------------------|---------------|-------------------|
8| JDR          | 2014-09-13 00:00:00 | 12.86         | 0                 |
9| JDR          | 2014-09-14 00:00:00 | 12.89         | 12.86             |
10| JDR          | 2014-09-15 00:00:00 | 12.94         | 12.89             |
11| JDR          | 2014-09-16 00:00:00 | 12.55         | 12.94             |
12| JDR          | 2014-09-17 00:00:00 | 14.03         | 12.55             |
13| JDR          | 2014-09-18 00:00:00 | 14.75         | 14.03             |
14| JDR          | 2014-09-19 00:00:00 | 13.98         | 14.75             |

该 SQL 首先会对数据集按照 stock_symbol 分组，每组内按 closing_date 列进行排序。之后对每组内的每行数据，取其前一行的值作为输出，写入 yesterday closing 列。如果没有前一行，则使用默认值 0。

LEAD()

和 LAG() 方法相反。用来计算当前行向后数若干行的值。

语法：

SQL

1LEAD (expr, offset, default]) OVER (partition_by_clause order_by_clause)

举例：

计算第二天的收盘价对比当天收盘价的走势，即第二天收盘价比当天高还是低。

SQL

1> select stock_symbol, closing_date, closing_price,    
2  case   
3  (lead(closing_price, 1, 0) over (partition by stock_symbol order by closing_date) - closing_price) > 0   
4  when true then "higher"   
5  when false then "flat or lower"    
6  end as "trending"   
7  from stock_ticker    
8  order by closing_date;
9  
10| stock_symbol | closing_date        | closing_price | trending      |
11|--------------|---------------------|---------------|---------------|
12| JDR          | 2014-09-13 00:00:00 | 12.86         | higher        |
13| JDR          | 2014-09-14 00:00:00 | 12.89         | higher        |
14| JDR          | 2014-09-15 00:00:00 | 12.94         | flat or lower |
15| JDR          | 2014-09-16 00:00:00 | 12.55         | higher        |
16| JDR          | 2014-09-17 00:00:00 | 14.03         | higher        |
17| JDR          | 2014-09-18 00:00:00 | 14.75         | flat or lower |
18| JDR          | 2014-09-19 00:00:00 | 13.98         | flat or lower |

该 SQL 首先会对数据集按照 stock_symbol 分组，每组内按 closing_date 列进行排序。之后对每组内的每行数据，取其后一行的值作为输出，再减去该行的 closing_price 值。如果没有后一行，则使用默认值 0。

MAX()

计算分组内的最大值。

语法：

SQL

1MAX([DISTINCT | ALL] expression) [OVER (analytic_clause)]

举例：

计算从第一行到当前行之后一行的最大值

SQL

1> select x, property,   
2  max(x) over (order by property, x rows between unbounded preceding and 1 following) as 'local maximum'    
3  from int_t where property in ('prime','square');
4  
5| x | property | local maximum |
6|---|----------|---------------|
7| 2 | prime    | 3             |
8| 3 | prime    | 5             |
9| 5 | prime    | 7             |
10| 7 | prime    | 7             |
11| 1 | square   | 7             |
12| 4 | square   | 9             |
13| 9 | square   | 9             |

该 SQL 的窗口函数没有分组子句，则全部在一个组内。每组内按 property 和 x 列进行排序。之后对每组内的每行数据，取之前的所有行、自身和之后的1行，计算最大值作为输出写入 local maximum 列。

MIN()

计算分组内的最小值。

语法：

SQL

1MIN([DISTINCT | ALL] expression) [OVER (analytic_clause)]

ROW_NUMBER()

为每个分组的每一行返回一个从1开始连续递增的整数。类似行号。

语法：

SQL

1ROW_NUMBER() OVER(partition_by_clause order_by_clause)

举例：

SQL

1> select x, y, row_number() over(partition by x order by y) as rank from int_t;
2
3| x | y | rank |
4|---|---|------|
5| 1 | 1 | 1    |
6| 1 | 2 | 2    |
7| 1 | 2 | 3    |
8| 2 | 1 | 1    |
9| 2 | 2 | 2    |
10| 2 | 3 | 3    |
11| 3 | 1 | 1    |
12| 3 | 1 | 2    |
13| 3 | 2 | 3    |

SUM()

分组内求和。

语法：

SQL

1SUM([DISTINCT | ALL] expression) [OVER (analytic_clause)]

举例：

按照property进行分组，在组内计算当前行以及前后各一行的x列的和。

SQL

1> select x, property,   
2  sum(x) over (partition by property order by x rows between 1 preceding and 1 following) as 'moving total'    
3  from int_t where property in ('odd','even');
4
5| x  | property | moving total |
6|----|----------|--------------|
7| 2  | even     | 6            |
8| 4  | even     | 12           |
9| 6  | even     | 18           |
10| 8  | even     | 24           |
11| 10 | even     | 18           |
12| 1  | odd      | 4            |
13| 3  | odd      | 9            |
14| 5  | odd      | 15           |
15| 7  | odd      | 21           |
16| 9  | odd      | 16           |

该 SQL 首先会对数据集按照 property 分组，每组内按 x 列进行排序。之后对每组内的每行数据，取其前一行、自身和后一行的值求和，结果写入 moving total 列。

BITMAP函数

哈希函数

数据仓库 PALO

数据仓库 PALO

窗口函数

窗口函数

窗口函数语法

Function 介绍

AVG()

COUNT()

RANK()

DENSE_RANK()

FIRST_VALUE()

LAST_VALUE()

LAG()

LEAD()

MAX()

MIN()

ROW_NUMBER()

SUM()