| CREATE TABLE ticks( | |
| ts DateTime64(3), ex LowCardinality(String), symbol LowCardinality(String), | |
| kind LowCardinality(String), side Nullable(String), | |
| price Float64, size Nullable(Float64), | |
| best_bid Nullable(Float64), best_ask Nullable(Float64), | |
| funding_rate Nullable(Float64), funding_ts Nullable(DateTime64(3)), | |
| open_interest Nullable(Float64), mark_price Nullable(Float64), index_price Nullable(Float64) | |
| ) ENGINE=MergeTree ORDER BY (symbol, ts); | |
| CREATE MATERIALIZED VIEW spread_1s TO spread_1s AS | |
| SELECT symbol, ex, toStartOfSecond(ts) t, | |
| anyLast(best_ask - best_bid) spread, | |
| anyLast((best_ask + best_bid)/2) mid | |
| FROM ticks WHERE kind='ticker' GROUP BY symbol, ex, t; |