窗口函數(shù)
窗口函數(shù)也稱為OLAP函數(shù)肥缔，全稱Online Analytical Processing，意為對(duì)數(shù)據(jù)庫(kù)進(jìn)行實(shí)時(shí)分析處理 汹来。
- 窗口函數(shù)的語(yǔ)法
語(yǔ)法：
<窗口函數(shù)> OVER ([PARTITION BY <列清單>]
ORDER BY <排序用清單列>)
其中重要的關(guān)鍵字是PARTITION和ORDER BY续膳，理解這兩個(gè)關(guān)鍵詞的作用是幫助理解窗口函數(shù)的關(guān)鍵。
能夠作為窗口函數(shù)使用的函數(shù)
窗口函數(shù)大致可以分為以下兩種：
1.能夠作為窗口函數(shù)的聚合函數(shù)（SUM收班、AVG坟岔、COUNT、MAX摔桦、MIN）社付；
2.RANK、DENSE_RANK、ROW_NUMBER等專用窗口函數(shù)瘦穆；
- 語(yǔ)法的基本使用方法——使用RANK函數(shù)
RANK函數(shù)如其名纪隙，是用來(lái)計(jì)算記錄排序的函數(shù)。
--各類商品按照銷售單價(jià)從低到高排序
SELECT shop_name,shop_type,sell_price,
RANK () OVER (PARTITION BY shop_type ORDER BY sell_price) AS ranking
FROM shop_list;
+-----------+-----------+------------+---------+
| shop_name | shop_type | sell_price | ranking |
+-----------+-----------+------------+---------+
| 砧板 | 廚房用具 | 2000 | 1 |
| 菜刀 | 廚房用具 | 6000 | 2 |
| 打孔器 | 工具 | 600 | 1 |
| 扳手 | 工具 | 3000 | 2 |
| T恤衫 | 衣服 | 2000 | 1 |
| 運(yùn)動(dòng)T恤 | 衣服 | 3000 | 2 |
+-----------+-----------+------------+---------+
PARTITION BY能夠設(shè)定排序的對(duì)象范圍扛或，示例中我們按照商品的種類進(jìn)行排序。
ORDER BY可以指定按照哪一列碘饼、哪一種順序進(jìn)行排列熙兔。為了按照銷售單價(jià)的升序進(jìn)行排列，我們指定了sell_price艾恼。
窗口函數(shù)中的ORDER BY可以通過(guò)在末尾加上關(guān)鍵字ASC/DESC來(lái)指定升序和降序住涉。省略該關(guān)鍵字默認(rèn)為升序。
- 無(wú)須指定PARTITION BY
使用窗口函數(shù)起到關(guān)鍵作用的是PARTITION BY和ORDER BY钠绍。其中舆声，PARTITION BY不是必須的，不指定也是可以正常使用窗口的柳爽。
--不使用PARTITION BY指定列的情況
SELECT shop_name,shop_type,sell_price,
RANK () OVER (ORDER BY sell_price) AS ranking
FROM shop_list;
+-----------+-----------+------------+---------+
| shop_name | shop_type | sell_price | ranking |
+-----------+-----------+------------+---------+
| 打孔器 | 工具 | 600 | 1 |
| T恤衫 | 衣服 | 2000 | 2 |
| 砧板 | 廚房用具 | 2000 | 2 |
| 運(yùn)動(dòng)T恤 | 衣服 | 3000 | 4 |
| 扳手 | 工具 | 3000 | 4 |
| 菜刀 | 廚房用具 | 6000 | 6 |
+-----------+-----------+------------+---------+
不使用PARTITION BY指定列媳握，變成了全部商品的排序。
- 專用窗口函數(shù)的種類
如上面排序結(jié)果有兩個(gè)第2位磷脯，因此下一位直接跳過(guò)第3位變成第4位蛾找。有些特殊情況下，可能需要1赵誓，2打毛，3，4這種不需要跳過(guò)的排序俩功，這時(shí)就需要RANK之外的函數(shù)來(lái)實(shí)現(xiàn)了幻枉。
RANK函數(shù)：計(jì)算排序時(shí)，如果存在相同的位次的記錄诡蜓，則會(huì)跳過(guò)位次熬甫。
DENSE_RANK函數(shù)：計(jì)算排序時(shí)，即使存在相同位次的記錄万牺，也不會(huì)跳過(guò)相同的位次罗珍。
ROW_NUMBER函數(shù)：賦予唯一的連續(xù)位次。
--比較三種函數(shù)的區(qū)別
SELECT shop_name,shop_type,sell_price,
RANK () OVER (ORDER BY sell_price) AS ranking,
DENSE_RANK () OVER (ORDER BY sell_price) AS dense_ranking,
ROW_NUMBER () OVER (ORDER BY sell_price) AS row_num
FROM shop_list;
+-----------+-----------+------------+---------+---------------+---------+
| shop_name | shop_type | sell_price | ranking | dense_ranking | row_num |
+-----------+-----------+------------+---------+---------------+---------+
| 打孔器 | 工具 | 600 | 1 | 1 | 1 |
| T恤衫 | 衣服 | 2000 | 2 | 2 | 2 |
| 砧板 | 廚房用具 | 2000 | 2 | 2 | 3 |
| 運(yùn)動(dòng)T恤 | 衣服 | 3000 | 4 | 3 | 4 |
| 扳手 | 工具 | 3000 | 4 | 3 | 5 |
| 菜刀 | 廚房用具 | 6000 | 6 | 4 | 6 |
+-----------+-----------+------------+---------+---------------+---------+
窗口函數(shù)的適用范圍
窗口函數(shù)只能在SELECT子句中使用脚粟，另外覆旱，這類函數(shù)不能在WHERE子句或者GROUP BY子句中使用。
因?yàn)榇翱诤瘮?shù)的目的是排序核无，當(dāng)我們使用WHERE去除了某些記錄或者使用GROUP BY對(duì)記錄進(jìn)行聚合處理扣唱，排序的結(jié)果就是錯(cuò)誤的，因此沒有任何意義。
所以噪沙，SELECT之外使用窗口函數(shù)是沒有意義的炼彪。作為窗口函數(shù)使用聚合函數(shù)
所有的聚合函數(shù)都能用在窗口函數(shù)，其語(yǔ)法和專用窗口函數(shù)完全相同正歼。
--將SUM函數(shù)作為窗口函數(shù)
SELECT shop_id,shop_name,sell_price,
SUM(sell_price) OVER (ORDER BY shop_id)AS current_sum
FROM shop_list;
+---------+-----------+------------+-------------+
| shop_id | shop_name | sell_price | current_sum |
+---------+-----------+------------+-------------+
| 0001 | T恤衫 | 2000 | 2000 |
| 0002 | 打孔器 | 600 | 2600 |
| 0003 | 運(yùn)動(dòng)T恤 | 3000 | 5600 |
| 0004 | 菜刀 | 6000 | 11600 |
| 0005 | 砧板 | 2000 | 13600 |
| 0006 | 扳手 | 3000 | 16600 |
+---------+-----------+------------+-------------+
- 計(jì)算移動(dòng)平均
窗口函數(shù)就是將表以窗口位單位進(jìn)行分割辐马，并在其中進(jìn)行排序的函數(shù)。其實(shí)其中還包括在窗口中指定更加詳細(xì)的統(tǒng)計(jì)范圍的備選功能局义。該備選功能中的統(tǒng)計(jì)范圍稱為"框架"喜爷。
--指定"最靠近的3行"作為統(tǒng)計(jì)對(duì)象
SELECT shop_id,shop_name,sell_price,
-> AVG(sell_price) OVER (ORDER BY shop_id ROWS 2 PRECEDING)AS moving_avg
-> FROM shop_list;
+---------+-----------+------------+------------+
| shop_id | shop_name | sell_price | moving_avg |
+---------+-----------+------------+------------+
| 0001 | T恤衫 | 2000 | 2000.0000 |
| 0002 | 打孔器 | 600 | 1300.0000 |
| 0003 | 運(yùn)動(dòng)T恤 | 3000 | 1866.6667 |
| 0004 | 菜刀 | 6000 | 3200.0000 |
| 0005 | 砧板 | 2000 | 3666.6667 |
| 0006 | 扳手 | 3000 | 3666.6667 |
+---------+-----------+------------+------------+
從編號(hào)0003的數(shù)據(jù)開始數(shù)據(jù)有所不同，我們制定了框架——指定"最靠近的3行"作為統(tǒng)計(jì)對(duì)象萄唇，這里使用了ROWS（行）和PRECEDING(之前）兩個(gè)關(guān)鍵字檩帐，將框架指定為"截止到之前~行"，因此ROWS 2 PRECEDING的意思就是截止到之前2行另萤。
由于框架是根據(jù)當(dāng)前記錄決定的湃密，所有和固定的窗口不一樣，其范圍會(huì)隨著當(dāng)前的記錄的變化而變化四敞。
自身（當(dāng)前記錄）→之前1行的記錄→之前2行的記錄
這樣的統(tǒng)計(jì)方法稱為移動(dòng)平均泛源。由于這種方法在希望實(shí)時(shí)把握最近狀態(tài)時(shí)非常方便，所以常常會(huì)應(yīng)用在對(duì)股市趨勢(shì)的實(shí)時(shí)跟蹤中目养。
使用關(guān)鍵字FOLLOWING(之后）替換PRECEDING俩由，就可以指定"截至到之后~行"作為框架了。
- 兩個(gè)ORDER BY
窗口函數(shù)中的ORDER BY只是對(duì)排序的序號(hào)進(jìn)行了排列癌蚁，那么如何對(duì)排序做降序呢幻梯？
很簡(jiǎn)單，在末尾加上ORDER BY子句就行了努释。
SELECT shop_id,shop_name,sell_price,
RANK() OVER (ORDER BY sell_price)AS ranking
FROM shop_list
ORDER BY ranking DESC;
+---------+-----------+------------+---------+
| shop_id | shop_name | sell_price | ranking |
+---------+-----------+------------+---------+
| 0004 | 菜刀 | 6000 | 6 |
| 0003 | 運(yùn)動(dòng)T恤 | 3000 | 4 |
| 0006 | 扳手 | 3000 | 4 |
| 0001 | T恤衫 | 2000 | 2 |
| 0005 | 砧板 | 2000 | 2 |
| 0002 | 打孔器 | 600 | 1 |
+---------+-----------+------------+---------+
GROUPING運(yùn)算符
- 同時(shí)計(jì)算出合計(jì)值
我們?cè)谑褂肎ROUP BY子句合計(jì)各個(gè)種類的總價(jià)的時(shí)候碘梢，會(huì)得到如下的表：
+-----------+-----------------+
| shop_type | sum(sell_price) |
+-----------+-----------------+
| 衣服 | 5000 |
| 工具 | 3600 |
| 廚房用具 | 8000 |
+-----------+-----------------+
但是如果想要將這幾個(gè)種類合計(jì)到一起，那么就需要UNION ALL將他們連接到一起了伐蒂。
SELECT '合計(jì)' AS shop_type,SUM(sell_price)
FROM shop_list
UNION ALL
SELECT shop_type,SUM(sell_price)
FROM shop_list
GROUP BY shop_type;
+-----------+-----------------+
| shop_type | sum(sell_price) |
+-----------+-----------------+
| 合計(jì) | 16600 |
| 衣服 | 5000 |
| 工具 | 3600 |
| 廚房用具 | 8000 |
+-----------+-----------------+
這樣雖然能計(jì)算出我們想要的結(jié)果煞躬，但是需要執(zhí)行兩次相同的SELECT語(yǔ)句，再將其結(jié)果連接逸邦，不僅繁瑣恩沛，而且DBMS內(nèi)部處理成本也非常高。因此缕减，我們需要試著找出其他更高效的方法雷客。
- ROLLUP——同時(shí)計(jì)算出合計(jì)值和小計(jì)值
為了滿足類似上面的要求，標(biāo)準(zhǔn)SQL引入了GROUPING運(yùn)算符桥狡，GROUPING運(yùn)算符主要包括以下幾種：
1.ROLLUP
2.CUBE
3.GROUPING SETS
ROLLUP的用法
SELECT shop_type,SUM(sell_price) AS sum_price
FROM shop_list
GROUP BY shop_type WITH ROLLUP;
+-----------+-----------+
| shop_type | sum_price |
+-----------+-----------+
| 廚房用具 | 8000 |
| 工具 | 3600 |
| 衣服 | 5000 |
| NULL | 16600 |
+-----------+-----------+
該運(yùn)算符的作用搅裙，就是"一次計(jì)算出不同聚合鍵的組合結(jié)果"皱卓，其計(jì)算結(jié)果默認(rèn)使用NULL作為聚合鍵。
將"登記日期"添加到聚合鍵中
--不使用ROLLUP的結(jié)果
SELECT shop_type,register_date,SUM(sell_price) AS sum_price
FROM shop_list
GROUP BY shop_type,register_date;
+-----------+---------------+-----------+
| shop_type | register_date | sum_price |
+-----------+---------------+-----------+
| 衣服 | 2009-09-20 | 5000 |
| 工具 | 2009-09-20 | 3600 |
| 廚房用具 | 2009-09-20 | 8000 |
+-----------+---------------+-----------+
--使用ROLLUP的結(jié)果
SELECT shop_type,register_date,SUM(sell_price) AS sum_price
FROM shop_list
GROUP BY shop_type,register_date WITH ROLLUP;
+-----------+---------------+-----------+
| shop_type | register_date | sum_price |
+-----------+---------------+-----------+
| 廚房用具 | 2009-09-20 | 8000 |
| 廚房用具 | NULL | 8000 | ←小計(jì)
| 工具 | 2009-09-20 | 3600 |
| 工具 | NULL | 3600 |
| 衣服 | 2009-09-20 | 5000 |
| 衣服 | NULL | 5000 |
| NULL | NULL | 16600 | ←合計(jì)
+-----------+---------------+-----------+
將兩次結(jié)果做比較之后部逮，發(fā)現(xiàn)使用ROLLUP多出了對(duì)每個(gè)類別的小計(jì)（使用了未登記日期作為聚合鍵）娜汁。
- GROUPING函數(shù)——讓NULL更加容易分辨
在使用ROLLUP的時(shí)候，出現(xiàn)了幾處NULL兄朋，當(dāng)日期本身為NULL時(shí)掐禁，那么NULL代表的意義就不太明確了。為了避免混淆颅和，SQL提供了一個(gè)用來(lái)判斷超級(jí)分組記錄為NULL的特定函數(shù)——GROUPING函數(shù)穆桂。該函數(shù)在其參數(shù)列的值為超級(jí)分組記錄所產(chǎn)生的NULL時(shí)返回1，其他情況返回0融虽。
SELECT GROUPING(shop_type)AS shop_type,GROUPING(register_date)AS register_date,SUM(sell_price)AS sum_price
FROM shop_list
GROUP BY shop_type,register_date WITH ROLLUP;
+-----------+---------------+-----------+
| shop_type | register_date | sum_price |
+-----------+---------------+-----------+
| 0 | 0 | 8000 |
| 0 | 1 | 8000 |
| 0 | 0 | 3600 |
| 0 | 1 | 3600 |
| 0 | 0 | 5000 |
| 0 | 1 | 5000 |
| 1 | 1 | 16600 | ←超級(jí)分組產(chǎn)生的NULL返回1
+-----------+---------------+-----------+
該表對(duì)應(yīng)上面ROLLUP產(chǎn)生的表，查找NULL的位置就能知道是不是超級(jí)分組產(chǎn)生的了灼芭。
使用GROUPING函數(shù)還能在超級(jí)分組記錄的鍵中插入字符串有额。也就是說(shuō)，當(dāng)GROUPING函數(shù)返回值為1時(shí)彼绷，指定”合計(jì)“和”小計(jì)“等字符串巍佑，其他情況返回正常的列的值。
SELECT CASE WHEN GROUPING(shop_type)=1
THEN '商品種類合計(jì)'
ELSE shop_type END AS shop_type,
CASE WHEN GROUPING(register_date)=1
THEN '登記日期合計(jì)'
ELSE register_date END AS register_date,
SUM(sell_price) AS sum_price
FROM shop_list
GROUP BY shop_type,register_date WITH ROLLUP;
+--------------+---------------+-----------+
| shop_type | register_date | sum_price |
+--------------+---------------+-----------+
| 廚房用具 | 2009-09-20 | 8000 |
| 廚房用具 | 登記日期合計(jì) | 8000 |
| 工具 | 2009-09-20 | 3600 |
| 工具 | 登記日期合計(jì) | 3600 |
| 衣服 | 2009-09-20 | 5000 |
| 衣服 | 登記日期合計(jì) | 5000 |
| 商品種類合計(jì) | 登記日期合計(jì) | 16600 |
+--------------+---------------+-----------+
CUBE——用數(shù)據(jù)搭積木
CUBE是立方體的意思寄悯，和ROLLUP一樣萤衰，生動(dòng)地說(shuō)明了其作用。CUBE和ROLLUP的語(yǔ)法一樣猜旬，只要將ROLLUP用CUBE代替就行了脆栋。GROUPING SETS——取得期望的積木
該運(yùn)算符可以從ROLLUP和CUBE結(jié)果中取得部分記錄（即GROUPING返回值為1的數(shù)據(jù)）。
