8 SQL高級(jí)處理

8-1窗口函數(shù)

8-1-1什么是窗口函數(shù)

窗口函數(shù)也稱為 OLAP 函數(shù)遣鼓。OLAP是OnLine Analytical Processing的簡稱大审，意思是對(duì)數(shù)據(jù)庫數(shù)據(jù)進(jìn)行實(shí)時(shí)分析處理坐昙。窗口函數(shù)就是為了實(shí)現(xiàn) OLAP 而添加的標(biāo)準(zhǔn) SQL 功能博杖。

窗口函數(shù)大體可以分為以下兩種：
1.能夠作為窗口函數(shù)的聚合函數(shù)(SUM蛾找、AVG烤黍、COUNT续担、MAX擅耽、MIN)
2.RANK、DENSE_RANK物遇、ROW_NUMBER 等專用窗口函數(shù)

8-1-2語法的基本使用方法--使用RANK函數(shù)

根據(jù)不同的商品種類乖仇，按照銷售單價(jià)從低到高的順序創(chuàng)建排序表:

8-1.png

PARTITION BY能夠設(shè)定排序的對(duì)象范圍。本例中询兴，為了按照商品種類進(jìn)行排序乃沙，我們指定了 product_type。
ORDER BY能夠指定按照哪一列诗舰、何種順序進(jìn)行排序警儒。為了按照銷售單價(jià)的升序進(jìn)行排列，我們指定了 sale_price眶根。省略該關(guān)鍵字時(shí)會(huì)默認(rèn)按照 ASC蜀铲，也就是升序進(jìn)行排序。
通過PARTITION BY分組后的記錄集合稱為窗口属百。

8-1-3無需指定PARTITION BY

8-2.png

當(dāng)不指定PARTITION BY函數(shù)時(shí)记劝，排序變成了全部商品的排序。當(dāng)希望先將表中的數(shù)據(jù)分為多個(gè)部分(窗口)族扰，再使 用窗口函數(shù)時(shí)厌丑，可以使用PARTITION BY選項(xiàng)。

8-1-4專用窗口函數(shù)的種類

排序函數(shù)的種類：

• 1.RANK 函數(shù)
  計(jì)算排序時(shí)渔呵，如果存在相同位次的記錄怒竿，則會(huì)跳過之后的位次。 例)有 3 條記錄排在第 1 位時(shí):1 位厘肮、1 位愧口、1 位、4 位......
• 2.DENSE_RANK 函數(shù)
  同樣是計(jì)算排序类茂，即使存在相同位次的記錄耍属，也不會(huì)跳過之后的位次托嚣。 例)有 3 條記錄排在第 1 位時(shí):1 位、1 位厚骗、1 位示启、2 位......
• 3.ROW_NUMBER 函數(shù)
  賦予唯一的連續(xù)位次。
  例)有 3 條記錄排在第 1 位時(shí):1 位领舰、2 位夫嗓、3 位、4 位......

來看看這幾種排序的結(jié)果有什么不同：

8-3.png

8-1-5窗口函數(shù)的適用范圍

使用窗口函數(shù)的位置有非常大的限制：
窗口函數(shù)只能在 SELECT 子句中使用(也就是不能在 WHERE 子句或者 GROUP BY 子句中使用)

8-1-6作為窗口函數(shù)使用的聚合函數(shù)

“按照商品id排序后計(jì)算累計(jì)的商品價(jià)格總量”

8-4.png

8-5.png

“按照用戶id排序以后計(jì)算累計(jì)的商品價(jià)格平均值”

8-1-7計(jì)算移動(dòng)平均

窗口函數(shù)就是將表以窗口為單位進(jìn)行分割冲秽，并在其中進(jìn)行排序的函數(shù)舍咖。 其實(shí)其中還包含在窗口中指定更加詳細(xì)的匯總范圍的備選功能，該備選功能中的匯總范圍稱為框架锉桑。
“指定最靠近的三行做平均”：

8-6.png

指定框架（匯總范圍）

這里我們使用了 ROWS(“行”)和 PRECEDING(“之前”)兩個(gè)關(guān)鍵字排霉，將框架指定為“截止到之前 ~ 行”，這樣的統(tǒng)計(jì)方法被稱為移動(dòng)平均民轴。
使用關(guān)鍵字 FOLLOWING(“之后”)替換 PRECEDING攻柠，就可以指定“截止到之后 ~ 行”作為框架了。

將當(dāng)前記錄的前后行作為匯總對(duì)象

8-7.png

8-2 GROUPING運(yùn)算符

8-2-1同時(shí)得到合計(jì)行

8-8.png

希望得到上圖所示的表格后裸，首先試試只用GROUP BY和SUM能不能實(shí)現(xiàn)：

8-9.png

結(jié)果是分組計(jì)算和的瑰钮，但是和我們想要的結(jié)果相比，沒有合計(jì)那一行微驶。
通常的辦法是分別計(jì)算出合計(jì)行和匯總結(jié)果再通過UNION ALL進(jìn)行連接：

8-10.png

8-2-2ROLLUP--同時(shí)得出合計(jì)和小計(jì)

GROUPING運(yùn)算符包含以下三種：
● ROLLUP
● CUBE
● GROUPING SETS

ROLLUP的使用方法

8-11.png

ROLLUP運(yùn)算符的作用祈搜，一言以蔽之较店，就 是“一次計(jì)算出不同聚合鍵組合的結(jié)果”。例如容燕，在本例中就是一次計(jì)算出了如下兩種組合的匯總結(jié)果:

1. GROUP BY () --又稱為超級(jí)分組記錄，計(jì)算全部數(shù)據(jù)的合計(jì)行
2. GROUP BY (product_type)

將“登記日期”添加到聚合鍵中

在GROUP BY中添加“登記日期”(不使用ROLLUP):

8-12.png

在GROUP BY中添加“登記日期”(使用ROLLUP):

8-13.png

使用ROLLUP相當(dāng)于UNION了以下三行的結(jié)果：
GROUP BY ()
GROUP BY (product_type)
GROUP BY (product_type, regist_date)

ROLLUP 可以同時(shí)得出合計(jì)和小計(jì)蝗茁，是非常方便的工具醋虏。

8-2-3 GROUPING函數(shù)--讓NULL更加容易分辨

仔細(xì)觀察圖8-13，可以發(fā)現(xiàn)一個(gè)不合理的地方哮翘，由于第二行的時(shí)間記錄為空值颈嚼，所以regist_date沒有顯示。但是最后我們合計(jì)衣服的結(jié)果（倒數(shù)第二行）也是無日期的饭寺，所以regist_date也為空阻课。兩行可能會(huì)造成誤解叫挟。解決這個(gè)的辦法是需要顯示某行的日期數(shù)值是否為空值。

使用 GROUPING 函數(shù)來判斷 NULL

8-14.png

這樣就能分辨超級(jí)分組記錄中的 NULL 和原始數(shù)據(jù)本身的 NULL 了限煞。
使用 GROUPING 函數(shù)還能在超級(jí)分組記錄的鍵值中插入字符串抹恳。

8-15.png

8-2-4CUBE--用數(shù)據(jù)來搭積木

使用CUBE取得全部組合的結(jié)果

8-16.png

與 ROLLUP 的結(jié)果相比，CUBE 的結(jié)果中多出了幾行把regist_date作為聚合鍵所獲得的匯總結(jié)果署驻。相當(dāng)于CUBE的結(jié)果是下列幾行：

1.GROUP BY ()
2.GROUP BY (product_type)
3.GROUP BY (regist_date) ←添加的組合
4.GROUP BY (product_type, regist_date)

8-2-5GROUPING SETS--取得期望的積木

之前的 CUBE 的結(jié)果就是根據(jù)聚合鍵的所有可能的組合計(jì)算而來的奋献。如果希望從中選取出將“商品種類”和“登記日期”各自作為聚合鍵的結(jié)果，或者不想得到“合計(jì)記錄和使用 2 個(gè)聚合鍵的記錄”時(shí)旺上，可 以使用GROUPING SETS瓶蚂。

8-17.png

習(xí)題8.2

8-18.png

不知道regist_date為NULL值是怎么弄到最前面？
答案有兩種：

8-19.png