Оконные агрегаты, как считать по группе и не терять строки!

Оконные функции удобны не только для ранжирования. Частая задача на практике — посчитать агрегаты по группе и при этом оставить каждую строку исходной таблицы.

Представим таблицу заказов:

orders(id, customer_id, amount, created_at)

Хотим увидеть все заказы и одновременно понимать, сколько денег каждый клиент потратил в сумме.

Первая мысль — посчитать через GROUP BY:

SELECT
    customer_id,
    SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;

Да, сумму он посчитает. Но список заказов пропадёт — останется по одной строке на клиента.

Если коротко: GROUP BY считает по группе, но детали внутри группы выбрасывает.

Решение через оконную функцию:

SELECT
    id,
    customer_id,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id) AS total_amount
FROM orders;

PARTITION BY customer_id разбивает строки на независимые окна по клиентам.
SUM(...) OVER (...) считает сумму внутри каждого окна, но строки не схлопывает — каждая остаётся на месте.

В результате каждая строка — отдельный заказ и total_amount — сумма всех заказов этого клиента.

Фильтрация по агрегату без GROUP BY — например, нужно выбрать только заказы тех клиентов, у которых суммарный оборот больше 1000:

SELECT *
FROM (
    SELECT
        *,
        SUM(amount) OVER (PARTITION BY customer_id) AS total_amount
    FROM orders
) t
WHERE total_amount > 1000;

Обратите внимание: фильтрация происходит уже после вычисления оконной функции. Никаких GROUP BY, никаких дополнительных JOIN.

Та же логика через JOIN (для сравнения):

SELECT o.*
FROM orders o
JOIN (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
) s ON s.customer_id = o.customer_id
WHERE s.total_amount > 1000;

Работает, но выглядит тяжелее: отдельный подзапрос, джойн, дублирование таблицы.

Можно посчитать сразу несколько агрегатов по тем же окнам:

SELECT
    id,
    customer_id,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id) AS total_amount,
    COUNT(*)  OVER (PARTITION BY customer_id) AS orders_count
FROM orders;

Получаем и общую сумму, и количество заказов клиента — прямо в каждой строке.

🔥 Используйте оконные функции, когда нужно посчитать итог по группе, но не потерять сами строки. Это базовый приём аналитического SQL, который постоянно встречается в реальных задачах.

➡️ SQL Ready | #практика

👍 TutorialsPoint — сайт с теорий и практикой в одном месте!

Удобный онлайн-редактор, где можно быстро проверить SQL-запросы без установки СУБД и настройки окружения. Встроенные примеры позволяют сразу попробовать SELECT, JOIN, агрегатные функции, подзапросы и другие конструкции на готовых таблицах. Подходит для обучения, практики, подготовки к собесам. Также можно использовать, как мини шпаргалку.

📌 Оставляю ссылочку: tutorialspoint.com

➡️ SQL Ready | #сайт

Проверка наличия связанных данных!

Когда нужно проверить существование связанных строк, обычный JOIN часто увеличивает результат или заставляет делать DISTINCT, что бьёт по производительности:

SELECT *
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM payments p
  WHERE p.order_id = o.id
);

EXISTS останавливается на первой найденной строке и не создаёт дубликатов, поэтому обычно работает быстрее и предсказуемее:

SELECT *
FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM payments p
  WHERE p.order_id = o.id
);

NOT EXISTS - наиболее корректный способ найти записи без соответствующих связанных данных.

В отличие от NOT IN он корректнее работает при наличии NULL:

SELECT *
FROM orders o
LEFT JOIN payments p ON p.order_id = o.id
WHERE p.order_id IS NULL;

🔥 LEFT JOIN + IS NULL делает то же самое логически, но чаще требует больше работы оптимизатора и может уступать по производительности на больших объёмах.

➡️ SQL Ready | #совет

💡 Отличную статью нашёл на Хабре: «Демобаза 2.0 для PostgreSQL»!

В этой статье:
• Показано, как устроена учебная база, приближенная к реальным продакшен-сценариям с миллионами записей;
• Разобрано, как генерируются правдоподобные данные через имитацию работы системы, включая бронирования и жизненный цикл событий;
• Объясняется, какие задачи по SQL, аналитике и производительности можно отрабатывать на такой базе.

🔊 Продолжайте читать на Habr!

➡️ SQL Ready | #статья

TRUNC(date) — как правильно обрезать дату в Oracle без потери индекса!

В Oracle тип DATE хранит дату и время одновременно. Частая задача — сравнить только по дате, игнорируя время. Многие используют TRUNC(date), но это может сильно ухудшить производительность.

Представим таблицу:

orders(id, created_at DATE)

Нужно выбрать заказы за 18 февраля 2026.

Интуитивный вариант:

SELECT *
FROM orders
WHERE TRUNC(created_at) = DATE '2026-02-18';

Логически верно, но есть проблема.

Функция применяется к колонке — поэтому обычный индекс по created_at в основном не используется, и Oracle вынужден вычислять TRUNC для множества строк, что часто приводит к полному сканированию.

Правильный способ — диапазон по дате:

SELECT *
FROM orders
WHERE created_at >= DATE '2026-02-18'
  AND created_at <  DATE '2026-02-19';

Теперь условие индекс-дружелюбное (sargable) — оптимизатор может использовать обычный индекс по created_at.

Этот подход корректно работает независимо от времени в поле.

TRUNC(date) полезен в SELECT или GROUP BY, когда фильтрация по индексу не критична:

SELECT
    TRUNC(created_at) AS day,
    COUNT(*) AS orders_count
FROM orders
GROUP BY TRUNC(created_at)
ORDER BY day;

Если всё же нужно часто фильтровать по TRUNC(created_at), можно создать функциональный индекс:

CREATE INDEX idx_orders_created_day
ON orders (TRUNC(created_at));

После этого запрос с TRUNC сможет использовать индекс.

Функция TRUNC умеет обрезать дату до разных уровней:

SELECT
    TRUNC(SYSDATE)         AS day,
    TRUNC(SYSDATE, 'MM')   AS month_start,
    TRUNC(SYSDATE, 'YYYY') AS year_start
FROM dual;

Поддерживаются форматы: DD (день), MM (месяц), YYYY (год), IW (ISO-неделя) и др.

DATE хранит дату со временем, поэтому = DATE '2026-02-18' не находит все записи за день; функции над колонками мешают индексам.

🔥 Для фильтрации по дате используйте диапазоны, а TRUNC — для отображения, агрегации или вместе с функциональным индексом.

➡️ SQL Ready | #практика

FlowchartAI — это бесплатный AI-генератор блок-схем, который из текста или кода автоматически строит диаграмму. Он анализирует твой ввод (описание процесса/алгоритма или код) и превращает его в визуальную блок-схему, которую можно смотреть прямо в браузере без регистрации.

📌 Оставляю ссылочку: flowchartai.org

➡️ SQL Ready | #ресурс

💡 SQL Fiddle — онлайн платформа для тестирования SQL без установки БД!

Сервис позволяет писать, запускать и проверять SQL-запросы прямо в браузере. Можно создать схему базы, заполнить тестовыми данными и сразу увидеть результат выполнения, идеально для экспериментов, обучения и разбора задач. Поддерживает разные СУБД (MySQL, PostgreSQL, Oracle, SQL Server и др.), поэтому удобно сравнивать поведение запросов.

📌 Оставляю ссылочку: sqlfiddle.com

➡️ SQL Ready | #сайт

Вычисление временных интервалов между датами!

PostgreSQL умеет возвращать полноценный интервал одной операцией:

now() - created_at

Если нужно получить возраст записи в читаемом виде (годы-месяцы-дни), есть функция age(), которая учитывает календарь, а не просто секунды:

SELECT age(now(), created_at)
FROM orders;

А когда требуется число дней или часов для аналитики, интервал можно сразу привести к нужной единице:

EXTRACT(EPOCH FROM now() - created_at) / 3600

🔥 Полезно для TTL-логики, SLA-метрик, мониторинга задержек и любых задач, где время жизни записи, является ключевым параметром.

➡️ SQL Ready | #совет

📂 Напоминалка по SQL — базовые команды и порядок выполнения запроса!

Например, SELECT отвечает за выборку данных, FROM — за указание источника (таблицы), а WHERE позволяет отфильтровать строки по условию.

На картинке — основные операторы SQL, типы JOIN’ов и правильный порядок выполнения запроса внутри СУБД.

Сохрани, чтобы не забыть!

➡️ SQL Ready | #ресурс

✍️ Bilabon — задачи и теория по MySQL!

Это шпаргалка с ключевой теорией и упражнениями: управление БД и таблицами, типы данных, индексы, транзакции и оптимизация запросов. Также приведены практические задачи, позволяющие закрепить знания на примерах реальных предметных областей.

Оставляю ссылочку: GitHub 📱

➡️ SQL Ready | #репозиторий

SYSDATE в условиях — почему = SYSDATE почти никогда не то, что нужно!

В Oracle SYSDATE возвращает текущие дату и время с точностью до секунды. Частая ошибка — пытаться выбрать записи «за сегодня» через прямое сравнение.

Представим таблицу:

orders(id, created_at DATE)

Интуитивный вариант:

SELECT *
FROM orders
WHERE created_at = SYSDATE;

Логически кажется правильным — взять записи за текущий день. Но условие почти никогда не выполнится.

Тип DATE в Oracle хранит и дату, и время (до секунды). Чтобы совпадение произошло, created_at должен быть ровно равен текущему моменту до секунды. На практике такой запрос почти всегда возвращает пустой результат.

Попытка исправить через TRUNC:

SELECT *
FROM orders
WHERE TRUNC(created_at) = TRUNC(SYSDATE);

Работает корректно, но есть нюанс — функция применяется к колонке, поэтому обычный индекс по created_at не используется (если только нет function-based index на TRUNC(created_at)).

Правильный способ — диапазон по дате:

SELECT *
FROM orders
WHERE created_at >= TRUNC(SYSDATE)
  AND created_at <  TRUNC(SYSDATE) + 1;

Такое условие индекс-дружелюбное (sargable), и оптимизатор может использовать индекс. Запрос выбирает все записи за текущие сутки независимо от времени (по часовому поясу сессии/сервера БД).

Аналогично можно искать за любой день:

WHERE created_at >= DATE '2026-02-18'
  AND created_at <  DATE '2026-02-19'

Поскольку DATE хранит время, прямое равенство по дате почти никогда не подходит для выборки «за день».

🔥 Для выборки за день используйте полуоткрытый диапазон, а не сравнение с SYSDATE.

➡️ SQL Ready | #практика

Проверка совпадений сразу по нескольким полям одной операцией!

Когда нужно сравнить строки по составному ключу, многие пишут условия с AND, которые хуже читаются и сложнее поддерживаются:

u.email = b.email AND u.phone = b.phone

SQL умеет сравнивать кортежи целиком (row value), поэтому несколько колонок можно проверить одним выражением:

(email, phone)

Поддержка зависит от СУБД. Учитывайте поведение NULL и наличие составных индексов, оптимизация не всегда идентична AND.

🔥 Это особенно полезно при фильтрации, дедупликации, проверке попадания в списки, синхронизации таблиц и миграциях данных.

➡️ SQL Ready | #совет

Дедупликация с приоритетом, оставляем лучшую строку!

Когда есть дубликаты по ключу (например, email), нужно сохранить запись с максимальным приоритетом — подтверждённую или самую новую.

Таблица: users(id, email, is_verified, created_at)

Сначала можно увидеть, какая строка будет считаться главной. Нумеруем строки внутри каждого email по приоритету:

SELECT id, email,
       ROW_NUMBER() OVER (
         PARTITION BY email
         ORDER BY is_verified DESC, created_at DESC
       ) rn
FROM users;

Оконная функция делит строки по email и присваивает номер согласно приоритету, где подтверждённые и более новые записи идут первыми.

Оставим только одну запись на email — ту, у которой rn = 1:

SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY email
           ORDER BY is_verified DESC, created_at DESC
         ) rn
  FROM users
) t
WHERE rn = 1;

Все остальные строки в каждой группе автоматически становятся дублями.

В PostgreSQL можно сделать то же самое короче:

SELECT DISTINCT ON (email)
       id, email, is_verified, created_at
FROM users
ORDER BY email, is_verified DESC, created_at DESC;

Здесь ORDER BY фактически задаёт правило выбора победителя внутри каждой группы. СУБД оставит одну строку на email согласно приоритету сортировки.

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

➡️ SQL Ready | #практика