PostgreSQL关联Notification与Photo表匹配通知后首条照片方案问询

原写法错误原因 #

你写的子查询是全局关联通知和照片后仅返回1条匹配结果，无法为每条通知单独匹配对应同设备下时间晚于通知的第一条照片。

正确实现方案1：LATERAL JOIN（PostgreSQL特有，性能最优） #

LATERAL JOIN允许子查询引用外层查询的字段，为每条通知单独执行子查询匹配符合要求的第一条照片：

SELECT
  n.notification_timestamp,
  n.data1,
  p.photo_timestamp,
  p.data2 AS data,
  n.deviceid
FROM public.notification n
LEFT JOIN LATERAL (
  SELECT photo_timestamp, data2
  FROM public.photo
  WHERE deviceid = n.deviceid
    AND photo_timestamp >= n.notification_timestamp
  ORDER BY photo_timestamp ASC
  LIMIT 1
) p ON true
-- 保留原有过滤条件
WHERE n.notification_timestamp > '2021-10-28 01:37:20.305+00'::TIMESTAMPTZ - INTERVAL '7 days'
ORDER BY n.deviceid ASC, n.notification_timestamp DESC
LIMIT 100;

该方案适合数据量较大的场景，只要在photo(deviceid, photo_timestamp)上建立联合索引，查询效率会非常高。

正确实现方案2：窗口函数（兼容标准SQL） #

先关联所有符合设备和时间条件的通知和照片，再用窗口函数筛选每个通知对应的最早照片：

WITH matched_photos AS (
  SELECT
    n.notification_timestamp,
    n.data1,
    p.photo_timestamp,
    p.data2 AS data,
    n.deviceid,
    -- 按通知ID分组，照片时间升序排序，最早的照片编号为1
    ROW_NUMBER() OVER (PARTITION BY n.id1 ORDER BY p.photo_timestamp ASC) AS rn
  FROM public.notification n
  LEFT JOIN public.photo p
    ON p.deviceid = n.deviceid
    AND p.photo_timestamp >= n.notification_timestamp
  WHERE n.notification_timestamp > '2021-10-28 01:37:20.305+00'::TIMESTAMPTZ - INTERVAL '7 days'
)
SELECT notification_timestamp, data1, photo_timestamp, data, deviceid
FROM matched_photos
WHERE rn = 1
ORDER BY deviceid ASC, notification_timestamp DESC
LIMIT 100;

内容的提问来源于stack exchange，提问作者Purushoth.Kesav