Как агрегировать биржевые свечи через timescaledb?
Пилю диплом по рыночным данным и встала задача отображать биржевые свечи. Есть установленная postgresql 12, накатил timescaledb. Создаю таблицы:
Собственно, в data_raw таблицу залил тестовых данных за последние пару лет по нескольким тикерам и запустил тестовый клиент, который имитирует живую биржу и наполняет рандомными значениями, которые приближены к реальному поведению.
Так вот, те данные, что залил, по итогу не агрегируются почему-то, а те, что пишу тестовым клиентам (примерно 3600 записей в минуту в так называемый "пик активности") агрегируются в свечи как надо. Последние 5 минут в real-time агрегаты, а предыдущие 10 в continuous.
Проблема в том, что select свечей потом выполняется достаточно долго. Исходя из explain он перебирает кучу чанков. Даже за последней свечой ходит в чанки примерно 6-7 секунд. limit 100 где-то 7-8 секунд. Но это 5-минутные свечи, те же недельные очень долго выполняются.
Таких вью больше, просто не стал все добавлять. Хотелось бы понять правильный алгоритм использования таких агрегаций, а к остальным уже применю.
CREATE TABLE IF NOT EXISTS data_raw ( ticktime timestamp with time zone NOT NULL, ticker text COLLATE pg_catalog."default" NOT NULL, board text COLLATE pg_catalog."default", exchange text COLLATE pg_catalog."default", price double precision, amount bigint ); SELECT create_hypertable('data_raw','ticktime', chunk_time_interval => INTERVAL '1 day', migrate_data := true, if_not_exists => TRUE); -- Create views CREATE MATERIALIZED VIEW IF NOT EXISTS candle_5m WITH (timescaledb.continuous, timescaledb.create_group_indexes = true) AS SELECT ticker, board, exchange, time_bucket('5 minutes', ticktime) AS bucket, FIRST(price, ticktime) as open, LAST(price, ticktime) as close, MIN(price) as low, MAX(price) as high, sum(amount*price) as volume FROM data_raw GROUP BY ticker, board, exchange, bucket; SELECT add_continuous_aggregate_policy( 'candle_5m', start_offset => INTERVAL '15 minutes', end_offset => INTERVAL '5 minutes', schedule_interval => INTERVAL '5 minutes', if_not_exists => true ); |
CREATE TABLE IF NOT EXISTS data_raw ( ticktime timestamp with time zone NOT NULL, ticker text COLLATE pg_catalog."default" NOT NULL, board text COLLATE pg_catalog."default", exchange text COLLATE pg_catalog."default", price double precision, amount bigint ); SELECT create_hypertable('data_raw','ticktime', chunk_time_interval => INTERVAL '1 day', migrate_data := true, if_not_exists => TRUE); -- Create views CREATE MATERIALIZED VIEW IF NOT EXISTS candle_5m WITH (timescaledb.continuous, timescaledb.create_group_indexes = true) AS SELECT ticker, board, exchange, time_bucket('5 minutes', ticktime) AS bucket, FIRST(price, ticktime) as open, LAST(price, ticktime) as close, MIN(price) as low, MAX(price) as high, sum(amount*price) as volume FROM data_raw GROUP BY ticker, board, exchange, bucket; SELECT add_continuous_aggregate_policy( 'candle_5m', start_offset => INTERVAL '15 minutes', end_offset => INTERVAL '5 minutes', schedule_interval => INTERVAL '5 minutes', if_not_exists => true );
Подскажите, пожалуйста, что не так с агрегациями начудил. Timescale впервый раз использую и доку еще не всю прочитал, но скоро защита и уже нет времени читать всё заново.
Дополнительно:
Задавая вопрос по базам данных, пользуйтесь терминологией из этой предметной области. А рассуждения насчёт свечей - это в медицинский форум.
PS. SELECT create_hypertable(..., SELECT add_continuous_aggregate_policy(... - это ещё что за функции?
Это функция непрерывных агрегатов. Обновляет агрегированные данные в чанках только по изменённым параметрам.
Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.
Пока нет других ответов. Будьте первым, кто поможет автору.
Ответить на вопрос
Для агрегации биржевых свечей через TimescaleDB вам необходимо создать соответствующие запросы SQL, которые будут выполнять нужные операции над данными. В данном случае, предположим, что у вас есть таблица, где хранятся данные о биржевых свечах (например, цена открытия, цена закрытия, максимальная и минимальная цены за определенный период времени).
Прежде всего, у вас должна быть создана таблица для хранения данных о биржевых свечах. Например:
CREATE TABLE candles ( time TIMESTAMP NOT NULL, open FLOAT, high FLOAT, low FLOAT, close FLOAT );
Затем, чтобы агрегировать данные по определенному временному интервалу (например, по дням или часам), вы можете использовать функцию time_bucket, которая позволяет разбить временной ряд на интервалы. Например, чтобы агрегировать данные по дням, вы можете написать запрос следующим образом:
SELECT time_bucket('1 day', time) AS bucket_time, first(open, time) AS open, max(high) AS high, min(low) AS low, last(close, time) AS close FROM candles GROUP BY bucket_time ORDER BY bucket_time;
Этот запрос разбивает данные по дням и агрегирует цену открытия, максимальную и минимальную цены, а также цену закрытия для каждого дня. Вы можете изменить временной интервал в функции time_bucket на любой другой, в зависимости от ваших потребностей.
Таким образом, используя подобные запросы SQL, вы сможете агрегировать данные о биржевых свечах через TimescaleDB и анализировать их в нужном формате.