🗄️ SQL

Короткий ответ Нужно объявить несколько CTE через запятую в одном блоке WITH, где каждый следующий CTE может ссылаться на предыдущие, формируя цепочку шагов «очистка → дедупликация → агрегация → финальный отбор». Как это работает подробнее в блоке WITH cte1 AS (...), cte2 AS (SELECT ... FROM cte1 ...), cte3 AS (...) каждый следующий CTE может использовать результат предыдущего по имени, как обычную таблицу; основной SELECT в конце обычно обращается к последнему CTE. Пример кода Что использовать на практике такой подход делает сложную логику читаемой и тестируемой по шагам — можно временно заменить финальный SELECT на SELECT * FROM cte2 и проверить промежуточный результат, не переписывая весь запрос. Это особенно полезно, когда данные перед агрегацией нужно почистить (убрать дубликаты, отфильтровать тестовые записи, привести типы). Проверка: временно выполнить SELECT COUNT(*) FROM cleaned_orders и SELECT COUNT(*) FROM deduped_orders, чтобы увидеть, сколько строк отсеялось на каждом шаге. Подводные камни нагром

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

Короткий ответ Не начинайте с угадывания причины. Сначала воспроизведите метрику эталонным запросом, определите момент изменения и оцените масштаб, затем последовательно локализуйте вклад по платформе, региону, источнику и шагу воронки. Как это работает подробнее Полезно заставлять аналитика перед каждым запросом записывать: наблюдение, гипотезу, ожидаемый результат и решение для каждого исхода. Так SQL становится инструментом проверки, а не генератором случайных срезов. Декомпозиция должна сохранять арифметическую связь с верхнеуровневой метрикой: например, выручка равна числу заказов, умноженному на средний чек, а сумма сегментов должна сходиться с общим итогом. Пример кода Что использовать на практике Начните с простого. Проверьте полноту загрузки, календарь, изменение определения и известные релизы. Сужайте пространство. Найдите сегмент, дату и компоненту, которые объясняют основную часть отклонения. Сохраняйте журнал гипотез. Он показывает, что уже исключено, и позволяет другому аналитику продолжить расс

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

Короткий ответ Нужно использовать оператор LIKE с шаблоном WHERE email LIKE '%@gmail.com', где % обозначает любую последовательность символов до искомой подстроки. Как это работает подробнее LIKE сравнивает строку с шаблоном, где % заменяет любое количество (в том числе ноль) произвольных символов, а _ — ровно один символ. По умолчанию в большинстве СУБД LIKE чувствителен к регистру (в PostgreSQL для нечувствительного поиска есть ILIKE). Пример кода Что использовать на практике На практике: шаблон '%@gmail.com' найдёт email, заканчивающиеся именно на этот домен, но не отличит реальный домен от строки, которая просто содержит эту подстроку в другом месте, если убрать % в конце. Для больших таблиц поиск с % в начале шаблона (LIKE '%gmail%') обычно не может использовать обычный B-tree индекс эффективно и может быть медленным. Результат: Результат — все клиенты с почтой на gmail.com независимо от регистра. Проверка: вручную просмотреть 5-10 строк результата и убедиться, что домен действительно gmail.com, а не пох

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

Короткий ответ Нужно сделать LEFT JOIN от полного списка дат периода (сгенерированного или из отдельной календарной таблицы) к агрегированным по дням данным и отобрать те даты, где после соединения не нашлось ни одной строки событий (COUNT(event) = 0 или event_id IS NULL). Как это работает подробнее если просто сделать SELECT DISTINCT date FROM events, пропущенные дни физически отсутствуют в данных и их невозможно «увидеть» — нужна внешняя точка отсчёта (полный список ожидаемых дат), от которой можно оттолкнуться через LEFT JOIN, чтобы явно выявить даты без единой строки в основной таблице. Пример кода Что использовать на практике Практика. такую проверку целесообразно делать частью регулярного мониторинга качества данных (data quality) — например, ежедневная автоматическая проверка «за последние 24 часа данные точно поступили» помогает поймать сбой в пайплайне до того, как искажённый отчёт увидит бизнес. Проверка. Проверка: для каждой найденной «пустой» даты проверить логи загрузки данных (ETL) за этот день,

Читать полностьюcalendar-table

Короткий ответ Нужно для каждого клиента определить месяц первой покупки (когорту), затем для каждой его покупки посчитать «номер месяца жизни» относительно когорты, и сгруппировать по когорте и номеру месяца, посчитав количество уникальных активных клиентов. Как это работает подробнее шаг 1 — CTE с MIN(order_date) по клиенту для определения когорты; шаг 2 — присоединение этой когорты к каждой покупке и вычисление разницы в месяцах между покупкой и датой когорты (например, через DATE_PART или DATE_DIFF в зависимости от диалекта); шаг 3 — GROUP BY cohort_month, month_number с COUNT(DISTINCT customer_id). Пример кода Что использовать на практике удержание обычно представляют как таблицу «когорта × номер месяца» с процентом от исходного размера когорты (месяц 0 = 100%). Важно явно решить, что считается «активностью» — любая покупка, определённое действие, вход в приложение — и зафиксировать это определение, так как оно сильно влияет на итоговые цифры. Проверка: для month_number = 0 количество активных клиентов д

Читать полностьюcohort-analysis

Короткий ответ Нужно сначала для каждого пользователя определить дату первой активности (через MIN(event_date) в CTE), затем в основном запросе помечать событие как «новый пользователь», если его дата совпадает с датой первой активности, и как «вернувшийся» — если не совпадает, и сгруппировать по дате и этой метке. Как это работает подробнее сравнение event_date = first_seen_date для каждой строки активности однозначно классифицирует её на «первый визит» (новый пользователь в этот день) или «повторный визит» (вернувшийся пользователь); агрегация COUNT(DISTINCT user_id) по дате и метке даёт обе метрики за один проход по данным. Пример кода Что использовать на практике Практика. важно явно договориться об определении «нового» и «вернувшегося» пользователя относительно выбранного события — активность в приложении, покупка, регистрация — разные определения дают разные абсолютные числа, хотя общая логика расчёта одна и та же. Проверка. Проверка: new_users, просуммированные по всем дням, должны равняться общему чис

Читать полностьюconditional-aggregation

Короткий ответ Нужно использовать CONCAT(first_name, ' ', COALESCE(last_name, '')) или оператор || в PostgreSQL, сначала заменив возможный NULL в фамилии на пустую строку через COALESCE. Как это работает подробнее оператор конкатенации || в PostgreSQL возвращает NULL, если хотя бы один из операндов NULL, из-за чего всё итоговое поле «пропадает»; функция CONCAT в PostgreSQL устроена мягче и обычно трактует NULL как пустую строку, но поведение может отличаться между диалектами, поэтому надёжнее явно обезопасить себя COALESCE. Пример кода Что использовать на практике На практике: перед склеиванием текстовых полей стоит всегда продумывать: что должно получиться, если одно из полей пустое? Для отчётов обычно хотят увидеть «Иван» вместо пустой строки или NULL, а не терять всю запись. Результат: Для клиента с first_name = 'Анна', last_name = NULL результат — 'Анна ' (с пробелом, который можно дополнительно обработать TRIM). Проверка: сравнить количество непустых full_name с COUNT(*) клиентов. Подводные камни Частая

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

Короткий ответ Сначала GROUP BY customer_id формирует одну строку на клиента, затем SUM(amount) вычисляет объём покупок. ORDER BY ... DESC ставит крупнейшие суммы первыми, а LIMIT 10 оставляет десять строк. Как это работает подробнее Ограничение без сортировки не означает топ: без ORDER BY СУБД вправе вернуть произвольные строки. Для воспроизводимого результата при одинаковых суммах добавьте уникальный вторичный критерий. Если бизнес-задача требует включить всех клиентов, разделивших десятое место, фиксированный LIMIT 10 не подходит. Тогда используют RANK или DENSE_RANK и фильтр по рангу. Пример кода Что использовать на практике Обычный топ-10. Применяйте стабильную сортировку и LIMIT 10. Топ с ничьими. Рассчитайте ранг по сумме в CTE и оставьте ранг не больше десяти. Подводные камни NULL в сумме. Если все значения клиента равны NULL, итоговая сумма тоже будет NULL. Размножение до агрегации. Неверное соединение способно завысить сумму клиента.

Читать полностьюgroup-by

Короткий ответ Нужно отсортировать результат по стабильному критерию и использовать LIMIT 20 OFFSET 20, где OFFSET пропускает первые 20 строк, а LIMIT возвращает следующие 20. Как это работает подробнее OFFSET n указывает СУБД пропустить первые n строк отсортированного результата перед тем, как начать отдавать строки согласно LIMIT. Без явного и однозначного ORDER BY порядок строк не гарантирован, а значит «страницы» могут перекрываться или задваиваться между запросами. Пример кода Что использовать на практике На практике: постраничный вывод обязательно нужно сортировать по уникальному или хотя бы детерминированному ключу (например, customer_id), иначе при наличии одинаковых значений в сортируемом столбце строки могут «перетасоваться» между вызовами, и часть клиентов будет показана два раза или не показана вовсе. Результат: Результат — клиенты с 21-го по 40-й в порядке возрастания customer_id. Проверка: первая строка этой «страницы» должна совпадать с 21-й строкой полного списка, отсортированного тем же образ

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