PostgreSQL 中 DISTINCT ON 的用法

sql SELECT DISTINCT ON (column1, column2, ...) column_list FROM table_name ORDER BY column1, column2, ..., criteria;

- `DISTINCT ON (表达式)` 中的括号不可省略。
- `ORDER BY` 必须以 `DISTINCT ON` 中出现的列作为开头，然后才能指定其他排序规则。
- 返回的行是每个分组中按 `ORDER BY` 排序后的第一行。

### 准备示例数据

假设有一个 `product_prices` 表，记录了商品在不同时间点的价格：

```sql
CREATE TABLE product_prices (
    id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    updated_at DATE NOT NULL
);

INSERT INTO product_prices (product_name, price, updated_at) VALUES
('Keyboard', 99.99, '2025-01-10'),
('Keyboard', 109.99, '2025-03-15'),
('Mouse', 49.99, '2025-02-01'),
('Mouse', 44.99, '2025-03-20'),
('Monitor', 299.99, '2025-01-25'),
('Monitor', 289.99, '2025-04-01');

获取每件商品的最新价格

我们希望查询每件商品最近一次更新的价格，也就是按 product_name 分组，每组取 updated_at 最新的那一行。

SELECT DISTINCT ON (product_name)
    product_name,
    price,
    updated_at
FROM product_prices
ORDER BY product_name, updated_at DESC;

结果：

观察 ORDER BY：先去重字段 product_name，再按 updated_at DESC 排序。每个 product_name 分组内，第一行就是最新日期的那条记录。

每个分组取多条记录？

DISTINCT ON 只保留每个分组的第一行。如果你需要每个分组的前 N 行，请使用窗口函数（如 ROW_NUMBER()），DISTINCT ON 无法直接实现。

必须遵循 ORDER BY 规则

DISTINCT ON 的结果高度依赖于 ORDER BY。如果省略 ORDER BY，或者 ORDER BY 不以去重列开头，查询会报错或产生不可预测的结果。

-- 错误：ORDER BY 不以 product_name 开头
SELECT DISTINCT ON (product_name) *
FROM product_prices
ORDER BY updated_at DESC;

PostgreSQL 会直接报错：

ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

安全准则：始终让 ORDER BY 的开头列与 DISTINCT ON 中的列完全一致，并且顺序相同。然后你可以在后面添加任意排序字段来控制“哪一行胜出”。

省略 ORDER BY 虽然语法合法，但会随机返回每个分组的一行，结果不可靠，切勿在生产环境中使用。

高级技巧与场景

结合多个去重列

如果去重依据是多个列的组合，比如按 department_id 和 job_title 分组，取每个职位内工资最高的员工：

SELECT DISTINCT ON (department_id, job_title)
    employee_name,
    salary
FROM employees
ORDER BY department_id, job_title, salary DESC;

控制结果列的返回

DISTINCT ON 只影响行的选择，不会限制你在 SELECT 中写哪些列。被选中的行可以包含表中任何需要的字段。

与索引配合提升性能

如果查询频繁使用 DISTINCT ON 并按特定顺序排序，一个合适的索引可以显著加速。对于前文的商品价格查询：

CREATE INDEX ON product_prices (product_name, updated_at DESC);