PostgreSQL多表查询如何按最新日期过滤获取目标结果？

问题背景 #

现有关联查询：

SELECT accounts.number acc, counters.service serv, meter_pok.date, counters.tarif, meter_pok.value
    FROM stack.accounts
    LEFT JOIN stack.counters ON counters.acc_id = accounts.row_id
    LEFT JOIN stack.meter_pok ON meter_pok.acc_id = accounts.row_id AND meter_pok.counter_id = counters.row_id
    WHERE accounts.type = 3 AND counters.service = 100

该查询返回多日期的计量数据，需求是过滤出每个账户对应的最新日期的所有记录（如目标结果所示，111账户保留自身最新的2023-02-25记录，其余账户保留2023-02-27的记录，且301账户同一天的两条记录都保留）。

之前尝试的查询未达预期：

SELECT accounts.number acc, counters.service serv, meter_pok.date, counters.tarif, meter_pok.value
    FROM stack.accounts
    LEFT JOIN stack.counters ON counters.acc_id = accounts.row_id
    LEFT JOIN stack.meter_pok ON meter_pok.acc_id = accounts.row_id AND meter_pok.counter_id = counters.row_id
    WHERE accounts.type = 3 AND counters.service = 100 AND meter_pok.date = (SELECT MAX(meter_pok.date) FROM stack.meter_pok)

错误原因 #

上述查询中的子查询SELECT MAX(meter_pok.date) FROM stack.meter_pok取的是整个表的全局最大日期，而非每个账户自身的最新日期，导致过滤掉了那些自身最新日期不等于全局最大的记录（比如目标结果中的111账户）。

正确实现方式 #

方法1：窗口函数（推荐，灵活处理同日期多记录） #

使用RANK()窗口函数，按账户分组，对每个账户的记录按日期倒序排名，取排名为1的记录（会保留同一天的所有记录）：

WITH ranked_meter_data AS (
    SELECT 
        accounts.number AS acc,
        counters.service AS serv,
        meter_pok.date,
        counters.tarif,
        meter_pok.value,
        -- 按账户分组，日期倒序排名，同日期记录排名相同
        RANK() OVER (PARTITION BY accounts.row_id ORDER BY meter_pok.date DESC) AS record_rank
    FROM stack.accounts
    LEFT JOIN stack.counters ON counters.acc_id = accounts.row_id
    LEFT JOIN stack.meter_pok ON meter_pok.acc_id = accounts.row_id AND meter_pok.counter_id = counters.row_id
    WHERE accounts.type = 3 AND counters.service = 100
)
SELECT acc, serv, date, tarif, value
FROM ranked_meter_data
WHERE record_rank = 1;

如果需要对同日期的记录进一步筛选（比如取某条特定记录），可将RANK()替换为ROW_NUMBER()，并在ORDER BY后添加额外排序字段（如meter_pok.value DESC）。

方法2：关联子查询获取账户最新日期 #

先查询每个账户的最新日期，再关联原表筛选对应记录：

SELECT 
    a.number AS acc,
    c.service AS serv,
    mp.date,
    c.tarif,
    mp.value
FROM stack.accounts a
LEFT JOIN stack.counters c ON c.acc_id = a.row_id
LEFT JOIN stack.meter_pok mp ON mp.acc_id = a.row_id AND mp.counter_id = c.row_id
-- 匹配账户自身的最新日期
WHERE a.type = 3 
  AND c.service = 100
  AND (a.row_id, mp.date) IN (
      SELECT acc_id, MAX(date)
      FROM stack.meter_pok
      GROUP BY acc_id
  );

方法3：JOIN子查询 #

通过JOIN存储每个账户最新日期的临时表，筛选目标记录：

SELECT 
    a.number AS acc,
    c.service AS serv,
    mp.date,
    c.tarif,
    mp.value
FROM stack.accounts a
LEFT JOIN stack.counters c ON c.acc_id = a.row_id
LEFT JOIN stack.meter_pok mp ON mp.acc_id = a.row_id AND mp.counter_id = c.row_id
JOIN (
    SELECT acc_id, MAX(date) AS latest_date
    FROM stack.meter_pok
    GROUP BY acc_id
) latest_acc_dates 
    ON mp.acc_id = latest_acc_dates.acc_id 
    AND mp.date = latest_acc_dates.latest_date
WHERE a.type = 3 AND c.service = 100;

结果验证 #

以上三种方法均可得到目标结果：每个账户仅保留自身最新日期的所有记录，符合需求。

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