【教奶奶學SQL】(task5)SQL進階處理（視窗函數

學習總結

（1）PARTITON BY 是用來分組，即選擇要看哪個視窗，類似于 GROUP BY 子句的分組功能，但是 PARTITION BY 子句并不具備 GROUP BY 子句的彙總功能，并不會改變原始表中記錄的行數。

ORDER BY 是用來排序，即決定視窗内，是按那種規則(字段)來排序的。

文章目錄

學習總結
一、視窗函數

1.1 視窗函數概念及基本的使用方法

二、視窗函數種類

2.1 專用視窗函數
2.2 聚合函數在視窗函數上的使用

三、視窗函數的的應用 - 計算移動平均

3.1 視窗函數适用範圍和注意事項

四、GROUPING運算符

4.1 ROLLUP - 計算合計及小計

五、練習題

5.1 結果題
5.2 排序題
5.3 思考題

Reference

一、視窗函數

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_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 rows in set (0.00 sec)

我們先忽略生成的新列 - [ranking]，看下原始資料在

PARTITION BY

和

ORDER BY

關鍵字的作用下發生了什麼變化。

PARTITION BY

能夠設定視窗對象範圍。本例中，為了按照商品種類進行排序，我們指定了

product_type

。即一個商品種類就是一個小的"視窗"。

ORDER BY

能夠指定按照哪一列、何種順序進行排序。為了按照銷售單價的升序進行排列，我們指定了

sale_price

。此外，視窗函數中的

ORDER BY

與

SELECT

語句末尾的

ORDER BY

一樣，可以通過關鍵字

ASC

DESC

來指定升序/降序。省略該關鍵字時會預設按照

ASC

，也就是

升序進行排序。本例中就省略了上述關鍵字。

二、視窗函數種類

大緻來說，視窗函數可以分為兩類。

一是将SUM、MAX、MIN等聚合函數用在視窗函數中

二是 RANK、DENSE_RANK等排序用的專用視窗函數

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 位

運作以下代碼：

因為不用像上面栗子進行分類商品中的排序，是以不需要用

PARTITION BY

：

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_num
  FROM product;  

+--------------+--------------+------------+---------+---------------+---------+
| product_name | product_type | sale_price | ranking | dense_ranking | row_num |
+--------------+--------------+------------+---------+---------------+---------+
| 原子筆       | 辦公用品     |        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 rows in set (0.00 sec)

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 | product_name | sale_price | current_sum | current_avg |
+------------+--------------+------------+-------------+-------------+
| 0001       | T恤          |       1000 |        1000 |   1000.0000 |
| 0002       | 打孔器       |        500 |        1500 |    750.0000 |
| 0003       | 運動T恤      |       4000 |        5500 |   1833.3333 |
| 0004       | 菜刀         |       3000 |        8500 |   2125.0000 |
| 0005       | 高壓鍋       |       6800 |       15300 |   3060.0000 |
| 0006       | 叉子         |        500 |       15800 |   2633.3333 |
| 0007       | 擦菜闆       |        880 |       16680 |   2382.8571 |
| 0008       | 原子筆       |        100 |       16780 |   2097.5000 |
+------------+--------------+------------+-------------+-------------+
8 rows in set (0.00 sec)

可以看出，聚合函數結果是，按我們指定的排序，這裡是

product_id

，目前所在行及之前所有的行的合計或均值。即累計到目前行的聚合。

三、視窗函數的的應用 - 計算移動平均

在上面提到，聚合函數在視窗函數使用時，計算的是累積到目前行的所有的資料的聚合。實際上，還可以指定更加詳細的彙總範圍。該彙總範圍成為架構 (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_id
                               ROWS 2 PRECEDING) AS moving_avg
       ,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 | moving_avg |
+------------+--------------+------------+------------+------------+
| 0001       | T恤          |       1000 |  1000.0000 |   750.0000 |
| 0002       | 打孔器       |        500 |   750.0000 |  1833.3333 |
| 0003       | 運動T恤      |       4000 |  1833.3333 |  2500.0000 |
| 0004       | 菜刀         |       3000 |  2500.0000 |  4600.0000 |
| 0005       | 高壓鍋       |       6800 |  4600.0000 |  3433.3333 |
| 0006       | 叉子         |        500 |  3433.3333 |  2726.6667 |
| 0007       | 擦菜闆       |        880 |  2726.6667 |   493.3333 |
| 0008       | 原子筆       |        100 |   493.3333 |   490.0000 |
+------------+--------------+------------+------------+------------+
8 rows in set (0.00 sec)

執行結果：

注意觀察架構的範圍。

第一列的

moving_avg

：ROWS 2 PRECEDING：

第二列的

moving_avg

：ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：

3.1 視窗函數适用範圍和注意事項

原則上，視窗函數隻能在SELECT子句中使用。
視窗函數OVER 中的 ORDER BY 子句并不會影響最終結果的排序。其隻是用來決定視窗函數按何種順序計算。

四、GROUPING運算符

4.1 ROLLUP - 計算合計及小計

正常的GROUP BY 隻能得到每個分類的小計，有時候還需要計算分類的合計，可以用

ROLLUP

關鍵字。

SELECT  product_type
       ,regist_date
       ,SUM(sale_price) AS sum_price
  FROM product
 GROUP BY product_type, regist_date WITH ROLLUP;  

+--------------+-------------+-----------+
| product_type | regist_date | sum_price |
+--------------+-------------+-----------+
| 辦公用品     | 2009-09-11  |       500 |
| 辦公用品     | 2009-11-11  |       100 |
| 辦公用品     | NULL        |       600 |
| 廚房用具     | 2008-04-28  |       880 |
| 廚房用具     | 2009-01-15  |      6800 |
| 廚房用具     | 2009-09-20  |      3500 |
| 廚房用具     | NULL        |     11180 |
| 衣服         | NULL        |      4000 |
| 衣服         | 2009-09-20  |      1000 |
| 衣服         | NULL        |      5000 |
| NULL         | NULL        |     16780 |
+--------------+-------------+-----------+
11 rows in set (0.01 sec)

這裡

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_price
  FROM product;

利用視窗函數

OVER

，計算按照

product_id

升序排序後，周遊到目前時候的最大商品價格：

5.2 排序題

繼續使用product表，計算出按照登記日期（

regist_date

）升序進行排列的各日期的銷售單價（

sale_price

）的總額。排序是需要将登記日期為NULL 的“運動 T 恤”記錄排在第 1 位（也就是将其看作比其他日期都早）

一開始想着如下這樣做（計算出每個日期的當天的），但是是要有顯示每個商品的，從題目說的排序規則也可以看出來。

SELECT regist_date,
     SUM(sale_price) OVER(ORDER BY regist_date) AS sum_sale_price
FROM product;

是以就改成如下這樣，以每個日期為視窗（利用

PARTITION BY

），進行計算當天的銷售總額，注意這裡的銷售總額不會累加之前日期的銷售總額。

SELECT product_name,
       product_id,sale_price,regist_date,
       SUM(sale_price) OVER (PARTITION BY regist_date
                   ORDER BY regist_date) AS current_sum_price
FROM product;

5.3 思考題

① 視窗函數不指定

PARTITION BY

的效果是什麼？

【答】木有劃分視窗，整個表是一個視窗，拿剛才的5.2舉栗子，如果去掉

PARTITION BY

，結果變為按照日期排序，然後計算累加單價：

SELECT product_name,
       product_id,sale_price,regist_date,
       SUM(sale_price) OVER (ORDER BY regist_date) AS current_sum_price
FROM product;

② 為什麼說視窗函數隻能在

SELECT

子句中使用？實際上，在ORDER BY 子句使用系統并不會報錯。

【答】SQL執行順序：

【教奶奶學SQL】(task5)SQL進階處理（視窗函數 | ROLLUP）

學習總結

文章目錄

一、視窗函數

1.1 視窗函數概念及基本的使用方法

二、視窗函數種類

2.1 專用視窗函數

2.2 聚合函數在視窗函數上的使用

三、視窗函數的的應用 - 計算移動平均

3.1 視窗函數适用範圍和注意事項

四、GROUPING運算符

4.1 ROLLUP - 計算合計及小計

五、練習題

5.1 結果題

5.2 排序題

5.3 思考題

Reference

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案