–天池龍珠計劃SQL訓練營
5.1窗口函數
5.1.1窗口函數概念及基本的使用方法
窗口函數也稱為OLAP函數。OLAP 是OnLine AnalyticalProcessing 的簡稱，意思是對數據庫數據進行實時分析處理。
為了便于理解，稱之為窗口函數。常規的SELECT語句都是對整張表進行查詢，而窗口函數可以讓我們有選擇的去某一部分數據進行匯總、計算和排序。
窗口函數的通用形式：
<窗口函數> OVER ([PARTITION BY <列名>]ORDER BY <排序用列名>)
[]中的內容可以省略。
窗口函數最關鍵的是搞明白關鍵字****PARTITON BY和ORDER BY*****的作用。
PARTITON BY是用來分組，即選擇要看哪個窗口，類似于GROUP BY 子句的分組功能，但是PARTITION BY 子句并不具備GROUP BY 子句的匯總功能，并不會改變原始表中記錄的行數。
ORDER BY是用來排序，即決定窗口內，是按那種規則(字段)來排序的。
舉個栗子:
SELECT product_name,product_type,sale_price,RANK() OVER (PARTITION BY product_typeORDER BY sale_price) AS rankingFROM product
得到的結果是:
我們先忽略生成的新列 - [ranking]， 看下原始數據在PARTITION BY 和 ORDER BY 關鍵字的作用下發生了什么變化。
PARTITION BY 能夠設定窗口對象范圍。本例中，為了按照商品種類進行排序，我們指定了product_type。即一個商品種類就是一個小的"窗口"。
ORDER BY 能夠指定按照哪一列、何種順序進行排序。為了按照銷售單價的升序進行排列，我們指定了sale_price。此外，窗口函數中的ORDER BY與SELECT語句末尾的ORDER BY一樣，可以通過關鍵字ASC/DESC來指定升序/降序。省略該關鍵字時會默認按照ASC，也就是
升序進行排序。本例中就省略了上述關鍵字 。
5.2窗口函數種類
大致來說，窗口函數可以分為兩類。
一是 將SUM、MAX、MIN等聚合函數用在窗口函數中
二是 RANK、DENSE_RANK等排序用的專用窗口函數
5.2.1專用窗口函數
- **RANK函數 **（英式排序）
計算排序時，如果存在相同位次的記錄，則會跳過之后的位次。
例）有 3 條記錄排在第 1 位時：1 位、1 位、1 位、4 位……
- DENSE_RANK函數**（中式排序）**
同樣是計算排序，即使存在相同位次的記錄，也不會跳過之后的位次。
例）有 3 條記錄排在第 1 位時：1 位、1 位、1 位、2 位……
- ROW_NUMBER函數
賦予唯一的連續位次。
例）有 3 條記錄排在第 1 位時：1 位、2 位、3 位、4 位
運行以下代碼：
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_numFROM product
5.2.2聚合函數在窗口函數上的使用
聚合函數在開窗函數中的使用方法和之前的專用窗口函數一樣，只是出來的結果是一個累計的聚合函數值。
運行以下代碼：
SELECT product_id,product_name,sale_price,SUM(sale_price) OVER (ORDER BY product_id) AS current_sum,AVG(sale_price) OVER (ORDER BY product_id) AS current_avg FROM product;
可以看出，聚合函數結果是，按我們指定的排序，這里是product_id，當前所在行及之前所有的行的合計或均值。即累計到當前行的聚合。
5.3窗口函數的的應用 - 計算移動平均
在上面提到，聚合函數在窗口函數使用時，計算的是累積到當前行的所有的數據的聚合。 實際上，還可以指定更加詳細的匯總范圍。該匯總范圍成為框架(frame)。
語法
<窗口函數> OVER (ORDER BY <排序用列名>ROWS n PRECEDING ) <窗口函數> OVER (ORDER BY <排序用列名>ROWS BETWEEN n PRECEDING AND n FOLLOWING)
PRECEDING（“之前”）， 將框架指定為 “截止到之前 n 行”，加上自身行
FOLLOWING（“之后”）， 將框架指定為 “截止到之后 n 行”，加上自身行
BETWEEN 1 PRECEDING AND 1 FOLLOWING，將框架指定為 “之前1行” + “之后1行” + “自身”
執行以下代碼：
SELECT product_id,product_name,sale_price,AVG(sale_price) OVER (ORDER BY product_idROWS 2 PRECEDING) AS moving_avg,AVG(sale_price) OVER (ORDER BY product_idROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg FROM product
執行結果：
注意觀察框架的范圍。
ROWS 2 PRECEDING：
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：
5.3.1窗口函數適用范圍和注意事項
- 原則上，窗口函數只能在SELECT子句中使用。
- 窗口函數OVER 中的ORDER BY 子句并不會影響最終結果的排序。其只是用來決定窗口函數按何種順序計算。
5.4GROUPING運算符
5.4.1ROLLUP - 計算合計及小計
常規的GROUP BY 只能得到每個分類的小計，有時候還需要計算分類的合計，可以用 ROLLUP關鍵字。
SELECT product_type,regist_date,SUM(sale_price) AS sum_priceFROM productGROUP BY product_type, regist_date WITH ROLLUP
得到的結果為：
這里ROLLUP 對product_type, regist_date兩列進行合計匯總。結果實際上有三層聚合，如下圖 模塊3是常規的 GROUP BY 的結果，需要注意的是衣服 有個注冊日期為空的，這是本來數據就存在日期為空的，不是對衣服類別的合計； 模塊2和1是 ROLLUP 帶來的合計，模塊2是對產品種類的合計，模塊1是對全部數據的總計。
ROLLUP 可以對多列進行匯總求小計和合計。
練習題
5.1
請說出針對本章中使用的product（商品）表執行如下 SELECT 語句所能得到的結果。
SELECT product_id,product_name,sale_price,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_priceFROM product
** 答：按照 product_id 升序排列，計算出截?當前?的最? sale_price 。 **
5.2
繼續使用product表，計算出按照登記日期（regist_date）升序進行排列的各日期的銷售單價（sale_price）的總額。排序是需要將登記日期為NULL 的“運動 T 恤”記錄排在第 1 位（也就是將其看作比其他日期都早）
-- ①regist_date為NULL時，顯示“1年1?1?”。
SELECT regist_date, product_name, sale_price,SUM(sale_price) OVER (ORDER BY COALESCE(regist_date, CAST('0001-01-01'
AS DATE))) AS current_sum_priceFROM Product;-- ②regist_date為NULL時，將該記錄放在最前顯示。
SELECT regist_date, product_name, sale_price,SUM(sale_price) OVER (ORDER BY regist_date NULLS FIRST) AS
current_sum_priceFROM Product;5.3
思考題
① 窗口函數不指定PARTITION BY的效果是什么？
答： 窗?函數不指定 PARTITION BY 就是針對排序列進?全局排序
② 為什么說窗口函數只能在SELECT子句中使用？實際上，在ORDER BY 子句使用系統并不會報錯。
答： 本質上是因為 SQL 語句的執?順序。 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY 如果在 WHERE, GROUP BY, HAVING 使?了窗?函數，就是說提前進?了?次排序，排序之后再去除 記錄、匯總、匯總過濾，第?次排序結果就是錯誤的，沒有實際意義。? ORDER BY 語句執?順序在 SELECT 語句之后，?然是可以使?的。
