PostgreSQL按最大日期查询各客户最新指标值的实现方案咨询

核心实现（性能最优） #

PostgreSQL 原生支持DISTINCT ON语法，可以高效提取分组后排序的第一条记录，结合行转列逻辑即可实现需求，代码如下：

WITH latest_item_records AS (
    SELECT DISTINCT ON (customer_id, item)
        customer_id,
        item,
        value
    FROM table_name
    ORDER BY customer_id, item, timestamp DESC
)
SELECT 
    customer_id,
    MAX(CASE WHEN item = 'price' THEN value END) AS price,
    MAX(CASE WHEN item = 'condition' THEN value END) AS condition
FROM latest_item_records
GROUP BY customer_id;

性能优化建议 #

如果表数据量较大，可创建联合索引(customer_id, item, timestamp DESC)，查询会直接走索引扫描，无需全表遍历，性能提升显著。

兼容写法（窗口函数实现） #

如果需要兼容标准SQL语法，也可以使用ROW_NUMBER窗口函数实现，性能略低于DISTINCT ON方案：

WITH ranked_records AS (
    SELECT
        customer_id,
        item,
        value,
        ROW_NUMBER() OVER(PARTITION BY customer_id, item ORDER BY timestamp DESC) AS rank_num
    FROM table_name
)
SELECT 
    customer_id,
    MAX(CASE WHEN item = 'price' AND rank_num = 1 THEN value END) AS price,
    MAX(CASE WHEN item = 'condition' AND rank_num = 1 THEN value END) AS condition
FROM ranked_records
GROUP BY customer_id;

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