В СУБД для работы с временными рядами (time series DBMS) есть такое понятие, как ASOF JOIN. Хотя PostgreSQL и не поддерживает данный синтаксис, он позволяет выполнять те же запросы при помощи LATERAL JOIN’ов . Давайте разберемся, что это за JOIN’ы такие, и какую проблему они решают.
Рассмотрим такой сценарий. Есть два временных ряда. Для примера пусть будут температура и влажность:
ts TIMESTAMP NOT NULL ,
city TEXT NOT NULL ,
temperature INT NOT NULL ) ;
CREATE TABLE humidity (
ts TIMESTAMP NOT NULL ,
city TEXT NOT NULL ,
humidity INT NOT NULL ) ;
INSERT INTO temperature ( ts , city , temperature )
SELECT ts + ( INTERVAL ’60 minutes’ * random ( ) ) , city , 30 * random ( )
FROM generate_series ( ‘2022-01-01’ :: TIMESTAMP ,
‘2022-01-31’ , ‘1 day’ ) AS ts ,
unnest ( array [ ‘Moscow’ , ‘Berlin’ ] ) AS city;
INSERT INTO humidity ( ts , city , humidity )
SELECT ts + ( INTERVAL ’60 minutes’ * random ( ) ) , city , 100 * random ( )
FROM generate_series ( ‘2022-01-01’ :: TIMESTAMP ,
‘2022-01-31’ , ‘1 day’ ) AS ts ,
unnest ( array [ ‘Moscow’ , ‘Berlin’ ] ) AS city;
Временные ряды приезжают из разных источников и не обязательно синхронно, поэтому к временным меткам добавлен случайный разброс. Если теперь мы попытаемся сделать JOIN первой таблицы со второй по временной метке, то ничего не выйдет:
FROM temperature AS t
LEFT JOIN humidity AS h ON t . ts = h . ts;
Временные метки разные и сделать JOIN по их равенству невозможно.
Специально для таких случаев и придумали ASOF JOIN’ы . Их семантика следующая: берем данные из левой таблицы, а из правой — данные с такой же временной меткой или меньше. То есть, мы как бы спрашиваем «каким было последнее известное значение в правой таблице на такой-то момент времени».
PostgreSQL позволяет решить эту задачу, используя в запросе ключевое слово LATERAL. Подробнее о нем можно прочитать в документации . Вкратце, LATERAL можно использовать в SELECT- и JOIN-частях запроса. Ключевое слово позволяет ссылаться на строки, извлеченные из таблиц остальной частью запроса. Обычные SELECT и JOIN так не умеют.
Вот пример запроса:
FROM temperature AS t
LEFT JOIN LATERAL
( SELECT * FROM humidity
WHERE city = t . city AND ts <= t . ts
ORDER BY ts DESC LIMIT 1
) AS h ON TRUE
WHERE t . ts < ‘2022-01-05’
Попробуйте убрать ключевое слово LATERAL и посмотрите, что будет.
Убедимся, что запрос возвращает то, что нужно:
ts | city | temperature
—————————-+———+————-
2022-01-01 00:15:56.237508 | Berlin | 6
2022-01-01 00:32:47.457148 | Moscow | 29
2022-01-02 00:19:26.726612 | Berlin | 2
2022-01-02 00:44:55.010613 | Moscow | 6
2022-01-03 00:30:12.831369 | Moscow | 6
2022-01-03 00:50:51.026596 | Berlin | 2
2022-01-04 00:03:29.004641 | Berlin | 15
2022-01-04 00:38:16.257926 | Moscow | 9
=# SELECT * FROM humidity WHERE ts < ‘2022-01-05’ ORDER BY ts, city;
ts | city | humidity
—————————-+———+———-
2022-01-01 00:04:29.034182 | Moscow | 79
2022-01-01 00:04:50.904393 | Berlin | 36
2022-01-02 00:00:31.940381 | Berlin | 46
2022-01-02 00:12:16.295859 | Moscow | 18
2022-01-03 00:26:22.054815 | Moscow | 43
2022-01-03 00:53:20.204302 | Berlin | 66
2022-01-04 00:30:30.095735 | Moscow | 97
2022-01-04 00:35:36.344991 | Berlin | 66
=# SELECT … приведенный выше запрос с LATERAL …
ts | city | temperature | humidity
—————————-+———+————-+———-
2022-01-01 00:15:56.237508 | Berlin | 6 | 36
2022-01-01 00:32:47.457148 | Moscow | 29 | 79
2022-01-02 00:19:26.726612 | Berlin | 2 | 46
2022-01-02 00:44:55.010613 | Moscow | 6 | 18
2022-01-03 00:30:12.831369 | Moscow | 6 | 43
2022-01-03 00:50:51.026596 | Berlin | 2 | 46 <— !!!
2022-01-04 00:03:29.004641 | Berlin | 15 | 66
2022-01-04 00:38:16.257926 | Moscow | 9 | 97
Обратите внимание на третью строчку снизу. Влажность в Берлине была взята за второе января, а не за третье. Но если вы внимательно посмотрите на временные метки, то убедитесь, что результат верный.
И напоследок проверим, что такие запросы могут в индексы:
— как-то правильнее, но и ускоряет запрос на ~13% по сравнению
— с простым индексом.
CREATE INDEX humidity_idx ON humidity ( ts , city ) INCLUDE ( humidity ) ;
— Нужно добавить побольше данных, чтобы СУБД предпочла использовать
— индекс, а не сканировать закэшированные строки в памяти.
INSERT INTO temperature ( ts , city , temperature )
SELECT ts + ( INTERVAL ’60 minutes’ * random ( ) ) , city , 30 * random ( )
FROM generate_series ( ‘2010-01-01’ :: TIMESTAMP ,
‘2020-12-31’ , ‘1 hour’ ) AS ts ,
unnest ( array [ ‘Moscow’ , ‘Berlin’ ] ) AS city;
INSERT INTO humidity ( ts , city , humidity )
SELECT ts + ( INTERVAL ’60 minutes’ * random ( ) ) , city , 100 * random ( )
FROM generate_series ( ‘2010-01-01’ :: TIMESTAMP ,
‘2020-12-31’ , ‘1 hour’ ) AS ts ,
unnest ( array [ ‘Moscow’ , ‘Berlin’ ] ) AS city;
— Смотрим EXPLAIN (или EXPLAIN ANALYZE).
EXPLAIN SELECT t . ts , t . city , t . temperature , h . humidity
FROM temperature AS t
LEFT JOIN LATERAL
( SELECT * FROM humidity
WHERE city = t . city AND ts <= t . ts
ORDER BY ts DESC LIMIT 1
) AS h ON TRUE ;
Какой же план мы увидим? Что можно сказать об эффективности запроса? Ответы на эти вопросы я предлагаю получить вам самостоятельно, в качестве упражнения.
