跳转到内容

结构化查询语言/按组检索前 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://wikibooks.cn/w/index.php?title=Structured_Query_Language/Retrieve_Top_N_Rows_per_Group&oldid=4206555"
华夏公益教科书