1 小時 SQL 極速入門(三）

李英傑同學A發表於2019-02-22
SQL

1 小時 SQL 極速入門

前面兩篇我們從 SQL 的最基礎語法講起，到表聯結多表查詢。 大家可以點選連結檢視

1 小時 SQL 極速入門（一）

1 小時 SQL 極速入門（二）

今天我們講一些在做報表和複雜計算時非常實用的分析函式。由於各個資料庫函式的實現不太一樣，本文基於 Oracle 12c 。

  1. ROW_NUMBER()函式

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

ORDER.png

共 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

複製程式碼

結果：

ROW_NUMBER.png

可以看到，每一行最後都有一個從低到高的編號，有了這個編號我們就可以通過取編號為 1 的行來得到每個分組中訂單數量最少的一行記錄。

解釋一下，ROW_NUMBER() 為每一行返回一個行號, partition by 表示分組，這裡表示根據 order_type 分組，然後我們按照訂單數量排序。就會得到每個分組內的按照訂單數量排序的行號。

  1. 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.png

看到後面多了一個數量列，就是每個分組的訂單總數量。是不是很方便？

除了 SUM 函式，其他幾個計算函式如 AVG(),MAX(),MIN(),COUNT()的使用方法和 SUM 一樣。

  1. 視窗函式

視窗函式可以對一個結果集內的一定範圍內值進行累積，或者通過移動視窗進行累積。還是看例子吧。

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;

複製程式碼

視窗.png

解釋一下：還是用 SUM 來計算總和，這裡我們使用了新的語法， ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定義了視窗的起點和終點，UNBOUNDED PRECEDING表示起點在第一行，CURRENT ROW 表示終點在當前行。我們看一下上圖的結果，能看到最後一列的值是逐行累加的。

  1. 移動視窗

上面我們的視窗的起點是固定的，終點逐漸往下移，我們可以建立一個固定大小的視窗，起點和終點同時往下移動。只需要修改 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;
複製程式碼

移動視窗.png

解釋下：倒數第二列我們修改視窗起點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;

複製程式碼

FIRST_VALUE.png

  1. 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.png

看到，通過 LISTAGG ，把每個分組中的訂單描述欄位連線起來。第一個參數列示要合併的欄位名字，第二個參數列示分隔符。

  1. TOP-N 查詢

Oracle 12c中新增了對 TOP-N的支援。

SELECT order_no,
  order_type,
  order_text,
  order_qty
FROM wip_order_test
FETCH FIRST 3 ROWS ONLY;
複製程式碼

TOPN.png

我們用 FETCH FIRST 3 取出了前 3 行資料，這裡也可以使用 FETCH FIRST 20 PERCENT ROWS ONLY 用百分比來取出前 20% 的資料。

還可以使用 OFFSET 關鍵字，來表示從第幾行開始取，比如 OFFSET 5 ROWS FETCH NEXT 3 ROWS ONLY 就表示從第 5 行開始往下取 3 行。

  1. 中位數 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

複製程式碼

中位數.png

我們根據訂單型別分組後，分別算出每種訂單型別數量的 1/2 中位數和 3/4中位數。

相關文章