SQL

Question 1

Логические джоины

Answer 1

Left [outer] join – основной в работе, берёт все записи из левой таблицы и те записи из правой, которые совпадают по условию соединения.

Колонки присоединяются справа, и можно через алиасы обращаться к обеим таблицам.
Используется всегда, кроме случаев, когда нам важно полное сопоставление данных из двух таблиц и “дополнительная фильтрация” по условию соединения.
Outer никто не пишет, но по стандарту можно.

select o.date, o.price, c.name 
from orders o 
left join clients c
on o.client_id = c.id;

Левая таблица находится в секции FROM, правая – джойнится к ней.
Грубо (логически) можно представлять, что сначала каждой строке сопоставляется каждая (полное декартово произведение), а потом происходит фильтрация по условию ON.

Если левая таблица 10 строк, а правая таблица 5 строк:
- Минимальное количество строк (если нет совпадений) = 10
- Обычно (если значения пересекаются и не повторяются внутри одной таблицы, от 1 до 10 и от 1 до 5) = 10
- Максимальное количество строк (если все строки совпадают и все значения равны друг другу) = 50

with l as (
  select generate_series(1,10) / 11 as id /* all 0 */
 ),
 r as (
  select generate_series(1,5) / 6 as id /* all 0 */
 )
 select count(1)
 from l
 left join r on l.id = r.id;

Right [outer] join – почти не используется, мем в сфере данных.
Равнозначен left join и всегда ему проигрывает, т.е. любой запрос с right join можно переписать на left join.
Нужен только если лень переписать начатый не с той таблицы запрос нормально.

[Inner] join – похож на left join, но ещё и из основной, левой, таблицы отбрасывает данные, которые не джойнятся на данные из правой по условию ON.
Inner писать не обязательно.

Если левая таблица 10 строк, а правая таблица 5 строк:
- Мин = 0
- Обычно = 5
- Макс = 50

Full [outer] join – не фильтрует данные, просто сопоставляет строки из левой и правой таблиц.
А вот задублить может.

Если левая таблица 10 строк, а правая таблица 5 строк:
- Мин = 10
- Обычно = 10
- Если строки не повторяются и не пересекаются = 15
- Макс = 50

Cross join – единственный join без условия, все строки со всеми, полное декартово произведение.
Применяется, когда нужно каждой строке из календаря, мелкого справочника или другой структуры с константами сопоставить каждую строку из основной таблицы.

Если левая таблица 10 строк, а правая таблица 5 строк:
- Мин = 50
- Макс = 50

Self join – любой join, где левая и правая таблица – это та же самая таблица.
Обязательны алиасы для указания, какая колонка к какой таблице относится.
Применяется для сопоставления строк по неравенству (>=, !=) или раскручивания иерархии (типовая таблица employee_manager с полем manager_id, который тоже является employee и имеет свой employee_id).

Anti join – любой join, в результате которого остаются только записи, для которых не нашлось пары в другой таблице.
Пример реализации ниже:

select l.*
from l
join r on l.id = r.id
where r.id is null;

Question 2

123

Физические джоины

123

123

Answer 2

Hash join — экви-джоин (строго по равенству) большой и маленькой таблицы.

Маленькая помещается в память, ключи обеих таблиц хэшируются, один раз проходим по большой таблице и один раз по маленькой.
O(m+n) по времени + O(n) по памяти.

Sort merge join — экви-джоин двух больших таблиц, которые не помещаются в память.
Сортируем по ключу и проходимся “замочком”, состёгивая две таблицы вместе.
Если сортировка проводится за O(n*log(n)), то O(n*log(n) + m*log(m) + n + m),
где (n+m) схлопываются как незначительные, получаем O(n*log(n) + m*log(m)).
В заранее отсортированных массивах сложность O(m+n).

Визуализация:
https://bertwagner.com/posts/visualizing-merge-join-internals-and-understanding-their-implications/?ref=yuji.page

Nested loop — все остальные джоины (a.id >= b.id, a.id like '%word%', != и прочие).
Каждое значение левой таблицы сопоставляем со значением из правой таблицы (аналог CROSS JOIN).
Сложность O(n*m).
Визуализировать можно через вложенные циклы:

for a in list_a:
for b in list_b:
….

Может выбираться при экви-джоине, если сортировка таблицы займёт больше времени:
- Отбираемые колонки покрыты составными индексами в обеих таблицах.
- Фильтр оставляет мало записей в каждой таблице.
- Параллельная обработка вложенным циклом на каждом сегменте может быть быстрее сортировки.

Для этого нужна актуальная статистика по таблицам
(после каждой крупной операции вставки/изменения прогонять ANALYZE table;).

Question 3

Truncate vs Delete

Answer 3

TRUNCATE — это DDL операция (нужны соответствующие права).
Выполняется мгновенно, так как не оставляет записей в журнале операций БД.
В большинстве баз данных не откатывается в случае неуспешной транзакции.

DELETE — это DML операция, выполняется медленнее,
так как для каждой удалённой строки оставляет запись в журнале БД.
В случае удаления построчно в Big Data системах может быть очень дорогой и медленной,
поэтому её заменяют INSERT-ONLY строчкой с флагом is_deleted
или перезаписывают партицию целиком.

Question 4

Как оптимизировать SQL запрос

Answer 4

Основная идея – обрабатывать как можно меньше данных (фильтровать раньше).
Достигается через:
- Pushdown filter (пробрасывание WHERE на системы-источники в случае федеративных систем/ETL систем
или в таблицы-источники в случае использования VIEW/функций в БД).
- Индексы в OLTP.
- Партиции (реже – кластеры) в OLAP.
- Распределение в MPP системе по шардам равномерно (вся упряжка бежит со скоростью самой медленной лошадки).
- В MPP стоит минимизировать shuffle, где возможно:
- Не менять ключ распределения и сортировку на каждом шаге расчёта витрин.
- Использовать broadcast join для справочников.

Любят спрашивать про чтение плана запроса.
Волшебные слова:
- Через EXPLAIN ANALYZE смотрим план, ищем нехорошие слова:
- Nested loop, Shuffle, Table scan (игнорирование индекса).
- Радуемся, когда видим хорошие:
- Hash join, Merge join, Index seek (использование индекса).

Для Greenplum нужно минимизировать:
- Количество спиллов (сохранение данных на диск, когда они не помещаются в памяти).
- Слайсы (каждый слайс – это перемещение данных по сети).
- Выполнение операций на мастере (gather motion n:1), где n – число сегментов.

Question 5

Разница между window fnc и group by

Answer 5

Группировка, обычно, уменьшает количество строк (как сводная таблица),
а оконная функция – сохраняет исходное количество и присоединяет результат к каждой строке.

select sum(price), year(order_date) || ' ' || month(order_date) yy_mm
from orders
group by year(order_date) || ' ' || month(order_date)

Оконки учитывают порядок строк (lag, lead, first_value) через ORDER BY
и могут гибко управлять границами окон в PARTITION BY через
ROWS [UNBOUNDED PRECEDING, n FOLLOWING, CURRENT ROW] и др.

Обычно достаточно упомянуть это и отшутиться, что эти указатели границ окна сложно и длинно пишутся
и редко нужны, поэтому сейчас не помнишь, но при необходимости быстро нагуглишь.

С указанием ORDER BY окно по умолчанию – RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
Без указания ORDER BY окно по умолчанию – ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

ROWS – фиксированное количество строк,
RANGE – заданный через условие диапазон.

Question 6

Примеры оконных функций

Answer 6

Смещения -
lag(col, [offset], [default]) – предыдущая строка в партиции с учётом ORDER BY,
NULL для первой, если не указан default.

lead(col, [offset], [default]) – следующая строка в партиции,
NULL для последней, если не указан default.

first_value(col), last_value(col) – первое и последнее значение с учётом ORDER BY;
более гибко, чем MAX/MIN.

Все агрегации – MAX(col), MIN(col), AVG(col), COUNT(col), SUM(col), …

Ранжирования -
row_number() – пронумеровать строки в пределах партиции в порядке ORDER BY.
Удобно для дедубликации, основанной на бизнес-правилах.
Часто соседствует с WHERE rn = 1 в следующем по цепочке подзапросе/CTE (1,2,3,4,5,6,7).

rank() – как ROW_NUMBER, но выдаёт повторяющимся значениям одинаковое место,
а потом перескакивает на следующее (например: 1,1,1,4,5,5,7).

dense_rank() – как RANK, но следующим после повторяющихся значений
выдаёт места без пропусков, “плотный” ранк (1,1,1,2,3,3,4).

При работе обращай внимание на детерминированность условий в ROW_NUMBER,
чтобы ORDER BY однозначно определял порядок сортировки полей.

Question 7

CTE vs Subquery (подзапрос)

Answer 7

Если в СУБД есть CTE, использование подзапросов – это почти всегда плохой тон,
кроме случаев, когда можно использовать только подзапрос.

Последний может пригодиться в:
- Шаблонизированных Jinja запросах для DBT, где уже есть секция WITH.
- Секции WHERE.
- Сложном UPDATE-запросе.

Subquery – это запрос в скобках, к которому чаще всего обращаются в секции FROM:

select *
from (
	select col, id
	from table
)

CTE – именованный подзапрос, отличается конструкцией WITH,
может соединяться по цепочке в несколько шагов.

with meaningful_name as (
	select id, col from table
),
long_name_of_ref_table as (
	select id, col2 from table2
)
select mn.col, lnref.col2
from meaningful_name mn
join long_name_of_ref_table lnref
	on mn.id = lnref.id

CTE может быть рекурсивным, но такие запросы опасно выводить на прод
и стоит избегать всеми силами.

Пример той самой таблички с сотрудниками и их руководителями:

WITH employee_manager_cte AS (
  SELECT
	id,
	name,
	department,
	manager_id,
	manager_name,
	1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT
	e.id,
	e.name,
	e.department,
	e.manager_id,
	e.manager_name,
	level + 1
  FROM employees e
  INNER JOIN employee_manager_cte r
	ON e.manager_id = r.id
)
SELECT *
FROM employee_manager_cte;

Question 8

Порядок выполнения команд

Answer 8

Порядок выполнения SQL-запроса:

1. FROM / JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. UNION / UNION ALL
6. SELECT (здесь же оконные функции)
7. ORDER BY
8. LIMIT / OFFSET

Важно знать, чтобы объяснить:
- Почему алиасы из SELECT не работают в HAVING.
- Почему нельзя отфильтровать оконную функцию сразу,
а только в следующем CTE/подзапросе.

Хотя ClickHouse и некоторые современные СУБД умеют пробрасывать алиасы для таких случаев.

Question 9

Корнер-кейсы в агрегирующих функциях, обработка NULL

Answer 9

1. COUNT():
   
   • COUNT(*) считает все строки, включая NULL.
   • COUNT(column) игнорирует NULL в указанной колонке.
   • COUNT(<constant>) считает все строки, включая NULL.
     Часто в качестве константы берут COUNT(1).
   • COUNT(NULL) вернёт 0.
2. SUM(), AVG():
   
   • Полностью игнорируют NULL.
   • AVG считает среднее только по не-NULL значениям.
3. MIN(), MAX():
   
   • Также игнорируют NULL.
   • Если все значения NULL, вернут NULL.
4. GROUP BY:
   
   • Считает NULL как одну группу.

Question 10

Различия distinct и group by

Answer 10

Команды DISTINCT и GROUP BY в SQL используются для разных целей, хотя обе помогают устранить дубликаты в результатах запроса.

• DISTINCT используется для возвращения уникальных значений из одного или нескольких столбцов.
  Применяется к отдельным столбцам.
• GROUP BY используется для группировки строк, которые имеют одинаковые значения, в одну группу.
  Применяется вместе с агрегатными функциями (SUM, AVG, COUNT и т.д.) для вычисления сводных данных по группам.
  Группирует строки по одному или нескольким столбцам.

Question 11

Primary key, foreign key

Answer 11

Primary key (первичный ключ) – это NOT NULL + UNIQUE констрейнты. Всё.
Уникальный идентификатор строки. Может быть составным (из нескольких колонок).
Если в таблице добавляется историчность, одно из полей (например, effective_from) добавляется в ключ.
Может быть естественным (бизнес-ключ) и суррогатным (сгенерирован на стороне базы).

Foreign key (вторичный ключ) – ссылка на уникальную запись в другой таблице.
Хорошо, если поле будет NOT NULL, но не обязательно. Обычно для связки используется PK другой таблицы.
Позволяет реализовать связь “один ко многим” (см. моделирование), поле FK будет находиться в таблице “многих”.
Позволяет поддерживать ссылочную связность на уровне базы данных.

Question 12

Сумма накопительным итогом

Answer 12

Проще всего посчитать через sum() over (order by). Например, накопительный итог цены товаров, проданных в рамках каждого города с начала года:

select sum(price) over (partition by city order by order_date) cumul_price 
from orders
where year(order_date) = 2024