SQL CROSS JOIN
Действительно редко встречающееся, но важное соединение: перекрестное соединение. Большинство вашей аналитической инженерной работы потребует объединения таблиц для создания надежных, широких таблиц, которые в конечном итоге будут доступны конечным бизнес-пользователям. Эти модели обычно создаются с использованием в основном левых (и некоторых внутренних) соединений.
Перекрестное соединение, с другой стороны, обычно берет два столбца между двумя объектами базы данных и создает таблицу, формируя комбинацию всех строк из соединенных таблиц, называемую декартовым произведением. Используйте эту страницу, чтобы понять, как использовать перекрестные соединения и где вы можете их применить в вашем проекте dbt.
Как создать перекрестное соединение
В отличие от обычных соединений, перекрестные соединения не используют ключи для объединения объектов базы данных:
select
<fields>
from <table_1> as t1
cross join <table_2> as t2
Перекрестные соединения — это одна из тех концепций SQL, которые легче понять на конкретном примере, так что давайте перейдем к нему.
Пример перекрестного соединения SQL
Таблица A date_spine
| Loading table... |
Таблица B users
| Loading table... |
select
users.user_id as user_id,
date.date as date
from {{ ref('users') }} as users
cross join {{ ref('date_spine') }} as date
order by 1
Этот простой запрос вернет декартово произведение всех пользователей и дат, фактически создавая уникальную комбинацию пользователя на каждую дату в строке:
| Loading table... |
В сгенерированной таблице выше уникальный ключ — это комбинация user_id и date для каждой строки. Чтобы добавить primary key в эту таблицу, вы можете сгенерировать surrogate key с использованием MD5-хэша макроса generate_surrogate_key в dbt-utils (например, {{ dbt_utils.generate_surrogate_key(user_id, type) }}), который в конечном итоге может быть присоединен к другим таблицам.
Пример использования перекрестного соединения SQL
Когда может быть полезна сгенерированная выше таблица? Кросс‑соединение уникальных дат и пользователей может быть эффективным способом создать базовую таблицу, к которой затем можно присоединять различные счётчики событий, например ключевые события на сайте, в email‑рассылках или в продукте. Такие отчётные таблицы удобно предоставлять конечным бизнес‑пользователям в BI‑инструментах, чтобы анализировать агрегированные показатели — количество событий по дням и пользователям, а также другие полезные метрики.
