Эта функция сэкономит вам часы боли при написании SQL.

Сколько раз вам приходилось собирать слишком сложное решение для решения простой проблемы? Если вы когда-либо программировали раньше, вы, вероятно, можете перечислить хотя бы несколько примеров. Для меня несколько таких моментов были связаны со сводными таблицами. Они звучат так просто, но их сложно написать на SQL.

К счастью, я недавно обнаружил, что dbt имеет функцию в dbt utils, которая делает создание сводных таблиц быстрым и простым. Никаких сложных агрегатов или объединений не требуется!

Что такое сводная таблица?

Во-первых, давайте начнем с того, что такое сводная таблица. Нет, это не сводная таблица Excel. Это функция, которая превращает уникальные значения в столбце в отдельный столбец, заполняя соответствующую ячейку для этой строки логическим значением.

Например, сводные данные, которые выглядят так…

… приведет к данным, которые выглядят так…

Варианты использования сводной таблицы

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

Использование сводных таблиц также идеально подходит для обеспечения уникальности первичного ключа в таблице. Часто, когда у вас есть столбцы, содержащие разные атрибуты, которые могут существовать для нескольких идентификаторов, ваш первичный ключ больше не становится уникальным. В итоге вы получите несколько строк с одним и тем же идентификатором из-за одного столбца, который создает дубликаты.

Все еще не понимаете?

Давайте посмотрим на это вино. Посмотрите, как значения в каждом столбце одинаковы, ЗА ИСКЛЮЧЕНИЕМ столбца wine_features? Поскольку это вино сухое, органическое и биодинамическое, создается новый ряд, чтобы представить каждую из этих характеристик.

Обычно это происходит из-за способа соединения таблиц в базе данных. К сожалению, сводные таблицы — единственный способ избежать этой проблемы и сохранить чистоту моделей данных.

Как вы сводите таблицы с помощью dbt?

dbt содержит функцию под названием dbt_utils.pivot, которая помогает вам сворачивать таблицы с помощью одной простой строки кода.

Это выглядит так:

{{ dbt_utils.pivot(‘<column name>’, dbt_utils.get_column_values(ref(‘<table name>’), ‘<column name’)) }}

Вы просто указываете имя столбца, который хотите повернуть. Убедитесь, что вы включили его в одинарные кавычки.

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

К сожалению, это должна быть постоянная таблица в вашей базе данных. Вы не можете ссылаться на таблицу, созданную CTE, что является обломом. Мне пришлось создать постоянную таблицу с таблицами, которые мне нужно было соединить вместе, чтобы затем ссылаться на эту сводную функцию. Просто сделайте его своей промежуточной моделью.

При использовании сводной функции в реальном запросе это должно выглядеть примерно так:

select
   wine_name,
   {{ dbt_utils.pivot('wine_features', dbt_utils.get_column_values(ref('wine_features_joined'), 'wine_features')) }}
from {{ ref('wine_features_joined') }} 
group by wine_name

Вы можете видеть, что функция заменяет имя столбца в вашем SQL-запросе. Столбцы, по которым вы группируете, являются единственными другими столбцами, которые должны быть выбраны.

Также обратите внимание, что я использую функцию {{ ref() }}, потому что я выбираю из постоянной таблицы, которая уже существует как модель данных в моей среде dbt. Нет выбора из CTE!

Если мы начнем с таблицы вин и их различных характеристик, которая выглядит так…

Выполнение запроса, который я показал выше, с помощью сводной функции приведет к тому, что таблица будет выглядеть так:

Обратите внимание, что функции, которые когда-то были значениями в столбце wine_features, теперь являются заголовками столбцов. Логические значения в этих столбцах показывают, присутствовала ли эта функция в исходном столбце.

Еще одна вещь, о которой следует помнить…

Часто бывает сложно работать с типами данных сводных столбцов. Убедитесь, что вы привели их к соответствующему типу данных и переименовали их по своему усмотрению. Здесь вы можете видеть, что тип данных сводного столбца — число. Очевидно, это неправильно.

Мне пришлось переделать и переименовать свои столбцы, выполнив это для каждого из моих столбцов:

wine_features."organic" AS is_organic

Кавычки позволяют выбрать столбец в Snowflake, несмотря на то, что тип данных отличается от ожидаемого.

Заключение

Теперь вы готовы сводить таблицы с помощью функции сводки dbt utils! Это сделает вашу жизнь намного проще, а код — чище. Следите за новыми статьями о полезных функциях, предоставляемых вам dbt в их пакете utils.

А пока узнайте больше о dbt и современном стеке данных, подписавшись на мою рассылку.