🗄️ SQL

Короткий ответ Нужно использовать GROUP BY ROLLUP(region, category) (или явный GROUPING SETS ((region, category), (region), ())), который за один проход выдаёт и детальные группы, и промежуточные подытоги, и общий итог, помечая «свёрнутые» измерения значением NULL. Как это работает подробнее Как это работает: ROLLUP(a, b) строит иерархию подытогов: сначала группы по (a, b), затем по (a) с агрегацией по всем b, затем общий итог без разбивки вообще — причём на местах «свёрнутых» столбцов в результате появляется NULL. GROUPING SETS даёт более гибкий контроль, позволяя явно указать произвольный набор нужных комбинаций группировки, не ограничиваясь строгой иерархией. Пример кода Что использовать на практике На практике: такой подход заменяет несколько отдельных запросов (детальный + подытог по региону + общий итог) одним, что удобно для построения сводных таблиц в отчётах, но требует аккуратной функции GROUPING() для различения «настоящего» NULL в данных от NULL, означающего «эта строка — подытог». Проверка: Прове

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

Короткий ответ Нужно получить значение предыдущего месяца функцией LAG(revenue) OVER (ORDER BY month) и вычислить (revenue - prev_revenue) / prev_revenue * 100, аккуратно обработав случай, когда предыдущего месяца нет или его выручка равна нулю. Как это работает подробнее LAG(column, n) возвращает значение этого столбца из строки, которая находится на n позиций раньше в заданном порядке окна (по умолчанию n = 1); для первой строки окна результат LAG — NULL, так как «предыдущей» строки не существует. Пример кода Что использовать на практике перед делением обязательно нужно защититься от деления на ноль или на NULL — например, через NULLIF(prev_revenue, 0), иначе запрос либо упадёт с ошибкой деления на ноль, либо (при NULL в знаменателе) просто тихо вернёт NULL. Также важно убедиться, что ряд месяцев не содержит пропущенных месяцев — иначе LAG «перепрыгнет» через отсутствующий месяц, не заметив разрыва. Проверка: для второго месяца в выборке вручную вычислить (revenue2 - revenue1) / revenue1 * 100 и сравнить с

Читать полностьюgrowth-rate

Короткий ответ Нужно перечислить оба столбца в GROUP BY region, category — тогда каждая уникальная пара «регион + категория» станет отдельной группой со своим агрегатом. Как это работает подробнее при группировке по нескольким столбцам СУБД формирует группы по уникальным комбинациям значений всех указанных столбцов, а не отдельно по каждому. Количество итоговых строк равно числу различных сочетаний «регион + категория», встречающихся в данных. Пример кода Что использовать на практике На практике: перед интерпретацией такой таблицы стоит проверить, что в данных нет пропущенных комбинаций (например, региона без какой-то категории) — это может быть реальным отсутствием продаж, а может быть проблемой данных, если такая комбинация ожидалась. Результат: Результат — по одной строке на каждую встретившуюся пару. Проверка: сумма total_amount по всем строкам должна совпасть с SELECT SUM(amount) FROM sales. Подводные камни Частая ошибка: группируют только по одному из двух столбцов, а второй добавляют в SELECT без агрег

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

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

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

Короткий ответ Нужно прогнать оба варианта через EXPLAIN ANALYZE на представительном объёме данных (близком к продакшену, а не на маленькой тестовой выборке) и сравнить фактическое время выполнения и объём обработанных данных, а не полагаться на общие утверждения вроде «CTE всегда медленнее подзапроса». Как это работает подробнее в современных версиях PostgreSQL (начиная с 12) простой, не рекурсивный CTE по умолчанию может быть «встроен» (inlined) оптимизатором в основной запрос точно так же, как подзапрос — то есть различие в производительности, характерное для более старых версий, во многих случаях исчезло. Поведение может отличаться, если CTE явно помечен как материализуемый (AS MATERIALIZED) или используется многократно. Пример кода Что использовать на практике Практика. универсального правила «что быстрее» нет — решение зависит от конкретной версии СУБД, объёма данных, наличия индексов и того, насколько сложен план для каждого варианта. Самый надёжный способ — измерить на реалистичных данных, а не делать

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

Короткий ответ Можно посчитать два отдельных агрегата в одном SELECT, используя AVG(CASE WHEN status = 'delivered' THEN processing_time END) и аналогично для cancelled, без необходимости делать два отдельных запроса или GROUP BY status. Как это работает подробнее CASE внутри агрегатной функции возвращает значение только для строк, удовлетворяющих условию, а для остальных — NULL (если ELSE не задан); агрегатные функции (AVG, SUM, COUNT) по умолчанию игнорируют NULL, поэтому в расчёт попадают только нужные строки. Пример кода Что использовать на практике На практике: такой подход удобен, когда нужно вывести несколько «разрезов» метрики в одной строке результата — например, для сравнения в одной таблице отчёта, а не в нескольких строках после GROUP BY status. В PostgreSQL для той же задачи есть более короткий синтаксис AVG(processing_time) FILTER (WHERE status = 'delivered'). Результат: Результат — одна строка с двумя числами для сравнения. Проверка: сравнить avg_delivered с результатом отдельного запроса SELECT

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

Короткий ответ Нужно пронумеровать дубликаты внутри каждой группы бизнес-ключа функцией ROW_NUMBER() OVER (PARTITION BY business_key ORDER BY updated_at DESC) и оставить (или удалить все, кроме) строки с номером 1. Как это работает подробнее PARTITION BY business_key делит строки на группы дублей, ORDER BY updated_at DESC внутри каждой группы ставит самую свежую запись первой, и ROW_NUMBER() присваивает ей номер 1; все остальные строки группы получают номера 2, 3 и так далее и считаются «лишними» дублями. Пример кода Что использовать на практике в PostgreSQL для реального удаления обычно оборачивают это в CTE и используют DELETE ... WHERE id IN (SELECT id FROM ranked WHERE rn > 1); важно сначала выполнить только SELECT, чтобы визуально проверить, какие строки будут удалены, прежде чем запускать DELETE. В ClickHouse для таблиц с движком ReplacingMergeTree похожая задача решается через FINAL или OPTIMIZE ... DEDUPLICATE, что работает принципиально иначе. Проверка: количество строк с rn = 1 должно равняться COUN

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

Короткий ответ FLOAT/DOUBLE хранят числа с приблизительной точностью и могут давать мелкие ошибки округления при суммировании денежных значений, тогда как NUMERIC/DECIMAL (или целые копейки в INTEGER) хранят точное значение — поэтому для денег предпочтительны NUMERIC или целочисленное хранение в минимальных единицах. Как это работает подробнее FLOAT/DOUBLE PRECISION — это числа с плавающей точкой по стандарту IEEE 754, они не могут точно представить многие десятичные дроби (например, 0.1), из-за чего при множестве сложений может накопиться заметная погрешность. NUMERIC(p, s) хранит точное десятичное значение с заданной точностью, хоть и немного медленнее в вычислениях. Пример кода Что использовать на практике На практике: для сумм в отчётах, особенно при проверке «сходится ли общая выручка до копейки» с бухгалтерией, разница между FLOAT и NUMERIC может стать заметной на больших объёмах операций. Хранение цены как целого числа копеек — ещё один надёжный способ избежать проблем с округлением вообще. Результат:

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

Короткий ответ Материализованную таблицу или вью стоит создавать, когда один и тот же тяжёлый расчёт выполняется многократно (например, дашборд, который открывают десятки раз в день) и исходные данные меняются не чаще, чем требуется обновлять агрегат; если запрос выполняется редко или всегда должен быть «в реальном времени», обычный запрос на лету проще и избавляет от риска показать устаревшие данные. Как это работает подробнее материализованное представление (MATERIALIZED VIEW в PostgreSQL, таблица, обновляемая по расписанию, или материализованная вью в BigQuery) физически сохраняет результат агрегации, избавляя от повторного пересчёта по сырым данным при каждом обращении; обновление такой таблицы требует отдельного процесса (REFRESH MATERIALIZED VIEW, scheduled query и т.п.), который должен быть надёжным и мониториться отдельно. Пример кода Что использовать на практике Практика. выбор — это компромисс между скоростью чтения и свежестью данных плюс дополнительной инфраструктурной сложностью (ещё один процесс

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