postgresql 如何从时间序列数据中获取按天分组的特定时间范围的最大值和最小值

v09wglhw 于 2023-02-15 发布在 PostgreSQL

关注(0)|答案(3)|浏览(334)

我正在使用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 │
└─────────────────────┴─────────┴─────────┴─────────┴─────────┴────────┴────────────┘

我正在寻找一个"复杂"的SQL查询，可以完成以下：

选择一天中的特定时间段（例如10：25：00 - 13：40：00）
在此时间范围内，我希望从f. e High获取MAX值，从Low获取MIN值
我还需要相应的时间戳，以便知道MAX和MIN值出现的时间
我希望结果按天分组
我想进一步分析查询结果

理想情况下，结果应该如下所示：

Day    | HighMAX |     HighMAXTime     |  LowMIN  |     LowMINTime
--------------------------------------------------------------------------
2023-01-29 | 1.07545 | 2023-01-29 04:10:00 |  1.0726  | 2023-01-29 18:05:00
2023-01-30 | 1.08465 | 2023-01-30 23:55:00 |  1.08015 | 2023-01-30 15:35:00
2023-01-31 ...
...

这是我目前使用的SQL查询：

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;

当前结果：

┌────────────┬──────────────┬───────────────┐
│ CustomDate │ LowOfSession │ HighOfSession │
│  varchar   │    double    │    double     │
├────────────┼──────────────┼───────────────┤
│ 2023-01-26 │      1.08505 │        1.0906 │
│ 2023-01-25 │       1.0874 │        1.0925 │
│ 2023-01-24 │       1.0835 │       1.08905 │
│     ·      │          ·   │           ·   │
│     ·      │          ·   │           ·   │
│     ·      │          ·   │           ·   │
│ 2006-04-13 │      1.20945 │       1.21175 │
│ 2006-04-12 │       1.2094 │       1.21145 │
│ 2006-04-11 │      1.21205 │       1.21415 │
└────────────┴──────────────┴───────────────┘

目前，我得到了最低和最高的最小值，但我不知道如何检索这些值的相应时间戳。

postgresql

来源：https://stackoverflow.com/questions/75420548/how-can-i-get-the-max-and-the-min-value-for-a-specific-timeframe-grouped-by-day

3条答案

按热度按时间

m2xkgtsf1#

您可以使用EURUSD表选择inner join来获取所需的时间戳：

WITH mySession AS (
    SELECT *
    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
),
getDetails As (
  select s.*, l.Timestamps as TimestampsOfLow, h.Timestamps as TimestampsOfHigh
  from getSpecificData as s
  inner join mySession as l on s.LowOfSession = l.low and s.CustomDate = l.CustomDate
  inner join mySession as h on s.HighOfSession = h.High and s.CustomDate = h.CustomDate
)
SELECT customdate, lowofsession, highofsession, max(timestampsoflow), max(TimestampsOfHigh) FROM getDetails
group by customdate, lowofsession, highofsession;

赞(0）回复(0）举报 2023-02-15

z9gpfhce2#

您需要窗口函数first_value和last_value。（DuckDB doc）

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 DISTINCT
        CustomDate,
        FIRST_VALUE(timestamps) over (PARTITION BY CustomDate order by Low) as LowOfSession_timestamp,
        LAST_VALUE(timestamps) over (PARTITION BY CustomDate order by High) as HighOfSession_timestamp,
        FIRST_VALUE(Low) over (PARTITION BY CustomDate order by Low) as LowOfSession_value,
        LAST_VALUE(High) over (PARTITION BY CustomDate order by High) as HighOfSession_value,
      FROM mySession
      ORDER BY CustomDate DESC
    )
    SELECT * FROM getSpecificData;

赞(0）回复(0）举报 2023-02-15

ffx8fchx3#

您可以尝试以下查询

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)
),
minMaxData AS (
  SELECT 
    CustomDate,
    MIN(Low) AS LowOfSession,
    MAX(High) AS HighOfSession
  FROM mySession
  GROUP BY CustomDate
),
getSpecificData AS (
  SELECT 
    m.CustomDate,
    m.HighOfSession AS HighMAX,
    s1.Timestamps AS HighMAXTime,      
    m.LowOfSession AS LowMIN,
    s2.Timestamps AS LowMINTime
  FROM minMaxData m 
    INNER JOIN mySession s1 ON m.CustomDate = s1.CustomDate AND m.HighOfSession = s1.High
    INNER JOIN mySession s2 ON m.CustomDate = s2.CustomDate AND m.LowOfSession = s2.Low
  ORDER BY m.CustomDate DESC
)
SELECT * FROM getSpecificData;

赞(0）回复(0）举报 2023-02-15

我来回答

postgresql 如何从时间序列数据中获取按天分组的特定时间范围的最大值和最小值

3条答案

相关问题

热门标签

最新问答