Соединение AsOf

Что такое соединение AsOf?

Данные временных рядов не всегда идеально выровнены. Часы могут немного отличаться, или может быть задержка между причиной и следствием. Это может затруднить соединение двух наборов упорядоченных данных. Соединения AsOf являются инструментом для решения этой и других аналогичных проблем.

Одна из проблем, которую помогают решить соединения AsOf, заключается в поиске значения изменяемого свойства в определенный момент времени. Этот случай использования настолько распространен, что отсюда и название:

Дайте мне значение свойства на эту дату.

Однако в более общем плане соединения AsOf воплощают в себе некоторые общие семантические концепции временного анализа, которые могут быть громоздкими и медленными при реализации в стандартном SQL.

Пример набора данных портфеля

Давайте начнем с конкретного примера. Предположим, у нас есть таблица котировок акций prices со временными отметками:

У нас есть еще одна таблица, содержащая портфель holdings в разные моменты времени:

Для загрузки этих таблиц в DuckDB выполните:

CREATE TABLE prices AS FROM 'https://duckdb.org/data/prices.csv';
CREATE TABLE holdings AS FROM 'https://duckdb.org/data/holdings.csv';

Внутренние соединения AsOf

Мы можем вычислить значение каждого владения в этот момент времени, найдя последнюю цену перед временной отметкой владения с помощью соединения AsOf:

SELECT h.ticker, h.when, price * shares AS value
FROM holdings h
ASOF JOIN prices p
       ON h.ticker = p.ticker
      AND h.when >= p.when;

Это прикрепляет значение владения в это время к каждой строке:

По существу, он выполняет функцию, определяемую путем поиска близлежащих значений в таблице prices. Обратите также внимание, что отсутствующие ticker значения не имеют соответствия и не отображаются в выходных данных.

Внешние соединения AsOf

Поскольку AsOf производит не более одного совпадения из правой части, таблица левой стороны не будет увеличиваться в результате соединения, но может уменьшиться, если на правой стороне отсутствуют временные метки. Для обработки этой ситуации можно использовать внешнее соединение AsOf:

SELECT h.ticker, h.when, price * shares AS value
FROM holdings h
ASOF LEFT JOIN prices p
            ON h.ticker = p.ticker
           AND h.when >= p.when
ORDER BY ALL;

Как можно ожидать, это приведет к NULL ценам и значениям вместо удаления строк левой части, когда нет тикера или время находится до начала цен.

Соединения AsOf с ключевым словом USING

До сих пор мы явно указываем условия для AsOf, но SQL также имеет упрощенную синтаксическую конструкцию условия соединения для распространенного случая, когда имена столбцов одинаковы в обеих таблицах. Этот синтаксис использует ключевое слово USING для перечисления полей, которые должны сравниваться на равенство. AsOf также поддерживает этот синтаксис, но с двумя ограничениями:

• Последним полем является неравенство
• Неравенство является >= (самый распространенный случай)

Наш первый запрос можно записать как:

SELECT ticker, h.when, price * shares AS value
FROM holdings h
ASOF JOIN prices p USING (ticker, "when");

Пояснение выбора столбцов с использованием USING в соединениях ASOF

Когда вы используете ключевое слово USING в соединении, столбцы, указанные в USING фразе, объединяются в наборе результатов. Это означает, что если вы выполните:

SELECT *
FROM holdings h
ASOF JOIN prices p USING (ticker, "when");

Вы получите только столбцы h.ticker, h.when, h.shares, p.price. Столбцы ticker и when будут появляться только один раз, со значениями ticker и when из левой таблицы (holdings).

Это поведение приемлемо для столбца ticker, так как значение одинаково в обеих таблицах. Однако для столбца when значения могут отличаться между таблицами из-за условия >= , используемого в соединении AsOf. Соединение AsOf предназначено для сопоставления каждой строки в левой таблице (holdings) с ближайшей предшествующей строкой в правой таблице (prices) на основе столбца when.

Если вы хотите извлечь столбец when из обеих таблиц, чтобы увидеть обе временные метки, вам необходимо явно перечислить столбцы вместо того, чтобы полагаться на *, как показано ниже:

SELECT h.ticker, h.when AS holdings_when, p.when AS prices_when, h.shares, p.price
FROM holdings h
ASOF JOIN prices p USING (ticker, "when");

Это гарантирует, что вы получите полную информацию из обеих таблиц, избегая возможной путаницы, вызванной поведением ключевого слова USING по умолчанию.

См. также

Для получения подробностей реализации см. статью в блоге «Соединения DuckDB AsOf: нечеткие временные запросы».