詳解SQL操作的視窗函式

華為雲開發者社群發表於2022-05-27
SQL函式
摘要：視窗函式是聚集函式的延伸，是更高階的SQL語言操作，主要用於AP場景下對資料進行一些分析、彙總、排序的功能。

本文分享自華為雲社群《GaussDB(DWS) SQL進階之SQL操作之視窗函式》，作者： 兩杯咖啡。

視窗函式是聚集函式的延伸，是更高階的SQL語言操作，主要用於AP場景下對資料進行一些分析、彙總、排序的功能。本文將從視窗函式的基本概念入手，介紹其常用SQL語法和主要的應用場景，同時會講到在GaussDB(DWS)中使用視窗函式需要考慮的一些因素。

一. 視窗函式的作用

視窗函式(Window Function)，又被叫做分析函式(Analytics Function)，通常在需要對資料進行分組彙總計算時使用，因此與聚集函式有一定的相似性。但與聚集函式不同的是，聚集函式通過對資料進行分組，僅能夠輸出分組彙總結果，而原始資料則無法展現在結果中。而視窗函式則可以同時將原始資料和聚集分析結果同時顯示出來。通過下例，大家可以體會一下區別。

給定表score(id, classid, score)，每行表示學生id，所在班級id以及考試成績，資料如下圖所示：

詳解SQL操作的視窗函式

如果我們想獲取每個班的總分、平均分及學生數量，可以通過對classid進行聚集，查詢語句為：

select classid, sum(score), avg(score), count(*) from score group by classid order by classid;

結果如下：

詳解SQL操作的視窗函式

通過這個結果，我們瞭解了班級1和2的基本資訊，但是此時丟掉了學生資訊，也不知道每個學生在班級中的排名如何。如果想查詢這些資訊，當然可以通過將聚集結果和原表進行Join得出，但顯然更繁瑣。而通過視窗函式的語句，我們可以輕而易舉地將所需要的資訊查詢出來。

select classid, id, score,
sum(score) over(partition by classid),
avg(score) over(partition by classid),
count(*) over(partition by classid),
rank() over(partition by classid order by score desc)
from score
order by classid;

結果如下：

詳解SQL操作的視窗函式

通過以上資訊，我們可以很方便地進行進一步的查詢，例如：查詢每個班超過平均分的學生id，排名前5的學生id等。

可以看出，視窗函式其實是對查詢，聚集等多個操作所做的一個組合操作，但相對於多個操作而言，使用視窗函式在完成功能的情況下，書寫也更加簡潔。同時，視窗函式還提供了更多的函式、更多的聚集方式以支援多樣化的功能，而且支援分組中的排序功能。通過與聚集結果比較，可以方便地提取符合一定統計要求的記錄資訊。

二. 視窗函式的SQL語法介紹

視窗函式的功能這麼強大，那支援的語法一定也非常複雜吧。別急，通過系統性地分析，你也可以快速掌握視窗函式語法的精髓！

單個視窗函式表示式的主要語法為：

SUM(SCORE) OVER (PARTITION BY CLASSID ORDER BY SCORE ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)

該表示式主要由以下部分組成（下圖為圖解）：

詳解SQL操作的視窗函式
  • 視窗函式表示式（紫色部分）：指定該視窗函式進行計算的聚集函式，可以是SUM(), COUNT(), AVG(), MIN(), MAX()等聚集函式，可以通過以下語句在GaussDB(DWS)中查到：
SELECT proname FROM pg_proc WHERE proisagg = TRUE;

也可以是其它專有的視窗函式，可以通過如下語句在GaussDB(DWS)系統表中查到：

SELECT proname FROM pg_proc WHERE proiswindow = TRUE;

GaussDB(DWS)目前支援的專有視窗函式有15個：

詳解SQL操作的視窗函式

下面將詳細說明一下其中常用的視窗函式ROW_NUMBER(), RANK(), DENSE_RANK()的區別。首先通過如下查詢的結果來對比。

SELECT id, classid, score,
ROW_NUMBER() OVER(ORDER BY score DESC),
RANK() OVER(ORDER BY score DESC),
DENSE_RANK() OVER(ORDER BY score DESC)
FROM score;
詳解SQL操作的視窗函式

可以看出，三個函式都是用於進行行排序的，且引數個數為0。通過①可以看出ROW_NUMBER()和RANK()的區別：前者顧名思義，對行從1開始進行編號，無論資料是否重複，結果不重；而後者對於相同的資料，給出的排序結果是相同的。通過②可以看出RANK()和DENSE_RANK()的區別：前者在重複值後，編號和ROW_NUMBER()是相同的，雖然相同的資料編號相同，但仍然佔用多個編號位置；而後者對於重複值只佔用一個編號，重複後緊接著進行編號。

在實際應用著，前兩個函式應用較多，ROW_NUMBER()主要用於行編號，用於分頁展示等應用中；而RANK()主要用於對結果進行排序後展示。

  • 視窗函式分割槽列（紅色部分）：表示根據哪一列進行分組計算，類似於聚集語句中的GROUP BY子句。該部分可以沒有，類似於聚集語句，表示對所有語句劃分同一組處理。
  • 視窗函式排序列（綠色部分）：表示陣列劃分到同一組後，在進行視窗函式計算前排序的順序，可以指定多列，語法與ORDER BY類似。當聚集函式計算結果與順序無關時，此子句可以省略。
  • 視窗函式移動視窗選項（藍色部分）：該選項也稱為Window Frame Option，預設可以省略，表示對每個分組內所有行進行聚集計算（無排序列時）或對每個分組內起始行到當前行進行聚集計算（有排序列時）。但指定該選項後，僅針對指定的視窗內的元組進行聚集計算。

對分組內所有行結果，當需要指定一個視窗時，我們需要指定開始的行和結束的行，則聚集函式將針對視窗之內的所有行的結果進行計算。因此，移動視窗選項的主要語法為：

RANGE|ROWS [BETWEEN] <rows_loc> [AND <rows_loc>]
或
RANGE|ROWS <rows_loc>

第一種語法同時指定開始行和結束行，第二種語法僅指定開始行，結束行預設為當前行。

<rows_loc>用於指定某一行，支援以下五種用法：

  • UNBOUNDED PRECEDING

表示該分組的第一行

  • UNBOUNDED FOLLOWING

表示該分組的最後一行

  • CURRENT ROW

表示當前行。

  • <expression> PRECEDING

表示從當前行往前數<expression>數量的行，其中<expression>不能包含變數。RANGE選項禁用。

  • <expression> FOLLOWING

表示從當前行往後數<expression>數量的行，其中<expression>不能包含變數。RANGE選項禁用。

例如：

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 以該分組所有元組為視窗

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 以該分組起始行到當前行為視窗

ROWS BETWEEN 10 PRECEDING AND 5 FOLLOWING 以該分組當前行前10行到後5行為視窗（不能超過起始行和結束行）

示例：下圖左邊表含有列x，計算的表示式值SUM(x) OVER(ROWS 2 PRECEDING AND 2 FOLLOWING)的值見右表所示，中間箭頭上的數字表示起始和結束視窗的行號。例如：第1行的視窗為[1-2, 1+2]=[1, 3]（不超過起始行）；第4行的視窗為[4-2, 4+2]=[2, 6]。

詳解SQL操作的視窗函式

瞭解完單個視窗函式表示式的語法，我們來看下在SQL語句中的使用規範。

1、單個查詢中可以包含一個或多個視窗函式表示式。

2、視窗函式僅能出現在輸出列中。如果需要使用視窗函式的值進行條件過濾，需要將視窗函式巢狀在子查詢中，在外層使用視窗函式表示式的別名進行條件過濾。例如：

select classid, id, score
from
(select *, avg(score) over(partition by classid) as avg_score from score)
where score >= avg_score;

3、視窗函式所在查詢塊中支援使用GROUP BY表示式進行分組去重，但要求視窗函式中的PARTITION BY子句中必須是GROUP BY表示式的子集，以保證視窗函式在GROUP BY列去重後的結果上進行視窗運算，同時ORDER BY子句的表示式也需要是GROUP BY表示式的子集，或聚集運算的聚集函式。例如：

select classid,
rank() over(partition by classid order by sum(score)) as avg_score
from score
group by classid, id;

標準bench-mark TPC-DS中有多個語句使用到了視窗函式，以TPC-DS Q51為例：

WITH web_v1 as (
select
  ws_item_sk item_sk, d_date,
  sum(sum(ws_sales_price))
      over (partition by ws_item_sk order by d_date rows between unbounded preceding and current row) cume_sales
from web_sales
    ,date_dim
where ws_sold_date_sk=d_date_sk
  and d_month_seq between 1212 and 1212+11
  and ws_item_sk is not NULL
group by ws_item_sk, d_date),
store_v1 as (
select
  ss_item_sk item_sk, d_date,
  sum(sum(ss_sales_price))
      over (partition by ss_item_sk order by d_date rows between unbounded preceding and current row) cume_sales
from store_sales
    ,date_dim
where ss_sold_date_sk=d_date_sk
  and d_month_seq between 1212 and 1212+11
  and ss_item_sk is not NULL
group by ss_item_sk, d_date)
 select  *
from (select item_sk
     ,d_date
     ,web_sales
     ,store_sales
     ,max(web_sales)
         over (partition by item_sk order by d_date rows between unbounded preceding and current row) web_cumulative
     ,max(store_sales)
         over (partition by item_sk order by d_date rows between unbounded preceding and current row) store_cumulative
     from (select case when web.item_sk is not null then web.item_sk else store.item_sk end item_sk
                 ,case when web.d_date is not null then web.d_date else store.d_date end d_date
                 ,web.cume_sales web_sales
                 ,store.cume_sales store_sales
           from web_v1 web full outer join store_v1 store on (web.item_sk = store.item_sk
                                                          and web.d_date = store.d_date)
          )x )y
where web_cumulative > store_cumulative
order by item_sk
        ,d_date
limit 100;

上面查詢中使用了四個視窗函式，以CTE web_v1的定義為例：

select
  ws_item_sk item_sk, d_date,
  sum(sum(ws_sales_price))
      over (partition by ws_item_sk order by d_date rows between unbounded preceding and current row) cume_sales
from web_sales
    ,date_dim
where ws_sold_date_sk=d_date_sk
  and d_month_seq between 1212 and 1212+11
  and ws_item_sk is not NULL
group by ws_item_sk, d_date;

該語句塊計算了在一定時間內，網上銷售的貨物的累計銷售額。

該語句塊在web_sales和date_dim表Join後，使用ws_item_sk, d_date列進行GROUP BY，計算sum(ws_sales_price)，而後對聚集函式的結果進行視窗函式，對sum(ws_sales_price)再進行視窗函式的求和。視窗函式sum(sum(ws_sales_price)) over (partition by ws_item_sk order by d_date rows between unbounded preceding and current row)的含義是：以ws_item_sk為分組，以d_date為順序，計算從開始截止到當天累計銷售額。

三. Window子句

通過這個語句，細心的讀者可以發現，視窗函式基本都是相同的格式partition by item_sk order by d_date rows between unbounded preceding and current row，存在冗餘書寫的問題。因此，SQL語句中支援使用window子句，類似於WITH子句定義公共的分組排序視窗，這樣使用時僅引用對應的視窗名稱即可。例如Q51的主語句可以寫成：

select  *
from (select item_sk
     ,d_date
     ,web_sales
     ,store_sales
     ,max(web_sales)
         over (s rows between unbounded preceding and current row) web_cumulative
     ,max(store_sales)
         over (s rows between unbounded preceding and current row) store_cumulative
     from (select case when web.item_sk is not null then web.item_sk else store.item_sk end item_sk
                 ,case when web.d_date is not null then web.d_date else store.d_date end d_date
                 ,web.cume_sales web_sales
                 ,store.cume_sales store_sales
           from web_v1 web full outer join store_v1 store on (web.item_sk = store.item_sk
                                                          and web.d_date = store.d_date)
          )x window s as (partition by item_sk order by d_date))y
where web_cumulative > store_cumulative
order by item_sk
        ,d_date
limit 100;

在GaussDB(DWS)中使用window子句有以下需要注意的：

  1. window子句僅能在相同查詢塊語句中複用，跨查詢塊需要定義不同的window子句。
  2. window子句中僅能包含OVER之後的視窗子句。視窗子句可以包含：PARTITION BY子句、ORDER BY子句和移動視窗選項，而window不支援移動視窗選項。
  3. 視窗函式在複用window子句時，可以在window子句的基礎上增加ORDER BY子句和移動視窗子句，類似於繼承。

例如下面的例子：

select classid, id, score,
sum(score) over(s),
avg(score) over(s),
count(*) over(s),
sum(score) over(s rows between 1 preceding and current row)
from score
window s as (partition by classid order by score, id desc)
order by classid;

查詢結果為：

詳解SQL操作的視窗函式

四. GaussDB(DWS)使用視窗函式注意事項

  • 與Teradata等主流資料庫不同的是，GaussDB(DWS)中視窗函式的PARTITION BY後的表示式如果是數字，表示常量值，而不是輸出列的序號，這點與GROUP BY的語法也有出入。例如：
select *, rank() over(partition by 1 order by id) as avg_score from score;

此處會將所有資料分為一組。

  • 由於視窗函式一般需要對資料進行排序之後進行視窗計算，因此在GaussDB(DWS)中使用Sort + WindowAgg運算元來實現其功能。如果有多個不同的視窗子句，則每個視窗子句對應一對Sort + WindowAgg運算元，示例語句及計劃如下：
explain
select classid, id, score,
sum(score) over(s),
avg(score) over(s),
count(*) over(s),
sum(score) over(s rows between 1 preceding and current row)
from score
window s as (partition by classid order by score, id desc)
order by classid;
詳解SQL操作的視窗函式

此查詢中包含兩類視窗子句，(partition by classid order by score, id desc)以及(partition by classid order by score, id desc rows between 1 preceding and current row)，因此需要兩對Sort + WindowAgg運算元，見2-4號運算元（為什麼計劃中僅出現了一個SORT？請讀者思考）。由於按classid做partition，因此首先按其進行重分佈，見5號運算元。

  • 對於分散式資料庫GaussDB(DWS)，資料需要在各DN執行以獲得更好的效能。對於視窗函式，相同PARTITION BY的值的元組會在同一個DN上執行，因此對於缺少PARTITION BY子句，或PARTITION BY的值較少的場景，將無法完全發揮出分散式的效果。

(1) 當沒有PARTITION BY子句時，Gauss(DWS)需要進行全域性排序及視窗計算，因此需要先在各DN進行排序，然後將資料彙總到一個DN上進行合併排序及視窗計算，最終進行計算的DN將成為整個系統的瓶頸。這種場景需要儘量避免。（Teradata早期支援的MSUM等函式其語義即進行全域性排序並區域性求和，就屬於這種情況。後由於視窗函式可實現同樣的功能，MSUM僅作前向相容對外提供。）此種場景示例語句及計劃如下圖所示：

SELECT * from (select id, classid, score, ROW_NUMBER() OVER(ORDER BY score DESC) rn from score) where rn <= 5;
詳解SQL操作的視窗函式

第4號運算元Broadcast，將各DN排序後的資料（第5號運算元）廣播到某一個DN上，該DN上進行ROW_NUMBER()的計算並進行過濾、輸出結果。

(2) 當存在PARTITION BY子句時，會根據PARTITION BY的列進行重分佈，將資料分佈到各個DN上進行各自的分組計算。此時如果PARTITION BY的值較少，則資料會重分佈到少數幾個DN上，導致資料傾斜。因此，如果使用排序類的專有視窗函式，且僅關於排序靠前的若干行時，可以增加相應的過濾條件，GaussDB(DWS)會根據過濾條件進行雙層WindowAgg計算，以TPC-DS的Q67為例：

select  *
from (select i_category
            ,i_class
            ,i_brand
            ,i_product_name
            ,d_year
            ,d_qoy
            ,d_moy
            ,s_store_id
            ,sumsales
            ,rank() over (partition by i_category order by sumsales desc) rk
      from (select i_category
                  ,i_class
                  ,i_brand
                  ,i_product_name
                  ,d_year
                  ,d_qoy
                  ,d_moy
                  ,s_store_id
                  ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales
            from store_sales
                ,date_dim
                ,store
                ,item
       where  ss_sold_date_sk=d_date_sk
          and ss_item_sk=i_item_sk
          and ss_store_sk = s_store_sk
          and d_month_seq between 1212 and 1212+11
       group by  rollup(i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id))dw1) dw2
where rk <= 100
order by i_category
        ,i_class
        ,i_brand
        ,i_product_name
        ,d_year
        ,d_qoy
        ,d_moy
        ,s_store_id
        ,sumsales
        ,rk
limit 100;

該查詢中包含如下視窗函式：rank() over (partition by i_category order by sumsales desc)，以i_category進行分組，求每個分組前100名的資料。在TPC-DS資料集中，i_category表示商品種類，數量較少，因此我們採取雙層WindowAgg策略，先在本地進行WindowAgg，並過濾出前100名的資料，在資料量減少後，再進行全域性重分佈，以及全域性WindowAgg運算。計劃如下圖所示：

詳解SQL操作的視窗函式

第10-11號運算元為各DN計算排序和WindowAgg，第9號Result節點進行各DN的條件過濾，獲取各DN前100名的資料，而後第8層運算元根據i_category列進行重分佈，這樣各DN可以獲取i_category值相同的資料進行全域性計算。第6-7號運算元計算全域性的WindowAgg結果，由第5號運算元進行結果過濾後輸出。

五. 總結

視窗函式作為SQL的高階功能，用法比較靈活、複雜且比較難掌握，而駕馭了它卻對我們SQL編寫提供了更廣闊的天地，實現各種有一定技術含量的功能。同時，GaussDB(DWS)支援比較完備的視窗函式及其實現，可以助各位讀者實現理想中的應用！

想了解GuassDB(DWS)更多資訊，歡迎微信搜尋“GaussDB DWS”關注微信公眾號，和您分享最新最全的PB級數倉黑科技，後臺還可獲取眾多學習資料哦~

華為夥伴暨開發者大會2022火熱來襲，重磅內容不容錯過！

【精彩活動】

勇往直前·做全能開發者→12場技術直播前瞻，8大技術寶典高能輸出，還有程式碼密室、知識競賽等多輪神祕任務等你來挑戰。即刻闖關，開啟終極大獎！點選踏上全能開發者晉級之路吧！

【技術專題】

未來已來，2022技術探祕→華為各領域的前沿技術、重磅開源專案、創新的應用實踐，站在智慧世界的入口，探索未來如何照進現實，乾貨滿滿點選瞭解

 

點選關注，第一時間瞭解華為雲新鮮技術~

相關文章