1 小時 SQL 極速入門 前面兩篇我們從 SQL 的最基礎語法講起，到表聯結多表查詢。 大家可以點擊鏈接查看 "1 小時 SQL 極速入門（一）" "1 小時 SQL 極速入門（二）" 今天我們講一些在做報表和複雜計算時非常實用的分析函數。由於各個資料庫函數的實現不太一樣，本文基於 Oracle ...
1 小時 SQL 極速入門
前面兩篇我們從 SQL 的最基礎語法講起，到表聯結多表查詢。
大家可以點擊鏈接查看
1 小時 SQL 極速入門（一）
1 小時 SQL 極速入門（二）
今天我們講一些在做報表和複雜計算時非常實用的分析函數。由於各個資料庫函數的實現不太一樣，本文基於 Oracle 12c 。
ROW_NUMBER()函數
這個函數在平時用的還是比較多的。這個函數的作用是為分組內的每一行返回一個行號。我們還是舉例來說明。
假設我們有以下數據表：
共 8 個訂單，分為 A,B,C,D四種類型，後面兩列是訂單描述和訂單數量。
假如我們現在想找到每個訂單類型中數量最少的一行記錄，比如想找到 A 類型訂單數量最少的，B 類型訂單數量最少的。。。
我們要怎麼寫呢 ? 用 GROUP BY 可能會很麻煩。這裡用 ROW_NUMBER() 就很合適
SELECT order_no,
order_type,
order_text,
order_qty,
row_number() OVER(PARTITION BY order_type order by order_qty) AS rowno
FROM wip_order_test
結果：
可以看到，每一行最後都有一個從低到高的編號，有了這個編號我們就可以通過取編號為 1 的行來得到每個分組中訂單數量最少的一行記錄。
解釋一下，ROW_NUMBER() 為每一行返回一個行號, partition by 表示分組，這裡表示根據 order_type 分組，然後我們按照訂單數量排序。就會得到每個分組內的按照訂單數量排序的行號。
SUM() OVER()函數
假如我們現在要 查詢每個類型的訂單總數分別是多少,要怎麼做？
大家可能會想到 GROUP BY，不過大家可以自己試試，是否能得到和我同樣的結果
SELECT order_no,
order_type,
order_text,
order_qty,
sum(order_qty) OVER(PARTITION BY order_type) AS sum_qty
FROM wip_order_test
結果：
看到後面多了一個數量列，就是每個分組的訂單總數量。是不是很方便？
除了 SUM 函數，其他幾個計算函數如 AVG(),MAX(),MIN(),COUNT()的使用方法和 SUM 一樣。
視窗函數
視窗函數可以對一個結果集內的一定範圍內值進行累積，或者通過移動視窗進行累積。還是看例子吧。
SELECT order_no,
order_type,
order_text,
order_qty,
sum(order_qty) OVER
(ORDER BY order_no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS cumulative_qty
FROM wip_order_test;
解釋一下：還是用 SUM 來計算總和，這裡我們使用了新的語法， ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定義了視窗的起點和終點，UNBOUNDED PRECEDING表示起點在第一行，CURRENT ROW 表示終點在當前行。我們看一下上圖的結果，能看到最後一列的值是逐行累加的。
移動視窗
上面我們的視窗的起點是固定的，終點逐漸往下移，我們可以創建一個固定大小的視窗，起點和終點同時往下移動。只需要修改 UNBOUNDED 為一個固定的數字就可以了。我們修改成 2， 和 3 分別看一下
SELECT order_no,
order_type,
order_text,
order_qty,
SUM(order_qty) OVER (ORDER BY order_no ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS cumulative_qty2,
SUM(order_qty) OVER (ORDER BY order_no ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS cumulative_qty3
FROM wip_order_test;
解釋下：倒數第二列我們修改視窗起點2，表示當前行與前兩行之間的範圍。可以看到每一行的值都是當前行與它前面兩行的值的累加。而最後一列，是當前行與它之前3行的值的累加。每處理一行，視窗的起點和終點都向下移動。
同理，SUM 也可以改為 AVG 求視窗的平均值
FIRST_VALUE() 和 LAST_VALUE()可以獲取視窗的第一行和最後一行，NTH_VALUE()可以獲取第 N 行。看一下例子：
SELECT order_no,
order_type,
order_text,
order_qty,
first_value(order_qty) OVER (ORDER BY order_no ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS first_value,
last_value(order_qty) OVER (ORDER BY order_no ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS last_value,
nth_value(order_qty,2) OVER (ORDER BY order_no ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS second_value
FROM wip_order_test;
LISTAGG() 函數
這個函數很有用，有時候在 GROUP BY 以後，我們想讓分組內的某一列的幾個值顯示在一行上，比如：
SELECT
order_type,
listagg(to_char(order_text),'-') WITHIN GROUP (ORDER BY order_type) AS text
FROM wip_order_test
GROUP BY order_type結果：
看到，通過 LISTAGG ，把每個分組中的訂單描述欄位連接起來。第一個參數表示要合併的欄位名字，第二個參數表示分隔符。
TOP-N 查詢
Oracle 12c中新增了對 TOP-N的支持。
SELECT order_no,
order_type,
order_text,
order_qty
FROM wip_order_test
FETCH FIRST 3 ROWS ONLY;
我們用 FETCH FIRST 3 取出了前 3 行數據，這裡也可以使用 FETCH FIRST 20 PERCENT ROWS ONLY 用百分比來取出前 20% 的數據。
還可以使用 OFFSET 關鍵字，來表示從第幾行開始取，比如 OFFSET 5 ROWS FETCH NEXT 3 ROWS ONLY 就表示從第 5 行開始往下取 3 行。
中位數 PERCENTILE_CONT()
可以算一組值的中位數，傳入一個參數，比如傳入0.5 表示 1/2 中位數，0.75 表示 3/4 中位數
SELECT order_type,
percentile_cont(0.5) WITHIN GROUP (
ORDER BY order_qty) AS A,
percentile_cont(0.75) WITHIN GROUP (
ORDER BY order_qty) AS b
FROM wip_order_test
GROUP BY order_type
我們根據訂單類型分組後，分別算出每種訂單類型數量的 1/2 中位數和 3/4中位數。
