PostgreSQL窗口函数未按预期工作：获取时段开盘收盘价遇阻

我需要编写PostgreSQL查询，获取不同时段（最近7天、上月、最近6个月等）市场K线的开盘价与收盘价，原本以为简单查询就能实现，实际却遇到了超出预期的困难（可能是没掌握正确方法）。

为简化问题，我做了两个测试示例：

• 第一个示例插入若干K线数据，查询中创建两个时段（分别始于'2023-03-12'和'2023-03-15'），关联K线与时段后的结果符合预期——P1包含所有K线，P2仅包含始于'2023-03-15'的K线。
• 第二个示例尝试获取P1和P2时段的开盘价与收盘价，预期结果如下：

range|open |close 
P1   | 10.2| 11.2
P2   | 11.7| 11.2

我推测窗口函数的工作逻辑和我的理解不符，希望有人解释原因，尤其是第一个示例结果符合预期，第二个却出问题的原因。

问题核心原因 #

你遇到的核心问题是关联时段与K线后，窗口函数的作用范围、默认行为和你预期的分组聚合逻辑不匹配：

1. 第一个示例仅做了时段与K线的关联过滤（JOIN），输出的是每个符合条件的K线-时段对，逻辑简单直接，所以结果符合预期。
2. 第二个示例要提取时段级别的聚合值（开盘/收盘），但如果直接使用窗口函数而忽略以下细节，就会出错：
   • LAST_VALUE() 默认的窗口范围是当前行到当前行，而非整个分区，因此无法直接取到时段内最后一条K线的收盘价。
   • 关联后的结果是多行数据（每个K线对应所属时段），窗口函数会为每一行计算一次值，如果不去重，会得到重复的时段行。
   • 若未显式指定按K线时间排序，窗口函数无法正确识别时段内最早/最晚的K线。

解决方案 #

方案1：修正窗口函数逻辑

通过显式指定窗口范围+去重，实现预期结果：

WITH time_ranges AS (
    SELECT 'P1' AS range, '2023-03-12'::TIMESTAMP AS start_time, '2023-03-18'::TIMESTAMP AS end_time
    UNION ALL
    SELECT 'P2' AS range, '2023-03-15'::TIMESTAMP AS start_time, '2023-03-18'::TIMESTAMP AS end_time
),
range_kline AS (
    SELECT 
        tr.range,
        k.time,
        -- 取时段内最早K线的开盘价
        FIRST_VALUE(k.open) OVER (PARTITION BY tr.range ORDER BY k.time) AS range_open,
        -- 显式指定窗口范围为整个分区，取最晚K线的收盘价
        LAST_VALUE(k.close) OVER (
            PARTITION BY tr.range 
            ORDER BY k.time 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS range_close
    FROM time_ranges tr
    JOIN kline k ON k.time BETWEEN tr.start_time AND tr.end_time
)
-- 按时段去重，保留唯一结果
SELECT DISTINCT ON (range)
    range,
    range_open AS open,
    range_close AS close
FROM range_kline
ORDER BY range;

方案2：使用子查询聚合（更直观）

直接对每个时段单独查询最早/最晚K线的价格，逻辑更清晰：

WITH time_ranges AS (
    SELECT 'P1' AS range, '2023-03-12'::TIMESTAMP AS start_time, '2023-03-18'::TIMESTAMP AS end_time
    UNION ALL
    SELECT 'P2' AS range, '2023-03-15'::TIMESTAMP AS start_time, '2023-03-18'::TIMESTAMP AS end_time
)
SELECT
    tr.range,
    -- 取时段内第一条K线的开盘价
    (SELECT open FROM kline WHERE time BETWEEN tr.start_time AND tr.end_time ORDER BY time LIMIT 1) AS open,
    -- 取时段内最后一条K线的收盘价
    (SELECT close FROM kline WHERE time BETWEEN tr.start_time AND tr.end_time ORDER BY time DESC LIMIT 1) AS close
FROM time_ranges tr;

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