Памятка по SQL

Небольшая памятка для начинающих учить SQL. В каком порядке выполняется запрос, как его можно быстро и дёшево оптимизировать и несколько часто используемых полезных практик.

§1. Структура SQL-запроса и последовательность выполнения

Для начала давай разберёмся с основными составляющими типичного запроса и их порядком выполнения:

1. FROM: Выбор таблиц-источников.
2. WHERE: Фильтрация строк по значениям.
3. JOIN: Соединение таблиц каким-либо образом.
4. GROUP BY: Группировка строк. Если ты используешь алиасы для столбцов, то тут они не сработают, потому что SELECT ещё не выполнен (скажи это кликхаусу, ха-ха).
5. HAVING: Фильтрация групп. Эта часть фильтрует уже сформированные аггрегаты после GROUP BY, т.е. это такой резервный фильтр, обычно применяется чтобы отрезать всякие пустые или почти пустые сгруппированные строки (например выбрать все города где продаж больше 100 и т.д.).
6. SELECT: Выбор столбцов и вычисляемых выражений.
7. Оконные функции: Как ты можешь заметить, оконки выполняются после фильтрации, т.е. если в фильтре WHERE ты вырежешь половину значений, то оконка тоже их проигнорирует.
8. ORDER BY: Сортировка финального результата.
9. LIMIT/OFFSET: Ограничивает количество возвращаемых строк и/или пропускает определенное количество строк.

§2. CTE vs линейные подзапросы

И CTE (Common Table Expressions), и подзапросы можно использоваться для фильтрации данных перед выполнением JOIN операций. Оба подхода имеют свои преимущества и недостатки.

Пример CTE:

WITH Table1 AS (
    SELECT id, column1, column2
    FROM table1
    WHERE condition1
),

Table2 AS (
    SELECT id, column3, column4
    FROM table2
    WHERE condition2
)

SELECT a.id, a.column1, a.column2, b.column3, b.column4
FROM Table1 a
 LEFT JOIN Table2 b ON a.id = b.id;

Пример подзапросов:

SELECT a.id, a.column1, a.column2, b.column3, b.column4
FROM (SELECT id, column1, column2 FROM table1 WHERE condition1) a
 LEFT JOIN (SELECT id, column3, column4 FROM table2 WHERE condition2) b ON a.id = b.id;

Разница между подходами

1. Удобочитаемость: CTE лучше читается в больших скриптах. В маленьких запросах быстрее использовать линейные подзапросы, потому что при большой вложенности код превращается в кашу.
2. Производительность: В теории может отличаться от БД к БД, но на практике я разницы не видел, работает одинаково, плюс-минус.

§3. Названия столбцов vs их номера

В операциях GROUP BY и ORDER BY можно использовать как названия столбцов, так и их порядковые номера.

Пример с использованием названий столбцов:

SELECT column1, column2, COUNT(*)
FROM table
GROUP BY column1, column2
ORDER BY column1, column2;

Пример с использованием порядковых номеров:

SELECT column1, column2, COUNT(*)
FROM table
GROUP BY 1, 2
ORDER BY 1, 2;

Разница между подходами

1. Удобочитаемость: Использование названий столбцов делает код самодокументирующимся, что удобно если он не одноразовый. На маленьких скриптах цифры просто быстрее проставлять.
2. Поддерживаемость: Если структура таблицы изменится, а ты используешь названия — всё будет ок, если цифры — может поехать.
3. Производительность: Поговаривают, что зависит от БД, но по факту там минимальная разница, которую невооружённым глазом не заметить.

§4. Переменные

Не везде, но в некоторых БД есть замечательная штука DECLARE, которая позволяет обозначить переменную. Особенно удобно когда у тебя 10 штук CTE, с одинаковыми фильтрами в каждом. Строишь перед запросом блок с настройками и все изменяемые части выгружаешь туда. Прям как взрослый кодинг.

Пример использования:

DECLARE my_date DATE DEFAULT "2024-01-01";
DECLARE os_name STRING DEFAULT "android";

SELECT column1, column2, column3
FROM table
WHERE os = os_name
 AND date >= my_date

§5. Вьюшки (Views)

Views — это виртуальные таблицы, которые представляют собой результат выполнения запроса. В них можно хранить результаты сложных запросов и использовать потом как обычные таблицы. По логике напоминает большой CTE.

Создание view:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table1
 JOIN table2 ON table1.id = table2.id
WHERE condition;

Пример использования:

CREATE VIEW sales_summary AS
SELECT sales.date, 
    sales.amount, 
    customers.name AS customer_name
FROM sales
 JOIN customers ON sales.customer_id = customers.id
WHERE sales.date >= '2024-01-01';

SELECT * 
FROM sales_summary 
WHERE amount > 1000;

§6. Бакетирование (Bucketing)

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

Это больше инженерная история, но если у вас данные в Хайве или, прости господи, в Афине, то очень часто помогает выруливать большие данные. Об этом лучше проконсультироваться со своим дата-инженером, скорее всего у него уже заготовлен скрипт. Просто имей ввиду что такое бывает.

CREATE TABLE students (
    name STRING,
    age INT,
    gpa FLOAT
)

CLUSTERED BY (age) INTO 4 BUCKETS;

§7. Общие советы по написанию запросов

1. В блоке SELECT хорошей практикой является перечисление нужных столбцов. Использование * — это, чаще всего, плохой тон. Особенно в колоночных БД.
2. Всегда старайся фильтровать через WHERE, если это возможно. Хотя бы по дате. Данные за 10 лет редко когда нужны, а запрос будет крутиться долго.
3. Если ты джойнишь большие таблицы, то лучше сначала зафильтруй их, чтобы в итоге тянуть меньше строк в основной запрос. Это можно делать через CTE или линейными подзапросами, как больше нравится.
4. По возможности, не забывай про LIMIT.
5. Если запрос написан только наполовину, а там уже 500 строк, не скупись на вьюхи, они существенно ускоряют процесс.