🗄️ SQL

Короткий ответ Нужно сначала посчитать максимальную сумму заказа на каждого клиента в отдельном подзапросе (GROUP BY customer_id), а затем присоединить исходную таблицу заказов к этому подзапросу по customer_id и совпадению суммы с максимумом. Как это работает подробнее подзапрос SELECT customer_id, MAX(amount) AS max_amount FROM orders GROUP BY customer_id возвращает одну строку на клиента с его максимальной суммой; соединение этого результата с исходной таблицей по customer_id и amount = max_amount оставляет только те строки заказов, которые действительно равны этому максимуму. Пример кода Что использовать на практике если несколько заказов одного клиента имеют одинаковую максимальную сумму, такой подход вернёт их все — это нужно явно учитывать и решать, что требуется в задаче: «все заказы с максимальной суммой» или «ровно один, самый ранний/последний из них». Результат — для каждого клиента одна или несколько строк с максимальной суммой заказа. Проверка: для одного клиента вручную найти максимум среди его

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

Короткий ответ Нужно сгенерировать полный список дат (через generate_series в PostgreSQL или аналог в другом диалекте) и сделать LEFT JOIN к агрегированным по дням продажам, заменив NULL в сумме на 0 через COALESCE. Как это работает подробнее Как это работает: generate_series(start_date, end_date, interval '1 day') в PostgreSQL возвращает по одной строке на каждый день периода; LEFT JOIN сохраняет все эти даты независимо от того, нашлась ли для них продажа, и COALESCE(SUM(amount), 0) явно превращает отсутствие продаж в 0, а не в пропущенную точку графика. Пример кода Что использовать на практике На практике: без этого шага график временного ряда просто «перескочит» через дни без продаж, что может визуально искажать тренд (линия на графике соединит соседние по данным, но не по времени точки) и затруднить расчёт корректных средних за период, включающих нулевые дни. Проверка: Результат — ровно 31 строка, включая дни с daily_amount = 0. Проверка: SELECT COUNT(*) должен быть 31 независимо от того, были ли продажи

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

Короткий ответ Нужно присоединить таблицу фактов к таблице SCD2 (с полями valid_from/valid_to) по условию, что дата события попадает в интервал действия версии записи: fact.event_date BETWEEN scd.valid_from AND scd.valid_to. Как это работает подробнее Как это работает: SCD Type 2 хранит историю изменений атрибута как несколько строк на один бизнес-объект, каждая с периодом действия (valid_from, valid_to, где valid_to может быть NULL или датой в будущем для текущей версии). Соединение по интервалу даёт именно ту версию атрибута, которая была актуальна в момент события, а не текущую версию на сегодня. Пример кода Что использовать на практике На практике: частая ошибка в компаниях без SCD2 — соединение факта с «текущим» измерением (например, текущий регион клиента), что искажает исторические отчёты: если клиент переехал из региона A в регион B, все его прошлые заказы в отчёте задним числом «переезжают» в регион B, хотя фактически были сделаны из региона A. Проверка: Проверка: для клиента, сменившего регион, зака

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

Короткий ответ В PostgreSQL можно использовать EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)), где AGE возвращает интервал между двумя датами, а EXTRACT(YEAR FROM ...) берёт из него количество полных лет с учётом того, был ли уже день рождения в этом году. Как это работает подробнее AGE(end_date, start_date) в PostgreSQL корректно учитывает календарные месяцы и дни, а не просто делит разницу в днях на 365 — поэтому результат точнее для расчёта «полных лет». Простое вычисление (CURRENT_DATE - birth_date) / 365 даёт приблизительный и иногда неверный на один год результат из-за високосных лет. Пример кода Что использовать на практике для возрастной сегментации важно явно зафиксировать точку отсчёта — «на сегодня» (CURRENT_DATE) или «на момент события» (например, на дату заказа), особенно если отчёт строится не в реальном времени, а на исторических данных. Для birth_date = '2000-05-01' и текущей даты 2024-04-20 результат — 23 (день рождения ещё не наступил). Проверка: вручную посчитать возраст для нескольких гр

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

Короткий ответ Нужно использовать оконную функцию с PARTITION BY category: SUM(revenue) OVER (PARTITION BY category) даёт сумму по всей категории в каждой строке товара, после чего легко посчитать долю revenue / category_total, не теряя детализацию по товарам. Как это работает подробнее PARTITION BY делит все строки на независимые группы (аналогично GROUP BY), но, в отличие от GROUP BY, оконная функция не сворачивает строки в одну — каждая исходная строка сохраняется, и к ней просто «пристёгивается» агрегат по её группе. Пример кода Что использовать на практике это ключевое отличие оконных функций от обычной агрегации — можно одновременно видеть детальный уровень (товар) и контекст уровня группы (категория) в одной строке, что удобно для отчётов типа «вклад каждого товара в выручку категории». Сумма pct_of_category внутри одной категории должна быть примерно равна 100%. Проверка: SELECT category, SUM(pct_of_category) FROM (...) GROUP BY category — для каждой категории около 100. Подводные камни пытаются решит

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

Короткий ответ В PostgreSQL выражение DATE_TRUNC('month', order_date) приводит все даты одного месяца к общей границе. Затем GROUP BY создаёт одну группу на месяц, а COUNT и SUM вычисляют метрики. Как это работает подробнее Группировка по полноценной границе месяца сохраняет год. В отличие от неё, EXTRACT(MONTH FROM order_date) объединит, например, январь 2024 и январь 2025 в одну группу. Диалекты отличаются: BigQuery использует DATE_TRUNC(order_date, MONTH), а ClickHouse — toStartOfMonth(order_date). Пример кода Что использовать на практике Группируйте по дате месяца. Это сохраняет хронологию и позволяет корректно сортировать несколько лет. Сверяйте итог. Сумма месячных продаж должна совпасть с общей суммой за тот же фильтрованный период. Подводные камни COUNT(*). Он считает строки заказов; если после соединения один заказ размножен, метрика будет завышена. Пропущенные месяцы. Агрегация не создаёт месяцы без заказов; для полного ряда нужен календарь дат.

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

Короткий ответ Нужно соединить таблицу назначений в эксперимент с таблицей целевых событий через LEFT JOIN, сгруппировать по варианту и посчитать и общее число пользователей, и число совершивших целевое действие, чтобы конверсия (converted / total) и размер выборки были видны вместе. Как это работает подробнее Как это работает: LEFT JOIN от назначений к событиям гарантирует, что пользователи без целевого действия тоже учтутся (с NULL в поле события), и COUNT(DISTINCT CASE WHEN event_user_id IS NOT NULL THEN assignment_user_id END) корректно посчитает только тех, у кого нашлось событие, деля на общее COUNT(DISTINCT assignment_user_id). Пример кода Что использовать на практике На практике: важно всегда показывать рядом с конверсией размер выборки каждой группы — без этого «разница в конверсии 2 п.п.» невозможно правильно интерпретировать: на малой выборке это может быть шумом, на большой — значимым эффектом. SQL может посчитать сырые цифры, но решение о статистической значимости обычно требует дополнительного т

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

Короткий ответ В PostgreSQL можно использовать PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) как агрегатную функцию с GROUP BY region; в BigQuery — аналог PERCENTILE_CONT(amount, 0.5) OVER (PARTITION BY region) как оконную функцию, в ClickHouse — quantile(0.5)(amount). Как это работает подробнее Как это работает: медиана — это 50-й перцентиль, значение, относительно которого ровно половина наблюдений меньше и половина больше (с интерполяцией для PERCENTILE_CONT при чётном количестве наблюдений). В отличие от среднего, медиана устойчива к выбросам — один аномально большой заказ не сдвинет медиану так, как сдвинет среднее. Пример кода Что использовать на практике На практике: медиану стоит приводить рядом со средним, когда в данных есть выбросы (крупные корпоративные заказы на фоне розничных) — иначе среднее может создать ложное впечатление о «типичном» заказе. Разные СУБД по-разному называют и реализуют функции перцентиля, и важно проверить, какая именно интерполяция используется (PERCENTILE_CONT интерпо

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

Короткий ответ Нужно использовать оконную функцию SUM(daily_amount) OVER (ORDER BY day), которая по умолчанию считает сумму от первой строки окна до текущей, формируя накопительный итог. Как это работает подробнее при наличии ORDER BY внутри OVER(...) без явного указания рамки (ROWS BETWEEN) большинство СУБД по умолчанию применяют рамку RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — то есть суммируют все строки от начала до текущей по порядку сортировки. Пример кода Что использовать на практике перед накопительной суммой по дням данные обычно сначала агрегируют до одной строки на день (через GROUP BY в CTE), а затем оконную функцию применяют уже к агрегированным дневным суммам — иначе при RANGE-рамке строки с одинаковой датой «сольются» в одну точку накопления, что может быть неожиданным. Последняя строка cumulative_amount должна совпадать с общей суммой за весь период. Проверка: сравнить это значение с SELECT SUM(amount) FROM orders. Подводные камни не агрегируют данные до одного дня предварительно и по

Читать полностьюcumulative-sum