结构化查询语言(Structured Query Language)

挑战

有时，用户会发现数据库中存在重复或无效的数据。例如，在创建表时忘记定义主键，导致键列中插入了重复值；或发现某些列或列的组合未能满足唯一性要求，从而违反了业务规则。这种问题通常在执行 ALTER TABLE ... ADD PRIMARY KEY (...) 或 CREATE INDEX index_1 ON table_1 (col_1, col_2) 等操作时被发现。

在这种情况下，数据需要被修正或部分行需要被删除。以下将重点讨论删除冗余行的场景，删除命令通常包括两部分：DELETE 命令和标识冗余行的逻辑。在复杂场景中，可能需要多条 SQL 语句（例如通过游标循环和动态操作）才能完成。

---

示例表

以下是用于示例的 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：识别受影响的行

1. 通过 GROUP BY 查看重复情况

首先通过 GROUP BY 和 COUNT(*) 确定哪些分组存在重复行。

SELECT product_group, prize, COUNT(*)
FROM   product
GROUP BY product_group, prize
HAVING COUNT(*) > 1;

结果：

product_group	prize	count
Laptop	675	2

---

步骤 2：识别具体行及其分组计数

2.1 使用窗口函数

通过窗口函数 COUNT(*) OVER 按分组统计行数，同时显示所有列，便于精确分析。

SELECT product.*,
       COUNT(*) OVER (PARTITION BY product_group, prize) AS cnt
FROM   product;

结果：

id	name	product_group	prize	cnt
7	WhiteHorse	Laptop	675	2
2	SmartAndElegant	Laptop	675	2

2.2 计算组内行的排名

将窗口函数结果嵌套到子查询中，通过 ROW_NUMBER() 标记每个分组内行的顺序。

SELECT tmp.*
FROM   (
  SELECT product.*,
         ROW_NUMBER() OVER (PARTITION BY product_group, prize ORDER BY id) AS position_within_group
  FROM   product
) tmp
WHERE  tmp.position_within_group > 1;

结果：

id	name	product_group	prize	position_within_group
7	WhiteHorse	Laptop	675	2

---

步骤 3：删除冗余行

确认删除的行

在继续删除前，通过以下查询确认将要删除的行：

SELECT tmp.*
FROM   (
  SELECT product.*,
         ROW_NUMBER() OVER (PARTITION BY product_group, prize ORDER BY id) AS position_within_group
  FROM   product
) tmp
WHERE  tmp.position_within_group > 1;

执行删除

将需要删除的行 ID 嵌套到 DELETE 语句中：

BEGIN TRANSACTION;

DELETE
FROM   product
WHERE  id IN
  (SELECT tmp.id
   FROM   (
     SELECT product.*,
            ROW_NUMBER() OVER (PARTITION BY product_group, prize ORDER BY id) AS position_within_group
     FROM   product
   ) tmp
   WHERE  tmp.position_within_group > 1
  );

COMMIT;

执行后，表中将仅保留每组中排名为 1 的行。

---

总结

1. 窗口函数：使用 COUNT(*) OVER 和 ROW_NUMBER() 精确定位重复数据及分组内的行排名。
2. 灵活性：可根据业务规则调整 ORDER BY 排序列，以选择保留的行。
3. 效率：通过窗口函数直接在 SQL 层处理，避免多次查询和复杂的应用逻辑。

此方法对处理重复数据提供了清晰且高效的解决方案。