百度数据仓库 Palo SQL语法手册 - 窗口函数

百度数据仓库 Palo Doris版

简介/价格/文档

百度数据仓库 Palo SQL语法手册 - 窗口函数

文档简介：

窗口函数是一类特殊的内置函数。和聚合函数类似，窗口函数也是对于多个输入行做计算得到一个数据值。不同的是，窗口函数是在一个特定的窗口内对输入数据做处理，而不是按照 group by 来分组计算。

*此产品及展示信息均由百度智能云官方提供。免费试用咨询热线：400-826-7010，为您提供专业的售前咨询，让您快速了解云产品，助您轻松上云！微信咨询

免费试用、价格特惠

文档详情

窗口函数

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

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

窗口函数语法

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

partition_by_clause:

PARTITION BY expr [, expr ...]

order_by_clause:

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

window_clause:

ROWS BETWEEN [ { m | UNBOUNDED } PRECEDING | CURRENT ROW] [ AND [CURRENT ROW | { UNBOUNDED | n } FOLLOWING] ]
RANGE 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列排序。

举例说明：
```
SELECT row_number() OVER (PARTITION BY class_id ORDER BY eventdate) AS id, c1, c2, c3, c4 FROM 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是每天的收盘价。
```
> 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 | ```

以下查询使用窗口函数产生 `moving_average` 这一列，它的值是3天的股票均价，即前一天、当前以及后一天三天的均价。

第一天没有前一天的值，最后一天没有后一天的值，所以这两行只计算了两天的均值。

这里 Partition By 没有起到作用，因为所有的数据都是JDR的数据，但如果还有其他股票信息，Partition By

 会保证窗口函数值作用在本Partition之内。 ```sql > 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 | ```

Function 介绍

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

AVG()

计算平均值。

语法：

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

举例：

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

> select x, property, avg(x) over (partition by property order by x rows between 1 preceding and 1

following) as 'moving average' from int_t where property in ('odd','even'); | x | property | moving average

 | |----|----------|----------------| | 2 | even | 3 | | 4 | even | 4 | | 6 | even | 6 | | 8 | even | 8

| | 10 | even | 9 | | 1 | odd | 2 | | 3 | odd | 3 | | 5 | odd | 5 | | 7 | odd | 7 | | 9 | odd | 8 |

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

COUNT()

统计个数。

语法：

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

举例：

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

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

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

RANK()

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

语法：

RANK() OVER(partition_by_clause order_by_clause)

举例：

根据x列进行排名

> select x, y, rank() over(partition by x order by y) as rank from int_t; | x | y | rank | |---|---|--

----| | 1 | 1 | 1 | | 1 | 2 | 2 | | 1 | 2 | 2 | | 2 | 1 | 1 | | 2 | 2 | 2 | | 2 | 3 | 3 | |

 3 | 1 | 1 | | 3 | 1 | 1 | | 3 | 2 | 3 |

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

DENSE_RANK()

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

语法：

DENSE_RANK() OVER(partition_by_clause order_by_clause)

举例：

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

> select x, y, dense_rank() over(partition by x order by y) as rank from int_t; | x | y | rank

| |----|------|----------| | 1 | 1 | 1 | | 1 | 2 | 2 | | 1 | 2 | 2 | | 2 | 1 | 1 | | 2 | 2 | 2

 | | 2 | 3 | 3 | | 3 | 1 | 1 | | 3 | 1 | 1 | | 3 | 2 | 2 |

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

FIRST_VALUE()

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

语法：

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

举例：

我们有如下数据

> select name, country, greeting from mail_merge;

 | name    | country | greeting     |
 |---------|---------|--------------|
 | Pete    | USA     | Hello        |
 | John    | USA     | Hi           |
 | Boris   | Germany | Guten tag    |
 | Michael | Germany | Guten morgen |
 | Bjorn   | Sweden  | Hej          |
 | Mats    | Sweden  | Tja          |

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

> select country, name, first_value(greeting) over (partition by country order by name, greeting)

as greeting from mail_merge; | country | name | greeting | |---------|---------|-----------| | Germany

| Boris | Guten tag | | Germany | Michael | Guten tag | | Sweden | Bjorn | Hej | | Sweden | Mats | Hej

| | USA | John | Hi | | USA | Pete | Hi |

LAST_VALUE()

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

语法：

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

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

> select country, name, last_value(greeting) over (partition by country order by name, greeting)

as greeting from mail_merge; | country | name | greeting | |---------|---------|--------------|

| Germany | Boris | Guten morgen | | Germany | Michael | Guten morgen | | Sweden | Bjorn | Tja | |

Sweden | Mats | Tja | | USA | John | Hello | | USA | Pete | Hello |

LAG()

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

语法：

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

举例：

计算前一天的收盘价

> select stock_symbol, closing_date, closing_price, lag(closing_price, 1, 0) over (partition by

stock_symbol order by closing_date) as "yesterday closing" from stock_ticker order by closing_date;

| stock_symbol | closing_date | closing_price | yesterday closing | |--------------|-----------------

----|---------------|-------------------| | JDR | 2014-09-13 00:00:00 | 12.86 | 0 | | JDR |

 2014-09-14 00:00:00 | 12.89 | 12.86 | | JDR | 2014-09-15 00:00:00 | 12.94 | 12.89 | | JDR |

2014-09-16 00:00:00 | 12.55 | 12.94 | | JDR | 2014-09-17 00:00:00 | 14.03 | 12.55 | | JDR |

 2014-09-18 00:00:00 | 14.75 | 14.03 | | JDR | 2014-09-19 00:00:00 | 13.98 | 14.75 |

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

LEAD()

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

语法：

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

举例：

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

> select stock_symbol, closing_date, closing_price, case (lead(closing_price, 1, 0) over

(partition by stock_symbol order by closing_date) - closing_price) > 0 when true then "higher"

 when false then "flat or lower" end as "trending" from stock_ticker order by closing_date;

 | stock_symbol | closing_date | closing_price | trending | |--------------|------------------

---|---------------|---------------| | JDR | 2014-09-13 00:00:00 | 12.86 | higher | | JDR |

2014-09-14 00:00:00 | 12.89 | higher | | JDR | 2014-09-15 00:00:00 | 12.94 | flat or lower

| | JDR | 2014-09-16 00:00:00 | 12.55 | higher | | JDR | 2014-09-17 00:00:00 | 14.03 | higher

 | | JDR | 2014-09-18 00:00:00 | 14.75 | flat or lower | | JDR | 2014-09-19 00:00:00 | 13.98 | flat or lower |

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

MAX()

计算分组内的最大值。

语法：

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

举例：

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

> select x, property, max(x) over (order by property, x rows between unbounded preceding and 1

following) as 'local maximum' from int_t where property in ('prime','square'); | x | property |

 local maximum | |---|----------|---------------| | 2 | prime | 3 | | 3 | prime | 5 | | 5 | prime

| 7 | | 7 | prime | 7 | | 1 | square | 7 | | 4 | square | 9 | | 9 | square | 9 |

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

MIN()

计算分组内的最小值。

语法：

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

ROW_NUMBER()

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

语法：

ROW_NUMBER() OVER(partition_by_clause order_by_clause)

举例：

> select x, y, row_number() over(partition by x order by y) as rank from int_t; | x | y | rank | |---|-

--|------| | 1 | 1 | 1 | | 1 | 2 | 2 | | 1 | 2 | 3 | | 2 | 1 | 1 | | 2 | 2 | 2 | | 2 | 3 | 3 | | 3 |

1 | 1 | | 3 | 1 | 2 | | 3 | 2 | 3 |

SUM()

分组内求和。

语法：

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

举例：

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

> select x, property, sum(x) over (partition by property order by x rows between 1 preceding and 1 following)

 as 'moving total' from int_t where property in ('odd','even'); | x | property | moving total | |----|----

------|--------------| | 2 | even | 6 | | 4 | even | 12 | | 6 | even | 18 | | 8 | even | 24 | | 10 | even

| 18 | | 1 | odd | 4 | | 3 | odd | 9 | | 5 | odd | 15 | | 7 | odd | 21 | | 9 | odd | 16 |

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

相似文档

百度数据仓库 Palo SQL语法手册 - 类型转换函数
PALO 支持以下类型转换函数： CAST Description： cast(expr as type) 功能：转换函数通常会和其他函数一同使用，显示的将expression转换成指定的参数类型。PALO 对于函数的参数类型有严格的数据类型定义。例如 PALO 不会自动将 bigtint 转换成 int 类型，或者其余可能会损失精度或者产生溢出的转换。
百度数据仓库 Palo SQL语法手册 - 聚合函数
聚合函数的行为是将多行的结果聚合成一行。 PALO 支持以下聚合函数： 1.avg 2.count 3.approx_count_distinct 4.max 5.min 6.sum 7.group_concat 8.variance_samp 9.variance_pop 10.percentile_approx 11.topn
百度数据仓库 Palo SQL语法手册 - 通用函数
PALO 通用函数如下： 1.sleep 2.version SLEEP Description： sleep(int n) 功能：会话休眠n秒钟，返回成功与否返回值：bool类型
百度数据仓库 Palo SQL语句详解（全）
DDL语句： Create Database：该语句用于新建数据库(database)。语法： CREATE DATABASE [IF NOT EXISTS] db_name; 举例：新建数据库 db_test CREATE DATABASE db_test;
百度数据仓库 Palo SQL手册 - 内置函数详解（全）
数学函数： abs(double a)：功能：返回参数的绝对值返回类型：double类型。使用说明：使用该函数需要确保函数的返回值是整数。 acos(double a)：功能：返回参数的反余弦值。返回类型：double类型。