Логический порядок: FROM и JOIN → WHERE → GROUP BY → HAVING → оконные функции → SELECT → DISTINCT → UNION → ORDER BY → LIMIT. HAVING работает уже по группам, поэтому в нём доступны агрегаты, а в WHERE нет. CTE из WITH логически вычисляется до FROM основного запроса (планировщик может встроить его в запрос), DISTINCT применяется к результату SELECT, то есть после оконных функций.
Из этого следуют частые ошибки: алиас из SELECT недоступен в WHERE и GROUP BY, результат оконной функции нельзя отфильтровать в том же запросе, а ORDER BY по алиасу работает, потому что он идёт после SELECT. Это логический порядок: физически оптимизатор переставляет шаги, но результат обязан совпасть.
WHERE фильтрует строки до группировки, HAVING фильтрует группы после неё и может использовать агрегаты. Условие на обычную колонку в HAVING формально допустимо, если колонка входит в GROUP BY, но это заставляет базу группировать лишние строки: год заказа ставится в WHERE, а порог «больше 10 заказов» в HAVING.
Нельзя: окна считаются после WHERE и HAVING. Оконное выражение выносится в подзапрос или CTE, а фильтр WHERE rn = 1 пишется уровнем выше. По той же причине оконную функцию нельзя вложить в другую и нельзя использовать в GROUP BY.
Для INNER JOIN разницы нет: строка либо соединилась и прошла фильтр, либо нет, планировщик даже строит одинаковый план. Для LEFT JOIN разница принципиальная. Условие на правую таблицу в ON применяется при соединении: строки левой таблицы без пары остаются с NULL справа. То же условие в WHERE применяется после соединения и отбрасывает строки, где правая часть NULL, превращая LEFT JOIN во внутренний. Условие на левую таблицу (t2.age > 10, где t2 слева) в ON и в WHERE даёт одинаковый набор строк, потому что левые строки LEFT JOIN не отбрасывает, но в ON оно повлияет лишь на то, найдётся ли пара, а в WHERE уберёт строку целиком.
Сравнение с NULL даёт NULL, а WHERE оставляет только строки, где условие истинно. Чтобы NULL считался обычным значением, нужен IS NULL в условии или IS DISTINCT FROM вместо <>.
Один NULL в подзапросе делает результат пустым: x <> NULL неизвестно для любого x, и ни одна строка не проходит. Безопасная замена: NOT EXISTS с коррелированным подзапросом. Обычный IN при NULL в списке работает ожидаемо: истина, если значение нашлось.
count(*) считает строки, count(col) считает строки, где колонка не NULL, count(DISTINCT col) считает уникальные не-NULL значения. Отсюда типичная ошибка: count(o.order_id) после LEFT JOIN даёт ноль для клиентов без заказов, а count(*) даст единицу.
NULL ни с чем не соединяется, даже с другим NULL: NULL = NULL даёт NULL, а не TRUE. При INNER JOIN строки с NULL в ключе выпадают с обеих сторон, при LEFT JOIN левые строки с NULL остаются с NULL справа. Дубли ключа перемножаются: если 2 встречается два раза слева и два раза справа, в результате будет четыре строки с двойкой. Для примера t1.num1 = (1,2,2,3,3,4,NULL,NULL) и t2.num2 = (1,2,2,3,5,NULL,NULL): 1 даст одну строку, 2 четыре строки, 3 две строки, 4 одну строку с NULL справа, два NULL слева по одной строке с NULL справа. Итого 10 строк. Если NULL нужно считать равными, пишут ON t1.k = t2.k OR (t1.k IS NULL AND t2.k IS NULL) или IS NOT DISTINCT FROM.
Деление целых даёт целое с отбрасыванием дробной части. Один из операндов нужно сделать дробным до деления: 100.0 * a / b или a::numeric / b. Самое частое место ошибки это доля в процентах, которая «вдруг» стала нулём.
float хранит двоичное приближение и накапливает погрешность: 0.1 + 0.2 не равно 0.3, а сумма миллиона платежей расходится с бухгалтерией на копейки. numeric считает точно в десятичной системе, за это платят скоростью, и для денег это правильная цена.
В PostgreSQL и Greenplum: ts::date или date_trunc('day', ts) для даты, ts::time для времени, extract(month from ts) или date_part('year', ts) для частей, date_trunc('month', ts) для первого числа месяца, to_char(ts, 'YYYY-MM') для строки с месяцем. Для фильтра по периоду лучше не извлекать части, а сравнивать с границами полуинтервалом: условие по самой колонке использует индекс и партиции, а extract(...) = 2025 нет.
now() и current_timestamp возвращают время начала текущей транзакции, одно и то же в пределах всей транзакции; clock_timestamp() даёт реальное время в момент вызова и меняется от строки к строке; current_date только дату. Для загрузок в хранилище удобно now(): все строки одной вставки получают одинаковую метку.
Если в поле только цифры, достаточно привести к числу: col::bigint или col::bigint::text, если результат нужен строкой. Если могут быть буквы, приведение упадёт, и нужна строковая функция: ltrim(col, '0') убирает все ведущие нули, в том числе у 0007A → 7A. Граничный случай: строка из одних нулей после ltrim станет пустой, и если это нежелательно, добавляют coalesce(nullif(ltrim(col, '0'), ''), '0'). Универсальный вариант через регулярное выражение: regexp_replace(col, '^0+', '').
Нормализовать обе стороны: lower(trim(col)), при необходимости translate или regexp_replace для лишних символов. Если такое сравнение нужно в фильтре регулярно, создают индекс по выражению lower(col) или используют тип citext. Регистр в статусах («Paid» и «paid») это типичный дефект источника, и нормализовать его нужно один раз на входе в хранилище, а не в каждом отчёте.
Через остаток от деления: WHERE id % 2 = 0 для чётных и = 1 для нечётных; в Oracle и некоторых диалектах MOD(id, 2). Ловушка с отрицательными числами: в PostgreSQL -3 % 2 даёт −1, поэтому для нечётных надёжнее id % 2 <> 0. Если id строковый, сначала привести к числу. В Python аналогично % для остатка и // для целой части деления.
UNION объединяет результаты двух запросов и убирает дубли, UNION ALL оставляет всё. Число и порядок колонок должны совпадать, имена берутся из первого запроса. Для T1 из трёх одинаковых строк (13, 15) и T2 из (13, 15) и (13, 14) UNION вернёт две строки: (13, 14) и (13, 15), UNION ALL вернёт пять. Если типы колонок различаются, база пытается привести их к общему типу (int и numeric сойдутся в numeric, int и text дадут ошибку); явное приведение ::text снимает вопрос.
UNION убирает полные дубли строк, для этого сортирует или хеширует результат, и это дорого на больших объёмах. UNION ALL просто склеивает результаты. В загрузках почти всегда нужен UNION ALL: дубли там либо невозможны, либо их нужно считать, а не молча терять. UNION уместен, когда дубли между частями ожидаемы и не нужны.
INNER JOIN: от 0 (нет совпадений) до 12 (все ключи одинаковые, каждая строка с каждой). LEFT JOIN: от 3 (все левые без пары или по одной паре на строку) до 12. FULL JOIN: минимум 4 (три левые строки нашли по паре, одна правая осталась с NULL), максимум 12; при полном отсутствии совпадений будет 7 (3 + 4 строк с NULL), это не минимум, а другой крайний случай. CROSS JOIN: всегда 12. Смысл вопроса в том, чтобы вы показали понимание: соединение по неуникальному ключу умножает строки, а внешние соединения не теряют строки своей стороны.
SELECT value FROM t GROUP BY value HAVING count(*) > 1. Если нужны сами строки-дубли, а не значения, то оконная функция count(*) OVER (PARTITION BY value) в подзапросе и фильтр > 1 снаружи: она не сворачивает строки.
Полное сравнение с учётом дублей: объединить обе таблицы через UNION ALL с меткой источника, сгруппировать по всем колонкам и сравнить число строк из каждой таблицы: SELECT a, b, c, count(*) FILTER (WHERE src = 1) AS n1, count(*) FILTER (WHERE src = 2) AS n2 ... GROUP BY a, b, c HAVING count(*) FILTER (WHERE src = 1) <> count(*) FILTER (WHERE src = 2). Пустой результат означает эквивалентность. Другой способ: FULL JOIN по всем колонкам с IS NOT DISTINCT FROM (чтобы NULL совпадали) и фильтр на отсутствующую сторону, плюс проверка count(*) с каждой стороны для дублей. Сравнение агрегатов (count, sum) это быстрая предварительная проверка, но она не ловит перестановки значений между строками.
Пронумеровать чётные и нечётные отдельно оконной функцией row_number() OVER (ORDER BY id) в каждом подмножестве, затем соединить по номеру через FULL JOIN: число строк равно большему из двух наборов, а недостающие значения станут NULL. Общий случай обязателен: нельзя полагаться на то, что чётных и нечётных поровну или что числа идут подряд.
SELECT max(num) FROM (SELECT num FROM my_numbers GROUP BY num HAVING count(*) = 1) t. Агрегат max по пустому набору вернёт одну строку с NULL сам по себе, отдельная обработка не нужна; если написать без внешнего агрегата с ORDER BY ... LIMIT 1, пустой результат даст ноль строк вместо NULL.
Справочник категорий задаётся прямо в запросе через VALUES или UNION ALL, а счета присоединяются LEFT JOIN по условию диапазона: LEFT JOIN accounts a ON a.income BETWEEN c.lo AND c.hi (или через CASE внутри агрегата count(*) FILTER (WHERE ...) для каждой категории в одной строке с последующим UNPIVOT). Главная мысль: если нужны строки, которых нет в данных, их нужно породить из справочника, а не надеяться на GROUP BY.
Для таблицы из восьми строк, где в колонке flag два NULL: COUNT(*), COUNT(1), COUNT(0) и COUNT('flag') вернут 8, потому что константа не NULL и считается для каждой строки; COUNT(flag) вернёт 6, считаются только не-NULL значения; COUNT(NULL) всегда 0. SUM(flag) сложит значения без NULL. SUM(1) это не 1, а число строк: выражение вычисляется для каждой строки и суммируется, поэтому SUM(2) даст 16, SUM(4) даст 32, то есть константа умножается на число строк выборки. SUM(*) ошибка синтаксиса: звёздочка означает все колонки, а складывать строку с датой невозможно, SUM требует конкретное выражение.
С подзапросом: отобрать имена с HAVING COUNT(*) > 10 по оценке 5, затем посчитать двойки для них. Без подзапроса всё делается одной группировкой через условную агрегацию: SELECT name, SUM(CASE WHEN mark = 2 THEN 1 ELSE 0 END) AS twos FROM school GROUP BY name HAVING SUM(CASE WHEN mark = 5 THEN 1 ELSE 0 END) > 10. В PostgreSQL короче COUNT(*) FILTER (WHERE mark = 2). Приём «агрегат с условием» закрывает большинство задач вида «посчитать одно для тех, у кого другое».
Запятая между таблицами это декартово произведение, то же, что CROSS JOIN: каждая строка t1 сочетается с каждой строкой t2, а затем WHERE оставляет пары, где условие истинно. Для t1.a = (1, 1, 2, 3, 3) и t2.b = (1, 3, 2, 4, 5) считаем по строкам t1: для 1 подходят 3, 2, 4, 5 (четыре строки, и так дважды), для 2 подходят 3, 4, 5 (три), для 3 подходят 4, 5 (две, дважды): 4 + 4 + 3 + 2 + 2 = 15. Такой запрос это JOIN с неравенством, он не использует хеш-соединение и на больших таблицах превращается в вложенные циклы.
INNER оставляет только пары. LEFT сохраняет все строки слева, справа подставляя NULL. FULL сохраняет обе стороны, полезен для сверок двух источников. CROSS даёт все пары и нужен для календарей и сеток. Semi join через EXISTS проверяет наличие без размножения строк, anti join через NOT EXISTS ищет то, чего нет во второй таблице. LATERAL позволяет подзапросу ссылаться на строку слева: «последние три заказа каждого клиента».
Выбор строк, для которых нет пары во второй таблице. Три записи: NOT EXISTS (SELECT 1 FROM t2 WHERE t2.k = t1.k), LEFT JOIN t2 ... WHERE t2.k IS NULL, NOT IN (SELECT k FROM t2). Первая самая безопасная: NOT IN ломается на NULL в подзапросе, а LEFT JOIN с проверкой на NULL размножит строки при дублях ключа справа. Планировщик PostgreSQL для NOT EXISTS строит Hash Anti Join, это дёшево.
Ключ справа неуникален, и строки слева размножились: у заказа три платежа, и его сумма посчиталась трижды. Лечится агрегацией второй таблицы до нужного зерна перед соединением или заменой JOIN на EXISTS, если из второй таблицы нужен только фильтр. Проверка: число строк до и после соединения и count(DISTINCT ключ) справа.
Фильтр по правой таблице в WHERE отбрасывает строки с NULL и превращает LEFT JOIN во внутренний. Условия на правую таблицу пишутся в ON, условия на левую остаются в WHERE.
Условие в ON помимо ключа: o.created_at >= h.valid_from AND o.created_at < h.valid_to, полуинтервалом, иначе граница попадёт в два периода. Если заказ не попал ни в один период, он пропадёт из INNER JOIN, и это нужно решить явно: LEFT JOIN с проверкой или отдельная строка «нет истории».
Четыре способа: NOT EXISTS с коррелированным подзапросом; LEFT JOIN credits ... WHERE credits.client_id IS NULL; NOT IN (SELECT client_id FROM credits WHERE client_id IS NOT NULL); EXCEPT между множеством клиентов и множеством клиентов с кредитами. На собеседовании ждут, что вы назовёте ловушки: NOT IN с NULL в подзапросе вернёт пустоту, LEFT JOIN при нескольких кредитах у клиента размножит строки до проверки на NULL, EXCEPT теряет дубли и требует одинакового набора колонок.
Условная агрегация: SELECT id, max(value) FILTER (WHERE key = 'fio') AS fio, max(value) FILTER (WHERE key = 'city') AS city FROM source_data GROUP BY id (в базах без FILTER: max(CASE WHEN key = 'fio' THEN value END)). Набор колонок фиксирован в запросе; если ключи заранее неизвестны, нужен динамический SQL или crosstab из расширения tablefunc. Обратная операция, unpivot, делается через UNION ALL или LATERAL (VALUES ...).
FULL JOIN это совпавшие пары плюс несовпавшие строки слева плюс несовпавшие справа. LEFT минус INNER даёт строки левой таблицы без пары: 10 − 6 = 4. RIGHT минус INNER даёт строки правой без пары: 12 − 6 = 6. Итого 6 + 4 + 6 = 16. Та же логика работает в обратную сторону: по числу строк FULL и INNER можно посчитать, сколько строк не нашли пару с каждой стороны, это удобный приём для сверок.
Nested Loop для маленького внешнего набора с индексом внутри: для каждой внешней строки поиск по индексу. Hash Join для большой таблицы против меньшей по равенству: по меньшей строится хеш-таблица в памяти, большая читается один раз. Merge Join для двух потоков, отсортированных по ключу (индексы или явная сортировка), и для неравенств по диапазону. Выбор делает планировщик по статистике; принудить можно настройками enable_hashjoin, enable_nestloop, но в проде это крайняя мера, лучше обновить статистику или добавить индекс.
Прямое сравнение каждой строки с каждой это O(n × m). Хеширование меньшей таблицы стоит O(m), а поиск по хешу для каждой строки большой таблицы O(1), итого O(n + m). Цена: хеш-таблица должна помещаться в память (work_mem), иначе соединение идёт батчами с записью на диск, и в плане видно Batches: 2+.
В распределённых системах (Spark, Greenplum) соединение требует, чтобы строки с одинаковым ключом оказались на одном узле. Маленькую таблицу можно разослать целиком на каждый узел (broadcast) и соединить локально, без перераспределения большой таблицы по сети. Если забродкастить большую таблицу, каждый узел получит её копию целиком: память исполнителей переполнится, задача упадёт по OOM или уйдёт в спилл на диск; поэтому порог broadcast ограничен (в Spark по умолчанию 10 МБ, spark.sql.autoBroadcastJoinThreshold).
Способы доставить строки с одинаковым ключом соединения на один узел: разослать маленькую таблицу целиком на все узлы (broadcast motion) или перераспределить большую по ключу через сеть (redistribute motion). Именно перемещение данных обычно стоит дороже самого соединения, поэтому в Greenplum и ClickHouse ключ распределения выбирают под самые частые соединения, а в плане Greenplum ищут узлы Motion.
Уменьшить объём до соединения: отфильтровать и агрегировать каждую сторону до нужного зерна, оставить только нужные колонки. Соединять по ключу, по которому таблицы распределены или отсортированы, чтобы избежать перераспределения. Убедиться, что статистика свежая и планировщик выбрал hash или merge, а не nested loop по большой таблице. В MPP и Spark бродкастить маленькую сторону, бороться с перекосом ключа. Самое дорогое оптимизировать после: DISTINCT поверх размноженного результата не лечит, а маскирует проблему.
Три семейства: ранжирующие (row_number, rank, dense_rank, ntile), смещения (lag, lead, first_value, last_value, nth_value) и агрегаты как окна (sum, avg, count, min, max OVER). В OVER три части: PARTITION BY делит строки на группы, ORDER BY задаёт порядок внутри группы, а рамка ROWS / RANGE / GROUPS BETWEEN ... ограничивает, какие строки группы участвуют в вычислении для текущей строки.
Без ORDER BY рамка это вся партиция: sum(amount) OVER (PARTITION BY client) даёт итог по клиенту в каждой строке. С ORDER BY рамка по умолчанию становится RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: та же сумма превращается в накопительный итог. Отсюда «странности»: last_value с ORDER BY возвращает текущую строку, а count(*) OVER (ORDER BY d) считает нарастающим итогом.
При ничьей row_number даёт разные номера, rank одинаковый с пропуском следующего (1, 2, 2, 4), dense_rank одинаковый без пропуска (1, 2, 2, 3). Для «ровно одна строка на группу» нужен row_number с однозначной сортировкой. Для «топ-3 с ничьими» rank или dense_rank в зависимости от того, сколько строк допустимо вернуть.
Рамка окна по умолчанию заканчивается на текущей строке: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Нужно раскрыть её: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. first_value от этого не страдает, поэтому ошибка заметна не сразу.
Рамка задаёт, какие строки партиции попадают в вычисление для текущей: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW это скользящее окно из семи строк, ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING это «всё до текущей», RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW это окно по значению, а не по числу строк. ROWS считает строки, RANGE значения (с пропусками в датах они расходятся), GROUPS группы равных значений. Без рамки скользящие средние, «сумма до текущей» и «был ли сбой раньше в этот день» не выразить.
sum(x) OVER (PARTITION BY account ORDER BY d) даёт накопительный итог. avg(x) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) даёт среднее по семи строкам, RANGE interval '6 days' PRECEDING по семи календарным дням. Чем оконная функция отличается от агрегатной: агрегат сворачивает группу в одну строку, окно оставляет все строки и приписывает каждой результат по её окну.
Да: lead(x) OVER (ORDER BY d) равен lag(x) OVER (ORDER BY d DESC) с точностью до обработки ничьих в сортировке. На практике пишут ту, что читается естественнее: lag для «предыдущее значение», lead для «дата следующей версии минус день» при построении периодов.
Пропуски: generate_series по календарю и anti join к данным. Сессии: lag по времени внутри пользователя, флаг «новая сессия», если разрыв больше порога, и накопительная sum флага даёт номер сессии. Тот же приём «флаг + накопительная сумма» решает задачи про периоды ненулевого остатка, схлопывание пересекающихся интервалов и «не учитывать загрузки после первой ошибки за день».
lag(val) даёт только одну строку назад, а NULL может тянуться несколько строк подряд. Приём: count(val) OVER (PARTITION BY id ORDER BY ttime) увеличивается только на не-NULL строках и даёт номер группы, внутри которой первое значение и есть нужное; затем first_value(val) OVER (PARTITION BY id, grp ORDER BY ttime). Альтернатива: max(val) FILTER не подходит, но работает last_value(val) IGNORE NULLS там, где поддерживается (Oracle, Snowflake, не PostgreSQL).
Ранг строки равен числу строк со значением больше плюс один: SELECT a.id, a.points, (SELECT count(*) FROM t b WHERE b.points > a.points) + 1 AS rnk FROM t a, или то же через self-join с LEFT JOIN t b ON b.points > a.points ... GROUP BY a.id, a.points и count(b.id) + 1. Это даёт семантику rank (с пропусками при ничьих); для dense_rank считают count(DISTINCT b.points). Сложность O(n²), поэтому в проде так не пишут, но на собеседовании проверяют именно понимание, что такое ранг.
Классическая задача «острова»: у абонента есть сессии (start, end), и если end предыдущей равен start следующей, это одна непрерывная сессия. Сначала через LAG(end) OVER (PARTITION BY msisdn ORDER BY start) помечаем начало нового острова: CASE WHEN start = prev_end THEN 0 ELSE 1 END. Затем накопительной суммой этого флага SUM(flag) OVER (PARTITION BY msisdn ORDER BY start) получаем номер острова, и группируем по абоненту и номеру: MIN(start), MAX(end). Если допускаются пересечения, а не только стык, условие меняется на start <= MAX(end) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING).
row_number() OVER (PARTITION BY ключ ORDER BY версия DESC) и фильтр = 1 снаружи, в PostgreSQL короче через DISTINCT ON (ключ) ... ORDER BY ключ, версия DESC. max() по каждой колонке не работает: агрегаты не помнят строку, и статус возьмётся от одной версии, а сумма от другой.
Проверка: GROUP BY все колонки HAVING count(*) > 1 или сравнение count(*) с count(DISTINCT ...). Способы убрать дубли: SELECT DISTINCT или GROUP BY при пересборке таблицы; row_number() OVER (PARTITION BY ключ ORDER BY ...) и оставить первую; в PostgreSQL DISTINCT ON; удаление через DELETE ... USING по ctid (физическому адресу строки), когда у строк нет уникального идентификатора; CREATE TABLE AS SELECT DISTINCT с последующим переименованием, что на больших таблицах быстрее DELETE. В Python то же самое: set для хешируемых записей, dict по ключу с сохранением последней версии, pandas.drop_duplicates(subset=..., keep='last').
Защитить целевую таблицу и перестроить путь данных вокруг неё. Загрузку направить в staging-таблицу без ограничений, затем перекладывать в целевую запросом с дедупликацией (INSERT ... SELECT DISTINCT ON или ON CONFLICT DO NOTHING), а staging очищать. На целевой таблице поставить UNIQUE или первичный ключ: поток, который попытается вставить дубль напрямую, упадёт с ошибкой нарушения ограничения, и это правильно, потому что ошибка видна, а молчаливый дубль нет; если падение недопустимо, вставка через ON CONFLICT DO NOTHING. Периодичность очистки, если частота вставок неизвестна: не по расписанию, а по событию (после каждой загрузки staging) либо раз в сутки перед построением отчётов с мониторингом числа удалённых дублей, чтобы увидеть, когда проблема ушла.
В PostgreSQL: DELETE FROM t a USING t b WHERE a.ctid < b.ctid AND a.key = b.key или через row_number в CTE и удаление строк с номером больше 1. В хранилищах чаще не удаляют, а пересобирают таблицу запросом с дедупликацией: дешевле и безопаснее для повторного запуска.
Рекурсивный CTE: якорная часть выбирает сотрудников без руководителя с уровнем 0, рекурсивная часть соединяет таблицу с предыдущим шагом по manager_id = parent.id и прибавляет единицу: WITH RECURSIVE h AS (SELECT id, name, 0 AS lvl FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, h.lvl + 1 FROM employees e JOIN h ON e.manager_id = h.id) SELECT * FROM h ORDER BY lvl, name. При цикле в данных рекурсия не остановится: защищаются накоплением пути в массив и условием NOT e.id = ANY(path) или ограничением глубины.
Self-join таблицы по связи «сотрудник → руководитель»: SELECT e.name FROM employees e JOIN employees m ON m.employee_id = e.id_master WHERE e.salary > m.salary. Классический вопрос на понимание, что одна таблица может играть две роли в запросе; алиасы обязательны.
Сгруппировать по manager_id, оставить группы с count(*) >= 5, затем соединить с той же таблицей, чтобы получить имена: SELECT e.name FROM employee e JOIN (SELECT managerId FROM employee GROUP BY managerId HAVING count(*) >= 5) m ON m.managerId = e.id. Прямые подчинённые это одна ступень иерархии; для всех подчинённых нужна рекурсия.
Таблица employee(id, department_id, chief_flg, birth_dt): руководитель это строка с chief_flg = true в том же подразделении. Self join по подразделению с условием на флаг и дату: SELECT e.id FROM employee e JOIN employee c ON c.department_id = e.department_id AND c.chief_flg AND e.birth_dt < c.birth_dt WHERE NOT e.chief_flg. Старше означает более раннюю дату рождения, на этом часто путаются. Если руководителей в подразделении может быть несколько, сотрудник попадёт в результат по каждому, тогда нужен DISTINCT или условие «старше всех» через агрегат по подразделению.
Фильтр по периоду действия версии: WHERE :d >= valid_from AND :d < valid_to, где закрытая дата последней версии хранится как 9999-12-31 или NULL (тогда coalesce(valid_to, 'infinity')). Полуинтервал [from, to) исключает попадание даты в две версии на границе. Текущее состояние: WHERE valid_to = '9999-12-31' или флаг is_current. При соединении фактов с такой таблицей условие по дате факта ставится в ON рядом с ключом, иначе каждая транзакция размножится на все версии клиента.
Выбрать транзакции за день полуинтервалом по дате, соединить с клиентами по client_id и условию, что дата транзакции попадает в период версии: JOIN clients c ON c.id = t.client_id AND t.date >= c.start_date AND t.date < c.end_date, затем GROUP BY c.id, c.name. Если нужен не «имя на момент транзакции», а текущее имя, соединяют с текущей версией. Интервьюер обычно уточняет именно это, поэтому стоит спросить до написания.
Поля: valid_from, valid_to, is_current (необязательно) и хеш отслеживаемых атрибутов для быстрого сравнения. Алгоритм на порцию из источника: 1) сравнить входящие строки с текущими версиями по бизнес-ключу; 2) новые ключи вставить с valid_from = дата загрузки и открытым valid_to; 3) у ключей с изменившимся хешем закрыть текущую версию (valid_to = дата загрузки) и вставить новую; 4) ключи без изменений не трогать; 5) если источник сообщает удаления, закрыть версию. В одной транзакции или идемпотентно через MERGE, чтобы повторный запуск за ту же дату не плодил версий.
DELETE удаляет построчно с журналированием, может иметь WHERE, срабатывает на триггеры, а в PostgreSQL оставляет мёртвые версии строк до VACUUM. TRUNCATE очищает таблицу целиком, мгновенно, сбрасывает файлы данных и может сбросить sequence. В PostgreSQL оба откатываются в транзакции, в некоторых базах TRUNCATE это DDL без отката. Для перезаписи партиции целиком TRUNCATE на порядки дешевле.
DDL описывает структуру (CREATE, ALTER, DROP), DML меняет данные (INSERT, UPDATE, DELETE, MERGE), DQL читает (SELECT), DCL управляет правами (GRANT, REVOKE), TCL управляет транзакциями (BEGIN, COMMIT, ROLLBACK). Инженеру данных важны все пять: загрузка это DML в транзакции, доступы аналитикам это DCL.
Обычное представление это сохранённый запрос: при каждом обращении выполняется заново, данные всегда свежие, стоимость запроса каждый раз. Материализованное хранит результат как таблицу: читается быстро, но данные устаревают до следующего REFRESH. Если данные часто меняются и нужна свежесть, обычное представление или сама таблица с индексами; материализованное для тяжёлых агрегатов, которые достаточно обновлять по расписанию. В ClickHouse материализованное представление работает иначе: это триггер на вставку, который сразу пишет преобразованные строки в целевую таблицу.
Ограничения: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT (строго говоря, DEFAULT не ограничение, но его обычно называют шестым). Ключи: первичный, уникальный (альтернативный), внешний, простой и составной, натуральный (бизнес-ключ) и суррогатный (сгенерированный), кандидатный. Внешний ключ не является идентификатором таблицы, в которой находится: он ссылается на другую таблицу. Подвиды суррогатных: автоинкремент (sequence, identity), UUID, хеш от бизнес-ключа (так делают в Data Vault, чтобы ключ был воспроизводим без обращения к базе).
Обычно так называют запрос, который нарушает уникальность или конфликтует с другой транзакцией: вставка с существующим ключом (решается ON CONFLICT / MERGE), два процесса одновременно обновляют одну строку (блокировка и порядок коммитов), либо коллизия имён колонок при соединении (id из двух таблиц без алиасов). На собеседовании стоит уточнить, что именно имеет в виду интервьюер, и показать INSERT ... ON CONFLICT (key) DO UPDATE как ответ на самую частую трактовку.
Алиас это псевдоним таблицы или выражения в запросе: FROM orders o, SUM(amount) AS total. Алиас колонки из SELECT формально появляется после вычисления SELECT, поэтому по стандарту в WHERE и HAVING его использовать нельзя, а в ORDER BY можно. В GROUP BY зависит от СУБД: PostgreSQL, MySQL и ClickHouse разрешают группировать по алиасу из SELECT, Oracle и SQL Server нет, там нужно повторить выражение или вынести его в подзапрос. Алиас таблицы обязателен при self join и удобен везде, где одна таблица встречается дважды.
ALTER TABLE это DDL: меняет структуру таблицы, добавляет или удаляет колонки, меняет тип, ограничения, переименовывает; обычно берёт исключительную блокировку и в ряде СУБД не откатывается транзакцией. UPDATE это DML: меняет значения в существующих строках по условию, не трогая структуру, логируется построчно и откатывается. Вопрос проверяет понимание разницы между схемой и данными; рядом обычно спрашивают про DELETE и TRUNCATE и про то, что UPDATE без WHERE обновит всю таблицу.
Повторный запуск за тот же период даёт тот же результат, без дублей и потерь. Достигается upsert через ON CONFLICT или MERGE с проверкой версии, либо перезаписью периода: DELETE окна и INSERT в одной транзакции (в партиционированных таблицах заменой партиции). В Airflow это означает, что задача получает дату периода параметром и не зависит от «сейчас». Любая загрузка в проде падает и перезапускается, поэтому это свойство обязательное.
Источник пишет в staging как есть, без ограничений и быстро. Дальше один запрос переносит данные в целевую таблицу: дедупликация внутри порции (DISTINCT ON по ключу с выбором последней версии) и защита от уже загруженного (ON CONFLICT DO NOTHING или anti join к целевой). Staging очищается после успешного переноса, всё в одной транзакции. Так ошибки источника не ломают витрину, а переливка идемпотентна.
По времени поступления данных в источник (updated_at, loaded_at, номер версии, LSN), не по времени события: события опаздывают, и окно по дате события их теряет. Окно берётся с запасом назад, а дубли на стыке окон убирает идемпотентная запись. Если в источнике нет такой колонки, остаются варианты: CDC по журналу базы, сравнение хешей строк с текущим состоянием, полная перезагрузка небольших таблиц.
По метке времени изменения или монотонному ключу (самый дешёвый, требует надёжной колонки в источнике); CDC по журналу транзакций (Debezium, логическая репликация: видны и удаления); сравнение снимков (snapshot): выгрузить всё, сравнить с предыдущим состоянием по хешу строки и получить diff, дорого, но работает с любым источником; триггеры в источнике. Snapshot это полное состояние на момент времени, diff это набор изменений между двумя снимками: вставки, обновления, удаления. Хранить снимки просто и позволяет откатиться, хранить diff экономно и сохраняет историю изменений.
10 ГБ это объём для одной машины, кластер не нужен. Выгрузка потоком, а не целиком в память: COPY из источника в файлы или чтение курсором батчами; хранение промежуточного результата в Parquet; дедупликация и преобразования в целевой базе SQL-запросом (staging → INSERT ... SELECT DISTINCT ON) или в DuckDB/Polars поверх файлов, если целевая база слабая; загрузка в приёмник COPY/executemany батчами в транзакции. Оркестрация в Airflow с идемпотентными шагами, проверки качества после загрузки. Spark здесь избыточен, pandas целиком не поместит 10 ГБ в память.
UPDATE employees SET salary = CASE WHEN salary = 900 THEN 1000 ELSE 1500 END. Условие внутри CASE вычисляется для каждой строки, поэтому один проход меняет всех. Важно помнить, что без WHERE обновится вся таблица, и это здесь задумано, а в реальной работе такой запрос сначала проверяют через SELECT с тем же CASE. Если «остальных» трогать не нужно, условие переносят в WHERE: UPDATE ... SET salary = 1000 WHERE salary = 900.
Atomicity: транзакция либо выполняется целиком, либо откатывается целиком. Consistency: база переходит из одного согласованного состояния в другое, ограничения не нарушаются. Isolation: параллельные транзакции не видят промежуточных состояний друг друга (степень задаётся уровнем изоляции). Durability: закоммиченные данные переживают сбой. BASE (Basically Available, Soft state, Eventually consistent) это модель распределённых систем, где ради доступности и масштабирования допускают временную несогласованность: данные «сойдутся» позже. Реляционные базы и хранилища живут по ACID, Kafka, S3 и многие NoSQL ближе к BASE.
Read Uncommitted допускает грязное чтение (в PostgreSQL его нет, он ведёт себя как Read Committed). Read Committed убирает грязное чтение, но допускает неповторяющееся чтение и фантомы. Repeatable Read гарантирует один снимок на всю транзакцию, в PostgreSQL он убирает и фантомы, но параллельные обновления одной строки заканчиваются ошибкой сериализации. Serializable гарантирует результат, эквивалентный последовательному выполнению. По умолчанию в PostgreSQL Read Committed; для загрузки, которая читает несколько таблиц согласованно, берут Repeatable Read.
Multi-Version Concurrency Control: каждая транзакция видит снимок данных на момент своего начала, читатели не блокируют писателей, а писатели читателей. Обратная сторона: обновлённые и удалённые строки остаются в таблице как мёртвые версии до VACUUM, и таблица с частыми обновлениями распухает.
По объекту: блокировка таблицы (DDL, TRUNCATE, LOCK TABLE), строки (UPDATE, DELETE, SELECT ... FOR UPDATE), страницы в некоторых СУБД, advisory-блокировки на уровне приложения. По режиму: разделяемые (shared, чтение) и исключительные (exclusive, запись), в PostgreSQL восемь режимов табличных блокировок с матрицей совместимости. Для инженера данных важны два следствия: ALTER TABLE ждёт окончания всех запросов к таблице и блокирует новые, а длинная транзакция загрузки держит блокировки строк и мешает VACUUM.
Из-за MVCC обновления и удаления оставляют мёртвые версии строк; VACUUM помечает их место как свободное для повторного использования и обновляет карту видимости, ANALYZE собирает статистику для планировщика. autovacuum делает это сам по порогам, но на больших таблицах с массовыми загрузками пороги по умолчанию (20% изменённых строк) срабатывают поздно: их снижают для конкретных таблиц или запускают VACUUM ANALYZE явно после загрузки. VACUUM FULL переписывает таблицу целиком, возвращая место системе, но держит исключительную блокировку. В Greenplum то же самое, плюс таблицы append-optimized накапливают «скрытые» строки, и их нужно вакуумить после UPDATE/DELETE.
Запрос ORDER BY ts DESC LIMIT 1000 с индексом по ts дёшев сам по себе: читается хвост индекса, без сортировки всей таблицы. Если индекса нет, добавить. Дальше снимать нагрузку с базы: кэшировать результат на несколько секунд (один запрос в базу, остальные из кэша), читать с реплики, в хранилищах держать небольшую «горячую» таблицу или материализованное представление с последними записями, которое обновляется одной задачей. Ограничить число одновременных соединений через пул (PgBouncer), чтобы 100 клиентов не превратились в 100 бэкендов.
Уникальность ключа, обязательные поля не NULL, допустимые значения статусов, ссылочная целостность, свежесть (когда последняя успешная загрузка) и полнота (число строк в сравнении с ожиданием и с предыдущими днями). Плюс сверка с источником в обе стороны: что есть только там и только здесь. Проверки запускаются после загрузки и блокируют публикацию при провале; отчёт по качеству это таблица «проверка, дата, число нарушений, порог, статус», по которой строят алерты.
FULL JOIN по бизнес-ключу с меткой, откуда пришла строка: left_only это пропуски в приёмнике, right_only лишнее, both сравнивают по хешу значимых колонок. Задвоения ищут до соединения: count(*) против count(DISTINCT ключ) на каждой стороне, иначе дубли размножат результат сверки. Для больших таблиц сверяют агрегаты по дням (число строк, суммы) и углубляются только в расходящиеся дни.
Правила задаются заранее: тип и формат поля, обязательность, допустимые значения, диапазоны, ссылки на справочники, бизнес-правила (сумма позиций равна сумме заказа). Ошибочная запись не исправляется молча: либо вся загрузка останавливается (строгие данные), либо запись уходит в карантин (отдельную таблицу с причиной и датой), а загрузка продолжается с отчётом «отклонено N». Карантин нужен, чтобы ошибки источника были видны и исправлялись у источника, а не терялись.
Построить ключ из содержимого: хеш от всех значимых колонок (md5(concat_ws('|', col1, col2, ...))), хранить его в таблице с уникальным индексом и сравнивать по нему при загрузке. Если часть колонок «шумит» (время загрузки, технические поля), исключить их из хеша. Так же устроена дедупликация в Data Vault: хеш бизнес-ключа и хеш-дифф атрибутов.
EXPLAIN (ANALYZE, BUFFERS), найти узел с наибольшим временем с учётом loops, сравнить оценку rows с фактом. Расхождение в разы указывает на устаревшую статистику или функции над колонками. Дальше по ситуации: индекс, переписать коррелированный подзапрос в соединение, убрать DISTINCT, который прячет размножение, уменьшить объём до соединения, проверить work_mem (сортировка на диске, батчи у hash join). В MPP добавляется проверка перекоса и Motion в плане.
Сначала выяснить, что изменилось: объём данных (рост или перекос в конкретный день), план запроса (устарела статистика, пропал индекс, изменился порядок соединений), ресурсы (другие задачи в то же окно, место на диске, спилл), код (новый JOIN, новая колонка). Сравнить план сегодня и в быстрый день, посмотреть мониторинг. Если объём по вторникам в пять раз больше: партиционировать обработку по частям, вынести тяжёлые шаги в инкремент, расширить ресурсы под этот день (в Spark динамическая аллокация), развести задачи по времени, и главное, заложить порог в мониторинг, чтобы узнать о проблеме до того, как её заметит заказчик.
CTE читается как единый запрос и удобен, пока частей мало, но в PostgreSQL до 12 CTE всегда материализовался, а сейчас может быть встроен в запрос, и статистики по нему нет: планировщик строит план по оценкам. Временная таблица даёт точку отладки, ANALYZE и индексы, её можно переиспользовать в нескольких запросах, и большой промежуточный результат не пересчитывается. Для витрины из нескольких тяжёлых шагов временные таблицы (или staging-таблицы с явной очисткой) обычно надёжнее; CTE для логически одного запроса.
Оптимизировать до соединения, а не после: отфильтровать по партициям и датам, оставить только нужные колонки, агрегировать до зерна соединения, убедиться, что ключи одного типа (иначе индекс не работает). Соединять по ключу, по которому данные распределены или отсортированы. Проверить статистику и план. Если результат соединения нужен многим витринам, посчитать его один раз в промежуточную таблицу.
Отдельная структура (обычно B-дерево), которая по значению колонки находит строки без чтения всей таблицы. Минусы: место на диске, замедление вставок и обновлений (каждый индекс обновляется вместе с таблицей), раздувание при массовых изменениях, устаревание статистики. При массовой загрузке индексы иногда удаляют перед вставкой и создают после: так быстрее, чем обновлять их построчно. В аналитических хранилищах (Greenplum, ClickHouse) индексы играют меньшую роль, чем распределение, партиции и ключ сортировки.
Плюс: точечные и диапазонные запросы по этим колонкам перестают читать всю таблицу, план меняется с Seq Scan на Index Scan. Минусы: индекс по нескольким колонкам работает только для условий по префиксу (порядок колонок важен), каждая вставка обновляет индекс, на 30 млн строк создание занимает минуты и в PostgreSQL блокирует запись (если не CONCURRENTLY), а если фильтр отбирает большую долю строк, планировщик всё равно выберет Seq Scan. Решение: индекс только по действительно селективным колонкам, под реальные запросы, с проверкой плана до и после.
B-tree по умолчанию: равенство, диапазоны, сортировка, подходит почти всегда. Hash только для равенства, выигрыш небольшой. GIN для массивов, JSONB и полнотекстового поиска. GiST и SP-GiST для геометрии и диапазонов. BRIN для огромных таблиц, где значение коррелирует с физическим порядком (дата загрузки): крошечный индекс по блокам. Частичный индекс (WHERE status = 'active') и индекс по выражению (lower(email)) под конкретные запросы. Кластеризованный индекс определяет физический порядок строк (в SQL Server и InnoDB это первичный ключ, в PostgreSQL CLUSTER делает это разово), некластеризованный живёт отдельно и хранит ссылки на строки.
Индекс ускоряет поиск строк внутри таблицы, партиция делит таблицу на физические части по значению колонки, и запрос с условием по этой колонке читает только нужные части. Индекс для точечного поиска, партиции для диапазонов дат и для обслуживания: удалить или перезагрузить месяц целиком это отбросить партицию, а не DELETE миллионов строк. Они не исключают друг друга: индексы создаются внутри каждой партиции.
Партиционирование делит таблицу на части внутри одного сервера по значению колонки (чаще по дате): ускоряет запросы с фильтром по ней и обслуживание. Шардирование делит данные между серверами по ключу: масштабирует запись и объём, но соединения между шардами и перебалансировка становятся вашей заботой. В Greenplum дистрибуция по сегментам это шардирование внутри кластера, а партиции внутри каждого сегмента.
Для запросов по диапазону дат и для перезаписи или удаления периодов целиком: отбросить партицию мгновенно, а DELETE по индексу долго. Индекс для точечного поиска, партиции для сканирования диапазонов. В аналитических базах (ClickHouse, Greenplum) партиционирование и ключ сортировки важнее индексов.
Разбить пространство на сетку: добавить колонки с номером ячейки (округлённые широта и долгота или геохеш) и искать сначала по ячейке, а точную проверку делать по строкам внутри неё. В MPP по ячейке можно и распределять, и партиционировать: запрос по области попадает в несколько сегментов и партиций, а не во все. Принцип тот же, что для любого распределения: ключ должен равномерно раскладывать данные и совпадать с тем, по чему фильтруют и соединяют.