PostgreSQL中按最早观看关联排序视频的SQL查询实现

需求对应的SQL语句 #

SELECT v.id
FROM videos v
LEFT JOIN watches w 
  ON v.id = w.video_id 
  AND w.user_id = @target_user_id -- 替换为你要指定的用户ID
GROUP BY v.id
ORDER BY 
  -- 从未观看的视频排在最前
  CASE WHEN MAX(w.datetime) IS NULL THEN 0 ELSE 1 END,
  -- 有观看记录的按最后观看时间升序排列（最早观看的在前）
  MAX(w.datetime) ASC;

逻辑说明 #

1. 关联表：使用LEFT JOIN关联videos和watches表，确保所有视频都会被返回，哪怕该用户没有观看过。
2. 聚合处理：通过GROUP BY v.id聚合同一视频的多条观看记录，用MAX(w.datetime)获取该用户对这个视频的最后观看时间。
3. 排序规则：
   • 第一排序条件：用CASE表达式标记从未观看的视频（MAX(w.datetime) IS NULL），给这类视频分配更低的排序优先级（0），确保它们排在最前面。
   • 第二排序条件：对有观看记录的视频，按最后观看时间升序排列，实现最早观看的视频在前的要求。

示例验证 #

对应你给出的示例场景：

• 未被观看的视频ID：1、3 → 排在最前
• 有观看记录的视频按最后观看时间升序：4（5天前）→ 2（2小时前）→ 5（刚观看）
  最终返回顺序正好是[1,3,4,2,5]，符合预期。

内容的提问来源于stack exchange，提问作者Dmitry Filippov