结构化查询语言(Structured Query Language)
Completion requirements
技术演化：“从原始到复杂，再到简单。”（安托万·德圣埃克苏佩里）
挑战
在 SQL 中，经常需要按某列的唯一值分组，并从每个分组中提取前几行数据。例如：
- 按产品组选择价格最低的产品（每组的第一行）。
- 按实体选择历史表中版本号最高的行。
- 按用户获取最新的 10 条日志记录。
解决这些问题通常需要三个步骤：
- 按指定列分组。
- 按某一排序条件为分组内的行排序。
- 获取排序后分组中的第一行或前 n 行。
对于这种复杂场景，SQL 提供了多种解决方法，虽然在逻辑层面等价，但性能可能存在显著差异。此外，同一解决方案在不同数据库系统上的性能表现也可能千差万别。这是因为 SQL 规范定义了“要做什么”（WHAT），但并未定义“如何实现”（HOW）。优化执行计划的任务则由数据库系统负责。
以下从简单到复杂介绍几种解决方法，包括使用子查询、连接、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;
目标是按 product_group 获取价格（prize）最高的行。
不足的解决方案
示例 1：仅使用 GROUP BY
使用 GROUP BY 和聚合函数 MAX() 可以获取每组中的最高价格，但无法访问分组中的其他列或第二高价格等信息：
SELECT product_group, MAX(prize)
FROM product
GROUP BY product_group;
结果：
| product_group | max |
|---|---|
| Smartphone | 535 |
| Desktop | 580 |
| Laptop | 675 |
但是，以下查询会报错，因为无法访问分组中未包含在 GROUP BY 或聚合函数中的列：
SELECT *
FROM product
GROUP BY product_group;
示例 2：结合子查询
通过关联子查询或非关联子查询，可以扩展 GROUP BY 的功能，显示所有列，但结果仍可能不符合预期。例如，MAX(prize) 可能并不唯一，因此一个分组可能返回多行。此外，这种方法无法访问第二高或其他行。
- 非关联子查询：
SELECT *
FROM product
WHERE prize IN (
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 | AllRounder | Smartphone | 535 |
- 关联子查询：
SELECT *
FROM product p1
WHERE prize IN (
SELECT MAX(prize)
FROM product p2
WHERE p1.product_group = p2.product_group
);
结果相同，但性能可能较差，因为子查询对每行都会执行一次。
方法问题分析
- 仅使用
GROUP BY：无法访问所有列，也无法显示次高或其他行。 - 子查询扩展：可以显示所有列，但多行分组结果可能导致误解，且关联子查询性能较低。
更高效的解决方法
后续可以探索基于窗口函数、ROW_NUMBER() 或 RANK() 的方法来解决这些问题。这些方法不仅能显示所有列，还能精确定位每组中排名前 n 的行，同时性能表现更优。
示例 3
该解决方案通过使用 JOIN 和 HAVING 子句，在每个 product_group 内选择价格最高的行。然而，结果与示例 2 相同，仍然会返回不唯一的多行。
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 | AllRounder | 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
);
更复杂的解决方案
为了解决上述问题，可以对查询结构进行两项改进：
- 利用唯一列（如
id）进行精确连接。 - 结合
FETCH FIRST和ORDER BY实现分组后的行数限制。
示例 5
在示例 2 的基础上修改，添加 ORDER BY 和 FETCH FIRST 子句，确保每个分组只返回价格最高的行。
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 -- 使用 "WITH TIES" 包括边界值相同的行
);
结果：
| id | name | product_group | prize |
|---|---|---|---|
| 11 | Tripple-A | Desktop | 580 |
| 2 | SmartAndElegant | Laptop | 675 |
| 6 | AllRounder | Smartphone | 535 |
示例 6
通过 JOIN LATERAL 子句，按组连接每个分组中价格最高的行。这种方法类似于关联子查询，但语法更直观。
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;
示例 7：使用窗口函数
窗口函数通过滑动窗口的方式处理结果集，提供灵活的分组和排序功能。在本例中，通过 row_number() 按组生成行号。
SELECT product.*,
row_number() OVER (PARTITION BY product_group ORDER BY prize DESC) AS row_number
FROM product;
结果：
| id | name | product_group | prize | row_number |
|---|---|---|---|---|
| 11 | Tripple-A | Desktop | 580 | 1 |
| 5 | Solid | Desktop | 565 | 2 |
| 8 | Workstation ONE | Desktop | 499 | 3 |
| 1 | Big Fat One | Desktop | 545 | 4 |
| 2 | SmartAndElegant | Laptop | 675 | 1 |
| 7 | WhiteHorse | Laptop | 675 | 2 |
| 3 | Angle | Laptop | 398 | 3 |
示例 8：嵌套查询优化
由于窗口函数不能直接用于 WHERE 子句，可通过嵌套查询限制每组结果行数。
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 = 1;
结果：
| id | name | product_group | prize | rownumber_per_group |
|---|---|---|---|---|
| 11 | Tripple-A | Desktop | 580 | 1 |
| 2 | SmartAndElegant | Laptop | 675 | 1 |
| 6 | AllRounder | Smartphone | 535 | 1 |
示例 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) AS min_prize,
avg(prize) OVER (PARTITION BY product_group) AS avg_prize,
max(prize) OVER (PARTITION BY product_group) AS max_prize
FROM product
) tmp
WHERE rownumber_per_group = 1;
结果：
| id | name | product_group | prize | min_prize | avg_prize | max_prize |
|---|---|---|---|---|---|---|
| 11 | Tripple-A | Desktop | 580 | 499 | 547.25 | 580 |
| 2 | SmartAndElegant | Laptop | 675 | 390 | 517.60 | 675 |
| 6 | AllRounder | Smartphone | 535 | 75 | 360.00 | 535 |
总结
- 窗口函数 是解决分组问题的推荐方法，尤其是当需要更灵活的分组和排序功能时。
- 使用
row_number()或rank()可以轻松选择分组中的前 n 行。 - 对于需要分组统计的情况，可以结合窗口函数计算统计值如
min、avg、max等。
Last modified: Tuesday, 28 January 2025, 2:44 PM