如何使用PostgreSQL的LAG()函数仅获取单条结果

LAG()函数是否适用？ #

是的，LAG()完全适用于这个场景。它的核心作用就是在按指定顺序排序的分区中，获取当前行的前一行数据，正好匹配你需要获取最新记录的前一日收盘价的需求。你之前的查询只是缺少了筛选最新记录的步骤，所以返回了所有行的变动数据。

最佳实现方式 #

这里提供几种高效的实现方式，其中窗口函数组合的写法最简洁通用：

方式1：结合ROW_NUMBER()筛选最新记录 #

通过ROW_NUMBER()给每条记录按时间倒序编号，最新的记录编号为1，最后筛选出这条记录即可：

SELECT stock_id, last_price, date, one_day_change
FROM (
    SELECT stock_id,
           close AS last_price,
           timestamp::DATE AS date,
           LAG(close) OVER (PARTITION BY stock_id ORDER BY timestamp DESC) AS one_day_change,
           ROW_NUMBER() OVER (PARTITION BY stock_id ORDER BY timestamp DESC) AS rn
    FROM historical_prices 
    WHERE stock_id = 2
) sub_query
WHERE rn = 1;

方式2：用CTE+最大值筛选 #

先通过CTE计算所有行的LAG值，再筛选出日期最新的那条记录：

WITH price_change_cte AS (
    SELECT stock_id,
           close AS last_price,
           timestamp::DATE AS date,
           LAG(close) OVER (PARTITION BY stock_id ORDER BY timestamp DESC) AS one_day_change
    FROM historical_prices 
    WHERE stock_id = 2
)
SELECT stock_id, last_price, date, one_day_change
FROM price_change_cte
WHERE date = (SELECT MAX(timestamp::DATE) FROM historical_prices WHERE stock_id = 2);

方式3：直接关联最新与次新记录（无需窗口函数） #

如果只针对单个股票，也可以通过子查询找到最新和次新的记录进行关联：

SELECT 
    t1.stock_id,
    t1.close AS last_price,
    t1.timestamp::DATE AS date,
    t2.close AS one_day_change
FROM historical_prices t1
LEFT JOIN historical_prices t2 
    ON t1.stock_id = t2.stock_id 
    AND t2.timestamp = (
        SELECT MAX(timestamp) 
        FROM historical_prices 
        WHERE stock_id = t1.stock_id AND timestamp < t1.timestamp
    )
WHERE t1.stock_id = 2
    AND t1.timestamp = (SELECT MAX(timestamp) FROM historical_prices WHERE stock_id = 2);

方案对比 #

• 方式1的ROW_NUMBER()写法最通用，支持同时查询多个股票的最新变动（只需去掉WHERE stock_id=2），逻辑清晰且性能稳定。
• 方式2和3更适合单股票场景，写法也相对直观。

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