caggs_scaled.sql

DROP TABLE if exists ticks CASCADE;
DROP view if exists ohlc_1m CASCADE;
CREATE TABLE ticks
( time TIMESTAMP NOT NULL,
    symbol varchar,
    price decimal,
    volume int);

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

CREATE MATERIALIZED VIEW ohlc_1m
WITH (timescaledb.continuous,
      timescaledb.materialized_only = false) AS
SELECT time_bucket('1m', time) as bucket,
          symbol,
          FIRST(price, time) as open,
          MAX(price) as high,
          MIN(price) as low,
          LAST(price, time) as close,
          SUM(volume) as volume,
          MIN(time) as open_time,
          MAX(time) as close_time
        FROM ticks
        GROUP BY 1, 2
WITH DATA;

SELECT add_continuous_aggregate_policy('ohlc_1m',
    start_offset => INTERVAL '1 month',
    end_offset => INTERVAL '1 minute',
    schedule_interval => INTERVAL '1 minute');

CREATE MATERIALIZED VIEW ohlc_1h
WITH (timescaledb.continuous,
      timescaledb.materialized_only = false) AS
SELECT time_bucket('1h', time) as bucket,
          symbol,
          FIRST(price, time) as open,
          MAX(price) as high,
          MIN(price) as low,
          LAST(price, time) as close,
          SUM(volume) as volume,
          MIN(time) as open_time,
          MAX(time) as close_time
        FROM ticks
        GROUP BY 1, 2
WITH DATA;

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

INSERT INTO ticks
SELECT time, 'SYMBOL', (random()*30)::int, 100*(random()*10)::int
FROM generate_series(TIMESTAMP '2000-01-01 00:00:00',
                 TIMESTAMP '2000-01-01 00:00:00' + INTERVAL '50 seconds',
             INTERVAL '1 second') AS time;

TABLE ohlc_1m ORDER BY bucket DESC LIMIT 1;
TABLE ohlc_1h ORDER BY bucket DESC LIMIT 1;

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

TABLE ohlc_1m ORDER BY bucket DESC LIMIT 1;
TABLE ohlc_1h ORDER BY bucket DESC LIMIT 1;

Output:

INSERT 0 51
┌─[ RECORD 1 ]─────────────────────┐
│ bucket     │ 2000-01-01 00:00:00 │
│ symbol     │ SYMBOL              │
│ open       │ 13                  │
│ high       │ 29                  │
│ low        │ 0                   │
│ close      │ 3                   │
│ volume     │ 23600               │
│ open_time  │ 2000-01-01 00:00:00 │
│ close_time │ 2000-01-01 00:00:50 │
└────────────┴─────────────────────┘

┌─[ RECORD 1 ]─────────────────────┐
│ bucket     │ 2000-01-01 00:00:00 │
│ symbol     │ SYMBOL              │
│ open       │ 13                  │
│ high       │ 29                  │
│ low        │ 0                   │
│ close      │ 3                   │
│ volume     │ 23600               │
│ open_time  │ 2000-01-01 00:00:00 │
│ close_time │ 2000-01-01 00:00:50 │
└────────────┴─────────────────────┘

INSERT 0 3601
┌─[ RECORD 1 ]─────────────────────┐
│ bucket     │ 2000-01-01 01:01:00 │
│ symbol     │ SYMBOL              │
│ open       │ 8                   │
│ high       │ 8                   │
│ low        │ 8                   │
│ close      │ 8                   │
│ volume     │ 900                 │
│ open_time  │ 2000-01-01 01:01:00 │
│ close_time │ 2000-01-01 01:01:00 │
└────────────┴─────────────────────┘

┌─[ RECORD 1 ]─────────────────────┐
│ bucket     │ 2000-01-01 01:00:00 │
│ symbol     │ SYMBOL              │
│ open       │ 14                  │
│ high       │ 30                  │
│ low        │ 0                   │
│ close      │ 8                   │
│ volume     │ 28800               │
│ open_time  │ 2000-01-01 01:00:00 │
│ close_time │ 2000-01-01 01:01:00 │
└────────────┴─────────────────────┘