Практические примеры SQL

{ "title": "Практические примеры SQL: отбор данных, оптимизация и типовые ошибки", "keywords": "SQL примеры, SELECT запросы, JOIN, GROUP BY, оптимизация SQL, ошибки SQL, подзапросы, CTE, оконные функции", "description": "Подборка практических примеров SQL с реальными сценариями: от простой фильтрации до оконных функций. Разбор типовых ошибок, способы ускорения запросов, конкретные цифры производительности.", "html_content": "

1. Базовый отбор данных: фильтры и сортировка для повседневных задач

Самый частый сценарий — выборка заказов за определённый период. Вместо WHERE date > '2025-01-01' используйте диапазон BETWEEN, чтобы исключить граничные ошибки. Например: WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31' — такой вариант включает обе даты и работает быстрее на индексах.

Фильтр по статусу и сумме чека: WHERE status = 'completed' AND total_amount > 5000. Важно: проверяйте типы данных. Если столбец total_amount хранит копейки как целое число (500000 = 5000 руб), сравнение должно быть > 500000. Иначе запрос вернёт пустой результат.

Сортировка по нескольким полям: ORDER BY status ASC, total_amount DESC. Статусы идут по алфавиту, а внутри каждого статуса — от дорогих заказов к дешёвым. Это удобно для отчётов: сначала «отменённые» (C), потом «выполненные» (E). Если нужно обратное — используйте CASE для кастомного порядка.

• Совет: для фильтрации по тексту избегайте LIKE '%текст%' — он убивает индекс. Заменяйте на полнотекстовый поиск (MATCH ... AGAINST в MySQL) или tsvector в PostgreSQL.
• Пример: найти всех клиентов с телефоном, начинающимся на '+7-901'. Пишите WHERE phone LIKE '+7-901%' — это сработает по индексу.
• Ошибка: WHERE total_amount = 0 покажет и NULL-значения только если явно указано OR total_amount IS NULL. NULL не равен нулю.
• Совет: для проверки на вхождение в список используйте IN, а не цепочку OR. WHERE city IN ('Москва','СПб','Казань') быстрее и читаемее.
• Производительность: сортировка десятков тысяч строк без индекса — подвисание на 2–5 секунд. Индекс по полю сортировки сокращает время до 10–50 мс.

2. JOIN: как соединять таблицы без потери строк и дубликатов

Частая задача: показать заказы вместе с именем клиента. INNER JOIN clients ON orders.client_id = clients.id. Если у заказа нет клиента (например, удалённая запись), такой заказ пропадёт из выборки. Для отчётов с суммой лучше использовать LEFT JOIN.

Пример: получить все товары и их последнюю поставку. LEFT JOIN (SELECT product_id, MAX(supply_date) AS last_date FROM supplies GROUP BY product_id) AS last_supply ON products.id = last_supply.product_id. Это даст NULL для товаров без поставок — явно видно, что нужно заказать.

Когда нужно соединить 4–5 таблиц, запрос становится громоздким. Разбивайте на WITH (CTE) — это повышает читаемость и часто ускоряет выполнение за счёт материализации промежуточных данных. Например: выделите отдельно список активных клиентов, затем соедините его с заказами.

• Типовая ошибка: забыть условие соединения — получится декартово произведение. Для 1000 заказов и 500 товаров — 500 000 строк.
• Совет: всегда используйте псевдонимы таблиц: FROM orders o. Это упрощает чтение и избегает конфликтов имён.
• Пример: SELECT o.id, c.name FROM orders o LEFT JOIN clients c ON o.client_id = c.id. Итог: все заказы, имя клиента — если есть.
• Производительность: LEFT JOIN на таблицу с миллионом строк может выполняться 3–5 секунд. Добавление индекса по client_id сокращает до 50–200 мс.
• Когда нужен FULL JOIN: найти клиентов без заказов и заказы без клиентов в одном запросе. Альтернатива — два LEFT JOIN с WHERE ... IS NULL.

3. Агрегация и группировка: точные подсчёты с фильтрацией групп

Посчитать сумму продаж по каждому менеджеру: SELECT manager_id, SUM(amount) AS total FROM orders GROUP BY manager_id. Если нужно оставить только тех, у кого сумма больше 100 000 — используйте HAVING SUM(amount) > 100000. HAVING работает после группировки, WHERE — до.

Пример: найти топ-5 категорий товаров по количеству продаж. SELECT category_id, COUNT(*) AS cnt FROM order_items GROUP BY category_id ORDER BY cnt DESC LIMIT 5. Запрос выполняется за 50 мс на таблице в 500 тыс. строк при наличии индекса по category_id.

Частая ошибка: включать в SELECT поля, не участвующие в GROUP BY и не агрегированные. В MySQL это может сработать, но выдаст случайное значение. В PostgreSQL — ошибка. Всегда либо добавляйте поле в GROUP BY, либо применяйте агрегатную функцию.

• Совет: для подсчёта уникальных клиентов используйте COUNT(DISTINCT client_id). Это точнее, чем COUNT(*), если один и тот же клиент совершил несколько покупок.
• Пример: средний чек по дням недели: SELECT EXTRACT(DOW FROM order_date) AS day, AVG(total) FROM orders GROUP BY day. Это полезно для планирования рекламных кампаний.
• Ошибка: использовать WHERE для фильтрации агрегатов. Пишите только HAVING.
• Производительность: группировка по двум полям с миллионом строк — 0.3–0.8 секунды. Индекс по этим полям может дать выигрыш в 5–10 раз.
• Совет: в отчётах за месяц суммируйте предрасчитанные дневные итоги, а не сырые данные. Это ускоряет отчёт в 20–50 раз.

4. Подзапросы и CTE: когда вложенный запрос работает быстрее

Подзапрос в WHERE: найти товары дороже среднего по категории. WHERE price > (SELECT AVG(price) FROM products WHERE category_id = p.category_id). Такой коррелированный подзапрос может выполняться для каждой строки — на 10 000 строк это 0.5–1 секунда. Альтернатива — оконная функция.

CTE (Common Table Expression) — временная таблица для одного запроса. Пример: WITH top_clients AS (SELECT id FROM clients WHERE total_spent > 50000). Затем используем её в основном запросе. CTE читается проще подзапроса и может материализоваться, если используется многократно.

Когда CTE невыгоден: если он вызывается один раз и фильтр уникален — подзапрос часто быстрее из-за отсутствия материализации. В PostgreSQL 16+ оптимизатор может сам решить, материализовать CTE или нет.

• Совет: избегайте вложенных подзапросов глубже 2 уровней. Это резко снижает читаемость и часто производительность.
• Пример: SELECT * FROM products WHERE id IN (SELECT product_id FROM order_items WHERE quantity > 5). Просто и быстро (при индексах).
• Ошибка: подзапрос, возвращающий больше одной строки в = — получите ошибку. Используйте IN или EXISTS.
• Производительность: EXISTS часто быстрее IN, если подзапрос большой и есть индекс. IN обычно быстрее для маленьких списков (до 100–200 значений).
• Совет: в CTE можно добавлять ORDER BY и LIMIT — это даёт контролируемый топ для каждого разреза.

5. Оконные функции: ранжирование и скользящие суммы без группировки

Оконные функции позволяют делать расчёты по группам, не сжимая строки. Пример: для каждого заказа показать его долю от суммы по клиенту. SELECT total, SUM(total) OVER (PARTITION BY client_id) AS client_total. Так вы видите и каждую покупку, и общий итог по клиенту в той же строке.

Ранжирование товаров по цене в категории: RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rnk. RANK оставляет пропуски при совпадении цен, DENSE_RANK — без пропусков. Выбирайте по смыслу: ранги с пропусками нужны для строгой нумерации, без пропусков — для распределения по группам.

Скользящее среднее за 7 дней: AVG(total) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Это даёт сглаженный тренд продаж без потери дат. Для 1000 записей такой запрос выполняется за 0.1 сек.

• Совет: оконные функции выполняются после WHERE, поэтому фильтровать сначала строки, потом считать окно — хорошая практика.
• Пример: найти второй по стоимости заказ для каждого клиента: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY total DESC) AS rn FROM orders) AS sq WHERE rn = 2.
• Ошибка: путать ROW_NUMBER и RANK. ROW_NUMBER даёт уникальный номер даже при равных значениях, RANK — одинаковый при равенстве.
• Производительность: оконные функции на миллионе строк с сортировкой по полю без индекса — 3–5 секунд. Индекс по PARTITION BY и ORDER BY может дать 10–20-кратное ускорение.
• Совет: для скользящих сумм за большие периоды (30 дней) убедитесь, что данные идут подряд. Пропуски дат — это нормально для ROWS (считает по физическим строкам), но для RANGE — учитывает значения, что может дать неверный результат.

6. Типовые ошибки и способы их избежать

Ошибка №1: неиспользование индексов. Если в запросе есть WHERE city = 'Москва' без индекса — база будет сканировать всю таблицу. На 500 тыс. строк время запроса — 2–5 секунд. С индексом — 0.01 секунды. Проверяйте планы выполнения через EXPLAIN.

Ошибка №2: SELECT * в продакшн-коде. Это тащит все столбцы, даже если нужно только 2. Лишняя нагрузка на диск и сеть. Всегда перечисляйте конкретные поля.

Ошибка №3: неправильная обработка NULL. Любое сравнение с NULL даёт NULL, а не TRUE/FALSE. В WHERE это работает как FALSE. Если нужно проверить на NULL — только IS NULL или IS NOT NULL.

• Ошибка №4: игнорирование типов данных. Сравнение строкового '123' с числом 123 — неявное приведение, которое убивает индекс.
• Ошибка №5: отсутствие LIMIT на этапе разработки. Случайный запрос без LIMIT может вернуть 2 млн строк — зависание на 10–20 секунд.
• Ошибка №6: вложенные запросы вместо JOIN. Часто JOIN выполняется быстрее, так как оптимизатор лучше управляет соединением.
• Ошибка №7: неправильная группировка: GROUP BY client_id, а в SELECT — order_date. Если дата не входит в группу и не агрегирована — MySQL выберет случайную, PostgreSQL упадёт с ошибкой.

7. Практические рекомендации: как ускорить запросы в 10 раз

Первое правило: создавайте индексы под самые частые фильтры и соединения. Один составной индекс (client_id, status) покрывает запросы с фильтром по клиенту и статусу, и соединение по клиенту. Экономия — от 50 мс до 2 секунд на миллион строк.

Второе правило: используйте EXPLAIN ANALYZE для каждого медленного запроса. Ищите последовательное сканирование таблиц (Seq Scan) — это сигнал к добавлению индекса. Если видите сортировку на 100 тыс. строк — возможно, нужен индекс с сортировкой.

Третье правило: для частых отчётов создавайте материализованные представления. Они обновляются раз в час/день, но запрос по ним выполняется за 0.1 сек вместо 5 секунд на сырых данных.

• Совет: используйте частичные индексы. CREATE INDEX idx_active ON orders (order_date) WHERE status = 'active'. Индекс занимает в 10 раз меньше места и работает быстрее.
• Пример: для фильтрации по year(order_date) создайте функциональный индекс: CREATE INDEX idx_year ON orders (EXTRACT(YEAR FROM order_date)).
• Производительность: переход от SELECT * к выборке конкретных 3 столбцов с индексом покрытия может ускорить запрос в 3–5 раз, даже без изменения плана.
• Совет: для больших UPDATE (миллионы строк) разби

  Добавлено: 27.04.2026