學習SQL【10】-SQL高級處理

05-11

所謂高級處理，從用戶的角度來講，就是那些對數值進行排序，計算銷售總額等我們熟悉的處理；從SQL的角度來講，就是近幾年才添加的新功能，這些新功能使得SQL的工作範圍不斷得到擴展。

窗口函數

窗口函數可以進行排序、生成序列號等一般的聚合函數無法完成的操作。

什麼是窗口函數

窗口函數也稱為OLAP函數。OLAP是OnLine Analytical Processing的簡稱，意思是對資料庫進行實時分析處理。

窗口函數就是為了實現OLAP而添加的標準SQL功能。

窗口函數的語法

窗口函數：

<窗口函數> OVER ( [PARTITION BY <列清單>] ORDER BY <排序用列清單>)

其中重要的關鍵字是PARTITON BY 和ORDER BY，理解這兩個關鍵字的作用是幫助我們理解窗口函數的關鍵。

能夠作為窗口函數使用的函數

窗口函數大致可以分為兩種：

能夠作為窗口函數的聚合函數（SUM, AVG,COUNT,MAX,MIN）
RANK、DENSE_RANK、ROW_NUMBER等專用窗口函數

語法的基本使用方法—使用RANK函數

RANK是用來計算記錄排序的函數。

例如，對於Product表，根據不同的商品種類，按照銷售單價從低到高的順序創建排序表：

--根據不同的商品種類，按照銷售單價從低到高的順序創建排序表 SELECT product_name, product_type, sale_price, RANK () OVER (PARTITION BY product_type ORDER BY sale_price) AS ranking FROM Product;

執行結果：

product_name | product_type | sale_price | ranking--------------+--------------+------------+--------- 圓珠筆 | 辦公用品 | 100 | 1 打孔器 | 辦公用品 | 500 | 2 叉子 | 廚房用具 | 500 | 1 擦菜板 | 廚房用具 | 880 | 2 菜刀 | 廚房用具 | 3000 | 3 高壓鍋 | 廚房用具 | 6800 | 4 T衫 | 衣服 | 1000 | 1 運動T衫 | 衣服 | 4000 | 2(8 行記錄)

PARTITON BY能夠指定排序的對象範圍，在上例中，為了按照商品種類排序，我們指定了Product_type.。

ORDER BY能夠指定按照哪一列、何種順序進行排序，為了按照銷售單價的升序進行排序，我們指定了sale_product，默認進行升序排序，（也可以通過指定關鍵字DESC進行降序排序）。

通過上述例子，我們很容易就理解了PARTITION BY和ORDER BY關鍵字的作用：PARTITION BY在橫向上對錶進行分組，ORDER BY決定了縱向排序的規則。

窗口函數兼具了GROUP BY子句的分組功能以及ORDER BY子句的排序功能。

但是PARTITION BY不具備GROUP BY子句的匯總功能。所以使用RANK函數不會減少原表中記錄的行數。

通過PARTITION BY分組後的記錄集合稱為窗口。此處的窗口表示範圍。

無需指定PARTITION BY

使用窗口函數時，PARTITION BY並不是必需的，如果我們不使用PARTITION BY，也就是將整個表作為一個大的窗口來使用。

--不指定PARTITION BY SELECT product_name, product_type, sale_price, RANK () OVER (ORDER BY sale_price) AS ranking FROM Product;

執行結果：

product_name | product_type | sale_price | ranking--------------+--------------+------------+--------- 圓珠筆 | 辦公用品 | 100 | 1 叉子 | 廚房用具 | 500 | 2 打孔器 | 辦公用品 | 500 | 2 擦菜板 | 廚房用具 | 880 | 4 T衫 | 衣服 | 1000 | 5 菜刀 | 廚房用具 | 3000 | 6 運動T衫 | 衣服 | 4000 | 7 高壓鍋 | 廚房用具 | 6800 | 8(8 行記錄)

專用窗口函數的種類

RANK函數
計算排序時，如果存在相同位次的記錄，則會跳過之後的位次。
例，有3條記錄排在第一位時：1位、1位、1位、4位…….
DENSE_RANK函數
同樣是計算排序，即使存在相同位次的記錄，也不會跳過之後的位次。
例，有3條記錄排在第一位時：1位、1位、1位、2位…….
ROW_NUMBER函數
賦予唯一的連續位次。

例，有3條記錄排在第一位時：1位、2位、3位、4位…….

我們使用一個例子來對比一下三個函數的區別：

-- 比較RANK、DENSE_RANK、ROW_NUMBER結果 SELECT product_name, product_type, sale_price, RANK () OVER (ORDER BY sale_price) AS ranking, DENSE_RANK () OVER (ORDER BY sale_price) AS dense_ranking, ROW_NUMBER () OVER (ORDER BY sale_price) AS row_number FROM Product;

執行結果：

product_name | product_type | sale_price | ranking | dense_ranking | row_number--------------+--------------+------------+---------+---------------+------------ 圓珠筆 | 辦公用品 | 100 | 1 | 1 | 1 叉子 | 廚房用具 | 500 | 2 | 2 | 2 打孔器 | 辦公用品 | 500 | 2 | 2 | 3 擦菜板 | 廚房用具 | 880 | 4 | 3 | 4 T衫 | 衣服 | 1000 | 5 | 4 | 5 菜刀 | 廚房用具 | 3000 | 6 | 5 | 6 運動T衫 | 衣服 | 4000 | 7 | 6 | 7 高壓鍋 | 廚房用具 | 6800 | 8 | 7 | 8(8 行記錄)

使用RANK或ROW_NUMBER是無需使用任何參數，因此只需要像RANK()這樣保持括弧為空就可以了。

注釋：專用窗口函數無需使用參數。

窗口函數的適用範圍

目前為止我們學過的函數大多數都沒有使用位置的限制，最多也就是在WHERE子句不能使用聚合函數。但是，使用窗口函數的位置卻有很大的限制，確切的說，窗口函數只能在SELECT子句中使用。

作為窗口函數使用的聚合函數

所有的聚合函數都能用作窗口函數，且使用語法與專用窗口函數完全相同。

例1，將SUM函數作為窗口函數使用：

--將SUM函數作為窗口函數使用 SELECT product_id, product_name, sale_price, SUM(sale_price) OVER (ORDER BY product_id) AS current_sum FROM Product;

執行結果：

product_id | product_name | sale_price | current_sum------------+--------------+------------+------------- 0001 | T衫 | 1000 | 1000 0002 | 打孔器 | 500 | 1500 0003 | 運動T衫 | 4000 | 5500 0004 | 菜刀 | 3000 | 8500 0005 | 高壓鍋 | 6800 | 15300 0006 | 叉子 | 500 | 15800 0007 | 擦菜板 | 880 | 16680 0008 | 圓珠筆 | 100 | 16780(8 行記錄)

使用聚合函數作為窗口函數時，需要在其括弧內指定相應的列。像上例中，使用sale_price(銷售單價)作為累加的對象, current——sum的結果為在它之前的銷售單價的合計。這種統計方法稱為累計。

例2，將AVG函數作為窗口函數使用：

--將AVG函數作為窗口函數使用 SELECT product_id, product_name, sale_price, AVG(sale_price) OVER (ORDER BY product_id) AS current_avg FROM Product;

執行結果：

product_id | product_name | sale_price | current_avg------------+--------------+------------+----------------------- 0001 | T衫 | 1000 | 1000.0000000000000000 0002 | 打孔器 | 500 | 750.0000000000000000 0003 | 運動T衫 | 4000 | 1833.3333333333333333 0004 | 菜刀 | 3000 | 2125.0000000000000000 0005 | 高壓鍋 | 6800 | 3060.0000000000000000 0006 | 叉子 | 500 | 2633.3333333333333333 0007 | 擦菜板 | 880 | 2382.8571428571428571 0008 | 圓珠筆 | 100 | 2097.5000000000000000(8 行記錄)

current_avg的結果為在它之前的銷售單價的平均值。像這樣以「自身記錄」（當前記錄）作為基準進行統計，就是將聚合函數作為窗口函數使用時的最大特徵。

計算移動平均

窗口函數就是將表以窗口為單位進行分割，並在其中進行排序的函數。其中還包含在窗口中指定更詳細的匯總範圍的備選功能，這種備選功能中的匯總範圍稱為框架。

例如，指定「最靠近的3行」作為匯總對象：

--指定「最靠近的3行」作為匯總對象 SELECT product_id, product_name, sale_price, AVG(sale_price) OVER (ORDER BY product_id ROWS 2 PRECEDING) AS moving_avg FROM Product;

執行結果：

product_id | product_name | sale_price | moving_avg------------+--------------+------------+----------------------- 0001 | T衫 | 1000 | 1000.0000000000000000 0002 | 打孔器 | 500 | 750.0000000000000000 0003 | 運動T衫 | 4000 | 1833.3333333333333333 0004 | 菜刀 | 3000 | 2500.0000000000000000 0005 | 高壓鍋 | 6800 | 4600.0000000000000000 0006 | 叉子 | 500 | 3433.3333333333333333 0007 | 擦菜板 | 880 | 2726.6666666666666667 0008 | 圓珠筆 | 100 | 493.3333333333333333(8 行記錄)

指定框架（匯總範圍）

上例中，我們使用了ROWS（行）和PRECEDING(之前)兩個關鍵字，將框架指定為「截止到之前~行」，因此，「 ROWS 2 PRECEDING」意思就是將框架指定為「截止到之前2行」，也就是「最靠近的3行」。

自身（當前記錄）
之前1行的記錄
之前2行的記錄

如果將條件中的數字改為「ROWS 5 PRECEDING」，就是「截止到之前5行」（最靠近的6行）的意思。

這樣的統計方法稱為移動平均。

使用關鍵字FOLLOWING(之後)替換PRECEDING，就可以指定「截止到之後~行」作為框架。

將當前記錄的前後行作為匯總對象

如果希望將當前記錄的前後行作為匯總對象，可以同時使用PRECEDING(之前)和FOLLOWING（之後）關鍵字來實現。

例，將當前記錄的前後行作為匯總對象：

--將當前記錄的前後行作為匯總對象 SELECT product_id, product_name, sale_price, AVG(sale_price) OVER (ORDER BY product_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg FROM Product;

執行結果：

product_id | product_name | sale_price | moving_avg------------+--------------+------------+----------------------- 0001 | T衫 | 1000 | 750.0000000000000000 0002 | 打孔器 | 500 | 1833.3333333333333333 0003 | 運動T衫 | 4000 | 2500.0000000000000000 0004 | 菜刀 | 3000 | 4600.0000000000000000 0005 | 高壓鍋 | 6800 | 3433.3333333333333333 0006 | 叉子 | 500 | 2726.6666666666666667 0007 | 擦菜板 | 880 | 493.3333333333333333 0008 | 圓珠筆 | 100 | 490.0000000000000000(8 行記錄)

當前記錄的前後行的具體含義就是：

之前1行的記錄
自身（當前記錄）
之後1行的記錄

如果能夠熟練掌握框架功能，就可以稱為窗口函數高手了。

GROUPING運算符

只使用GROUP BY子句和聚合函數是無法同時得出小計和合計的，如果想要同時得到，可以使用GROUPING運算符。

ROLLUP—同時得出合計和小計

使用GROUPING運算符可以很容易就得到合計和小計。

GROUPING運算符包含下列三種：

ROLLUP
CUBE
GROUPING SETS

ROLLUP的使用方法

用一個例子說明：

--使用ROLLUP同時得出合計和小計SELECT product_type, SUM(sale_price) AS sum_price FROM Product GROUP BY ROLLUP(product_type);

執行結果：

product_type | sum_price--------------+----------- 辦公用品 | 600 廚房用具 | 11180 衣服 | 5000 | 16780(4 行記錄)

從語法上來說，就是將GROUP BY子句中的聚合鍵清單像ROLLUP(<列1>, <列2>,…)這樣使用。

ROLLUP可以同時得出合計和小計，是非常方便的工具。

對了，還有一個超級分組記錄的概念。超級記錄分組就是使用GROUP BY()時，未指定聚合鍵，這是會得到全部數據的合計行的記錄，該合計行稱為超級分組記錄。超級分組記錄默認使用NULL作為聚合鍵。

將「登記日期」添加到聚合鍵中

我們再來使用一個例子來理解理解ROLLUP的作用。

在GROUP BY中添加「登記日期」（不使用ROLLUP）:

--在GROUP BY中添加「登記日期」（不使用ROLLUP） SELECT product_type, regist_date, SUM(sale_price) AS sum_price FROM Product GROUP BY product_type, regist_date;

執行結果：

product_type | regist_date | sum_price--------------+-------------+----------- 廚房用具 | 2017-09-20 | 3500 衣服 | | 4000 廚房用具 | 2016-04-28 | 880 廚房用具 | 2017-01-15 | 6800 辦公用品 | 2017-11-11 | 100 衣服 | 2017-09-20 | 1000 辦公用品 | 2017-09-11 | 500(7 行記錄)

再看看使用ROLLUP之後會是什麼樣子：

--在GROUP BY中添加「登記日期」（使用ROLLUP） SELECT product_type, regist_date, SUM(sale_price) AS sum_price FROM Product GROUP BY ROLLUP(product_type, regist_date);

執行結果：

product_type | regist_date | sum_price--------------+-------------+----------- 辦公用品 | 2017-09-11 | 500 辦公用品 | 2017-11-11 | 100 辦公用品 | | 600 廚房用具 | 2016-04-28 | 880 廚房用具 | 2017-01-15 | 6800 廚房用具 | 2017-09-20 | 3500 廚房用具 | | 11180 衣服 | 2017-09-20 | 1000 衣服 | | 4000 衣服 | | 5000 | | 16780(11 行記錄)

將上述兩個結果進行比較後，我們就可以發現，使用ROLLUP時，多出了最下面的合計行以及3條不同商品種類的小計行。這4行就是我們所說的超級分組記錄。

GROUPING函數—讓NULL更加容易分辨

在上例中我們會發現，在超級分組記錄中，regist_date列為NULL，而在原始記錄中，「運動T衫」的登記日期同樣為NULL，那麼這兩種NULL如何分辨呢？

為了避免混淆，SQL提供了一個用來判斷超級分組記錄的NULL的特定函數—GROUPING函數。該函數在其參數列的值為超級分組記錄所產生的NULL時返回1，其他情況下返回0（其他情況包括原始記錄為NULL和原始記錄不為NULL）。

--使用GROUPING函數來判斷NULL SELECT GROUPING(product_type) AS product_type, GROUPING(regist_date) AS regist_date, SUM(sale_price) AS sum_price FROM Product GROUP BY ROLLUP(product_type, regist_date);

執行結果：

product_type | regist_date | sum_price--------------+-------------+----------- 0 | 0 | 500 0 | 0 | 100 0 | 1 | 600 0 | 0 | 880 0 | 0 | 6800 0 | 0 | 3500 0 | 1 | 11180 0 | 0 | 1000 0 | 0 | 4000 0 | 1 | 5000 1 | 1 | 16780(11 行記錄)

這樣就能分辨超級分組記錄中的NULL和原始記錄中的NULL了。

CUBE—用數據來搭積木

ROLLUP之後我們學習另一個GROUPING運算符—CUBE。CUBE是「立方體」的意思。它的語法和ROLLUP相同，只需要將ROLLUP替換為CUBE即可。

--使用CUBE取得全部組合的結果 SELECT CASE WHEN GROUPING(product_type) = 1 THEN 商品種類合計 ELSE product_type END AS product_type, CASE WHEN GROUPING(regist_date) = 1 THEN 登記日期合計 ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_type, SUM(sale_price) AS sum_price FROM Product GROUP BY CUBE(product_type, regist_date);

執行結果：

product_type | regist_type | sum_price---------------+---------------+----------- 辦公用品 | 2017-09-11 | 500 辦公用品 | 2017-11-11 | 100 辦公用品 | 登記日期合計 | 600 廚房用具 | 2016-04-28 | 880 廚房用具 | 2017-01-15 | 6800 廚房用具 | 2017-09-20 | 3500 廚房用具 | 登記日期合計 | 11180 衣服 | 2017-09-20 | 1000 衣服 | | 4000 衣服 | 登記日期合計 | 5000 商品種類合計 | 登記日期合計 | 16780 商品種類合計 | 2016-04-28 | 880 商品種類合計 | 2017-01-15 | 6800 商品種類合計 | 2017-09-11 | 500 商品種類合計 | 2017-09-20 | 4500 商品種類合計 | 2017-11-11 | 100 商品種類合計 | | 4000(17 行記錄)

與ROLLUP相比，CUBE多出了幾行，多出來的記錄就是將regist_date作為聚合鍵所得到的匯總結果。

所謂CUBE，就是將GROUP BY子句中聚合鍵的「所有可能的組合」的匯總結果集中到一個結果中。因此，組合的個數為2的n次方（n是聚合鍵的個數）。

上例中聚合鍵有2個，所以2的2次方為4。

對於CUBE來說，一個聚合鍵就相當於立方體的一個軸，而結果就像是將數據像積木那樣堆積起來。可以把CUBE理解為將使用聚合鍵進行切割的模塊堆積成一個立方體（有點抽象了）。

GROUPING SETS—取得期望的積木

GROUPING SETS可以用於從ROLLUP或者CUBE的結果中取出部分記錄。

--使用GROUPING SETS取得部分組合的結果 SELECT CASE WHEN GROUPING(product_type) = 1 THEN 商品種類合計 ELSE product_type END AS product_type, CASE WHEN GROUPING(regist_date) = 1 THEN 登記日期合計 ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_type, SUM(sale_price) AS sum_price FROM Product GROUP BY GROUPING SETS(product_type, regist_date);

執行結果：

不過和ROLLUP或者CUBE比起來，使用GROUPING SETS的機會很少。

SQL的基礎語法知識大致就是這些，如果你稍微會一點SQL，看到這些代碼應該很好理解的。如果你沒有編程基礎，可能這些代碼對於你來說過於難以理解。寫的很粗糙，勉強可以用來溫習SQL的語法。大概也只有這個作用了。

每天學習一點點，每天進步一點點。