Таблицы и SQL

Шпаргалка SQL

Типы соединений на живом примере, оконные функции, CTE, индексы и различия PostgreSQL, MySQL, SQLite и SQL Server.

На трёх пользователях и четырёх заказах

Типы соединений

Кругами Эйлера не объяснить, почему LEFT JOIN с условием в WHERE превращается в INNER. Двумя крошечными таблицами — объяснить: смотрите, какие строки исчезают.

users

id	name
1	Аня
2	Борис
3	Вера

orders

user_id	total
1	1200
1	300
2	900
4	150

SELECT u.name, o.total
  FROM users u
  JOIN orders o ON o.user_id = u.id;

Строка попадает в результат, только если пара нашлась с обеих сторон. Вера без заказов и заказ несуществующего пользователя исчезают.

Результат — 3 строки

users.name	orders.total
Аня	1200
Аня	300
Борис	900

Одно и то же разными словами

Чем отличаются СУБД

Задача	PostgreSQL	MySQL / MariaDB	SQLite	SQL Server
Ограничить число строк	LIMIT 10 OFFSET 20	LIMIT 20, 10	LIMIT 10 OFFSET 20	OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
Склеить строки	a \|\| b	CONCAT(a, b)	a \|\| b	a + b
Текущее время	now()	NOW()	datetime('now')	GETDATE()
Автоинкремент	GENERATED ALWAYS AS IDENTITY	AUTO_INCREMENT	INTEGER PRIMARY KEY	IDENTITY(1,1)
Upsert	ON CONFLICT DO UPDATE	ON DUPLICATE KEY UPDATE	ON CONFLICT DO UPDATE	MERGE
Регистронезависимый LIKE	ILIKE	LIKE (по сортировке)	LIKE (только ASCII)	LIKE (по сортировке)
Логические значения	boolean	TINYINT(1)	0 и 1	BIT
Вернуть изменённые строки	RETURNING	—	RETURNING (3.35+)	OUTPUT
FULL OUTER JOIN	есть	нет, собирают из UNION	есть (3.39+)	есть
Кавычки для имени колонки	"name"	`name`	"name"	[name]
Тип для денег	numeric(12,2)	DECIMAL(12,2)	INTEGER в копейках	DECIMAL(12,2)
Регулярное выражение	~ и regexp_match	REGEXP	REGEXP (нужно расширение)	—
Список таблиц	\dt	SHOW TABLES	.tables	sys.tables
План запроса	EXPLAIN ANALYZE	EXPLAIN ANALYZE (8.0.18+)	EXPLAIN QUERY PLAN	SET SHOWPLAN_ALL ON

Поиск по шпаргалке

Раздел

Показано 61 из 61 строк.

Оконные функции

Оконная функция не схлопывает строки: она добавляет к каждой строке значение, посчитанное по её «окну». Этим она и отличается от GROUP BY.

ROW_NUMBER() OVER (ORDER BY created_at)
Сквозная нумерация без пропусков и без повторов.
RANK() OVER (ORDER BY total DESC)
Место в рейтинге: одинаковые значения получают один ранг, следующий номер пропускается.
DENSE_RANK() OVER (ORDER BY total DESC)
То же, но без пропусков в нумерации.
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)
Нумерация внутри каждого пользователя — основа выборки «последний заказ каждого клиента».
SUM(total) OVER (PARTITION BY user_id)
Сумма по клиенту рядом с каждой его строкой.
SUM(total) OVER (ORDER BY day ROWS UNBOUNDED PRECEDING)
Нарастающий итог.
AVG(total) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
Скользящее среднее за семь дней.
LAG(total) OVER (ORDER BY day)
Значение предыдущей строки — считать прирост день к дню.
LEAD(total, 1, 0) OVER (ORDER BY day)
Значение следующей строки, третий аргумент — чем заменить отсутствующее.
FIRST_VALUE(total) OVER (PARTITION BY user_id ORDER BY day)
Первое значение в группе.
NTILE(4) OVER (ORDER BY total)
Разложить строки по четырём равным корзинам — квартили.
COUNT(*) OVER ()
Общее число строк выборки рядом с каждой строкой — удобно для пагинации одним запросом.
WINDOW w AS (PARTITION BY user_id ORDER BY day)
Именованное окно: описать один раз и ссылаться из нескольких функций.

CTE и подзапросы

WITH recent AS (SELECT … ) SELECT * FROM recent;
Общее табличное выражение: длинный запрос читается сверху вниз, а не изнутри наружу.
WITH a AS (…), b AS (SELECT … FROM a) SELECT …;
Несколько CTE подряд, второй видит первый.
WITH RECURSIVE tree AS (SELECT … UNION ALL SELECT … JOIN tree …) SELECT * FROM tree;
Рекурсивный обход иерархии: категории, комментарии, оргструктура.
WITH RECURSIVE days AS (SELECT DATE '2026-01-01' d UNION ALL SELECT d + 1 FROM days WHERE d < DATE '2026-12-31') SELECT * FROM days;
Календарь без отдельной таблицы дат.
WITH moved AS (DELETE FROM a RETURNING *) INSERT INTO b SELECT * FROM moved;
Перенос строк одной командой (PostgreSQL).
SELECT * FROM t WHERE EXISTS (SELECT 1 FROM o WHERE o.t_id = t.id);
EXISTS останавливается на первой найденной строке — обычно быстрее, чем IN с подзапросом.
SELECT * FROM t WHERE NOT EXISTS (…);
Надёжнее NOT IN: NOT IN со списком, где есть NULL, не вернёт ничего.
SELECT … FROM t JOIN LATERAL (SELECT … WHERE o.t_id = t.id LIMIT 3) x ON true;
LATERAL: подзапрос видит текущую строку — «три последних заказа каждого клиента».

Группировка и агрегаты

GROUP BY user_id HAVING COUNT(*) > 3
WHERE фильтрует строки до группировки, HAVING — группы после.
COUNT(*) vs COUNT(col)
Первое считает строки, второе — только те, где значение не NULL.
COUNT(DISTINCT user_id)
Число уникальных значений.
SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END)
Условная сумма — сводная таблица без разворота.
FILTER (WHERE status = 'paid')
То же короче и понятнее: COUNT(*) FILTER (WHERE …) в PostgreSQL и SQLite.
GROUP BY GROUPING SETS ((a), (b), ())
Несколько разрезов одним запросом; ROLLUP и CUBE — частные случаи.
STRING_AGG(name, ', ' ORDER BY name)
Склеить значения группы в строку (в MySQL — GROUP_CONCAT).
COALESCE(value, 0)
Заменить NULL. Помните: NULL + 1 = NULL, и SUM по пустой группе тоже NULL.
NULLIF(a, 0)
Превратить значение в NULL — защита от деления на ноль: a / NULLIF(b, 0).
DISTINCT ON (user_id) … ORDER BY user_id, created_at DESC
Первая строка каждой группы одной строчкой (PostgreSQL).

Индексы и планы

Индекс ускоряет чтение и замедляет запись. Каждый лишний индекс — это дополнительная работа при каждом INSERT и UPDATE.

CREATE INDEX idx_orders_user ON orders (user_id);
Обычный B-tree: равенство, диапазоны, сортировка.
CREATE INDEX idx_orders_user_day ON orders (user_id, created_at);
Составной индекс работает слева направо: по одному user_id — да, по одному created_at — нет.
CREATE UNIQUE INDEX ON users (lower(email));
Уникальность без учёта регистра — функциональный индекс.
CREATE INDEX ON orders (user_id) WHERE status = 'active';
Частичный индекс: меньше и быстрее, если запросы всегда с этим условием.
CREATE INDEX CONCURRENTLY …
Строить индекс, не блокируя запись в таблицу (PostgreSQL). Дольше, но без простоя.
EXPLAIN ANALYZE SELECT …
Не только план, но и реальное время. Сравнивайте rows= ожидаемые с actual rows= — расхождение означает устаревшую статистику.
Seq Scan вместо Index Scan
Нормально для маленьких таблиц и выборок больше нескольких процентов. Проблема — когда таблица большая, а условие узкое.
WHERE date(created_at) = '2026-01-01'
Функция над колонкой отключает индекс. Пишите диапазон: created_at >= … AND created_at < ….
WHERE name LIKE '%текст%'
Ведущий процент индексом не покрывается. Нужен полнотекстовый поиск или триграммный индекс.
ANALYZE table_name;
Обновить статистику, по которой планировщик выбирает план.
CREATE INDEX ON t USING gin (data jsonb_path_ops);
Индекс по JSONB в PostgreSQL.

Изменение данных

INSERT INTO t (a, b) VALUES (1, 2), (3, 4);
Многострочная вставка — на порядок быстрее, чем по одной.
INSERT … ON CONFLICT (id) DO UPDATE SET a = EXCLUDED.a;
Upsert в PostgreSQL и SQLite.
INSERT … ON DUPLICATE KEY UPDATE a = VALUES(a);
Upsert в MySQL.
INSERT … ON CONFLICT DO NOTHING;
Пропустить дубликат молча.
UPDATE t SET a = 1 WHERE id = 2 RETURNING *;
Вернуть изменённые строки, не делая второй запрос (PostgreSQL, SQLite 3.35+).
UPDATE t SET a = s.a FROM s WHERE s.id = t.id;
Обновление по данным другой таблицы.
DELETE FROM t WHERE id IN (SELECT id FROM t ORDER BY id LIMIT 1000);
Удалять большими партиями по частям — иначе одна транзакция забьёт журнал и заблокирует таблицу.
TRUNCATE t;
Быстрая очистка таблицы целиком, но без триггеров и без возможности отфильтровать.
BEGIN; … COMMIT;
Явная транзакция; SAVEPOINT позволяет откатить только часть.
SELECT … FOR UPDATE SKIP LOCKED LIMIT 1;
Очередь задач без гонок: каждый воркер берёт свою строку.

Даты, строки, JSON

date_trunc('month', created_at)
Округлить дату вниз до месяца (PostgreSQL). В MySQL — DATE_FORMAT, в SQLite — strftime.
created_at >= now() - interval '7 days'
Последняя неделя. Условие на колонку без функции — индекс работает.
AGE(a, b) / EXTRACT(epoch FROM a - b)
Разница дат по-человечески и в секундах.
generate_series(date1, date2, '1 day')
Ряд дат, чтобы в отчёте не пропадали дни без событий (PostgreSQL).
data->>'name'
Значение из JSONB как текст; -> вернёт JSON.
data @> '{"active": true}'
Содержит ли JSON заданный фрагмент — покрывается GIN-индексом.
ILIKE / LOWER(col) = LOWER(?)
Регистронезависимое сравнение: первое в PostgreSQL, второе везде.
split_part(email, '@', 2)
Домен из адреса.
CAST(value AS integer) / value::integer
Приведение типа; вторая форма — сокращение PostgreSQL.