神奇的 SQL ，高階處理之 Window Functions → 打破我們的侷限！

開心一刻

　　今天兒子跟老婆聊天

　　兒子：媽媽，我為什麼沒有兩個爸爸呀

　　老婆：每個人都只有一個爸爸呀，你看誰有兩個爸爸了

　　兒子一臉真誠的看著老婆：那你為什麼就有兩個爸爸呢

　　老婆一臉疑惑的望向兒子：我哪有兩個爸爸了？

　　兒子有點不服氣，溫柔地說道：你管爺爺叫爸爸，你管姥爺還叫爸爸，這不就是兩個爸爸嗎

　　老婆輕聲解釋道：雖然我管他們兩個都叫爸爸，但是姥爺才是我的爸爸，爺爺是爸爸的爸爸，是我公公，明白了嗎

　　兒子兩眼朝天上看了下，若有所思道：公公不是太監嗎

　　老婆驚訝道：什麼太監呀，我說的公公和你說的公公不是一回事，你這一天天的腦子裡都想什麼呢

　　兒子生氣道：你不用解釋了，待會我就告訴奶奶，你說爺爺是太監

什麼是視窗函數

　　 Window Function 也稱為 OLAP（Online Analytical Processing）函數

　　對資料庫資料進行實時分析處理，例如市場分析、財務報表等，是標準的 SQL 功能

　　中文翻譯過來，叫視窗函數，或者開窗函數，在 Oracle 中也稱分析函數

　　與聚合函數一樣，也是對集合進行聚合計算，但和聚合函數又不一樣，使用聚合函數時，每組只返回一個值，但開窗函數可以為組中的每一行返回一個值

　　你們懂我說的意思吧

　　現在不懂也沒關係哈，繼續往下看，看完之後你肯定就懂了

　　支援情況

　　既然視窗函數是標準 SQL 功能，那關係型資料庫應該都支援吧

　　 Oracle 11g 、 SQL Server 2008 、 DB2 9.7 、 PostgreSQL 8.4 都支援視窗函數

　　但 MySQL 從 8 開始才支援， MySQL5.7 及之前的版本不支援視窗函數

　　關於對標準 SQL 的支援以及支援程度，還得看各個資料庫廠商，有的支援的早、支援的全，也有的支援的晚、支援的少

　　但隨著時間的推移，標準 SQL 終將能在所有的 DBMS 中使用

視窗函數的語法

　　基本語法如下

　　看著很簡單，但卻很陌生，我們將其進行拆分下

　　1、視窗函數，命名一般是見名知意，表明這個函數要實現的功能

　　2、 OVER 子句， OVER 是約定好的固定寫法，其內容是規則的指定，告訴視窗函數以怎樣的規則去實現功能

　　　　 PARTITION BY 類似 GROUP BY ，指定分組規則

　　　　 ORDER BY 就跟我們平時使用的 ORDER BY 一樣，指定排序規則

　　看完這個語法介紹，我相信大家還是很懵，我非常理解大家

　　但先別慌，結合案例來看，慢慢就懂了

　　能夠作為視窗函數使用的函數分兩種

　　1、專用視窗函數，如： RANK 、 ROW_NUMBER 、 DENSE_RANK 等等

　　2、能夠作為視窗函數的聚合函數，如： SUM 、 AVG 、 COUNT 、 MAX 、 MIN

　　後續的案例演示我們基於 MySQL8.0.30 ，初始表 tbl_ware 及資料如下

CREATE TABLE `tbl_ware` (
  `ware_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `ware_name` VARCHAR(100) NOT NULL COMMENT '商品名稱',
  `ware_category` VARCHAR(100) NOT NULL COMMENT '商品類別',
    `sale_unit_price` INT COMMENT '銷售單價',
    `purchase_unit_price` INT COMMENT '進貨單價',
    `registration_date` DATE COMMENT '等級日期',
  PRIMARY KEY (`ware_id`) USING BTREE
) ENGINE=InnoDB COMMENT='產品';

INSERT INTO tbl_ware VALUES
(1,'T恤衫','衣服',100, 50,'2023-12-15'),
(2,'打孔器','辦公用品',25, 10,'2023-12-15'),
(3,'運動T恤','衣服',150, 50,'2023-12-15'),
(4,'菜刀','廚房用具',75, 30,'2023-12-15'),
(5,'高壓鍋','廚房用具',600, 200,'2023-12-15'),
(6,'叉子','廚房用具',7, 3,'2023-12-15'),
(7,'菜板','廚房用具',98, 30,'2023-12-15'),
(8,'圓珠筆','辦公用品',5, 2,'2023-12-15');

View Code

專用視窗函數

　　這些函數是標準 SQL 定義的 OLAP 專用函數，通過函數名很容易看出其 OLAP 的用途

　　RANK

　　從名字可知，該函數用來排名、排序

　　1、假設我們對 tbl_ware 按售價從高到低進行排名， SQL 該如何寫

　　相信大家很容易就寫出來了： SELECT * FROM tbl_ware ORDER BY sale_unit_price DESC;

　　用 RANK 也能實現： SELECT *, RANK() OVER(ORDER BY sale_unit_price DESC) AS ranking FROM tbl_ware;

　　2、假設我們對 tbl_ware 按類別進行分組，然後組內按售價從高到低進行排名， SQL 又該如何寫

　　有小夥伴一看到分組二字，第一反應肯定想到了 GROUP BY ，不只是你們，我也是一樣的

　　但 GROUP BY 往往結合聚合函數使用，分組後每組只能得到一個值，顯然滿足不了需求

　　但 RANK 可以： SELECT *, RANK() OVER(PARTITION BY ware_category ORDER BY sale_unit_price DESC) AS ranking FROM tbl_ware;

　　 PARTITION BY 對錶的橫向進行分組，類似 GROUP BY ，但不具備聚合功能

　　 ORDER BY 則決定了縱向排序的規則，與 SELECT 子句末尾的 ORDER BY 子句完全相同

　　通過 PARTITION BY 分組後的記錄集合稱為「視窗」，代表「範圍」。這也是視窗函數名稱的由來

　　DENSE_RANK

　　一看名字就知道跟 RANK 有關係，為了對比它倆的區別，需要補充幾條資料

INSERT INTO tbl_ware VALUES
(9,'帶帽衛衣','衣服', 150, 90, '2023-12-15'),
(10,'砍骨刀','廚房用具', 150, 69, '2023-12-15');

View Code

　　 RANK 排序時，如果存在相同位次的記錄，會跳過之後的位次，如： 1,2,2,2,5 ， 3,4 被跳過了

　　 DENSE_RANK 排序時，如果存在相同位次的記錄，則不會跳過之後的位次，如： 1,2,2,2,3,4

　　ROW_NUMBER

　　獲取行數或者行號

　　如果我們想按售價從高到低排序後，獲取每一行的行號， SQL 可寫成： SELECT *, ROW_NUMBER() OVER(ORDER BY sale_unit_price DESC) AS row_num FROM tbl_ware;

　　如果再加上一個分組： SELECT *, ROW_NUMBER() OVER(PARTITION BY ware_category ORDER BY sale_unit_price DESC) AS row_num FROM tbl_ware;

　　此刻大家應該想起點什麼了

　　發現什麼了？

　　並不是一個單獨的彙總值，而是逐行彙總，是不是有點意思？

　　如果再加上分組

　　分組後，對每一組進行逐行彙總

　　AVG

　　類比 SUM ，我們直接看分組的情況

　　分組後，對每一組的每一行求歷史平均值

　　其他聚合函數的視窗化就不一一演示了，相信大家也都明白了

視窗函數的適用範圍

　　通過上述的幾個案例，相信大家對這個問題已經有了一個大致的答案

　　視窗函數只能在 SELECT 子句中使用，不能在 WHERE 子句或者 GROUP BY 子句中使用，為什麼了？

　　因為視窗函數是對 WHERE 子句或者 GROUP BY 子句處理後的「結果」進行的逐行操作

　　我們換個角度來看，視窗函數是不會改變結果行數的，而 WHERE 是會改變結果行數的，那把視窗函數放到 WHERE 子句的意義何在？

　　所以一不做二不休，直接在語法上做了這樣的限制：視窗函數只能在 SELECT 子句中使用

總結

　　1、視窗函數是標準的 SQL 功能，而非特定資料庫的功能

　　　　 SQL 功能的落地還得依賴各個資料庫廠商

　　　　提供了標準，資料庫廠商不一定實現，或者說暫時不實現

　　2、視窗函數與聚合函數並非矛盾，二者是互補關係

　　3、之所以對視窗函數這麼陌生，主要是我們使用太少，但是其在報表分析方面還是很有作用的

　　4、視窗函數的使用範圍很有限，你可以隨意使用，報語法錯誤了再調整唄

參考

　　《SQL 基礎教學》