PHP小码农

Appearance

去重（DISTINCT） ​

DISTINCT 用于从结果集中剔除重复行，是数据探查与统计去重的常用手段。本节讲解单列与多列去重、PostgreSQL 特有的 DISTINCT ON 语法、它与 GROUP BY 的等价性与性能差异，以及各种替代方案。

前置知识

阅读本节前，建议先了解：排序（ORDER BY）

基础概念 ​

术语对照 ​

中文English说明
去重DISTINCT移除结果集中完全相同的行
唯一值Unique Value某一列中互不相同的取值
去重键Distinct Key用于判定重复的列组合
排序去重Sort + Unique数据库内部常见的去重实现方式
哈希去重Hash Aggregate基于哈希表的去重实现
分组GROUP BY按列分组，可同时聚合

去重语义 ​

DISTINCT 作用于 SELECT 输出的整行。也就是说：

  • SELECT DISTINCT col —— 按 col 去重；
  • SELECT DISTINCT a, b —— 按 (a, b) 组合去重，只要组合不同就是不同的行。

这一点常被误解。下面两条语句含义完全不同：

sql
-- 按 (category) 去重：每个分类只出现一次
SELECT DISTINCT category FROM products;

-- 按 (category, price) 组合去重：同一分类下价格不同就会保留多行
SELECT DISTINCT category, price FROM products;

与 GROUP BY 的关系 ​

sql
-- 写法一：DISTINCT
SELECT DISTINCT category FROM products;

-- 写法二：GROUP BY（等价的语义）
SELECT category FROM products GROUP BY category;

对"纯去重、不做聚合"的需求，二者在语义上等价，PostgreSQL 的优化器通常会把 DISTINCT 展开为 HashAggregate 或 GroupAggregate，因此执行计划基本一致。区别在于 GROUP BY 还能挂聚合函数与 HAVING，而 DISTINCT 不能。

语法与用法 ​

完整语法 ​

sql
SELECT [DISTINCT | ALL] expression [, ...]
FROM table_name
[WHERE condition]
[ORDER BY ...];

-- PostgreSQL 扩展：按指定列去重，每组保留一行
SELECT DISTINCT ON (expression [, ...]) expression [, ...]
FROM table_name
[ORDER BY ...];

ALL 是默认值，表示保留全部行（不去重），一般省略不写。

示例数据 ​

后续示例统一使用 demo 库中的 orders 表：

sql
CREATE TABLE orders (
  order_no    varchar(20)  PRIMARY KEY,
  user_name   varchar(50)  NOT NULL,
  city        varchar(50),
  amount      numeric(10,2) NOT NULL,
  status      varchar(10)  NOT NULL,
  created_at  timestamptz  NOT NULL DEFAULT now()
);

INSERT INTO orders (order_no, user_name, city, amount, status, created_at) VALUES
  ('SO-1001', 'alice', '北京', 1200.00, 'paid',     '2026-01-05 10:00:00+08'),
  ('SO-1002', 'bob',   '上海',  350.50, 'paid',     '2026-01-06 11:30:00+08'),
  ('SO-1003', 'alice', '北京',  880.00, 'pending',  '2026-01-07 09:15:00+08'),
  ('SO-1004', 'carol', '广州',  1200.00, 'paid',    '2026-01-08 14:20:00+08'),
  ('SO-1005', 'bob',   '上海',  220.00, 'canceled', '2026-01-09 16:45:00+08'),
  ('SO-1006', 'dave',  '北京',  450.00, 'pending',  '2026-01-10 08:05:00+08'),
  ('SO-1007', 'carol', '广州',  1200.00, 'shipped', '2026-01-11 12:40:00+08'),
  ('SO-1008', 'eve',   '深圳',  760.00, 'paid',     '2026-01-12 15:10:00+08');
text
 order_no | user_name |  city  | amount  |  status  |     created_at
----------+-----------+--------+---------+----------+---------------------
 SO-1001  | alice     | 北京   | 1200.00 | paid     | 2026-01-05 10:00:00
 SO-1002  | bob       | 上海   |  350.50 | paid     | 2026-01-06 11:30:00
 SO-1003  | alice     | 北京   |  880.00 | pending  | 2026-01-07 09:15:00
 SO-1004  | carol     | 广州   | 1200.00 | paid     | 2026-01-08 14:20:00
 SO-1005  | bob       | 上海   |  220.00 | canceled | 2026-01-09 16:45:00
 SO-1006  | dave      | 北京   |  450.00 | pending  | 2026-01-10 08:05:00
 SO-1007  | carol     | 广州   | 1200.00 | shipped  | 2026-01-11 12:40:00
 SO-1008  | eve       | 深圳   |  760.00 | paid     | 2026-01-12 15:10:00
(8 rows)

详细说明 ​

单列去重 ​

sql
SELECT DISTINCT city FROM orders ORDER BY city;
text
  city
--------
 上海
 北京
 广州
 深圳
(4 rows)

多列组合去重 ​

sql
SELECT DISTINCT city, status FROM orders ORDER BY city, status;
text
  city  |  status
--------+----------
 上海   | canceled
 上海   | paid
 北京   | paid
 北京   | pending
 广州   | paid
 广州   | shipped
 深圳   | paid
(7 rows)

注意 广州 | paid 与 广州 | shipped 都保留：虽然 city 相同，但 status 不同，组合 (广州, paid) 与 (广州, shipped) 互不重复。

DISTINCT 与聚合函数组合 ​

DISTINCT 可以作用在聚合函数的参数上，先对参数去重再聚合：

sql
-- 统计有多少个不同的下单城市
SELECT count(DISTINCT city) AS city_cnt FROM orders;

-- 统计不同用户的订单总金额（每位用户只算一次金额）
SELECT sum(DISTINCT amount) AS sum_distinct_amount FROM orders;
text
 city_cnt
----------
        4
(1 row)

count(DISTINCT ...) 是统计"去重后行数"的标准写法，比 SELECT DISTINCT ... 再 count(*) 更省一次外层扫描。

DISTINCT ON：每组保留一行 ​

DISTINCT ON (col) 是 PostgreSQL 的扩展语法，用于按 col 分组、每组只保留第一行。它必须与 ORDER BY 配合，且 ORDER BY 的前导列必须与 DISTINCT ON 的表达式一致，否则结果不确定。

sql
-- 每个城市的金额最高的那一笔订单
SELECT DISTINCT ON (city) city, order_no, user_name, amount
FROM orders
ORDER BY city, amount DESC;
text
  city  | order_no | user_name | amount
--------+----------+-----------+---------
 上海   | SO-1002  | bob       |  350.50
 北京   | SO-1001  | alice     | 1200.00
 广州   | SO-1004  | carol     | 1200.00
 深圳   | SO-1008  | eve       |  760.00
(4 rows)

规则要点：

规则说明
ORDER BY 前导列必须与 DISTINCT ON 表达式匹配，否则报错或结果随机
保留哪一行由 ORDER BY 前导列之后的排序决定，取第一行
与 DISTINCT 区别DISTINCT 按整行去重，DISTINCT ON 按指定列去重
是否必须 ORDER BY语法上可不写，但结果不确定，实践上必须写

DISTINCT ON 的缺点是"每组取第一行"依赖排序，当每组数据量大时排序开销明显。

执行计划与排序开销 ​

sql
EXPLAIN (ANALYZE, COSTS OFF)
SELECT DISTINCT city FROM orders;
text
                QUERY PLAN
------------------------------------------
 HashAggregate (actual time=0.045..0.048 rows=4 loops=1)
   Group Key: city
   Batches: 1  Memory Usage: 24kB
   ->  Seq Scan on orders (actual time=0.011..0.020 rows=8 loops=1)
 Planning Time: 0.090 ms
 Execution Time: 0.075 ms
(6 rows)

去重有两种内部实现：

实现方式计划节点触发条件特点
哈希去重HashAggregate行数较少、内存足够不要求有序，速度快
排序去重GroupAggregate + Sort数据量大、内存不足或走索引需要排序，可能落盘
唯一索引扫描Unique去重列上有可用索引直接跳过重复，最快

如果去重的列上存在合适的索引（且顺序匹配），优化器可能选择 Index Only Scan + Unique，避免显式排序：

sql
CREATE INDEX idx_orders_city ON orders (city);

EXPLAIN (ANALYZE, COSTS OFF)
SELECT DISTINCT city FROM orders ORDER BY city;
text
                                QUERY PLAN
---------------------------------------------------------------------------
 Unique (actual time=0.028..0.031 rows=4 loops=1)
   ->  Index Only Scan using idx_orders_city on orders (actual time=0.024..0.026 rows=8 loops=1)
         Heap Fetches: 0
 Planning Time: 0.070 ms
 Execution Time: 0.055 ms
(5 rows)

DISTINCT ON 与窗口函数替代 ​

"每组取第一行"也可以用 row_number() 窗口函数实现，二者结果相同但写法不同：

sql
-- 写法一：DISTINCT ON（更简洁）
SELECT DISTINCT ON (city) city, order_no, amount
FROM orders
ORDER BY city, amount DESC;

-- 写法二：窗口函数（更通用，支持取第 N 行）
SELECT city, order_no, amount
FROM (
  SELECT city, order_no, amount,
         row_number() OVER (PARTITION BY city ORDER BY amount DESC) AS rn
  FROM orders
) t
WHERE rn = 1
ORDER BY city;
text
  city  | order_no | amount
--------+----------+---------
 上海   | SO-1002  |  350.50
 北京   | SO-1001  | 1200.00
 广州   | SO-1004  | 1200.00
 深圳   | SO-1008  |  760.00
(4 rows)

对比：

维度DISTINCT ON窗口函数
取第 N 行只能取第一行rn = N 可任意
排序要求必须与 ORDER BY 匹配独立 PARTITION BY + ORDER BY
可读性紧凑需子查询包裹
标准性PostgreSQL 扩展SQL 标准

EXISTS 替代：仅需判断是否存在 ​

如果只是想知道"某值是否出现过"，用 EXISTS 比 DISTINCT 更快，因为它在找到第一条匹配后立即短路：

sql
-- 判断是否存在北京用户（找到即停）
SELECT EXISTS (SELECT 1 FROM orders WHERE city = '北京') AS has_beijing;

-- 判断是否所有用户都下过单（不存在"从未下单"的用户）
SELECT NOT EXISTS (
  SELECT 1 FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_name = u.user_name)
) AS all_users_ordered;
text
 has_beijing
--------------
 t
(1 row)

NULL 的处理 ​

DISTINCT 的判定规则是：两个 NULL 视为相同，只保留一个。这与 = 比较运算符的语义不同（NULL = NULL 返回 NULL，不是真）。

sql
-- city 中有 NULL 时，去重后只保留一个 NULL 行
SELECT DISTINCT city FROM orders;
text
  city
--------
 北京
 上海
 广州
 NULL
 深圳
(5 rows)

这一行为与 GROUP BY 一致：GROUP BY 也会把所有 NULL 归入同一组。

实战示例 ​

需求：从 orders 出发，产出一份"各城市画像"报表，包括去重城市数、每个城市最近一笔订单、以及各城市状态分布。步骤 1~7 如下。

步骤 1：清洗前置校验，确认有无脏值 ​

sql
-- 检查是否有 NULL 城市，若有需先处理
SELECT count(*) AS total,
       count(city) AS city_not_null,
       count(*) - count(city) AS city_null
FROM orders;
text
 total | city_not_null | city_null
-------+---------------+-----------
     8 |             8 |         0
(1 row)

步骤 2：统计去重维度数量 ​

sql
SELECT count(DISTINCT city)   AS city_cnt,
       count(DISTINCT user_name) AS user_cnt,
       count(DISTINCT status)  AS status_cnt
FROM orders;
text
 city_cnt | user_cnt | status_cnt
----------+----------+------------
        4 |        5 |          4
(1 row)

status_cnt = 4 说明实际出现的状态是 paid、pending、canceled、shipped 四种。

步骤 3：生成城市 × 状态矩阵的去重组合 ​

sql
SELECT DISTINCT city, status
FROM orders
ORDER BY city, status;
text
  city  |  status
--------+----------
 上海   | canceled
 上海   | paid
 北京   | paid
 北京   | pending
 广州   | paid
 广州   | shipped
 深圳   | paid
(7 rows)

该结果可直接作为报表的"城市-状态"交叉表骨架。

步骤 4：每个城市取最近一笔订单 ​

sql
SELECT DISTINCT ON (city)
       city,
       order_no,
       user_name,
       amount,
       created_at
FROM orders
ORDER BY city, created_at DESC;
text
  city  | order_no | user_name | amount  |     created_at
--------+----------+-----------+---------+---------------------
 上海   | SO-1005  | bob       |  220.00 | 2026-01-09 16:45:00
 北京   | SO-1006  | dave      |  450.00 | 2026-01-10 08:05:00
 广州   | SO-1007  | carol     | 1200.00 | 2026-01-11 12:40:00
 深圳   | SO-1008  | eve       |  760.00 | 2026-01-12 15:10:00
(4 rows)

步骤 5：按金额去重校验金额分布 ​

sql
-- 有多少种不同的订单金额
SELECT count(*) AS distinct_amount_cnt
FROM (SELECT DISTINCT amount FROM orders) t;
text
 distinct_amount_cnt
---------------------
                   6
(1 row)

注意 1200.00 出现三次（SO-1001、SO-1004、SO-1007），去重后只剩一个，所以 8 行数据只有 6 种不同金额。

步骤 6：构造用于"每组取第一条"的索引 ​

sql
-- 支撑 DISTINCT ON (city) ... ORDER BY city, created_at DESC
CREATE INDEX idx_orders_city_created ON orders (city, created_at DESC);

EXPLAIN (ANALYZE, COSTS OFF)
SELECT DISTINCT ON (city) city, order_no, created_at
FROM orders
ORDER BY city, created_at DESC;
text
                                QUERY PLAN
---------------------------------------------------------------------------
 Unique (actual time=0.030..0.034 rows=4 loops=1)
   ->  Index Scan using idx_orders_city_created on orders (actual time=0.018..0.024 rows=8 loops=1)
 Planning Time: 0.105 ms
 Execution Time: 0.062 ms
(4 rows)

Unique 节点直接基于已有的有序索引跳过重复，不再需要额外排序。

步骤 7：窗口函数版"最近两笔订单" ​

DISTINCT ON 取不了第 2 条，改用 row_number()：

sql
SELECT city, order_no, amount, created_at, rn
FROM (
  SELECT city, order_no, amount, created_at,
         row_number() OVER (PARTITION BY city ORDER BY created_at DESC) AS rn
  FROM orders
) t
WHERE rn <= 2
ORDER BY city, rn;
text
  city  | order_no | amount  |     created_at      | rn
--------+----------+---------+---------------------+----
 上海   | SO-1005  |  220.00 | 2026-01-09 16:45:00 |  1
 上海   | SO-1002  |  350.50 | 2026-01-06 11:30:00 |  2
 北京   | SO-1006  |  450.00 | 2026-01-10 08:05:00 |  1
 北京   | SO-1003  |  880.00 | 2026-01-07 09:15:00 |  2
 广州   | SO-1007  | 1200.00 | 2026-01-11 12:40:00 |  1
 广州   | SO-1004  | 1200.00 | 2026-01-08 14:20:00 |  2
 深圳   | SO-1008  |  760.00 | 2026-01-12 15:10:00 |  1
(7 rows)

结果限制在最多 2 条，DISTINCT ON 无法表达这类需求。

注意事项 ​

  1. DISTINCT 作用于整行，不是单列。 SELECT DISTINCT a, b 按 (a, b) 组合去重，若你只想按 a 去重，必须用 DISTINCT ON (a) 或窗口函数。

  2. DISTINCT ON 必须与 ORDER BY 前导列一致。 下列写法虽然不报错，但结果不确定：

sql
-- 错误！ORDER BY 前导列不是 city，保留哪一行无法预期
SELECT DISTINCT ON (city) city, order_no, amount
FROM orders
ORDER BY amount DESC;

  1. DISTINCT 不保证输出顺序。 若需要顺序必须显式 ORDER BY，否则哈希去重的输出顺序是任意的。

  2. 大表 DISTINCT 可能落盘。 当去重结果集超出 work_mem，HashAggregate 会分批落盘，性能急剧下降。可用 EXPLAIN (ANALYZE, BUFFERS) 观察 Batches 是否大于 1。

  3. count(DISTINCT col) 需要排序或哈希。 在超大表上开销不低，若该列有索引可考虑 SELECT count(*) FROM (SELECT 1 FROM t GROUP BY col) s 之类的等价改写，让优化器有更多选择。

  4. NULL 视为相同值。 去重与分组时所有 NULL 合并为一组，若业务上要区分 NULL 需要用 COALESCE(col, '未知') 先做归一化。

  5. SELECT DISTINCT * 基本等价于 SELECT *。 如果表有主键，整行永远不会重复，加 DISTINCT 只是白白多一次去重开销。

  6. DISTINCT 不能与聚合函数、窗口函数在同一层混用取巧。 例如 SELECT DISTINCT count(*) FROM t 的结果是单行，与预期可能不符。

最佳实践 ​

  1. 能用 GROUP BY 就别用 DISTINCT 加聚合。 需要同时输出分组与聚合值时，GROUP BY 更清晰、也便于加 HAVING。

  2. 去重列建索引，让优化器走 Unique 而非显式排序。 索引顺序要与 ORDER BY 一致。

sql
-- 支撑 SELECT DISTINCT city FROM orders ORDER BY city
CREATE INDEX idx_orders_city ON orders (city);
  1. "每组取最新一条"优先用 DISTINCT ON，取第 N 条用窗口函数。
sql
-- 取最新一条
SELECT DISTINCT ON (user_name) user_name, order_no, created_at
FROM orders ORDER BY user_name, created_at DESC;

-- 取最新三条
SELECT user_name, order_no, created_at
FROM (SELECT *, row_number() OVER (PARTITION BY user_name ORDER BY created_at DESC) rn
      FROM orders) t
WHERE rn <= 3;
  1. 只判断"是否存在"时用 EXISTS，不要 SELECT DISTINCT。 EXISTS 找到即停，代价低得多。
sql
SELECT EXISTS (SELECT 1 FROM orders WHERE city = '北京');

  1. 去重前先想清楚"业务主键是什么"。 如果担心重复只出现在某几列，用 DISTINCT ON (业务键) 更精确，避免误删有效行。

  2. 大结果集去重优先在子查询里限制数据量。 先 WHERE 缩小范围再 DISTINCT，避免对全表去重。

sql
-- 推荐：先过滤再排序去重
SELECT DISTINCT city
FROM orders
WHERE created_at >= now() - interval '30 days';

  1. 用 EXPLAIN (ANALYZE, BUFFERS) 验证去重实现方式。 关注是否出现 Sort 与 Batches > 1，必要时提升 work_mem。

  2. DISTINCT 的结果不保证顺序，分页与去重组合时要显式排序。

sql
SELECT DISTINCT city FROM orders ORDER BY city LIMIT 20 OFFSET 0;

下一节

继续学习：分组（GROUP BY / HAVING）

参考链接 ​