PostgreSQL中创建Products视图：按最新timestamp取产品唯一记录

要实现每个产品（结合realm_name和device_id维度）只保留最新timestamp的记录，可使用PostgreSQL的窗口函数ROW_NUMBER()，这是直观且高效的实现方式。

改写后的视图创建语句 #

CREATE OR REPLACE VIEW public.Products AS
SELECT realm_name, device_id, product_id, product_name, product_price
FROM (
    SELECT 
        realm_name, 
        device_id, 
        product_id, 
        product_name, 
        product_price,
        ROW_NUMBER() OVER (
            PARTITION BY realm_name, device_id, product_id 
            ORDER BY timestamp DESC
        ) AS rn
    FROM public.Sales
) AS ranked_sales
WHERE rn = 1;

逻辑说明 #

1. 分组排序标记行号：
   • PARTITION BY realm_name, device_id, product_id：按租户域、设备、产品ID分组，确保每个产品（对应指定租户和设备）单独处理。
   • ORDER BY timestamp DESC：每个分组内按时间戳倒序排列，最新的记录排在最前。
   • ROW_NUMBER()：为分组内的每条记录分配行号，最新记录的行号为1。
2. 筛选最新记录：外层查询仅保留行号为1的记录，即每个产品的最新版本。

备选方案（子查询关联） #

若偏好子查询写法，也可通过关联最大时间戳的子查询实现：

CREATE OR REPLACE VIEW public.Products AS
SELECT s.realm_name, s.device_id, s.product_id, s.product_name, s.product_price
FROM public.Sales s
INNER JOIN (
    SELECT 
        realm_name, 
        device_id, 
        product_id, 
        MAX(timestamp) AS latest_timestamp
    FROM public.Sales
    GROUP BY realm_name, device_id, product_id
) AS latest_sales 
ON s.realm_name = latest_sales.realm_name
AND s.device_id = latest_sales.device_id
AND s.product_id = latest_sales.product_id
AND s.timestamp = latest_sales.latest_timestamp;

注意：如果同一产品在同一时间戳存在多条记录，此方案会返回所有符合条件的记录；而窗口函数方案会随机返回一条（若需稳定结果，可在ORDER BY中加入id等唯一字段）。

内容的提问来源于stack exchange，提问作者browser-bug