摘要：小時(shí)極速入門前面兩篇我們從的最基礎(chǔ)語(yǔ)法講起，到表聯(lián)結(jié)多表查詢。大家可以點(diǎn)擊鏈接查看小時(shí)極速入門一小時(shí)極速入門二今天我們講一些在做報(bào)表和復(fù)雜計(jì)算時(shí)非常實(shí)用的分析函數(shù)。就會(huì)得到每個(gè)分組內(nèi)的按照訂單數(shù)量排序的行號(hào)。

前面兩篇我們從 SQL 的最基礎(chǔ)語(yǔ)法講起，到表聯(lián)結(jié)多表查詢。
大家可以點(diǎn)擊鏈接查看
1 小時(shí) SQL 極速入門（一）
1 小時(shí) SQL 極速入門（二）
今天我們講一些在做報(bào)表和復(fù)雜計(jì)算時(shí)非常實(shí)用的分析函數(shù)。由于各個(gè)數(shù)據(jù)庫(kù)函數(shù)的實(shí)現(xiàn)不太一樣，本文基于 Oracle 12c 。

這個(gè)函數(shù)在平時(shí)用的還是比較多的。這個(gè)函數(shù)的作用是為分組內(nèi)的每一行返回一個(gè)行號(hào)。我們還是舉例來(lái)說(shuō)明。
假設(shè)我們有以下數(shù)據(jù)表：

共 8 個(gè)訂單，分為 A,B,C,D四種類型，后面兩列是訂單描述和訂單數(shù)量。

假如我們現(xiàn)在想找到每個(gè)訂單類型中數(shù)量最少的一行記錄，比如想找到 A 類型訂單數(shù)量最少的，B 類型訂單數(shù)量最少的。。。
我們要怎么寫呢 ? 用 GROUP BY 可能會(huì)很麻煩。這里用 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

結(jié)果：

可以看到，每一行最后都有一個(gè)從低到高的編號(hào)，有了這個(gè)編號(hào)我們就可以通過(guò)取編號(hào)為 1 的行來(lái)得到每個(gè)分組中訂單數(shù)量最少的一行記錄。

解釋一下，ROW_NUMBER() 為每一行返回一個(gè)行號(hào), partition by 表示分組，這里表示根據(jù) order_type 分組，然后我們按照訂單數(shù)量排序。就會(huì)得到每個(gè)分組內(nèi)的按照訂單數(shù)量排序的行號(hào)。

假如我們現(xiàn)在要 查詢每個(gè)類型的訂單總數(shù)分別是多少,要怎么做？
大家可能會(huì)想到 GROUP BY，不過(guò)大家可以自己試試，是否能得到和我同樣的結(jié)果

SELECT order_no,
  order_type,
  order_text,
  order_qty,
  sum(order_qty) OVER(PARTITION BY order_type) AS sum_qty
FROM wip_order_test

結(jié)果：

看到后面多了一個(gè)數(shù)量列，就是每個(gè)分組的訂單總數(shù)量。是不是很方便？

除了 SUM 函數(shù)，其他幾個(gè)計(jì)算函數(shù)如 AVG(),MAX(),MIN(),COUNT()的使用方法和 SUM 一樣。

窗口函數(shù)可以對(duì)一個(gè)結(jié)果集內(nèi)的一定范圍內(nèi)值進(jìn)行累積，或者通過(guò)移動(dòng)窗口進(jìn)行累積。還是看例子吧。

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 來(lái)計(jì)算總和，這里我們使用了新的語(yǔ)法， ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定義了窗口的起點(diǎn)和終點(diǎn)，UNBOUNDED PRECEDING表示起點(diǎn)在第一行，CURRENT ROW 表示終點(diǎn)在當(dāng)前行。我們看一下上圖的結(jié)果，能看到最后一列的值是逐行累加的。

上面我們的窗口的起點(diǎn)是固定的，終點(diǎn)逐漸往下移，我們可以創(chuàng)建一個(gè)固定大小的窗口，起點(diǎn)和終點(diǎn)同時(shí)往下移動(dòng)。只需要修改 UNBOUNDED 為一個(gè)固定的數(shù)字就可以了。我們修改成 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;

解釋下：倒數(shù)第二列我們修改窗口起點(diǎn)2，表示當(dāng)前行與前兩行之間的范圍。可以看到每一行的值都是當(dāng)前行與它前面兩行的值的累加。而最后一列，是當(dāng)前行與它之前3行的值的累加。每處理一行，窗口的起點(diǎn)和終點(diǎn)都向下移動(dòng)。

同理，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;

這個(gè)函數(shù)很有用，有時(shí)候在 GROUP BY 以后，我們想讓分組內(nèi)的某一列的幾個(gè)值顯示在一行上，比如：

SELECT 
  order_type,
  listagg(to_char(order_text),"-") WITHIN GROUP (ORDER BY order_type) AS text
FROM wip_order_test
GROUP BY order_type

結(jié)果：

看到，通過(guò) LISTAGG ，把每個(gè)分組中的訂單描述字段連接起來(lái)。第一個(gè)參數(shù)表示要合并的字段名字，第二個(gè)參數(shù)表示分隔符。

Oracle 12c中新增了對(duì) TOP-N的支持。

SELECT order_no,
  order_type,
  order_text,
  order_qty
FROM wip_order_test
FETCH FIRST 3 ROWS ONLY;

我們用 FETCH FIRST 3 取出了前 3 行數(shù)據(jù)，這里也可以使用 FETCH FIRST 20 PERCENT ROWS ONLY 用百分比來(lái)取出前 20% 的數(shù)據(jù)。

還可以使用 OFFSET 關(guān)鍵字，來(lái)表示從第幾行開(kāi)始取，比如 OFFSET 5 ROWS FETCH NEXT 3 ROWS ONLY 就表示從第 5 行開(kāi)始往下取 3 行。

可以算一組值的中位數(shù)，傳入一個(gè)參數(shù)，比如傳入0.5 表示 1/2 中位數(shù)，0.75 表示 3/4 中位數(shù)

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

我們根據(jù)訂單類型分組后，分別算出每種訂單類型數(shù)量的 1/2 中位數(shù)和 3/4中位數(shù)。