PostgreSQL查询不同城市对应最新预订日期记录的方法

之前的查询无法返回正确结果，核心是两处逻辑错误：

• WHERE子句中的子查询均以酒店为维度筛选最大预订日期、最大预订ID，仅能过滤出每个酒店自身的最后一笔预订，再从这些酒店级记录中按城市分组取值，自然无法匹配整个城市维度的最新预订
• DISTINCT ON的排序优先级不符合要求：DISTINCT ON的规则是按指定字段分组后，返回排序规则下排在最首位的行，之前的排序将hotel.id ASC放在booking.booking_date前面，分组后会优先按酒店ID升序选记录，而非优先选预订日期最新的行，最终就会出现城市下取到更早预订的问题。

不需要在WHERE层叠加多层酒店维度的子查询，调整排序规则即可实现需求，性能也更优：

SELECT DISTINCT ON (city.name)
  city.name,
  booking.booking_date AS last_booking_date,
  hotel.id AS hotel_id,
  hotel.photos ->> 0 AS hotel_photo
FROM city
INNER JOIN hotel 
  ON city.id = hotel.city_id
INNER JOIN booking 
  ON booking.hotel_id = hotel.id
ORDER BY 
  city.name ASC,
  booking.booking_date DESC,
  booking.id DESC,
  hotel.id ASC;

逻辑说明 #

• ORDER BY首位固定为city.name ASC，既满足DISTINCT ON的语法要求（分组字段必须放在排序最前列），也保证最终结果按城市名称升序返回
• 第二位用booking.booking_date DESC，确保每个城市分组下最新预订日期的记录排在最前，会被DISTINCT ON选中
• 第三位加booking.id DESC做兜底：如果同一城市下有多条预订记录的日期同为最新值，选ID更大（即生成时间更晚）的记录，避免结果返回不确定
• 最后加hotel.id ASC，符合同优先级下按酒店ID升序排序的要求。

如果表数据量较大，也可以用窗口函数实现，逻辑完全等价，部分场景下查询优化器会生成执行效率更高的计划：

WITH ranked_booking AS (
  SELECT
    city.name,
    booking.booking_date AS last_booking_date,
    hotel.id AS hotel_id,
    hotel.photos ->> 0 AS hotel_photo,
    ROW_NUMBER() OVER (
      PARTITION BY city.name
      ORDER BY booking.booking_date DESC, booking.id DESC, hotel.id ASC
    ) AS row_rank
  FROM city
  INNER JOIN hotel 
    ON city.id = hotel.city_id
  INNER JOIN booking 
    ON booking.hotel_id = hotel.id
)
SELECT name, last_booking_date, hotel_id, hotel_photo
FROM ranked_booking
WHERE row_rank = 1
ORDER BY name ASC;

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