摘要：小時極速入門前面兩篇我們從的最基礎語法講起，到表聯結多表查詢。大家可以點擊鏈接查看小時極速入門一小時極速入門二今天我們講一些在做報表和復雜計算時非常實用的分析函數。就會得到每個分組內的按照訂單數量排序的行號。

前面兩篇我們從 SQL 的最基礎語法講起，到表聯結多表查詢。
大家可以點擊鏈接查看
1 小時 SQL 極速入門（一）
1 小時 SQL 極速入門（二）
今天我們講一些在做報表和復雜計算時非常實用的分析函數。由于各個數據庫函數的實現不太一樣，本文基于 Oracle 12c 。

這個函數在平時用的還是比較多的。這個函數的作用是為分組內的每一行返回一個行號。我們還是舉例來說明。
假設我們有以下數據表：

共 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 分組，然后我們按照訂單數量排序。就會得到每個分組內的按照訂單數量排序的行號。

假如我們現在要 查詢每個類型的訂單總數分別是多少,要怎么做？
大家可能會想到 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;

這個函數很有用，有時候在 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 ，把每個分組中的訂單描述字段連接起來。第一個參數表示要合并的字段名字，第二個參數表示分隔符。

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 行。

可以算一組值的中位數，傳入一個參數，比如傳入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中位數。