如何在不规则时间序列上高效使用LEAD和LAG函数？

我有一个包含50多年数据的大型数据集，需要获取目标时间窗口（2024-09-23至2024-09-27）内每个条目的前序和后序价格记录。直接在WHERE筛选后使用LAG()/LEAD()会导致窗口首尾出现不符合预期的NULL（因为窗口函数只基于筛选后的数据计算），而全表计算窗口函数再筛选的方式性能极差，耗时过长。

示例数据 #

item    date        price
A       2024-09-20  $106.59 
A       2024-09-23  $91.84 
A       2024-09-24  $92.66 
A       2024-09-25  $107.87 
A       2024-09-26  $98.47 
A       2024-09-27  $91.65 
A       2024-09-30  $108.10 
B       2024-07-29  $120.71 
B       2024-08-13  $87.80 
B       2024-09-04  $115.80 
B       2024-09-26  $94.06 
B       2024-09-27  $120.88 

方案1：关联查询直接定位前后值（性能最优） #

通过子查询直接为每条目标窗口内的记录查找对应的前序（最近的更早日期）和后序（最近的更晚日期）价格，无需扫描全表。

SELECT 
    t.item, t.date, t.price,
    -- 获取当前记录的前序价格（同item下最近的更早日期）
    (SELECT price 
     FROM dailytable 
     WHERE item = t.item AND date < t.date 
     ORDER BY date DESC LIMIT 1) AS prevprice,
    -- 获取当前记录的后序价格（同item下最近的更晚日期）
    (SELECT price 
     FROM dailytable 
     WHERE item = t.item AND date > t.date 
     ORDER BY date ASC LIMIT 1) AS nextprice
FROM dailytable t
WHERE t.date BETWEEN '2024-09-23' AND '2024-09-27';

方案2：预筛选必要数据后使用窗口函数 #

如果需要扩展到获取前后N条记录，可先筛选出目标窗口数据+每个item窗口前最后一条+窗口后第一条的小数据集，再在这个子集上计算窗口函数：

WITH necessary_data AS (
    -- 目标窗口内的所有记录
    SELECT item, date, price
    FROM dailytable
    WHERE date BETWEEN '2024-09-23' AND '2024-09-27'
    
    UNION ALL
    
    -- 每个item在目标窗口前的最后一条记录
    SELECT item, date, price
    FROM (
        SELECT item, date, price,
               ROW_NUMBER() OVER (PARTITION BY item ORDER BY date DESC) rn
        FROM dailytable
        WHERE date < '2024-09-23'
    ) pre_data
    WHERE rn = 1
    
    UNION ALL
    
    -- 每个item在目标窗口后的第一条记录
    SELECT item, date, price
    FROM (
        SELECT item, date, price,
               ROW_NUMBER() OVER (PARTITION BY item ORDER BY date ASC) rn
        FROM dailytable
        WHERE date > '2024-09-27'
    ) post_data
    WHERE rn = 1
)
SELECT item, date, price,
       LAG(price) OVER (PARTITION BY item ORDER BY date) AS prevprice,
       LEAD(price) OVER (PARTITION BY item ORDER BY date) AS nextprice
FROM necessary_data
WHERE date BETWEEN '2024-09-23' AND '2024-09-27';

关键优化：建立复合索引 #

以上两种方案的性能都依赖于(item, date)的复合索引，它能让数据库快速定位到每个item的指定日期范围数据，避免全表扫描。创建索引的语句：

CREATE INDEX idx_dailytable_item_date ON dailytable(item, date);

预期结果 #

执行上述方案后，会得到符合需求的结果：

item    date        price       prevprice   nextprice
A       2024-09-23  $91.84      $106.59     $92.66 
A       2024-09-24  $92.66      $91.84      $107.87 
A       2024-09-25  $107.87     $92.66      $98.47 
A       2024-09-26  $98.47      $107.87     $91.65 
A       2024-09-27  $91.65      $98.47      $108.10 
B       2024-09-26  $94.06      $115.80     $120.88 
B       2024-09-27  $120.88     $94.06      NULL 

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