PostgreSQL 11 preview - SQL:2011 window frame clause全面支援 及 視窗、幀用法和業務場景介紹

PostgreSQL , window , frame , 視窗 , 幀 , 視窗函數 , 聚合函數 , range , rows , groups

PostgreSQL 在2009年釋出的8.4版本中，就已經支援了window文法，frame clause有部分未支援。

PostgreSQL 11将全面覆寫SQL:2011的視窗文法。

送出的PATCH介紹如下：

<a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0a459cec96d3856f476c2db298c6b52f592894e8">https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0a459cec96d3856f476c2db298c6b52f592894e8</a>

1、下載下傳PG 11

<a href="https://www.postgresql.org/ftp/snapshot/dev/">https://www.postgresql.org/ftp/snapshot/dev/</a>

2、安裝、初始化、啟動資料庫（略）

3、測試新增功能用到的SQL如下（回歸測試SQL）

<a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/test/regress/expected/window.out;h=b675487729b7a51ac2f668fc4668c9d6817d2856;hp=19f909f3d105087c2babcfa62ff3a77d442a3b03;hb=0a459cec96d3856f476c2db298c6b52f592894e8;hpb=23209457314f6fd89fcd251a8173b0129aaa95a2">https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/test/regress/expected/window.out;h=b675487729b7a51ac2f668fc4668c9d6817d2856;hp=19f909f3d105087c2babcfa62ff3a77d442a3b03;hb=0a459cec96d3856f476c2db298c6b52f592894e8;hpb=23209457314f6fd89fcd251a8173b0129aaa95a2</a>

我們也可以自己測試，新增一張測試表

寫入測試資料

發現一例BUG，已上報。(5小時後，社群修複了這個BUG)。當使用range between and時，如果同時使用了partition by, range好像沒有起作用(而是使用了整個window)。

正常情況下，應該是在window的分組内，range會控制對應的frame。

以下為BUG版的輸出。

正确結果如下：

當不使用partition by時，range控制的frame範圍起作用了。

社群響應使用者送出BUG，修複BUG的速度很給力，PostgreSQL社群給予使用者強大的信心。

如Bruce momjian說的，一個使用者送出的BUG一般在12小時内社群會響應。

附上送出BUG的方法：

<a href="https://www.postgresql.org/support/">https://www.postgresql.org/support/</a>

<a href="https://www.postgresql.org/account/submitbug/">https://www.postgresql.org/account/submitbug/</a>

<a href="https://github.com/digoal/blog/blob/master/201802/20180224_01_pic_001.jpg" target="_blank"></a>

patch如下

<a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9fe802c8185e9a53158b6797d0f6fd8bfbb01af1">https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9fe802c8185e9a53158b6797d0f6fd8bfbb01af1</a>

建議在送出BUG的時候，盡量的描述詳細，能複現的問題寫清楚複現方法，能引用的commit盡量引用，減少committer排查問題的時間。

下面詳細介紹視窗查詢的文法。

<a href="https://www.postgresql.org/docs/devel/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS">https://www.postgresql.org/docs/devel/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS</a>

<a href="https://www.postgresql.org/docs/devel/static/sql-select.html">https://www.postgresql.org/docs/devel/static/sql-select.html</a>

大括号表示必選，中括号可選。

1、僅window聚合函數，允許使用FILTER子句，表示隻處理filter中WHERE TRUE的記錄。

2、視窗定義，where window_definition has the syntax

3、幀定義，The optional frame_clause can be one of

rows 表示前後多少行。

groups 表示前後多少組。

例如1,1,1,2,2,3,3,3,4,5,6的順序值。目前記錄=3的任意一條時，前後1組表示2,2,3,3,3,4；目前記錄=2的任意一條時，前1組以及current row表示1,1,1,2,2。

range, groups的邊界都是以peer的最外圍作為邊界，例如字段值 1,1,1,2,3,3，邊界是1或者3時，包含所有的1以及所有的3。

如果order by是一個表達式，那麼邊界以表達式的值來計算，如果多行表達式的值一緻，那麼這些行就是一組PEER。

groups，相同表達式或列值作為一個peer簇，邊界輸入為前後N個簇。current_row表示包含目前行所在簇的最大範圍。

range，目前值的內插補點作為判斷邊界的條件。current_row表示包含目前行所在簇的最大範圍。

目前range的offset僅支援int2,int4,int8,interval類型，對應類型int,date,time,timestamp,timestamptz，即類似資料庫range類型的描述，order by表達式的類型決定了offset用到的類型

4、幀的開始和結束定義，where frame_start and frame_end can be one of

預設為 between UNBOUNDED PRECEDING and CURRENT ROW。

注意：如果window中未定義order by，那麼整個window就是一個group，或者整個window就是一個frame。

5、幀的不包含定義，and frame_exclusion can be one of

1、查詢與每門成績第一名相差幾分

2、查詢每門成績排行，并列時，消耗計數

3、查詢與總分前一名的同學的單門分差

4、查詢每3個相鄰分組的分數平均值，SUM值，COUNT值。

需要使用group的幀文法，order by中每簇相同表達式的值，或者列的值，表示一個peer group。

1、資料去重

c1,c2字段重複時，保留crt_time最大的一條，其餘删除。

2、輻射統計

每個學生，與它前後相差10分的學生，作為一個幀，計算他們的總分，平均值，學生數，最高分和最低分。

3、滑窗分析

1、本文介紹了PostgreSQL視窗查詢的用法。

2、PostgreSQL 11完全相容SQL:2011的window frame clause标準。

3、目前range offset支援了numeric, float4, float8, int2, int4, int8, datetime, interval等類型，可以進行花式滑動分析。

<a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8b29e88cdce17705f0b2c43e50219ce1d7d2f603">https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8b29e88cdce17705f0b2c43e50219ce1d7d2f603</a>

<a href="https://github.com/digoal/blog/blob/master/201711/20171129_01.md">《PostgreSQL、Greenplum 滑動視窗 分析SQL 實踐》</a>

<a href="https://github.com/digoal/blog/blob/master/201707/20170722_02.md">《車聯網案例，軌迹清洗 - 阿裡雲RDS PostgreSQL最佳實踐 - 視窗函數》</a>

<a href="https://github.com/digoal/blog/blob/master/201707/20170705_01.md">《PostgreSQL 海量時序資料(任意滑動視窗實時統計分析) - 傳感器、人群、物體等對象跟蹤》</a>

<a href="https://github.com/digoal/blog/blob/master/201705/20170504_04.md">《PostgreSQL 聚合表達式 FILTER , order , within group, over window 用法》</a>

<a href="https://github.com/digoal/blog/blob/master/201612/20161203_01.md">《用PostgreSQL描繪人生的高潮、尿點、低谷 - 視窗/幀 or 斜率/導數/曲率/微積分?》</a>

<a href="https://github.com/digoal/blog/blob/master/201611/20161128_01.md">《時序資料合并場景加速分析和實作 - 複合索引，視窗分組查詢加速，變态遞歸加速》</a>

<a href="https://github.com/digoal/blog/blob/master/201706/20170602_01.md">《PostgreSQL 資料去重方法大全》</a>