SQL Flashcards

Question 1

Q

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

Answer

A

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

Q

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

Answer

A

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

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

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

Q

Truncate vs Delete

Answer

A

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

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

Question 4

Q

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

Answer

A

Основная идея – обрабатывать как можно меньше данных (фильтровать раньше).
Достигается через:
- 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

Q

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

Answer

A

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

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

Q

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

Answer

A

Смещения -
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

Q

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

Answer

A

Если в СУБД есть 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

Q

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

Answer

A

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

FROM / JOIN
WHERE
GROUP BY
HAVING
UNION / UNION ALL
SELECT (здесь же оконные функции)
ORDER BY
LIMIT / OFFSET
Важно знать, чтобы объяснить:
- Почему алиасы из SELECT не работают в HAVING.
- Почему нельзя отфильтровать оконную функцию сразу,
а только в следующем CTE/подзапросе.

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

Question 9

Q

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

Answer

A

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

Question 10

Q

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

Answer

A

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

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

Question 11

Q

Primary key, foreign key

Answer

A

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

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

Question 12

Q

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

Answer

A

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

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

SQL Flashcards

(12 cards)