postgresql postgres +时间表b+时段+市场数据

你好吗？
我有一个包含股票价格的表，其中包含"stock_id"、"dt"（时间戳）、"open"、"high"、"low"、"close"和"volume"列，如下所示：
enter image description here
请注意，字段"dt"的时间范围为15个月，市场时间从上午10点开始，到下午18点结束。
我想使用函数time_bucket（或可能导致我的要求的另一个函数）创建一个具有另一个时间范围的实体化视图。例如，4小时：
SELECT stock_id, time_bucket(INTERVAL '4 hour', dt) AS "time", first(open, dt) as open, max(high) as high, min(low) as low, last(close, dt) as close, sum(volume) as volume FROM stock_prices where stock_id = 269 GROUP BY stock_id, "time" order by "time" DESC;
结果：
enter image description here
请注意，"dt"字段从上午8点开始，但我需要始终从上午10点开始，并在下午18点结束（市场时间）。
如果我使用另一个时间框架，如1小时，2小时，它的工作很好。
enter image description here
你能帮帮我吗？
多谢了!
我尝试使用time_bucket_gapfill，但也不起作用。

您可以使用continuous_aggregates来实现它。下面是一个使用一些随机数据的完整示例：

CREATE TABLE "ticks" ("time" timestamp with time zone not null, "symbol" text, "price" decimal, "volume" float);

SELECT create_hypertable('ticks', 'time', chunk_time_interval => INTERVAL '1 day');

ALTER TABLE ticks SET (
  timescaledb.compress,
  timescaledb.compress_orderby = 'time',
  timescaledb.compress_segmentby = 'symbol'
);
CREATE MATERIALIZED VIEW candlestick_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1m', time),
       "ticks"."symbol",
       toolkit_experimental.candlestick_agg(time, price, volume) as candlestick
FROM "ticks"
GROUP BY 1, 2
ORDER BY 1
WITH NO DATA;

CREATE MATERIALIZED VIEW candlestick_1h
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', "time_bucket"),
       symbol,
       toolkit_experimental.rollup(candlestick) as candlestick 
FROM "candlestick_1m"
GROUP BY 1, 2
WITH NO DATA;

CREATE MATERIALIZED VIEW candlestick_1d
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', "time_bucket"),
       symbol,
       toolkit_experimental.rollup(candlestick) as candlestick
FROM "candlestick_1h"
GROUP BY 1, 2
WITH NO DATA;

    INSERT INTO ticks
    SELECT time, 'SYMBOL', 1 + (random()*30)::int, 100*(random()*10)::int
    FROM generate_series(TIMESTAMP '2022-01-01 00:00:00',
                    TIMESTAMP '2022-02-01 00:01:00',
                INTERVAL '15 min') AS time;

如果可以使用toolkit_experimental. rollup（）在较长的时间范围内进行分组。
注意，对于每个属性，candlesstick对象都需要通过函数访问。

SELECT time_bucket,
  symbol,
  toolkit_experimental.open(candlestick),
  toolkit_experimental.high(candlestick),
  toolkit_experimental.low(candlestick),
  toolkit_experimental.close(candlestick),
  toolkit_experimental.volume(candlestick)
FROM candlestick_1d
WHERE time_bucket BETWEEN '2022-01-01' and '2022-01-07';

要构建where子句以只过滤正确的日期，您需要使用extract(hour from...)。
示例：

select extract(hour from TIMESTAMP '2022-01-19 10:00');
┌─────────┐
│ extract │
├─────────┤
│      10 │
└─────────┘

然后，对于您的情况，您可以在物化视图中构建一个额外的where子句来组成这个所需的场景。

postgresql postgres +时间表b+时段+市场数据

1条答案

相关问题

热门标签

最新问答