Проверь себя перед собеседованием: 30+ вопросов по SQL для аналитика

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

COUNT(*) и COUNT(1) считают количество строк во всей таблице. COUNT(column) считает только строки, в которых column IS NOT NULL.

2, поскольку COUNT(column) не учитывает NULL.

NULL (UNKNOWN).
NULL означает неизвестное значение, поэтому два NULL нельзя считать равными. Для проверки используется IS NULL.

UNION объединяет результаты запросов и удаляет дубликаты. UNION ALL сохраняет все строки, включая дубликаты, и обычно работает быстрее, поскольку не требует дедупликации.

Все уникальные строки таблицы.
Дубликатами считаются строки, у которых совпадают значения во всех выбранных столбцах.

Да. Она принимает набор значений группы и возвращает одно значение — непрерывный 50-й перцентиль (медиану). WITHIN GROUP (ORDER BY column) задает порядок значений, необходимый для вычисления перцентиля.

Сначала COUNT(id) считается внутри групп, сформированных GROUP BY. Затем оконная функция MAX(...) OVER () берет максимальное значение из всех полученных COUNT(id) и возвращает его для каждой строки результата.

4 строки.

Две строки 1 из A соединятся с одной строкой 1 из B → 2 строки.
Одна строка 2 из A соединится с двумя строками 2 из B → еще 2 строки.
Итого: 2 + 2 = 4.

4 строки.

NULL из A не соединится с NULL из B, поскольку NULL = NULL не является TRUE, но строка сохранится благодаря LEFT JOIN → 1 строка.
Значение 1 также не найдет соответствия → 1 строка.
Значение 2 соединится с двумя строками из B → 2 строки.
Итого: 1 + 1 + 2 = 4.

2 строки.

NULL не соединяется с NULL, а для 1 соответствия в B нет.

Единственное совпадающее значение — 2: одна строка из A соединится с двумя строками из B.

Минимум — 5, максимум — 50.

LEFT JOIN сохраняет каждую строку левой таблицы хотя бы один раз, поэтому результат не может содержать меньше 5 строк.
Максимум достигается, если каждая из 5 строкAсоединится с каждой из 10 строкB (50).

Если B.id не содержит NULL в совпавших строках, по результату разницы нет.
Оба запроса оставят только строки A, для которых найдено соответствие в B.
Однако INNER JOIN лучше отражает смысл операции и обычно предпочтительнее, если нужны только совпавшие строки.

Минимум — 10, максимум — 50.

FULL OUTER JOIN сохраняет все строки обеих таблиц.
Минимум 10 достижим, когда все 5 строк A сопоставлены со строками B без размножения результата, а остальные строки B остаются unmatched.
Максимум — 5 × 10 = 50, если каждая строка A соединяется с каждой строкой B.

5 строк.

Каждая из двух строк 1 таблицы A соединится с двумя строками 2 таблицы B:
2 × 2 = 4
Строка 2 таблицы A соединится с единственной строкой 1 таблицы B:
1 × 1 = 1
Итого: 4 + 1 = 5.

Все три функции нумеруют строки в соответствии с ORDER BY, но по-разному обрабатывают одинаковые значения.

ROW_NUMBER() всегда присваивает каждой строке уникальный последовательный номер:

1, 2, 3, 4

RANK() присваивает одинаковый ранг равным значениям и оставляет пропуски:

1, 2, 2, 4

DENSE_RANK() также присваивает одинаковый ранг равным значениям, но без пропусков:

1, 2, 2, 3

ORDER BY задает порядок строк внутри окна, от которого зависит результат функций ранжирования, накопительных агрегатов, LAG(), LEAD() и других оконных вычислений.

Без него порядок не определен, а некоторые функции либо потеряют смысл, либо будут работать иначе.

Например:

SUM(value) OVER ()

в каждой строке вернет общую сумму по всему набору строк, тогда как:

SUM(value) OVER (ORDER BY dt)

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

Нужно задать порядок строк через ORDER BY и оконный фрейм через ROWS или RANGE. Например:

AVG(value) OVER (ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

рассчитает среднее по текущей и двум предыдущим строкам.

SUM(value) OVER () рассчитает сумму value по всему набору строк и выведет ее для каждой строки.

SUM(value) OVER (PARTITION BY category) разделит строки на группы по category и рассчитает сумму отдельно внутри каждой группы, не схлопывая строки, как это делает обычный GROUP BY.

Некоррелированный подзапрос не зависит от внешнего запроса и может быть выполнен самостоятельно.

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

Например:

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.client_id = c.client_id)

При этом фактический план выполнения определяет оптимизатор, поэтому СУБД не обязательно буквально запускает подзапрос заново для каждой строки.

Главное различие связано с NULL.

Если набор значений в NOT IN содержит NULL, результат сравнения может стать UNKNOWN, из-за чего запрос не вернет ожидаемые строки.

NOT EXISTS проверяет наличие подходящих строк и этой проблемы не имеет.

Поэтому для проверки отсутствия связанных записей обычно безопаснее использовать NOT EXISTS.

Подзапросы можно использовать в разных частях SQL-запроса:

В SELECT — скалярный подзапрос должен возвращать одно значение для строки.

В FROM — подзапрос формирует промежуточный набор данных, с которым можно работать как с таблицей.

В WHERE — подзапрос обычно используется для фильтрации через IN, EXISTS, NOT EXISTS или операции сравнения.

В HAVING — подзапрос позволяет фильтровать уже сформированные группы.

Конкретные возможности и ограничения могут различаться между СУБД.

DELETE удаляет строки из таблицы и позволяет использовать WHERE.

TRUNCATE быстро удаляет все строки таблицы без возможности указать WHERE, при этом сама таблица сохраняется.

DROP удаляет сам объект таблицы вместе с его структурой.

DELETE FROM table удаляет данные, но сохраняет таблицу: ее столбцы, ограничения, индексы и другие связанные объекты.

DROP TABLE удаляет сам объект таблицы. После DELETE в таблицу можно сразу записывать новые данные, а после DROP таблицу придется создавать заново.

Временная таблица предназначена для хранения промежуточных данных и обычно существует только в рамках текущей сессии или транзакции (точное поведение зависит от СУБД).

Обычная таблица сохраняется в базе до явного удаления.

Временные таблицы можно использовать в нескольких последующих запросах, а во многих СУБД для них также можно создавать индексы.

Витрина данных — это подготовленный набор данных, сформированный из исходных таблиц под конкретную аналитическую задачу: расчет метрик, построение отчетности или проведение исследований.

Для создания витрины определяют ее назначение и уровень детализации, выбирают источники данных и правила расчета показателей, после чего реализуют SQL-логику преобразования и настраивают регулярное обновление через ETL/ELT-процесс.

Удалятся все строки из таблицы clients.
Символ «;» завершает инструкцию DELETE FROM clients, поэтому она удаляет все строки таблицы. Следующий WHERE client_id = 123 является отдельной некорректной инструкцией и завершится ошибкой.

План выполнения запроса показывает, каким способом СУБД выполняет запрос: как читает таблицы, какие индексы использует, в каком порядке соединяет таблицы, как выполняет сортировки и агрегации.

При поиске причин медленной работы запроса в плане стоит обращать внимание на объем обрабатываемых данных, способы чтения таблиц, использование индексов, операции JOIN, сортировки и агрегации.

Индекс — дополнительная структура данных, позволяющая СУБД находить нужные записи без полного просмотра таблицы.

Например, при условии:

WHERE client_id = 12345

без подходящего индекса СУБД может прочитать всю таблицу. Индекс позволяет быстрее найти подходящие значения и получить соответствующие строки.

Однако индексы занимают дополнительное место и создают расходы при INSERT, UPDATE и DELETE, поэтому индексировать все столбцы подряд не следует.

Индекс можно создать командой:

CREATE INDEX idx_table_idON table_name(id);

После этого явно указывать индекс в WHERE не нужно.

Например:

SELECT *FROM table_nameWHERE id = 123;

Оптимизатор СУБД самостоятельно решает, использовать созданный индекс или выбрать другой способ чтения данных.

Главный принцип — сначала найти узкое место, а затем оптимизировать его.
Начать стоит с анализа плана выполнения и фактического объема обрабатываемых данных.

Далее следует проверить:

• можно ли раньше отфильтровать ненужные строки;

• можно ли не читать ненужные столбцы;

• не мешают ли функции над фильтруемыми столбцами эффективному использованию индексов;

• не происходит ли размножение строк при JOIN;

• существуют ли подходящие индексы;

• нет ли дорогостоящих сортировок, агрегаций и других операций.