SQL CROSS JOIN
Действительно редко встречающееся, но важное соединение: перекрестное соединение. Большинство вашей аналитической инженерной работы потребует объединения таблиц для создания надежных, широких таблиц, которые в конечном итоге будут доступны конечным бизнес-пользователям. Эти модели обычно создаются с использованием в основном левых (и некоторых внутренних) соединений.
Перекрестное соединение, с другой стороны, обычно берет два столбца между двумя объектами базы данных и создает таблицу, формируя комбинацию всех строк из соединенных таблиц, называемую декартовым произведением. Используйте эту страницу, чтобы понять, как использовать перекрестные соединения и где вы можете их применить в вашем проекте dbt.
Как создать перекрестное соединение
В отличие от обычных соединений, перекрестные соединения не используют ключи для объединения объектов базы данных:
select
<fields>
from <table_1> as t1
cross join <table_2> as t2
Перекрестные соединения — это одна из тех концепций SQL, которые легче понять на конкретном примере, так что давайте перейдем к нему.
Пример перекрестного соединения SQL
Таблица A date_spine
| date |
|---|
| 2022-01-01 |
| 2022-01-02 |
| 2022-01-03 |
Таблица B users
| user_id |
|---|
| 1 |
| 3 |
| 4 |
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
Этот простой запрос вернет декартово произведение всех пользователей и дат, фактически создавая уникальную комбинацию пользователя на ка ждую дату в строке:
| user_id | type |
|---|---|
| 1 | 2022-01-01 |
| 1 | 2022-01-02 |
| 1 | 2022-01-03 |
| 2 | 2022-01-01 |
| 2 | 2022-01-02 |
| 2 | 2022-01-03 |
| 3 | 2022-01-01 |
| 3 | 2022-01-02 |
| 3 | 2022-01-03 |
В сгенерированной таблице выше уникальный ключ — это комбинация user_id и date для каждой строки. Чтобы добавить в эту таблицу, вы можете сгенерировать с использованием MD5-хэша макроса generate_surrogate_key в dbt-utils (например, {{ dbt_utils.generate_surrogate_key(user_id, type) }}), который в конечном итоге может быть присоединен к другим таблицам.
Пример использования перекрестного соединения SQL
Когда сгенерированная таблица выше может быть полезной? Перекрестное соединение уникальных дат и пользователей может быть эффективным способом создания базовой таблицы для присоединения различных счетчиков событий, таких как ключевые события на веб-сайте, в электронной почте или продукте. Эти таблицы отчетного типа полезны для предоставления конечным бизнес-пользователям в BI-инструментах для просмотра агрегированных счетчиков на день на пользователя и других полезных метрик.