code.vegaprotocol.io/vega@v0.79.0/datanode/sqlstore/migrations/0075_additional_candle_intervals.sql (about)

     1  -- +goose Up
     2  
     3  CREATE MATERIALIZED VIEW trades_candle_30_minutes
     4              WITH (timescaledb.continuous) AS
     5  SELECT market_id, time_bucket('30 minute', synthetic_time) AS period_start,
     6         first(price, synthetic_time) AS open,
     7         last(price, synthetic_time) AS close,
     8         max(price) AS high,
     9         min(price) AS low,
    10         sum(size) AS volume,
    11         sum(size * price) as notional,
    12         last(synthetic_time,
    13              synthetic_time) AS last_update_in_period
    14  FROM trades
    15  GROUP BY market_id, period_start WITH NO DATA;
    16  
    17  -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures
    18  -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day )
    19  SELECT add_continuous_aggregate_policy('trades_candle_30_minutes', start_offset => INTERVAL '1 day', end_offset => INTERVAL '30 minutes', schedule_interval => INTERVAL '30 minutes');
    20  
    21  CREATE MATERIALIZED VIEW trades_candle_4_hours
    22              WITH (timescaledb.continuous) AS
    23  SELECT market_id, time_bucket('4 hours', synthetic_time) AS period_start,
    24         first(price, synthetic_time) AS open,
    25         last(price, synthetic_time) AS close,
    26         max(price) AS high,
    27         min(price) AS low,
    28         sum(size) AS volume,
    29         sum(size * price) as notional,
    30         last(synthetic_time,
    31              synthetic_time) AS last_update_in_period
    32  FROM trades
    33  GROUP BY market_id, period_start WITH NO DATA;
    34  
    35  -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures
    36  -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day )
    37  SELECT add_continuous_aggregate_policy('trades_candle_4_hours', start_offset => INTERVAL '1 day', end_offset => INTERVAL '4 hours', schedule_interval => INTERVAL '4 hours');
    38  
    39  CREATE MATERIALIZED VIEW trades_candle_8_hours
    40              WITH (timescaledb.continuous) AS
    41  SELECT market_id, time_bucket('8 hours', synthetic_time) AS period_start,
    42         first(price, synthetic_time) AS open,
    43         last(price, synthetic_time) AS close,
    44         max(price) AS high,
    45         min(price) AS low,
    46         sum(size) AS volume,
    47         sum(size * price) as notional,
    48         last(synthetic_time,
    49              synthetic_time) AS last_update_in_period
    50  FROM trades
    51  GROUP BY market_id, period_start WITH NO DATA;
    52  
    53  -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures
    54  -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day )
    55  SELECT add_continuous_aggregate_policy('trades_candle_8_hours', start_offset => INTERVAL '1 day', end_offset => INTERVAL '8 hours', schedule_interval => INTERVAL '8 hours');
    56  
    57  CREATE MATERIALIZED VIEW trades_candle_12_hours
    58              WITH (timescaledb.continuous) AS
    59  SELECT market_id, time_bucket('12 hours', synthetic_time) AS period_start,
    60         first(price, synthetic_time) AS open,
    61         last(price, synthetic_time) AS close,
    62         max(price) AS high,
    63         min(price) AS low,
    64         sum(size) AS volume,
    65         sum(size * price) as notional,
    66         last(synthetic_time,
    67              synthetic_time) AS last_update_in_period
    68  FROM trades
    69  GROUP BY market_id, period_start WITH NO DATA;
    70  
    71  -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures
    72  -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day )
    73  SELECT add_continuous_aggregate_policy('trades_candle_12_hours', start_offset => INTERVAL '2 days', end_offset => INTERVAL '12 hours', schedule_interval => INTERVAL '12 hours');
    74  
    75  CREATE MATERIALIZED VIEW trades_candle_7_days
    76              WITH (timescaledb.continuous) AS
    77  SELECT market_id, time_bucket('7 days', synthetic_time) AS period_start,
    78         first(price, synthetic_time) AS open,
    79         last(price, synthetic_time) AS close,
    80         max(price) AS high,
    81         min(price) AS low,
    82         sum(size) AS volume,
    83         sum(size * price) as notional,
    84         last(synthetic_time,
    85              synthetic_time) AS last_update_in_period
    86  FROM trades
    87  GROUP BY market_id, period_start WITH NO DATA;
    88  
    89  -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures
    90  -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day )
    91  SELECT add_continuous_aggregate_policy('trades_candle_7_days', start_offset => INTERVAL '21 days', end_offset => INTERVAL '7 days', schedule_interval => INTERVAL '7 days');
    92  
    93  
    94  
    95  -- +goose Down
    96  SELECT remove_continuous_aggregate_policy('trades_candle_7_days', true);
    97  
    98  DROP MATERIALIZED VIEW trades_candle_7_days;
    99  
   100  SELECT remove_continuous_aggregate_policy('trades_candle_12_hours', true);
   101  
   102  DROP MATERIALIZED VIEW trades_candle_12_hours;
   103  
   104  SELECT remove_continuous_aggregate_policy('trades_candle_8_hours', true);
   105  
   106  DROP MATERIALIZED VIEW trades_candle_8_hours;
   107  
   108  SELECT remove_continuous_aggregate_policy('trades_candle_4_hours', true);
   109  
   110  DROP MATERIALIZED VIEW trades_candle_4_hours;
   111  
   112  SELECT remove_continuous_aggregate_policy('trades_candle_30_minutes', true);
   113  
   114  DROP MATERIALIZED VIEW trades_candle_30_minutes;