PostgreSQL按JSON属性关联表查询去重域名按最早事件空值优先排序

推荐实现方案（兼容性强，逻辑清晰） #

直接通过GROUP BY分组取每个域名的最早事件时间，写法如下：

SELECT d.domain, MIN(e.moment) as earliest_moment
FROM domains d
LEFT JOIN events e 
  ON e.attributes ->> 'domain' = d.domain 
  AND e.name = 'event1'
WHERE d.parent IS NULL
GROUP BY d.domain
ORDER BY earliest_moment ASC NULLS FIRST;

该方案直接按顶级域名分组，用MIN()聚合函数取每个域名对应的最早事件时间，天然自带去重效果，最后按要求排序即可。

修正后的DISTINCT ON写法 #

你之前的写法问题出在子查询未指定对应排序规则，导致DISTINCT ON无法正确取到每个域名最早的事件记录，修正后写法如下：

SELECT domain, moment
FROM (
  SELECT DISTINCT ON (d.domain) d.domain, e.moment
  FROM domains d
  LEFT JOIN events e 
    ON e.attributes ->> 'domain' = d.domain 
    AND e.name = 'event1'
  WHERE d.parent IS NULL
  -- 子查询内先按域名分组，再按事件时间升序，保证DISTINCT ON取到每个域名最早的记录
  ORDER BY d.domain, e.moment ASC NULLS FIRST
) t
-- 外层按要求全局排序
ORDER BY moment ASC NULLS FIRST;

测试验证结果 #

使用你提供的模拟数据执行上述查询，返回结果如下：

完全符合你提出的去重、排序要求。

内容的提问来源于stack exchange，提问作者003random