SQL magic

В общем, люди, работающие с данными, предпочитают более детализированные данные менее детализированным. Метки времени > даты, ежедневные данные > еженедельные данные и т.д.; наличие данных на более детализированном уровне всегда позволяет вам приблизиться. Однако, скорее всего, вы смотрите на свои данные на несколько отдаленном уровне — еженедельно, ежемесячно или даже ежегодно. Для этого вам понадобится удобная функция, которая поможет округлить поля даты или времени.

Функция DATE_TRUNC усечет дату или время до первой единицы заданной части даты. Многословно, многословно, многословно! Что это на самом деле значит? Если вы усечете 2021-12-13 до месяца, она вернет 2021-12-01 (первый день месяца).

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

«Сколько времени прошло с тех пор, как этот клиент последний раз делал у нас заказ?»

«Какое среднее количество дней до конверсии?»

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

Функция DATEDIFF возвращает разницу в указанных единицах (например, дни, недели, годы) между начальной и конечной датой/временем. Это простая и широко используемая функция, которую вы будете использовать чаще, чем ожидаете.

Мы все, вероятно, были в такой ситуации: Таблица A содержит 56 столбцов, и мы хотим выбрать все, кроме одного из них (column_56). Итак, начнем...

select
	column_1,
	column_2,
	column_3,
	please_save_me…
from {{ ref('table_a') }}

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

Но что, если бы был способ сократить эти 56+ строк кода до нескольких? Вот тут-то и приходит на помощь удобный макрос dbt.

Существует множество различных функций для работы с датами в SQL — например, DATEDIFF, DATEADD, DATE_PART и DATE_TRUNC. У каждой из них свои случаи использования, и понимание того, как и когда их использовать, является основой работы с SQL. Является ли какая-либо из них такой же простой в использовании, как функция EXTRACT? Ну, это тема для другого обсуждения...

В этом посте мы подробно рассмотрим функцию EXTRACT, как она работает и почему мы её используем.

Мы все были в такой ситуации:

• В форме регистрации пользователь A ввел своё имя как Kira Furuichi, пользователь B ввел его как john blust, а пользователь C написал DAvid KrevitT (что это было, Дэвид??)
• Ваши инженеры бэкенда настаивают, чтобы электронные адреса клиентов были написаны заглавными буквами
• Все ваши имена для отслеживания событий написаны строчными буквами

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

Итак, как создать единообразие для строковых значений, которые вы собираете из всех ваших источников данных? Функция LOWER!

В области аналитической инженерии неизбежно столкновение с моментами, когда в ваших данных появляются загадочные или бесполезные пустые значения. Null-значения, безусловно, имеют своё время и место, но когда вам нужно, чтобы эти null-значения были заполнены более значимыми данными, на помощь приходит COALESCE.

COALESCE — это невероятно полезная функция, которая позволяет заполнить бесполезные пустые значения, которые могут появиться в ваших данных. Как говорит аналитический инженер Лорен Бенезра, вы, вероятно, почти никогда не увидите модель данных, которая не использует COALESCE где-то.

Мы все это делали: расширяли данные во время объединения, создавая дублирующиеся записи (иногда дублирующиеся в нескольких экземплярах).

Тот случай, когда исторические данные о доходах удвоились в понедельник? Классический пример расширения.

Можно ли было этого избежать? Да, очень просто: определив уникальность grain для table с помощью первичного ключа и обеспечив её с помощью теста dbt.

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

Почему первичные ключи важны​

Мы все знаем одно из самых фундаментальных правил в данных: каждая table должна иметь primary key. Первичные ключи важны по многим причинам:

• Они гарантируют, что в вашей таблице нет дублирующихся строк
• Они помогают устанавливать связи с другими таблицами
• Они позволяют быстро определить grain таблицы (например, таблица customers с PK customer_id имеет одну строку на каждого клиента)
• Вы можете тестировать их в dbt, чтобы убедиться, что ваши данные полные и уникальные

Я использовал функцию dateadd в SQL тысячи раз.

Я гуглил синтаксис функции dateadd в SQL все эти разы, кроме одного, когда решил нажать кнопку "Мне повезет" и попробовать.

При переключении между диалектами SQL (BigQuery, Postgres и Snowflake — мои основные), я буквально никогда не могу запомнить порядок аргументов (или точное название функции) dateadd.

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

Это неблагодарная, но необходимая задача. В SQL часто нужно объединять (UNION ALL) две или более таблицы вертикально, чтобы объединить их значения.