PostgreSQL中按用户取最新照片：使用DISTINCT ON是否为正确方案？

问题背景 #

数据库结构与测试数据 #

create schema bv;
create table bv.user(id bigint primary key);
create table bv.user_photo (
  id bigint primary key,
  url varchar(255) not null,
  user_id bigint references bv.user(id)
);

insert into bv.user values (100), (101);
insert into bv.user_photo values
  (1, 'https://1.com', 100),
  (3, 'https://3.com', 100),
  (4, 'https://4.com', 101),
  (2, 'https://2.com', 100);

需求与问题 #

需求是查询每个用户，仅包含其最新的照片（以user_photo.id降序判断最新），预期返回JSON格式如下：

[
  {"id" : 100, "url" : "https://3.com"},
  {"id" : 101, "url" : "https://4.com"}
]

当前查询返回用户所有照片，不符合预期，尝试用DISTINCT ON改写的SQL如下：

select distinct on(u.id)
  json_build_object(
    'id', u.id,
    'url', up.url
  ) user
from bv.user u
left join bv.user_photo up
  on u.id = up.user_id
order by u.id, up.id DESC

疑问：这个方案是否正确？这类场景是否不该用DISTINCT？

解答 #

这个方案完全正确，而且非常适配当前需求场景。

PostgreSQL的DISTINCT ON是专门为「按指定字段分组，取每个组内排序后的首行」这类需求设计的语法，和普通DISTINCT有本质区别：

• 普通DISTINCT是对整行所有字段去重，而DISTINCT ON(u.id)是按用户ID分组，保留每个组内符合排序规则的第一行数据。
• 搭配ORDER BY u.id, up.id DESC，先按用户ID排序分组，再在每个用户组内按照片ID降序排列，这样每个用户对应的首行就是ID最大的最新照片，完全匹配需求。

如果存在无照片的用户，LEFT JOIN会保留用户记录，此时url字段会返回null，这也符合左连接的预期行为。

另外，这个写法比窗口函数（比如ROW_NUMBER()）的实现更简洁，在PostgreSQL中性能表现也很优异——只要bv.user(id)和bv.user_photo(user_id, id)上有合适的索引，查询效率会很高。

这类场景不仅可以用DISTINCT ON，它还是PostgreSQL中处理分组取首行问题的推荐方案之一。

内容的提问来源于stack exchange，提问作者Mike K