如何用PostgreSQL窗口函数跨午夜时段计算股票价差

问题描述 #

现有一份股票分钟级数据，datetime字段为timestamptz类型，其余字段为整数类型，数据跨度数年。需求是查询每日22:30至次日04:21时段内，首个open价格与最后一个close价格的差值。

尝试的错误SQL：

select distinct datetime::date, first_value(open) over w as first, last_value(close) over w as last 
from price_table window w as 
(partition by datetime::time range between 22:30 and 04:21); 

示例数据（price_table） #

datetime    open    high    low     close   volume -- datetime类型为timestamptz，其余为int
...
 
// 更早的数据（实际跨度数年） 

2023/01/03 23:50   25830   25840   25830   25830   1139
2023/01/03 23:51   25835   25835   25820   25825   786
2023/01/03 23:52   25825   25845   25825   25835   1196
2023/01/03 23:53   25840   25840   25825   25825   874
2023/01/03 23:54   25825   25835   25820   25825   680
2023/01/03 23:55   25820   25825   25810   25815   1237
2023/01/03 23:56   25810   25815   25805   25810   1163
2023/01/03 23:57   25810   25835   25810   25815   1753
2023/01/03 23:58   25810   25840   25810   25830   823
2023/01/03 23:59   25835   25845   25830   25845   1008
2023/01/04 00:00   25845   25855   25840   25850   1235
2023/01/04 00:01   25850   25850   25845   25845   439
2023/01/04 00:02   25845   25850   25835   25840   1146
2023/01/04 00:03   25840   25855   25840   25845   668
...

期望结果 #

date          open     close    diff
2023/01/03    25810    25840    30
2023/01/04    25830    25820    20

解决方案 #

核心思路是将跨午夜的时段数据归属到同一个统计日期：把当日22:30到次日04:21的数据，统一标记为当日的统计日期（比如2023-01-04 00:00的数据归属到2023-01-03的统计组）。之后基于统计日期分组，提取时段内首个open和最后一个close并计算差值。

方法一：分组聚合（高效适配大数据量） #

WITH tagged_data AS (
    SELECT
        datetime,
        open,
        close,
        -- 标记数据所属的统计日期
        CASE
            WHEN TIME(datetime) >= '22:30:00' THEN DATE(datetime)
            WHEN TIME(datetime) <= '04:21:00' THEN DATE(datetime) - INTERVAL '1 day'
        END AS stat_date
    FROM price_table
    -- 过滤目标时段数据，减少计算量
    WHERE TIME(datetime) >= '22:30:00' OR TIME(datetime) <= '04:21:00'
)
SELECT
    stat_date::DATE AS date,
    (SELECT open FROM tagged_data t2 WHERE t2.stat_date = t1.stat_date ORDER BY datetime ASC LIMIT 1) AS open,
    (SELECT close FROM tagged_data t2 WHERE t2.stat_date = t1.stat_date ORDER BY datetime DESC LIMIT 1) AS close,
    (SELECT close FROM tagged_data t2 WHERE t2.stat_date = t1.stat_date ORDER BY datetime DESC LIMIT 1)
    - (SELECT open FROM tagged_data t2 WHERE t2.stat_date = t1.stat_date ORDER BY datetime ASC LIMIT 1) AS diff
FROM tagged_data t1
GROUP BY stat_date
ORDER BY stat_date;

方法二：窗口函数实现 #

SELECT DISTINCT
    stat_date::DATE AS date,
    FIRST_VALUE(open) OVER (PARTITION BY stat_date ORDER BY datetime ASC) AS open,
    LAST_VALUE(close) OVER (PARTITION BY stat_date ORDER BY datetime ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS close,
    LAST_VALUE(close) OVER (PARTITION BY stat_date ORDER BY datetime ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    - FIRST_VALUE(open) OVER (PARTITION BY stat_date ORDER BY datetime ASC) AS diff
FROM (
    SELECT
        datetime,
        open,
        close,
        CASE
            WHEN TIME(datetime) >= '22:30:00' THEN DATE(datetime)
            WHEN TIME(datetime) <= '04:21:00' THEN DATE(datetime) - INTERVAL '1 day'
        END AS stat_date
    FROM price_table
    WHERE TIME(datetime) >= '22:30:00' OR TIME(datetime) <= '04:21:00'
) AS sub
ORDER BY date;

关键逻辑说明 #

1. 统计日期标记：通过CASE语句将22:30之后的数据归为当日，04:21之前的数据归为前一日，确保跨午夜的时段数据在同一个分组内。
2. 时段过滤：先筛选出目标时段的数据，避免无关数据参与计算，提升效率。
3. 首尾值提取：用FIRST_VALUE按时间升序取首个open，LAST_VALUE指定完整窗口范围取最后一个close，最终计算差值。

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