sql - 如何编写聚合外汇蜡烛数据的查询？

Question

我有一个具有以下架构的财务数据表:

         Table "public.candles"
   Column   |      Type      | Modifiers 
------------+----------------+-----------
 posix_time | bigint         | not null
 low        | numeric(8,2)   | not null
 high       | numeric(8,2)   | not null
 open       | numeric(8,2)   | not null
 close      | numeric(8,2)   | not null
 volume     | numeric(23,16) | not null
Indexes:
    "candles_pkey" PRIMARY KEY, btree (posix_time)

每根蜡烛跨越一分钟的时间间隔。我想将数据聚合到间隔为 5 分钟、1 小时、1 天等的蜡烛中。

我可以在五分钟的时间间隔内聚合 posix_time、high、low 和 volume

SELECT posix_time/(60*5)*(60*5) AS new_posix_time,
       max(high)                AS new_high,
       min(low)                 AS new_low,
       sum(volume)              AS new_volume
FROM candles
GROUP BY new_posix_time

并计算新的开盘价和收盘价值，适当的变化

SELECT posix_time/(60*5)*(60*5) AS new_posix_time,
       open                     AS new_open
FROM (SELECT open,
             posix_time,
             ROW_NUMBER() OVER (PARTITION BY posix_time/(60*5)*(60*5)
                                    ORDER BY posix_time ASC) AS r
      FROM candles
     ) AS o
WHERE o.r = 1

如 this question 中的建议，但我不知道如何将它们组合成一个查询。

我需要使用联接吗？子查询？完全重构查询？

Answer 1

您可以使用 generate_series() 来获取您正在寻找的时间范围。然后你可以使用 left join 和聚合。像这样:

select t.ts,
       min(low) as low, max(high) as high, sum(volume) as volume
from generate_series('2016-01-01'::timestamp, '2016-01-02'::timestamp, interval '5 minute'
                    ) t(ts) left join
     candles c
     on '1970-01-01' + c.posix_time * interval '1 second' between t.ts and t.ts + interval '5 minute'
group by t.ts;

编辑:

获取开闭时间需要多一层处理:

select ts, min(low) as low, max(high) as high, sum(volume) as volume,
       min(open) as open, min(close) as close
from (select t.*, c.*,
             first_value(open) over (partition by t.ts order by c.posix_time asc) as open,
             first_value(open) over (partition by t.ts order by c.posix_time desc) as close
      from generate_series('2016-01-01'::timestamp, '2016-01-02'::timestamp, interval '5 minute'
                          ) t(ts) left join
           candles c
           on '1970-01-01' + c.posix_time * interval '1 second' between t.ts and t.ts + interval '5 minute'
     ) t
group by ts;