🗄️ SQL

Короткий ответ Для строки ISO-формата используйте CAST(event_date_text AS DATE) или PostgreSQL-синтаксис event_date_text::date. После преобразования значение сравнивается как дата, а не лексикографически как текст. Как это работает подробнее Обычный CAST завершит запрос ошибкой, если встретит непарсируемую строку. Для нестандартного формата применяют явный парсер, например TO_DATE(value, 'DD.MM.YYYY'), но его поведение также нужно проверять на невалидных датах. Если преобразование выполняется над каждой строкой в WHERE, обычный индекс по текстовому столбцу может не использоваться. Надёжнее хранить очищенное значение в столбце типа date или создать подходящий функциональный индекс. Пример кода Что использовать на практике Используйте типизированный литерал. Запись DATE '2024-05-01' делает ожидаемый тип явным. Исправляйте модель данных. После очистки храните даты как date, а не как произвольный текст. Подводные камни Невалидная строка. Пустое значение, неверный день или смешанный формат могут остановить весь за

Читать полностьюcast

Короткий ответ Единственно верного ответа нет: решение зависит от требуемой свежести данных, количества и разнообразия нужных разрезов, объёма данных и стоимости вычислений — предагрегация выигрывает в скорости чтения и предсказуемой стоимости, но жертвует гибкостью разрезов и добавляет инфраструктурную сложность пайплайна обновления. Как это работает подробнее предагрегированные таблицы (построенные по расписанию через ETL/ELT) заранее сворачивают детальные данные до нужных измерений, резко уменьшая объём, который читается при каждом запросе отчёта; расчёт «на лету» всегда обращается к детальным данным и пересчитывает агрегаты заново при каждом запуске, что проще поддерживать, но дороже по ресурсам при росте объёма и частоты обращений. Компромисс. нет universal правильного порога объёма данных или частоты запросов, при котором нужно переходить к предагрегации — решение требует измерения реальной стоимости и задержки текущего подхода и сопоставления с инфраструктурными затратами на поддержку пайплайна обновле

Читать полностьюdata-freshness

Короткий ответ Нужно посчитать количество пользователей в каждой группе эксперимента через GROUP BY variant и сравнить фактическое соотношение с ожидаемым (например, 50/50) — SQL даёт только сырые числа, а формальную проверку (например, хи-квадрат) обычно делают уже вне SQL, хотя сами наблюдаемые и ожидаемые частоты можно посчитать запросом. Как это работает подробнее Как это работает: SELECT variant, COUNT(DISTINCT user_id) FROM experiment_assignments GROUP BY variant даёт фактическое число пользователей в каждой ветке; дальше это сравнивают с ожидаемым распределением (обычно пропорциональным доле трафика, заданной в конфигурации эксперимента) — большое отклонение сигнализирует о проблеме в рандомизации или в логировании, а не о реальном эффекте теста. Пример кода Что использовать на практике На практике: SRM нужно проверять до анализа основной метрики — если группы разъехались по размеру сильнее, чем можно объяснить случайностью, доверять результатам теста в целом нельзя, независимо от того, насколько «крас

Читать полностьюab-testing

Короткий ответ Нужно с помощью LAG посчитать разницу во времени с предыдущим событием того же пользователя, отметить начало новой сессии там, где разница больше 30 минут, а затем накопительной суммой этих отметок (через SUM(...) OVER) получить номер сессии для каждого события — это классический паттерн «gaps and islands». Как это работает подробнее Как это работает: на первом шаге LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) даёт время предыдущего события; на втором шаге условие event_time - prev_event_time > interval '30 minutes' (или prev_event_time IS NULL для самого первого события) помечается как 1 — начало новой сессии, иначе 0; на третьем шаге SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time) превращает эти отметки в растущий номер сессии. Пример кода Что использовать на практике На практике: порог в 30 минут — это условность, принятая во многих продуктовых аналитических системах по умолчанию, но не универсальный стандарт; для конкретного продукта имеет смысл подбир

Читать полностьюgaps-and-islands

Короткий ответ Нужно пронумеровать все касания (визиты/клики) каждого пользователя функцией ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touch_time) и оставить только строки с номером 1 — это и будет первое касание (first touch) для атрибуции. Как это работает подробнее Как это работает: PARTITION BY user_id группирует все касания одного пользователя, ORDER BY touch_time сортирует их по времени внутри группы, и ROW_NUMBER() = 1 однозначно выбирает самое раннее касание, даже если у пользователя их были десятки. Пример кода Что использовать на практике На практике: first-touch — одна из множества моделей атрибуции (есть также last-touch, линейная, с затухающим весом и т.д.), и выбор модели — не технический, а методологический вопрос: разные модели по-разному распределяют «вклад» каждого канала и могут давать очень разные выводы о том, какой канал эффективнее. Перед тем как использовать first-touch, стоит явно обозначить, что это осознанный выбор модели, а не единственно верный способ атрибуции. Проверка: Пр

Читать полностьюattribution

Короткий ответ CTE объявляется перед основным запросом конструкцией WITH имя AS (...). Именованный результат доступен ниже как таблица и помогает представить обработку данных последовательностью понятных шагов. Как это работает подробнее Несколько CTE перечисляются через запятую, причём следующий шаг может ссылаться на предыдущий. Это удобно для схемы: отфильтровать строки, агрегировать, добавить бизнес-правило, сформировать финальный вывод. CTE — часть одного SQL-запроса, а не постоянная или автоматически индексируемая таблица. Современный оптимизатор может встроить его в план либо материализовать в зависимости от диалекта и настроек. Пример кода Что использовать на практике Давайте смысловые имена. Название вроде monthly_sales лучше технического cte1. Один шаг — одна задача. Не разбивайте простой запрос на множество CTE без выигрыша в читаемости. Подводные камни Не гарантированный кэш. Повторная ссылка на CTE не всегда означает однократное вычисление. Граница оптимизации. В некоторых версиях и диалектах мат

Читать полностьюaggregations

Короткий ответ Нужно написать рекурсивный WITH RECURSIVE, где «якорная» часть задаёт стартовую дату, а рекурсивная часть добавляет по одному дню, пока не достигнута конечная дата; в BigQuery для той же задачи проще использовать встроенную функцию GENERATE_DATE_ARRAY. Как это работает подробнее рекурсивный CTE состоит из двух частей, объединённых UNION ALL: «базовый случай» (одна начальная строка) и «рекурсивный шаг», который ссылается на сам CTE и добавляет следующий день; СУБД выполняет рекурсивный шаг многократно, пока условие в WHERE не остановит рекурсию. Пример кода Что использовать на практике сгенерированный список дат обычно используют как «скелет» для LEFT JOIN к фактическим данным, чтобы явно увидеть дни без событий как 0, а не как отсутствующие строки — это важно для корректных графиков временных рядов и расчёта средних по дням. Результат — ровно 31 строка. Проверка: SELECT COUNT(*) FROM date_series должен совпасть с числом дней между начальной и конечной датой включительно. Подводные камни Не зада

Читать полностьюdates

Короткий ответ Нужно присоединить таблицу employees к самой себе по условию совпадения department_id, добавив условие e1.employee_id , чтобы не получить пары-дубликаты и пары сотрудника с самим собой. Как это работает подробнее self-join — это обычный JOIN, где обе стороны указывают на одну и ту же таблицу, но с разными алиасами (e1, e2); СУБД рассматривает это как соединение двух независимых копий данных по заданному условию. Пример кода Что использовать на практике На практике: условие e1.employee_id (а не !=) одновременно решает две проблемы: убирает пары вида «сотрудник сам с собой» и убирает зеркальные дубликаты вида (A, B) и (B, A), оставляя только одну версию каждой пары. Результат: Для отдела из 3 сотрудников результат — ровно 3 уникальные пары (а не 6). Проверка: для отдела с N сотрудниками количество пар должно равняться N*(N-1)/2. Подводные камни Частая ошибка: используют e1.employee_id != e2.employee_id без строгого неравенства порядка, из-за чего каждая пара сотрудников появляется в результате дв

Читать полностьюcombinations

Короткий ответ Конструкция CASE последовательно проверяет условия WHEN и возвращает значение для первого истинного условия. Если ни одно условие не подошло, используется ELSE или возвращается NULL. Как это работает подробнее Порядок ветвей важен: после первого совпадения остальные не рассматриваются. Поэтому диапазоны лучше задавать от частного к общему или как ясные непересекающиеся интервалы. Полученный псевдоним можно использовать как категорию в последующем запросе. Для агрегации без повторения выражения удобно сначала вычислить группу в CTE, а затем выполнить GROUP BY age_group. Пример кода Что использовать на практике Явно обработайте NULL. Выделите неизвестный возраст в отдельную группу, а не смешивайте его с группой старшего возраста. Проверьте границы. Все допустимые значения должны попадать ровно в один интервал. Подводные камни Перекрытия. Более широкое условие, записанное раньше, сделает следующую ветвь недостижимой. Аномалии. Отрицательный возраст или слишком большое значение не должны автоматиче

Читать полностьюcase-when