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

     1  -- +goose Up
     2  
     3  
     4  SELECT _timescaledb_internal.stop_background_workers();
     5  
     6  SELECT remove_continuous_aggregate_policy('trades_candle_1_minute');
     7  DROP MATERIALIZED VIEW IF EXISTS trades_candle_1_minute;
     8  
     9  CREATE MATERIALIZED VIEW trades_candle_1_minute
    10              WITH (timescaledb.continuous) AS
    11  SELECT market_id, time_bucket('1 minute', synthetic_time) AS period_start,
    12         first(price, synthetic_time) AS open,
    13         last(price, synthetic_time) AS close,
    14         max(price) AS high,
    15         min(price) AS low,
    16         sum(size) AS volume,
    17         sum(size * price) as notional,
    18         last(synthetic_time,
    19              synthetic_time) AS last_update_in_period
    20  FROM trades
    21  GROUP BY market_id, period_start WITH NO DATA;
    22  
    23  -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures
    24  -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day )
    25  
    26  SELECT add_continuous_aggregate_policy('trades_candle_1_minute', start_offset => INTERVAL '1 day', end_offset => INTERVAL '1 minute', schedule_interval => INTERVAL '1 minute');
    27  
    28  SELECT remove_continuous_aggregate_policy('trades_candle_5_minutes');
    29  DROP MATERIALIZED VIEW IF EXISTS trades_candle_5_minutes;
    30  
    31  CREATE MATERIALIZED VIEW trades_candle_5_minutes
    32              WITH (timescaledb.continuous) AS
    33  SELECT market_id, time_bucket('5 minutes', synthetic_time) AS period_start,
    34         first(price, synthetic_time) AS open,
    35         last(price, synthetic_time) AS close,
    36         max(price) AS high,
    37         min(price) AS low,
    38         sum(size) AS volume,
    39         sum(size * price) as notional,
    40         last(synthetic_time,
    41              synthetic_time) AS last_update_in_period
    42  FROM trades
    43  GROUP BY market_id, period_start WITH NO DATA;
    44  
    45  -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures
    46  -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day )
    47  SELECT add_continuous_aggregate_policy('trades_candle_5_minutes', start_offset => INTERVAL '1 day', end_offset => INTERVAL '5 minutes', schedule_interval => INTERVAL '5 minutes');
    48  
    49  SELECT remove_continuous_aggregate_policy('trades_candle_15_minutes');
    50  DROP MATERIALIZED VIEW IF EXISTS trades_candle_15_minutes;
    51  
    52  CREATE MATERIALIZED VIEW trades_candle_15_minutes
    53              WITH (timescaledb.continuous) AS
    54  SELECT market_id, time_bucket('15 minutes', synthetic_time) AS period_start,
    55         first(price, synthetic_time) AS open,
    56         last(price, synthetic_time) AS close,
    57         max(price) AS high,
    58         min(price) AS low,
    59         sum(size) AS volume,
    60         sum(size * price) as notional,
    61         last(synthetic_time,
    62              synthetic_time) AS last_update_in_period
    63  FROM trades
    64  GROUP BY market_id, period_start WITH NO DATA;
    65  
    66  -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures
    67  -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day )
    68  SELECT add_continuous_aggregate_policy('trades_candle_15_minutes', start_offset => INTERVAL '1 day', end_offset => INTERVAL '15 minutes', schedule_interval => INTERVAL '15 minutes');
    69  
    70  SELECT remove_continuous_aggregate_policy('trades_candle_1_hour');
    71  DROP MATERIALIZED VIEW IF EXISTS trades_candle_1_hour;
    72  
    73  CREATE MATERIALIZED VIEW trades_candle_1_hour
    74              WITH (timescaledb.continuous) AS
    75  SELECT market_id, time_bucket('1 hour', synthetic_time) AS period_start,
    76         first(price, synthetic_time) AS open,
    77         last(price, synthetic_time) AS close,
    78         max(price) AS high,
    79         min(price) AS low,
    80         sum(size) AS volume,
    81         sum(size * price) as notional,
    82         last(synthetic_time,
    83              synthetic_time) AS last_update_in_period
    84  FROM trades
    85  GROUP BY market_id, period_start WITH NO DATA;
    86  
    87  -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures
    88  -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day )
    89  
    90  SELECT add_continuous_aggregate_policy('trades_candle_1_hour', start_offset => INTERVAL '1 day', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '1 hour');
    91  
    92  SELECT remove_continuous_aggregate_policy('trades_candle_6_hours');
    93  DROP MATERIALIZED VIEW IF EXISTS trades_candle_6_hours;
    94  
    95  CREATE MATERIALIZED VIEW trades_candle_6_hours
    96              WITH (timescaledb.continuous) AS
    97  SELECT market_id, time_bucket('6 hours', synthetic_time) AS period_start,
    98         first(price, synthetic_time) AS open,
    99         last(price, synthetic_time) AS close,
   100         max(price) AS high,
   101         min(price) AS low,
   102         sum(size) AS volume,
   103         sum(size * price) as notional,
   104         last(synthetic_time,
   105              synthetic_time) AS last_update_in_period
   106  FROM trades
   107  GROUP BY market_id, period_start WITH NO DATA;
   108  
   109  -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures
   110  -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day )
   111  SELECT add_continuous_aggregate_policy('trades_candle_6_hours', start_offset => INTERVAL '1 day', end_offset => INTERVAL '6 hours', schedule_interval => INTERVAL '6 hours');
   112  
   113  SELECT remove_continuous_aggregate_policy('trades_candle_1_day');
   114  DROP MATERIALIZED VIEW IF EXISTS trades_candle_1_day;
   115  
   116  CREATE MATERIALIZED VIEW trades_candle_1_day
   117              WITH (timescaledb.continuous) AS
   118  SELECT market_id, time_bucket('1 day', synthetic_time) AS period_start,
   119         first(price, synthetic_time) AS open,
   120         last(price, synthetic_time) AS close,
   121         max(price) AS high,
   122         min(price) AS low,
   123         sum(size) AS volume,
   124         sum(size * price) as notional,
   125         last(synthetic_time,
   126              synthetic_time) AS last_update_in_period
   127  FROM trades
   128  GROUP BY market_id, period_start WITH NO DATA;
   129  
   130  SELECT add_continuous_aggregate_policy('trades_candle_1_day', start_offset => INTERVAL '3 days', end_offset => INTERVAL '1 day', schedule_interval => INTERVAL '1 day');
   131  
   132  CREATE OR REPLACE VIEW trades_candle_block AS
   133  SELECT market_id,  vega_time as period_start,
   134         first(price, synthetic_time) AS open,
   135         last(price, synthetic_time) AS close,
   136         max(price) AS high,
   137         min(price) AS low,
   138         sum(size) AS volume,
   139         last(synthetic_time, synthetic_time) AS last_update_in_period,
   140         sum(size * price) as notional
   141  
   142  FROM trades
   143  GROUP BY market_id, vega_time;
   144  
   145  SELECT _timescaledb_internal.start_background_workers();
   146  
   147  -- +goose Down
   148  
   149  DROP VIEW IF EXISTS trades_candle_block;