跳轉到內容

結構化查詢語言/按組檢索前 N 行

Add links
來自 Wikibooks，開放世界中的開放書籍
事務 結構化查詢語言
按組檢索前 N 行 		消除重複


"技術從原始發展到複雜，再到簡單。" (安託萬·德·聖-埃克蘇佩裡)

挑戰

[編輯 | 編輯原始碼]

通常需要訪問給定列每個唯一值的第一個或前 n 行：產品組內最便宜的產品（= 第一行）（= 給定列的唯一值）、歷史表中每個實體版本號最高的行、每個使用者最新的 10 條日誌條目，...。在 SQL 世界中，這是一個三步工作：a) 按給定列對錶進行分組 b) 根據標準對建立的組內的行進行排序，以及 c) 訪問建立的已排序組內的第一行或前 n 行。

在像這樣複雜的情況下，SQL 並不只提供一種解決方案。有多種公式可以獲得預期結果。在邏輯層面上，它們是等價的，但它們的效能很可能彼此之間有很大差異。而且，同一公式的效能在不同的資料庫系統上很可能會有很大差異。效能偏差的原因是，SQL 通常只定義系統應該做什麼，而沒有定義應該怎麼做。找到最佳執行計劃是資料庫系統的責任。

我們提供了一些可能的解決方案——從原始到複雜再到簡單。它們包括子查詢、聯接、FETCH FIRST 子句、謂詞的使用，以及最後作為首選方法的視窗函式。

示例表和資料

[編輯 | 編輯原始碼]

我們使用示例表product，其中包含少量資料行，來討論不同的策略。

CREATE TABLE product (
  id             INTEGER      NOT NULL,
  name           VARCHAR(50)  NOT NULL,
  product_group  VARCHAR(20)  NOT NULL,
  prize          DECIMAL(5,2)         ,
  CONSTRAINT product_pk PRIMARY KEY (id)
);

INSERT INTO product VALUES ( 1, 'Big Fat One'    , 'Desktop',    545);
INSERT INTO product VALUES ( 2, 'SmartAndElegant', 'Laptop',     675);
INSERT INTO product VALUES ( 3, 'Angle',           'Laptop',     398);
INSERT INTO product VALUES ( 4, 'Wizzard 7',       'Smartphone', 380);
INSERT INTO product VALUES ( 5, 'Solid',           'Desktop',    565);
INSERT INTO product VALUES ( 6, 'AllRounder',      'Smartphone', 535);
INSERT INTO product VALUES ( 7, 'WhiteHorse',      'Laptop',     675);
INSERT INTO product VALUES ( 8, 'Workstation ONE', 'Desktop',    499);
INSERT INTO product VALUES ( 9, 'Air',             'Laptop',     450);
INSERT INTO product VALUES (10, 'Rusty',           'Laptop',     390);
INSERT INTO product VALUES (11, 'Tripple-A',       'Desktop',    580);
INSERT INTO product VALUES (12, 'Oxygen 8',        'Smartphone', 450);
INSERT INTO product VALUES (13, 'AllDay Basic',    'Smartphone',  75);
COMMIT;

使用這種結構和資料，我們將嘗試訪問每個產品組中價格最高的行。

不充分的解決方案

[編輯 | 編輯原始碼]

示例 1

[編輯 | 編輯原始碼]

第一個解決方案僅使用GROUP BY 子句，並以兩種方式簡化問題：a) 透過使用 max() 或 min() 函式，它只提供每個組的第一行（忽略第二好、第三好等行），以及 b) 該解決方案只能訪問分組標準和 max() / min() 的結果。然而，由於GROUP BY 子句的性質，所有剩餘的列都無法訪問——請參閱此處。

SELECT product_group, MAX(prize)
FROM   product
GROUP BY product_group;


product_group | max
--------------+-----
Smartphone    | 535
Desktop       | 580
Laptop        | 675

-- access to other columns is not possible
SELECT *
FROM   product
GROUP BY product_group;

示例 2

[編輯 | 編輯原始碼]

我們可以透過將第一個解決方案與相關或非相關子查詢結合起來，擴充套件第一個解決方案以顯示更多列。這個第二個解決方案提供了對所有列的訪問。然而，結果並非我們預期的那樣，因為訪問的行數為 4。MAX(prize) 標準不一定是唯一的。因此，我們從示例表中獲得了 3 個組的 4 行。而且——如上所述——我們無法訪問價格第二高的行。

-- SELECT with a non-correlated subquery. The subquery is executed only once.
SELECT *
FROM   product
WHERE  prize IN      -- prize is a very weak criterion
  (SELECT MAX(prize)
   FROM   product
   GROUP BY product_group
  )
;

id |      name       | product_group | prize
---+-----------------+---------------+-------
11 | Tripple-A       | Desktop       |   580
 2 | SmartAndElegant | Laptop        |   675
 7 | WhiteHorse      | Laptop        |   675
 6 | AllRound        | Smartphone    |   535


-- SELECT with a correlated subquery. Observe the performance! The subquery is executed
-- once per row of p1 !!!
SELECT *
FROM   product p1
WHERE  prize IN      -- prize is a very weak criterion
  (SELECT MAX(prize)
   FROM   product p2
   WHERE  p1.product_group = p2.product_group
  )
;

id |      name       | product_group | prize
---+-----------------+---------------+-------
11 | Tripple-A       | Desktop       |   580
 2 | SmartAndElegant | Laptop        |   675
 7 | WhiteHorse      | Laptop        |   675
 6 | AllRound        | Smartphone    |   535

這些方法存在問題。如果只使用GROUP BY 子句，則不會顯示完整的列和行集。如果GROUP BY 放入子查詢中，則會顯示所有列，但如果滿足標準的行不止一行，則會顯示同一列的多行。

示例 3

[編輯 | 編輯原始碼]

對於第三個解決方案也是如此。可以建立對 product_group 的JOIN，並使用HAVING 子句將結果行縮減到組內價格最高的那些行。結果與第二個解決方案相同。

SELECT p1.*
FROM   product p1
JOIN   product p2 ON (p1.product_group = p2.product_group)
GROUP BY p1.id, p1.name, p1.product_group, p1.prize
HAVING p1.prize = MAX(p2.prize)
;

id |      name       | product_group | prize
---+-----------------+---------------+-------
 7 | WhiteHorse      | Laptop        |   675
 2 | SmartAndElegant | Laptop        |   675
11 | Tripple-A       | Desktop       |   580
 6 | AllRound        | Smartphone    |   535

示例 4

[編輯 | 編輯原始碼]

作為第四個解決方案，我們提供最後一個示例，說明如何表達相同的問題——結果同樣不完美。它使用NOT EXISTS 謂詞來搜尋那些在其組內沒有更高價格的行。

SELECT *
FROM   product p1
WHERE NOT EXISTS
  (SELECT *
   FROM  product p2
   WHERE p1.product_group = p2.product_group
   AND   p1.prize < p2.prize
  )
;

複雜解決方案

[編輯 | 編輯原始碼]

為了克服上述缺點，我們進行了 2 項調整。首先，兩個SELECT 之間的連結（透過聯接或子查詢）必須更改為具有唯一值的列。我們將使用 ID 列。其次，我們必須將FETCH FIRST 子句與ORDER BY 子句結合使用來計算行數。

示例 5

[編輯 | 編輯原始碼]

首先，我們展示了對上方的第二個解決方案的修改。ORDER BY 子句將行排序到所需的順序。FETCH FIRST 子句將每個組的結果行限制為任何所需數量。子查詢的結果是一個 ID 列表。因為 ID 是示例表中唯一的標準，所以外部SELECT 檢索了完全預期的行——以及它們的所有列。

-- modification of the second solution (correlated subquery)
SELECT *
FROM   product p1
WHERE  id IN
  (SELECT id
   FROM   product p2
   WHERE  p1.product_group = p2.product_group
   ORDER BY prize DESC
   FETCH FIRST 1 ROW ONLY    -- replace "ONLY" with "WITH TIES" to include rows with identical prize at the cutting edge
  )
;

id |      name       | product_group | prize
---+-----------------+---------------+-------
11 | Tripple-A       | Desktop       |   580
 2 | SmartAndElegant | Laptop        |   675
 6 | AllRound        | Smartphone    |   535

示例 6

[編輯 | 編輯原始碼]

接下來，我們使用JOIN LATERAL子句，它類似於相關子查詢，允許將先前命名的表及其列作為連結到後面命名的表及其列。在此示例中，p1 的每一行都與同一組（p1.product_group = p2.product_group）中 p2 的第一行（FETCH FIRST）連線。p2 的結果列會使用名稱 p3 傳播到查詢的外部部分。最後，連線透過 id（ON p1.id = p3.id）進行。p2/p3 別名僅檢索每組最高價格的行，因此它們成為結果。

SELECT p3.*
FROM   product p1
JOIN LATERAL (SELECT *
              FROM   product p2
              WHERE  p1.product_group = p2.product_group
              ORDER BY p2.prize DESC
              FETCH FIRST 1 ROW ONLY
             ) p3 ON p1.id = p3.id
;

視窗函式

[編輯 | 編輯原始碼]

視窗函式 提供了一套非常靈活且豐富的功能。它們作用於（中間）結果集的多行，像“視窗”一樣“滑動”並在視窗中看到的行上生成結果。

它們由兩部分組成：所需函式的名稱以及“滑動視窗”的定義，例如：SELECT row_number() OVER () as rownum ...。在這種情況下，函式名稱為“row_number()”，視窗定義“OVER ()”保持為空，這會導致一個所有行可見的視窗。顧名思義，該函式計算視窗內的行數。

在我們“每組 n 行”的情況下，我們必須定義作用於行組（在GROUP BY子句的意義上）的視窗。為此，我們將視窗定義擴充套件為OVER (PARTITION BY product_group ... )並獲得每組的計數器

示例 7

[編輯 | 編輯原始碼]
SELECT product.*, row_number() OVER (PARTITION BY product_group ORDER BY id) as row_number
FROM   product;

 id |      name       | product_group | prize  | row_number
----+-----------------+---------------+--------+------------
  1 | Big Fat One     | Desktop       | 545    |          1
  5 | Solid           | Desktop       | 565    |          2
  8 | Workstation ONE | Desktop       | 499    |          3
 11 | Tripple-A       | Desktop       | 580    |          4
  2 | SmartAndElegant | Laptop        | 675    |          1
  3 | Angle           | Laptop        | 398    |          2
  7 | WhiteHorse      | Laptop        | 675    |          3
  9 | Air             | Laptop        | 450    |          4
 10 | Rusty           | Laptop        | 390    |          5
  4 | Wizzard 7       | Smartphone    | 380    |          1
  6 | AllRounder      | Smartphone    | 535    |          2
 12 | Oxygen 8        | Smartphone    | 450    |          3
 13 | AllDay Basic    | Smartphone    |  75    |          4

現在 row_number 分別從每個組的“1”值開始分割槽。我們可以透過按需要對行進行排序並透過在外部WHERE子句中查詢此 row_number 來限制結果行到任何所需數量，利用這種行為。


示例 8

[編輯 | 編輯原始碼]

由於視窗函式不能在WHERE子句中使用，因此我們必須在巢狀在另一個 SELECT 中的 SELECT 中使用它。外部 SELECT 將最後檢索到的行數減少到每組一行，即具有最高價格的行，因為OVER ()子句包含一個ORDER BY。

SELECT tmp.*
FROM
  (SELECT product.*, row_number() OVER (PARTITION BY product_group ORDER BY prize DESC) AS rownumber_per_group
   FROM   product
  ) tmp
WHERE  rownumber_per_group < 2
;

 id |    name    | product_group | prize  | rownumber_per_group
----+------------+---------------+--------+---------------------
 11 | Tripple-A  | Desktop       | 580    |                   1
  7 | WhiteHorse | Laptop        | 675    |                   1
  6 | AllRounder | Smartphone    | 535    |                   1

您可以輕鬆地修改此解決方案以增加檢索到的行數或整合其他視窗函式 - 例如，如果您使用 rank() 而不是 row_number()，您將獲得具有 id=2 和 prize=675 的附加行。

示例 9

[編輯 | 編輯原始碼]

最後，我們將展示一個更復雜的查詢，它檢索每組的額外統計值。有關詳細資訊，請參閱頁面視窗函式。

SELECT *
FROM
  (SELECT product.*,
          row_number() OVER (PARTITION BY product_group ORDER BY prize DESC) AS rownumber_per_group,
          min(prize)   OVER (PARTITION BY product_group ORDER BY prize DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS min,
          avg(prize)   OVER (PARTITION BY product_group ORDER BY prize DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS avg,
          max(prize)   OVER (PARTITION BY product_group ORDER BY prize DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max
   FROM   product
  ) tmp
WHERE  rownumber_per_group < 2
;

 id |    name    | product_group | prize  | rownumber_per_group |  min   |  avg   | max  
----+------------+---------------+--------+---------------------+--------+--------+------
 11 | Tripple-A  | Desktop       | 580    |                   1 | 499    | 547.25 | 580
  7 | WhiteHorse | Laptop        | 675    |                   1 | 390    | 517.60 | 675
  6 | AllRounder | Smartphone    | 535    |                   1 |  75    | 360.00 | 535


事務 結構化查詢語言
按組檢索前 N 行 		消除重複
取自 "https://wikibook.tw/w/index.php?title=Structured_Query_Language/Retrieve_Top_N_Rows_per_Group&oldid=4206555"
華夏公益教科書