如何按最优位置获取PostgreSQL表的唯一结果？

我有一个大型PostgreSQL数据库表Car1_ScrapeHistoryLicensePlate，需要按Car_id和position分组取数。表中存在大量重复数据，需求是筛选出每个唯一Car_id对应的position最小、scrapedAt最新的行，无需关注具体的licensePlate数值。我已经写出了能得到正确结果的SQL，但希望优化得更简洁。

原SQL语句 #

select
    "eventDate",
    "Car_id",
    min("position") as "carPosition",
    groupArray(concat(toString("scrapedAt"), '_', toString("position"))) as "scrapedAtByPosition",
    groupArray(concat("licensePlate", '_', toString("position"))) as "licensePlateByPosition",
    groupArray(concat(toString("amazonChoice"), '_', toString("position"))) as "amazonChoicesByPosition",
    'organic' as "matchType"
from "Car1_ScrapeHistoryLicensePlate"
         inner join (
             select "Car_id", max("scrapedAt") as "scrapedAt"
             from "Car1_ScrapeHistoryLicensePlate"
             where "licensePlate" IN ('ALPR912', 'JGPD831') and "eventDate" between '2022-08-12' and '2022-09-12'
             group by "Car_id", "eventDate"
         ) as t1 USING ("Car_id", "scrapedAt")
where "licensePlate" IN ('ALPR912', 'JGPD831') and "eventDate" between '2022-08-12' and '2022-09-12'
group by "eventDate", "Car_id"
order by "eventDate" desc;

数据库示例记录 #

eventDate  Car_id  licensePlate position scrapedAt
---------- ------  ------------ -------  --------- 
2022-09-10,   1,   APRJSC512,    1,     1660000001
2022-09-10,   1,   APRJSC512,    1,     1660000002
2022-09-10,   1,   PLBQWN035,    1,     1660000003
2022-09-10,   1,   PLBQWN035,    1,     1660000004
2022-09-10,   1,   PLBQWN035,    2,     1660000002
2022-09-11,   2,   APRJSC512,    1,     1660000011
2022-09-11,   2,   APRJSC512,    2,     1660000022
2022-09-11,   2,   PLBQWN035,    1,     1660000033
2022-09-11,   2,   PLBQWN035,    2,     1660000044
2022-09-11,   2,   PLBQWN035,    5,     1660000022
2022-09-12,   3,   APRJSC512,    3,     1660000111
2022-09-12,   3,   PLBQWN035,    3,     1660000222
2022-09-13,   4,   PLBQWN035,    4,     1660001111
2022-09-14,   5,   PLBQWN035,    5,     1660011111

预期查询结果 #

eventDate  Car_id  licensePlate position scrapedAt
---------- ------  ------------ -------  ---------
2022-09-10,   1,   PLBQWN035,    1,     1660000004
2022-09-11,   2,   PLBQWN035,    1,     1660000033
2022-09-12,   3,   PLBQWN035,    3,     1660000222

优化后的简洁SQL #

利用PostgreSQL的窗口函数ROW_NUMBER()可以一次性完成排序和筛选，避免重复的WHERE条件和子查询关联，代码更简洁高效：

基础版（仅获取目标行） #

SELECT 
    "eventDate",
    "Car_id",
    "licensePlate",
    "position",
    "scrapedAt"
FROM (
    SELECT 
        *,
        -- 按Car_id分组，组内先按position升序取最小值，再按scrapedAt降序取最新时间
        ROW_NUMBER() OVER (
            PARTITION BY "Car_id" 
            ORDER BY "position" ASC, "scrapedAt" DESC
        ) AS rn
    FROM "Car1_ScrapeHistoryLicensePlate"
    WHERE "licensePlate" IN ('ALPR912', 'JGPD831') 
      AND "eventDate" BETWEEN '2022-08-12' AND '2022-09-12'
) AS ranked
WHERE rn = 1
ORDER BY "eventDate" DESC;

保留聚合字段版 #

如果需要保留原SQL中的scrapedAtByPosition等聚合字段，可调整为：

SELECT 
    "eventDate",
    "Car_id",
    MIN("position") AS "carPosition",
    ARRAY_AGG(CONCAT("scrapedAt"::TEXT, '_', "position"::TEXT)) AS "scrapedAtByPosition",
    ARRAY_AGG(CONCAT("licensePlate", '_', "position"::TEXT)) AS "licensePlateByPosition",
    ARRAY_AGG(CONCAT("amazonChoice"::TEXT, '_', "position"::TEXT)) AS "amazonChoicesByPosition",
    'organic' AS "matchType"
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY "Car_id" 
            ORDER BY "position" ASC, "scrapedAt" DESC
        ) AS rn
    FROM "Car1_ScrapeHistoryLicensePlate"
    WHERE "licensePlate" IN ('ALPR912', 'JGPD831') 
      AND "eventDate" BETWEEN '2022-08-12' AND '2022-09-12'
) AS ranked
WHERE rn = 1
GROUP BY "eventDate", "Car_id"
ORDER BY "eventDate" DESC;

优化说明 #

1. 窗口函数替代子查询关联：通过ROW_NUMBER()在子查询中直接为每个Car_id分组内的行排序，标记出符合条件的行（rn=1），无需额外关联子查询。
2. 避免重复筛选条件：原SQL中WHERE条件重复书写两次，优化后仅需在子查询中定义一次，减少冗余。
3. 高效扫描表：窗口函数逻辑只需扫描一次表，相比原SQL的两次扫描（主查询+子查询），性能更优。

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