免费捕鱼游戏在线玩

書名： SQL進階教程（第2版）
作者名： (日)MICK
本章字數(shù)： 7563字
更新時間： 2024-06-20 10:55:49

1-2　必知必會的窗口函數(shù)

順序編程的復活

本節(jié)將介紹窗口函數(shù)。窗口函數(shù)出現(xiàn)于 20 世紀 90 年代后半期，在 21 世紀初得到了 Oracle、DB2 和SQLServer 等 DBMS 的支持。隨著 2017 年 MySQL也開始支持窗口函數(shù)，現(xiàn)在的主流 DBMS 中就都可以使用窗口函數(shù)了。如果熟練掌握了窗口函數(shù)，從某種意義上來說，我們就可以像使用面向過程語言那樣操作數(shù)據(jù)。窗口函數(shù)能夠大幅擴展SQL編程的可能性，是一個非常重要的工具。通過本節(jié)，我們能夠加深自己對窗口函數(shù)的理解。

雖然窗口函數(shù)被SQL引入的時間比較晚，但它非常重要。可以說沒有窗口函數(shù)，就沒有現(xiàn)代SQL編程。在 2008 年編寫本書的第 1 版時，因為有些 DBMS 還不支持窗口函數(shù)，所以筆者當時并未對其進行詳細介紹，但在本書中，窗口函數(shù)成了重點內(nèi)容。

窗口函數(shù)的應用方法有很多，特別是在進行行間比較時必須依賴相關子查詢的情況下，通過使用窗口函數(shù)，我們可以去掉相關的子查詢，讓SQL語句變得更加優(yōu)雅（相關內(nèi)容將在 1-7 節(jié)中詳細介紹）。為了幫助大家熟練掌握便捷的窗口函數(shù)，本節(jié)將重點介紹窗口函數(shù)的基本操作。把介紹的重點放在基本操作上還有一個原因，那就是窗口函數(shù)乍一看很難理解，常常讓SQL用戶感到困惑。傳統(tǒng)的SQL編碼基于面向集合的思維方式，對熟悉這種編碼的數(shù)據(jù)庫工程師來說，窗口函數(shù)很難理解，因為它使用了“行的順序”這一面向過程語言的概念，而這個概念很早之前就脫離關系數(shù)據(jù)庫和SQL了。對初次接觸SQL的新手來說，若一個函數(shù)中包含諸多功能，便很難想象它是怎么工作的。

不過，如果因為這樣的理由就對窗口函數(shù)敬而遠之，那就太可惜了。本節(jié)，筆者會通過一些示例來講解窗口函數(shù)。這里以讀者大致掌握窗口函數(shù)的基本語法為前提，如果讀者完全沒有見過或使用過窗口函數(shù)，那么請先閱讀書末參考文獻中列舉的面向新手的有關窗口函數(shù)的圖書，這樣理解起來會更容易些。另外，不記得詳細的可選項語法并不會影響理解本節(jié)的內(nèi)容，請大家放心閱讀。

什么是窗口

首先，初次見到窗口函數(shù)的人會覺得這個名字有些不可思議 9。大家可能會想象這個函數(shù)是使用了某種類似于“窗口”的東西，但看到語法示例時，卻發(fā)現(xiàn)并沒有哪句代碼表明了“這是窗口”，只看到那些使用了 PARTITION BY 子句或 ORDER BY 子句的查詢示例。

9 窗口函數(shù)出現(xiàn)于 20 世紀 90 年代到 21 世紀初，當時它也被稱為“OLAP 函數(shù)”。人們打算把它用于 OLAP（聯(lián)機分析處理，online analytical processing），所以才取了這個名稱，但現(xiàn)在已經(jīng)不怎么使用該名稱了。筆者認為現(xiàn)在這個“窗口函數(shù)”的名稱要好一些，因為它能清楚地描述動作特征。

我們來看一下窗口函數(shù)的典型用例——計算移動平均值的語法的示例。這里不需要具體的數(shù)據(jù)，所以省略了表的定義。

■ 匿名窗口

SELECT products_id, products_name, sale_price,
       AVG (sale_price) OVER (ORDER BY products_id
                              ROWS BETWEEN 2 PRECEDING
                                       AND CURRENT ROW) AS moving_avg
  FROM Products;

上面的代碼按商品 ID 的升序來排列商品表，計算包含當前 ID 之前的兩個商品的價格移動平均值。雖然出現(xiàn)了 AVG、OVER、ROWS BETWEEN、CURRENT ROW 等窗口函數(shù)的關鍵字，但我們看不到窗口本身的定義。

然而，這并不是說該查詢并未使用窗口。實際上，該語法中也定義了窗口，只是操作是悄悄進行的，乍一看會讓人以為沒有窗口。

顯式定義窗口的語法如下所示。

■ 有名稱的窗口

SELECT products_id, products_name, sale_price,
       AVG(sale_price) OVER W AS moving_avg
  FROM Products
WINDOW W AS (ORDER BY products_id
                 ROWS BETWEEN 2 PRECEDING
                          AND CURRENT ROW);

這里顯式定義了窗口，并對其應用了 AVG 函數(shù)。這里所說的窗口，就是針對通過 FROM 子句選擇的記錄集，使用 ORDER BY 排序和使用 ROWS BETWEEN 定義幀之后所形成的數(shù)據(jù)集。窗口會通過各種可選項對記錄集進行數(shù)據(jù)加工，這就是它和記錄集的不同之處 10。

10 窗口（window）在英語中原本就有“范圍”“寬度”的意思，在系統(tǒng)開發(fā)領域也存在“批處理窗口”“維護窗口”這樣的術語。在這種情況下，“窗口”與普通意義上的窗口并無直接關系。
用這個詞表示時間段含義時也是如此。一般來說，該術語不僅用作將集合分割開的子集，還用作默認內(nèi)含某種順序性的“范圍”。筆者知道的其他示例還有世界一級方程式錦標賽（F1 比賽）中使用的術語“Pit Stop 窗口”，其含義是賽車進行 Pit Stop（進入維修站換臺及加油）的合適的時間間隔。這也是在確定某個時間段時使用的詞語。

通過比較這兩種語法可以知道，我們常用的窗口函數(shù)的語法，是默認使用“匿名窗口”的簡略版語法（這與匿名存儲過程或匿名函數(shù)是一樣的）。其優(yōu)點是內(nèi)容簡練，而帶名稱的窗口的優(yōu)點是窗口可以重復使用，能避免編輯錯誤。這與通過公用表表達式（CTE）重復使用視圖，以及在存儲過程中定義有名稱的存儲過程的效果是一樣的。

-- 有名稱的窗口可以被重復使用
SELECT products_id, products_name, sale_price,
       AVG(sale_price)   OVER W AS moving_avg,
       SUM(sale_price)   OVER W AS moving_sum,
       COUNT(sale_price) OVER W AS moving_count,
       MAX(sale_price)   OVER W AS moving_max
  FROM Products
WINDOW W AS (ORDER BY products_id
                 ROWS BETWEEN 2 PRECEDING
                          AND CURRENT ROW);

匿名窗口和有名稱的窗口各有優(yōu)勢，要根據(jù)具體情況進行選擇，但有一點必須注意，即有的 DBMS 不支持有名稱的窗口，一旦使用就會發(fā)生錯誤 11。人們通常認為有名稱的窗口是“正式”的語法，但實際情況恰好相反，被普遍使用的是匿名窗口。

11 例如，有名稱的窗口函數(shù)可以用在 PostgreSQL和 MySQL中，但在 Oracle 中使用就會發(fā)生錯誤。

這種語法上的不兼容會給 DBMS 之間的遷移帶來風險，因此原則上要使用匿名窗口（在理解了有名稱的窗口定義之后），這種做法或許更穩(wěn)妥一些。這和“爬完梯子要將其扔掉”12 是一個道理。

12 出自哲學家路德維希 · 維特根斯坦。——編者注

一張圖看懂窗口函數(shù)

前面介紹了窗口函數(shù)的定義，下面我們來看一下窗口函數(shù)的功能（圖 1.2.1）

圖 1.2.1　一張圖看懂窗口函數(shù)13

13 這張圖參考自論文“Efficient Processing of Window Functions in AnalyticalSQLQueries”（分析型SQL查詢中窗口函數(shù)的高效處理）。

窗口函數(shù)讓人難以理解的原因之一是 1 個窗口函數(shù)中包含多個操作，而如果像圖 1.2.1 那樣從整體來看，窗口函數(shù)實際上只包含下面 3 個功能（或許仍有人認為包含 3 個功能已經(jīng)很復雜了，這里我們暫且拋開這個問題）。

01.使用 PARTITION BY 子句分割記錄集合。

02.使用 ORDER BY 子句對記錄排序。

03.使用幀子句定義以當前記錄為中心的子集。

其中，第 1 個功能和第 2 個功能因為與現(xiàn)有的 GROUP BY 和 ORDER BY 的功能幾乎一樣，所以對于已經(jīng)掌握SQL基本語法的人來說都很容易理解 14。窗口函數(shù)真正特有的功能是上面列出的第 3 個功能。傳統(tǒng)的SQL編程中并沒有顯式地使用“當前記錄”的概念。另外，使用關系數(shù)據(jù)庫構(gòu)建過系統(tǒng)的人應該能立刻注意到，這個“當前記錄”源自“游標”（cursor）的引入——關系數(shù)據(jù)庫一直使用游標向面向過程語言傳遞數(shù)據(jù)（圖 1.2.2）。

14 PARTITION BY 子句只用來分割窗口，并不會像 GROUP BY 子句那樣對記錄進行聚合，因此在應用窗口函數(shù)時，記錄的行數(shù)不會發(fā)生改變，這一點與 GROUP BY 子句的功能并不完全一樣。大家記住“PARTITION BY = GROUP BY - 聚合”，就能更容易理解這個功能了。關于二者的不同，請參考本書 2-6 節(jié)的內(nèi)容。

圖 1.2.2　幀子句的原理是“游標”

之所以需要游標，是因為關系數(shù)據(jù)庫的表中的記錄是無序的，操作的基本單位是記錄的集合，也就是一次一集合（set at a time）的操作方式，而面向過程語言的記錄是有序的，操作的基本單位是一行記錄，也就是是一次一記錄（record at a time）的操作方式，我們需要用游標來填補二者之間的差異。

在面向過程語言中，根據(jù)鍵對記錄集合進行排序，通過 for 語句或 while 語句循環(huán)記錄集合，一行一行地移動當前記錄進行處理，這種操作方法至今都沒有變過。即使在引入地址隱藏和面向?qū)ο蠛螅矝]有發(fā)生改變。在這一點上，窗口函數(shù)可以說是將面向過程語言的思想引入到了SQL中 15。

15 可能有人會認為“在傳統(tǒng)的SQL中，ORDER BY 子句也會定義記錄的順序”，但實際上 ORDER BY 子句并不是SQL，而是游標定義的一部分。關于關系數(shù)據(jù)庫（的設計者）去掉編程中非常重要的記錄順序這一概念的原因，請參考本書的 2-1 節(jié)；關于記錄順序的概念去掉又恢復的原委，請參考本書的 2-5 節(jié)。

使用幀子句將其他行移至當前行

幀子句的作用是能通過SQL簡單計算出移動平均值等以當前記錄為基準計算的統(tǒng)計指標。除此之外，幀子句還有很廣泛的用途。直觀來講，幀子句可以將其他行移至當前行。之前使用SQL進行行間比較很困難，現(xiàn)在則變得很自如。

求過去最臨近的值

我們先來思考一下基本的時間序列分析。當比較時間序列中的數(shù)據(jù)時，SQL基本上是沿著時間序列，一行一行地向前追溯或向后推進。作為示例，我們來看一張記錄了服務器各個時點的負載量的表 LoadSample（這里選用了較為合適的數(shù)值作為負載量，大家不用關注其具體含義）。由于采樣是不定期的，所以存儲的日期并不連續(xù)，間隔隨機。

首先計算各行的“過去最臨近的日期”，也就是計算“上一行”的日期。

SELECT sample_date AS cur_date,
       MIN(sample_date)
          OVER (ORDER BY sample_date ASC
                ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_date
  FROM LoadSample;

■ 執(zhí)行結(jié)果

cur_date       latest_date
------------  ------------
2018-02-01
2018-02-02     2018-02-01
2018-02-05     2018-02-02
2018-02-07     2018-02-05
2018-02-08     2018-02-07
2018-02-12     2018-02-08

由于該表中并沒有 2 月 1 日之前的數(shù)據(jù)，所以 2 月 1 日這行之前的日期是 NULL。這一點應該不難理解。從 2 月 2 日起，每行日期的過去最臨近的日期都存在于表中，它們保存在 latest 列中。該查詢的重點是通過 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING 將幀子句的范圍限定在按 sample_date 排序后的上一行。一般來說，BETWEEN 大多用來指定多行的范圍，而這里用來將范圍限定為一行，自然就不會發(fā)生錯誤。

可以說，這里的幀子句以游標位于“當前行”為前提，創(chuàng)建了范圍是“上一行”的記錄集合。

除日期之外，計算與日期相對應的負載量也很簡單。當前記錄的負載量可以直接用 load 列計算出來。在相同的窗口定義下僅將列修改為 load 列，就可以計算出上一行的負載量。

SELECT sample_date AS cur_date,
       load_val AS cur_load,
       MIN(sample_date)
          OVER (ORDER BY sample_date ASC
                ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_date,
       MIN(load_val)
          OVER (ORDER BY sample_date ASC
                ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_load
  FROM LoadSample;

■ 執(zhí)行結(jié)果

 cur_date      cur_load  latest_date   latest_load
------------  ---------  -----------   -----------
 2018-02-01        1024
 2018-02-02        2366   2018-02-01          1024
 2018-02-05        2366   2018-02-02          2366
 2018-02-07         985   2018-02-05          2366
 2018-02-08         780   2018-02-07           985
 2018-02-12        1000   2018-02-08           780

大家注意到了嗎？這段代碼中出現(xiàn)了兩次相同的窗口定義。如果使用有名稱的窗口語法，就可以像下面這樣將窗口函數(shù)匯總為一個（結(jié)果是一樣的）。

SELECT sample_date AS cur_date,
       load_val    AS cur_load,
       MIN(sample_date) OVER W AS latest_date,
       MIN(load_val)    OVER W AS latest_load
  FROM LoadSample
WINDOW W AS (ORDER BY sample_date ASC
              ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING);

這是使用幀子句進行行間移動的基本內(nèi)容。這里，筆者來回答一些常見的疑問。

Q 1　除向前移動之外，幀還可以向“后”移動嗎

可以。這時要使用 FOLLOWING 關鍵字。例如，在過去最臨近的值的查詢中，將幀的范圍向后移動一行。

SELECT sample_date AS cur_date,
       load_val    AS cur_load,
       MIN(sample_date) OVER W AS next_date,
       MIN(load_val)    OVER W AS next_load
  FROM LoadSample
WINDOW W AS (ORDER BY sample_date ASC
              ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING);

■ 執(zhí)行結(jié)果

 cur_date      cur_load    next_date    next_load
------------  ----------  -----------  -----------
 2018-02-01         1024   2018-02-02         2366
 2018-02-02         2366   2018-02-05         2366
 2018-02-05         2366   2018-02-07          985
 2018-02-07          985   2018-02-08          780
 2018-02-08          780   2018-02-12         1000
 2018-02-12         1000

這次在 next_date 和 next_load 列中顯示將來最臨近的日期的記錄值。

另外，同時使用 PRECEDING 和 FOLLOWING，將當前記錄夾在中間，還可以設置范圍為“前后各 n 行”的幀。

Q 2　這里使用了 MIN 函數(shù)，請問它有什么含義呢

如果是像示例這樣將幀的范圍限定為一行，那么 MIN 并沒有什么特別的含義。即使使用的是 MAX、AVG 或 SUM，結(jié)果也是一樣的，因為這相當于對一行應用聚合函數(shù)。如果幀的范圍是多行，就需要應用相應的聚合函數(shù)了。

-- 執(zhí)行結(jié)果與使用 MIN 函數(shù)時相同
SELECT sample_date AS cur_date,
       load_val    AS cur_load,
       MAX(sample_date) OVER W AS latest_date,
       MAX(load_val)    OVER W AS latest_load
  FROM LoadSample
WINDOW W AS (ORDER BY sample_date ASC
              ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING);

Q 3　可以設置“一天前”或“兩天前”這樣基于列值（而不是行）的幀嗎

可以。這時要使用 RANGE 關鍵字來代替 ROWS16。

16 sample_date 是日期類型，所以 interval '1' day 是根據(jù)日期類型指定日期間隔的語法。如果使用 RANGE，就需要注意列的數(shù)據(jù)類型了（筆者認為它只能用于數(shù)值、日期和時間）。

SELECT sample_date AS cur_date,
       load_val    AS cur_load,
       MIN(sample_date)
         OVER (ORDER BY sample_date ASC
               RANGE BETWEEN interval '1' day PRECEDING
                         AND interval '1' day PRECEDING
              ) AS day1_before,
       MIN(load_val)
         OVER (ORDER BY sample_date ASC
               RANGE BETWEEN interval '1' day PRECEDING
                         AND interval '1' day PRECEDING
              ) AS load_day1_before
  FROM LoadSample;

■ 執(zhí)行結(jié)果

cur_date   cur_load  day1_before  load_day1_before
--------  ---------  -----------  ----------------
18-02-01       1024
18-02-02       2366     18-02-01              1024
18-02-05       2366
18-02-07        985
18-02-08        780     18-02-07               985
18-02-12       1000

表 LoadSample 中的數(shù)據(jù)不是連續(xù)的，如果沒有一天前的數(shù)據(jù)，day1_before 列和 load_day1_before 列中就會顯示 NULL。這樣看起來比較直觀。

下面是幀子句中可以使用的選項，大家可以參考。

● ROWS：按行設置移動單位

● RANGE：按列值設置移動單位。使用 ORDER BY 子句來指定基準列

● n PRECEDING：僅向前（行號較小的方向）移動 n 行。n 為正整數(shù)

● n FOLLOWING：僅向后（行號較大的方向）移動 n 行。n 為正整數(shù)

● UNBOUNDED PRECEDING：一直移動到最前面

● UNBOUNDED FOLLOWING：一直移動到最后面

● CURRENT ROW：當前行

行間比較的一般化

現(xiàn)在這樣已經(jīng)可以求出過去最臨近的日期了，但在實際工作中，人們還可能希望將比較范圍再擴大一些，比如將某個日期與其“過去最臨近的日期”或“過去第二臨近的日期”進行比較，甚至與“前面 n 行的日期”進行比較。這就是行間比較的一般化。

為了滿足該需求，我們首先要思考如何以某個日期為起點開始依次追溯之前的日期。假設我們先追溯前面三個臨近的日期，那么結(jié)果會像下頁這樣呈階梯形。之所以會呈現(xiàn)出這種形狀，是因為當追溯的日期數(shù)據(jù)不存在時，數(shù)據(jù)為 NULL。

■ 設想的執(zhí)行結(jié)果

cur_date    latest_1    latest_2    latest_3
--------    --------    --------    --------
2018-02-01
2018-02-02  2018-02-01
2018-02-05  2018-02-02  2018-02-01
2018-02-07  2018-02-05  2018-02-02  2018-02-01
2018-02-08  2018-02-07  2018-02-05  2018-02-02
2018-02-12  2018-02-08  2018-02-07  2018-02-05

求解的窗口函數(shù)如下所示。

SELECT sample_date AS cur_date,
       MIN(sample_date)
          OVER (ORDER BY sample_date ASC
                ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_1,
       MIN(sample_date)
           OVER (ORDER BY sample_date ASC
                 ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) AS latest_2,
       MIN(sample_date)
          OVER (ORDER BY sample_date ASC
                ROWS BETWEEN 3 PRECEDING AND 3 PRECEDING) AS latest_3
  FROM LoadSample;

這里只是將 BETWEEN 的指定行修改為“前一行”“前兩行”“前三行”……實現(xiàn)起來非常簡單。不管是前幾行，我們都可以使用相同的方法進行擴展。在這種情況下，可能有人認為可以使用有名稱的窗口來匯總定義，但是很遺憾，由于幀的定義不一樣，這一點無法實現(xiàn)。

使用窗口函數(shù)進行行間比較的應用方式有很多種，我們將在 1-7 節(jié)中詳細了解。

窗口函數(shù)的內(nèi)部動作

前面介紹過，窗口函數(shù)擁有下面 3 個功能。

01.使用 PARTITION BY 子句分割記錄集合。

02.使用 ORDER BY 子句對記錄排序。

03.使用幀子句定義以當前記錄為中心的子集。

看起來這是將復雜的功能集中在一個函數(shù)中，但實際上這些功能在SQL內(nèi)部是怎樣實現(xiàn)的呢？本節(jié)要講解的就是這個問題。

查看SQL語句內(nèi)部動作的手段通常是查看“執(zhí)行計劃”（execution plan）。所謂執(zhí)行計劃，其實就是一份由數(shù)據(jù)庫提供的計劃書，以幫助我們確定 DBMS 在執(zhí)行SQL語句時，以什么樣的訪問路徑獲取數(shù)據(jù)、執(zhí)行什么樣的計算是最高效的。可以說，它就像是一份用來判斷登山路線的參考書。

雖然執(zhí)行計劃的格式會隨 DBMS 的不同而發(fā)生改變，但只要是經(jīng)過一定培訓的人，應該就能看懂。因此，當SQL語句執(zhí)行較慢時，我們就要輸出并解析執(zhí)行計劃，查明原因并進行優(yōu)化（SQL語句越復雜，執(zhí)行計劃就越復雜，解析也就越辛苦）。

本書的目的不是教大家讀懂執(zhí)行計劃，所以書中并未講解執(zhí)行計劃的細節(jié)內(nèi)容，而窗口函數(shù)的執(zhí)行計劃很簡單，即使是第一次看到的人也能明白其含義。請試著看一下本節(jié)開頭介紹的查詢移動平均值的執(zhí)行計劃。

SELECT products_id, products_name, sale_price,
       AVG (sale_price) OVER (ORDER BY products_id
                              ROWS BETWEEN 2 PRECEDING
                                       AND CURRENT ROW) AS moving_avg
  FROM Products;

■ 執(zhí)行結(jié)果 PostgreSQL

                               QUERY PLAN
----------------------------------------------------------------------
 WindowAgg  (cost=20.76..24.61 rows=220 width=274)
   ->  Sort  (cost=20.76..21.31 rows=220 width=242)
         Sort Key: products_id
         ->  Seq Scan on products (cost=0.00..12.20 rows=220 width=242)

■ 執(zhí)行結(jié)果 MySQL

該示例展示了 PostgreSQL和 MySQL的執(zhí)行計劃。MySQL的執(zhí)行計劃是橫向布局，一頁放不下，所以這里只摘錄了重點內(nèi)容。

這兩個執(zhí)行計劃的含義都是掃描（讀取）表 Products 的數(shù)據(jù)，并對讀取的數(shù)據(jù)進行排序。PostgreSQL的執(zhí)行計劃中出現(xiàn)了 SORT 關鍵字，MySQL的執(zhí)行計劃中出現(xiàn)了 Using filesort 關鍵字，它們都表示排序。

窗口函數(shù)的本質(zhì)是排序

由剛才講解的內(nèi)容可知，窗口函數(shù)在內(nèi)部對記錄集合進行了排序。在筆者編寫本書時（2018 年），所有的 DBMS 都是如此。之所以要在窗口函數(shù)中進行排序，是出于使用 PARTITION BY 子句進行分組和使用 ORDER BY 子句對記錄排序時的需要。在關系數(shù)據(jù)庫中，表的記錄并不一定是物理排序的，因此一般來說，如果要基于鍵值對記錄排序，就需要先對記錄集合進行排序 17。

17 有時，如果數(shù)據(jù)已經(jīng)排完序，通過掃描索引能夠很好地使用它們，那么也可以省略掉窗口函數(shù)的排序，實現(xiàn)快速查詢。

所謂“進行排序”，就是執(zhí)行使用 for 語句或 while 語句的循環(huán)。雖然我們無法根據(jù)執(zhí)行計劃確定使用的是什么排序算法，但不管是快速排序，還是歸并排序，在面向過程語言中通常都是通過循環(huán)來實現(xiàn)的。實際上，如果大家不使用SQL而使用面向過程語言，對 CSV 或文本文件等適當形式的數(shù)據(jù)執(zhí)行與窗口函數(shù)同樣的計算，使用循環(huán)進行排序也可以解決問題。

散列和排序

不過，排序作為窗口函數(shù)的實現(xiàn)方法在性能方面是否最優(yōu)，也存在不同的意見。在圖 1.2.1 的邊注所提到的論文中，實際的測試結(jié)果顯示從原理上來說，某些情況下使用散列來計算 PARTITION BY 子句的性能會更好。

對于輸入行數(shù) n，如果分割數(shù)是 O(n)，那么散列就是 O(n)，最優(yōu)排序也得是 O(n log n)。
——出自論文 4.2 節(jié)“Determining the Window Frame Bounds”
（筆者摘譯）

散列函數(shù)擁有這樣的特性：若輸入值不同，輸出值基本上也不會一樣（值不會重復）。該輸出值稱為散列值。圖 1.2.3 展示了“30”→“cdae7jh02”的轉(zhuǎn)換。成對的輸入值和散列值稱為散列表。使用散列表進行分組，就可以在不進行排序的情況下執(zhí)行聚合操作（雖然輸入值不轉(zhuǎn)換為散列值也可以進行分組，但散列值的優(yōu)點是無須在意列數(shù)或數(shù)據(jù)類型，即可使用各種需要輸入散列值的函數(shù)）。

圖 1.2.3　散列分組的示意圖

實際上，GROUP BY 子句的功能與 PARTITION BY 子句的功能幾乎是一樣的。在 Oracle 或 PostgreSQL中，GROUP BY 子句除排序之外，還可以使用散列進行計算。不過，前述論文中也指出，散列要想發(fā)揮優(yōu)勢是有幾個前提的，并不是說它在任何情況下都有優(yōu)勢。或許，窗口函數(shù)早晚有一天會像 GROUP BY 子句那樣能使用散列進行計算。

本節(jié)小結(jié)

我們來回顧一下本節(jié)要點。

01.窗口函數(shù)中的“窗口”（原則上是有序的）是“范圍”的意思。

02.窗口函數(shù)在語法上是通過 PARTITION BY 子句和 ORDER BY 子句被賦予某種特征的記錄集合。由于較為常用的是窗口函數(shù)的簡略形式，所以我們很難注意到窗口。

03.PARTITION BY 子句去掉了 GROUP BY 子句的聚合功能，只保留了分割功能，而 ORDER BY 子句用于對記錄排序。

04.幀子句通過將游標功能引入SQL中來定義以“當前記錄”為中心的記錄集合的范圍。

05.通過幀子句，我們可以將不同行的數(shù)據(jù)移至同一行，輕松地進行行間比較。

06.目前，窗口函數(shù)的內(nèi)部動作是對記錄進行排序，將來或許會采用散列來處理。

專欄　為何是 OVER，而不是 ON

在窗口函數(shù)中，定義了使用 PARTITION BY 子句進行分割、使用 ORDER BY 子句進行排序這些操作的SQL語句，使用的關鍵字是 OVER。如果沒有這些SQL語句，AVG 或 SUM 就不算是窗口函數(shù)，只能作為聚合函數(shù)來執(zhí)行操作。因此，OVER 可以說是窗口函數(shù)的標記。

眾所周知，OVER 在英語中是表示“在（某個對象的）上面”的介詞。這里的“對象”當然就是記錄集合。不過，ON 也有“在上面”的意思，那么窗口函數(shù)為什么不使用 ON 呢？

由于SQL中已經(jīng)將 ON 用作指定連接條件的關鍵字，所以不使用 ON 的直接原因或許是為了避免混淆。不過筆者認為，窗口函數(shù)中之所以使用 OVER，是因為 ON 與 OVER 的語感稍有不同，OVER 有更積極的含義。下面就來聊一聊筆者的推測。

大家在英語課上學過，ON 與 OVER 的語感存在細微的差別。ON 給人的印象是在某個對象上處于靜止（貼合）狀態(tài)，而 OVER 包含在上面穿過的動作或移動的含義（圖 1.2.4）。

圖 1.2.4　ON 與 OVER 的區(qū)別

不管是對于人還是物，OVER 這個詞都擁有“從一端移動到另一端”這樣的語感，請看下面的例句。

The airplane is flying over the sea. （飛機在海上飛行）
The ball flew over the pond. 〔（高爾夫）球越過池塘〕

當表示這種移動時，如果使用 ON，會讓人感覺有點奇怪吧？

正如本節(jié)介紹的那樣，窗口函數(shù)按順序掃描多個記錄，并進行計算。為此，窗口函數(shù)內(nèi)部會進行排序。筆者認為，單詞 OVER 與該動作的形象一致，所以窗口函數(shù)中才使用了 OVER 一詞。雖然沒有確鑿的證據(jù)證明是這樣的，但應該不外乎如此吧。

練習題

● 練習題 1-2-1：窗口函數(shù)的結(jié)果預測 (1)

本節(jié)中使用了記錄了服務器負載量的表 LoadSample，假設我們要像下面這樣將其擴展為記錄了多臺服務器數(shù)據(jù)的表。

請大家猜測一下對該表執(zhí)行下述 SELECT 語句的結(jié)果。

SELECT server, sample_date,
       SUM(load_val) OVER () AS sum_load
  FROM ServerLoadSample;

這里的窗口函數(shù)非常簡單，其中并未定義 PARTITION BY 子句、ORDER BY 子句和幀子句。

這些子句都是可選的，因此該SQL語句并不會發(fā)生語法錯誤，會正確地返回結(jié)果，那么它會返回什么樣的結(jié)果呢？也請大家猜測一下，并思考其原因。

● 練習題 1-2-2：窗口函數(shù)的結(jié)果預測 (2)

對上一題中的表 ServerLoadSample 執(zhí)行下述 SELECT 語句，并猜測一下結(jié)果。

SELECT server, sample_date,
       SUM(load_val) OVER (PARTITION BY server) AS sum_load
  FROM ServerLoadSample;

這次添加了 PARTITION BY 子句，結(jié)果會發(fā)生什么變化呢？也請大家猜測一下，并思考其原因。

這兩道題可能讓人覺得自己是在做有關窗口函數(shù)詳細規(guī)范的競賽題。其實，這些規(guī)范在一些情境下使用起來非常便利。我們將在 1-7 節(jié)中了解詳細內(nèi)容。

官术网_书友最值得收藏!

SQL進階教程（第2版）

1-2 必知必會的窗口函數(shù)

什么是窗口

一張圖看懂窗口函數(shù)

使用幀子句將其他行移至當前行

求過去最臨近的值

Q 1 除向前移動之外，幀還可以向“后”移動嗎

Q 2 這里使用了 MIN 函數(shù)，請問它有什么含義呢

Q 3 可以設置“一天前”或“兩天前”這樣基于列值（而不是行）的幀嗎

行間比較的一般化

窗口函數(shù)的內(nèi)部動作

窗口函數(shù)的本質(zhì)是排序

散列和排序

本節(jié)小結(jié)

練習題

1-2　必知必會的窗口函數(shù)

Q 1　除向前移動之外，幀還可以向“后”移動嗎

Q 2　這里使用了 MIN 函數(shù)，請問它有什么含義呢

Q 3　可以設置“一天前”或“兩天前”這樣基于列值（而不是行）的幀嗎