如何在DuckDB中按日分组获取指定时段时序数据的极值及对应时间

数据结构 #

原始时序数据格式如下：

┌─────────────────────┬─────────┬─────────┬─────────┬─────────┬────────┬────────────┐
│     Timestamps      │  Open   │  High   │   Low   │  Close  │ Volume │ CustomDate │
│      timestamp      │ double  │ double  │ double  │ double  │ int32  │  varchar   │
├─────────────────────┼─────────┼─────────┼─────────┼─────────┼────────┼────────────┤
│ 2006-04-11 12:00:00 │ 1.21245 │ 1.21275 │ 1.21235 │ 1.21275 │      0 │ 2006-04-11 │
│ 2006-04-11 12:05:00 │ 1.21275 │ 1.21275 │ 1.21225 │ 1.21235 │      0 │ 2006-04-11 │
│ 2006-04-11 12:10:00 │ 1.21235 │ 1.21235 │ 1.21205 │ 1.21225 │      0 │ 2006-04-11 │
│          ·          │     ·   │     ·   │     ·   │    ·    │      · │     ·      │
│          ·          │     ·   │     ·   │     ·   │    ·    │      · │     ·      │
│          ·          │     ·   │     ·   │     ·   │    ·    │      · │     ·      │
│ 2023-01-31 22:55:00 │ 1.08705 │  1.0873 │ 1.08705 │ 1.08725 │      0 │ 2023-01-31 │
│ 2023-01-31 23:00:00 │ 1.08725 │ 1.08735 │   1.087 │ 1.08705 │      0 │ 2023-01-31 │
│ 2023-01-31 23:05:00 │ 1.08705 │  1.0871 │ 1.08695 │  1.0871 │      0 │ 2023-01-31 │
└─────────────────────┴─────────┴─────────┴─────────┴─────────┴────────┴────────────┘

需求 #

• 筛选每日指定时段（示例：10:25:00 - 13:40:00）的数据；
• 获取该时段内High字段的最大值、Low字段的最小值，以及对应的Timestamps；
• 结果按日分组，支持后续分析查询。

现有问题 #

当前查询仅能获取每日极值，但无法关联到对应的时间戳：

WITH mySession AS (
    SELECT *, strftime(Timestamps, '%Y-%m-%d') AS CustomDate,
    FROM EURUSD, 
    WHERE (Timestamps BETWEEN CONCAT(CustomDate, ' 12:00:00')::timestamp AND CONCAT(CustomDate, ' 15:30:00')::timestamp)
),
getSpecificData AS (
  SELECT 
    CustomDate,
    MIN(Low) AS LowOfSession,
    MAX(High) AS HighOfSession
  FROM mySession
  GROUP BY CustomDate
  ORDER BY CustomDate DESC
)
SELECT * FROM getSpecificData;

解决方案 #

方法一：使用窗口函数（推荐） #

通过ROW_NUMBER()窗口函数标记出每日极值对应的行，再聚合提取结果，可控制相同极值时的时间戳取舍（如取最早/最晚）：

WITH mySession AS (
    SELECT *
    FROM EURUSD
    -- 筛选每日指定时段
    WHERE Timestamps BETWEEN CONCAT(CustomDate, ' 10:25:00')::timestamp AND CONCAT(CustomDate, ' 13:40:00')::timestamp
),
ranked_data AS (
    SELECT 
        *,
        -- 按日分组，标记Low最小的行（相同最小值取最早时间戳）
        ROW_NUMBER() OVER (PARTITION BY CustomDate ORDER BY Low ASC, Timestamps ASC) AS low_rank,
        -- 按日分组，标记High最大的行（相同最大值取最早时间戳）
        ROW_NUMBER() OVER (PARTITION BY CustomDate ORDER BY High DESC, Timestamps ASC) AS high_rank
    FROM mySession
)
SELECT 
    CustomDate,
    -- 提取最小值及对应时间戳
    MAX(CASE WHEN low_rank = 1 THEN Low END) AS LowOfSession,
    MAX(CASE WHEN low_rank = 1 THEN Timestamps END) AS LowTimestamp,
    -- 提取最大值及对应时间戳
    MAX(CASE WHEN high_rank = 1 THEN High END) AS HighOfSession,
    MAX(CASE WHEN high_rank = 1 THEN Timestamps END) AS HighTimestamp
FROM ranked_data
GROUP BY CustomDate
ORDER BY CustomDate DESC;

方法二：子查询关联极值与原表 #

先计算每日极值，再通过关联原表获取对应时间戳，需注意处理同一日多个相同极值的情况：

WITH mySession AS (
    SELECT *
    FROM EURUSD
    WHERE Timestamps BETWEEN CONCAT(CustomDate, ' 10:25:00')::timestamp AND CONCAT(CustomDate, ' 13:40:00')::timestamp
),
daily_extremes AS (
    SELECT 
        CustomDate,
        MIN(Low) AS LowOfSession,
        MAX(High) AS HighOfSession
    FROM mySession
    GROUP BY CustomDate
)
SELECT 
    de.CustomDate,
    de.LowOfSession,
    ml.Timestamps AS LowTimestamp,
    de.HighOfSession,
    mh.Timestamps AS HighTimestamp
FROM daily_extremes de
-- 关联最小值对应的行
LEFT JOIN mySession ml 
    ON de.CustomDate = ml.CustomDate AND de.LowOfSession = ml.Low
-- 关联最大值对应的行
LEFT JOIN mySession mh 
    ON de.CustomDate = mh.CustomDate AND de.HighOfSession = mh.High
-- 去重，保证每日一行（若有多个相同极值，取最早时间戳）
GROUP BY de.CustomDate, de.LowOfSession, de.HighOfSession, ml.Timestamps, mh.Timestamps
ORDER BY de.CustomDate DESC;

说明 #

• 方法一通过窗口函数精准定位极值行，逻辑清晰且能灵活控制相同极值的取舍规则；
• 方法二适合需要直观关联原表数据的场景，但需额外处理重复极值的情况；
• 两种方案均按CustomDate分组，结果可直接用于后续分析查询。

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