caggs_cascade_minimal.sql

DROP TABLE "ticks" CASCADE;

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 week');

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 '2023-01-01 00:00:00',
        TIMESTAMP '2023-01-07 00:00:00',
        INTERVAL '1 second') AS time;

-- ## Want to test compression?
-- SELECT add_compression_policy('ticks', INTERVAL '1 month');

-- ## Continuous aggregates policy
-- SELECT add_continuous_aggregate_policy('candlestick_1m',
-- start_offset => INTERVAL '1 month',
-- end_offset => INTERVAL '1 minute',
-- schedule_interval => INTERVAL '1 minute');

-- SELECT add_continuous_aggregate_policy('candlestick_1h',
-- start_offset => INTERVAL '1 month',
-- end_offset => INTERVAL '1 hour',
-- schedule_interval => INTERVAL '1 hour');