PostgreSQL中的窗口函数

2 年 ago

清, 扬

2 minutes

首先

尽管PostgreSQL有许多功能，但窗口函数是相对较新的功能。(自PostgreSQL 8.4开始引入)

在排名中，我们在前五名之内。未来我们的产品会变得更好。

窗口函数是一个在聚合中很有用的功能，因此我们来概括一下。

窗口函数是什么？

窗口函数提供了对与当前查询行相关的行集合进行计算处理的功能。
窗口函数不仅可以访问查询结果的当前行，还可以访问更多的行。

集计函数适用于整个数据集，并且窗口函数可以处理相关的子数据。

通用窗口函数

関数戻り値説明row_number()bigint現在行のパーティション内での行番号（１から数える）rank()bigintギャップを含んだ現在行の順位。先頭ピアのrow_numberと同じになる。dense_rank()bigintギャップを含まない現在行の順位。この関数はピアのグループ数を数える。percent_rank()double precision現在行の相対順位。 (rank – 1) / (パーティションの総行数 – 1)cume_dist()double precision現在行の相対順位。 (現在行より先行する行およびピアの行数) / (パーティションの総行数)ntile(num_buckets integer)integerできるだけ等価にパーティションを分割した、1から引数値までの整数lag(value anyelement [, offset integer [, default anyelement ]])valueと同じ型パーティション内の現在行よりoffset行だけ前の行で評価されたvalueを返す。該当する行がない場合、その代わりとしてdefault(valueと同じ型でなければならない)を返す。 offsetとdefaultは共に現在行について評価される。省略された場合、offsetは1となり、defaultはNULLになる。lead(value anyelement [, offset integer [, default anyelement ]])valueと同じ型パーティション内の現在行よりoffset行だけ後の行で評価されたvalueを返す。該当する行がない場合、その代わりとしてdefault(valueと同じ型でなければならない)を返す。 offsetとdefaultは共に現在行について評価される。省略された場合、offsetは1となり、defaultはNULLになる。first_value(value any)valueと同じ型ウィンドウフレームの最初の行である行で評価されたvalue を返すlast_value(value any)valueと同じ型ウィンドウフレームの最後の行である行で評価されたvalue を返すnth_value(value any, nth integer)valueと同じ型ウィンドウフレームの（１から数えて）nth番目の行である行で評価されたvalueを返す。行が存在しない場合はNULLを返す

来源：https://www.postgresql.jp/document/11/html/functions-window.html

出处：https://www.postgresql.jp/document/11/html/functions-window.html

使用方法

窗口函数调用的语法

function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )

窗口定义语法

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

frame_clause構文（任意一种）

{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]

框架模式

RANGEモード:　現在行の列の値と、フレーム中の前あるいは後ろの行の値の最大の差を指定

frame_start和frame_end句法（任一种）

UNBOUNDED PRECEDING -- 先頭の行
n PRECEDING -- 現在行より<n>行前、RANGEの場合は<n>値前
CURRENT ROW -- 現在行
n FOLLOWING -- 現在行より<n>行後、RANGEの場合は<n>値後
UNBOUNDED FOLLOWING -- 末尾の行

以”ROWS”模式为例，使用”rows between unbounded preceding and unbounded following”的条件来对满足条件的所有数据行进行汇总。

以”RANGE”模式为例，如果排序列的类型为日期或时间戳，则可以使用”RANGE BETWEEN ‘1 day’ PRECEDING AND ’10 days’ FOLLOWING”来描述。

此外，在”ROWS”和”GROUPS”模式下，0 PRECEDING和0 FOLLOWING等同于CURRENT ROW。

排除框架语法（任选一个）

EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS

示例

可以通过计分来查看排名的例子。

select 
    score,
    row_number() OVER (ORDER BY score desc),
    rank() OVER (ORDER BY score desc),
    dense_rank() OVER (ORDER BY score desc),
    percent_rank() OVER (ORDER BY score desc),
    cume_dist() OVER (ORDER BY score desc) 
from scores

相同年龄组的平均分数、最高分数和最低分数。

select 
    age,
    score,
    first_value(score) over (PARTITION BY age ORDER BY score DESC rows between unbounded preceding and unbounded following) as "最高点数",
    last_value(score) over (PARTITION BY age ORDER BY score DESC rows between unbounded preceding and unbounded following) as "最低点数",
    avg(score) over (PARTITION BY age ORDER BY score DESC) as "平均点数"
from scores
order by score desc

由于已按照分数的降序进行排序，因此我们将first_value视为最高分，last_value视为最低分。

参考URL：
窗口函数调用：https://www.postgresql.jp/document/11/html/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
窗口函数：https://www.postgresql.jp/document/11/html/tutorial-window.html
窗口函数：https://www.postgresql.jp/document/11/html/functions-window.html

以上