Окна, наборы, DML

SQL оконные функции, наборы и изменения данных¶

Завершающая часть: аналитика в SQL, операции над наборами, изменения данных и несколько практических советов.

Оконные функции (Window Functions)¶

Оконные функции позволяют делать расчеты по группе, но не сжимать строки, как GROUP BY.

1) ROW_NUMBER: пронумеровать строки¶

SQL

SELECT
  id,
  user_id,
  created_at,
  ROW_NUMBER() OVER (ORDER BY created_at) AS rn
FROM orders
ORDER BY created_at;

2) Топ-1 заказ на пользователя¶

Берем самый новый заказ каждого пользователя:

SQL

WITH ranked AS (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at DESC
    ) AS rn
  FROM orders o
)
SELECT id, user_id, status, created_at
FROM ranked
WHERE rn = 1
ORDER BY user_id;

3) Накопительная сумма (running total)¶

1) считаем сумму по заказам, 2) поверх этого делаем окно SUM() OVER (...).

SQL

WITH order_totals AS (
  SELECT
    o.id AS order_id,
    o.user_id,
    o.created_at,
    SUM(oi.qty * oi.price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.id, o.user_id, o.created_at
)
SELECT
  user_id,
  order_id,
  created_at,
  total,
  SUM(total) OVER (
    PARTITION BY user_id
    ORDER BY created_at
  ) AS running_total
FROM order_totals
ORDER BY user_id, created_at;

Операции над наборами: UNION / INTERSECT / EXCEPT¶

Иногда нужно склеить результаты нескольких запросов.

UNION ALL: просто объединить¶

SQL

SELECT name AS title, 'user' AS kind
FROM users
UNION ALL
SELECT name AS title, 'product' AS kind
FROM products;

UNION ALL сохраняет дубликаты.

UNION: объединить и убрать дубликаты¶

SQL

SELECT name FROM users
UNION
SELECT name FROM products;

UNION делает уникализацию (может быть дороже по времени).

INTERSECT / EXCEPT¶

INTERSECT оставляет только строки, которые есть в обоих наборах.
EXCEPT возвращает строки из первого набора, которых нет во втором.

INSERT / UPDATE / DELETE: меняем данные¶

INSERT: добавить строку¶

SQL

INSERT INTO products (id, name, price)
VALUES (5, 'Коврик для мыши', 12.50);

UPDATE: изменить¶

SQL

UPDATE orders
SET status = 'paid'
WHERE id = 1002;

Важно: UPDATE без WHERE изменит все строки.

DELETE: удалить¶

SQL

DELETE FROM orders
WHERE id = 1003;

Без WHERE — удалите все строки.

Транзакции: безопасные несколько шагов¶

SQL

BEGIN;

UPDATE orders
SET status = 'paid'
WHERE id = 1002;

-- если все хорошо:
COMMIT;

-- если ошибка:
-- ROLLBACK;

Транзакции нужны там, где "или все, или ничего": платежи, перенос денег, создание заказа и его позиций.

Немного про производительность (для новичка)¶

Индексы (кратко)¶

Индекс ускоряет поиск по колонке. Если часто ищете заказы по user_id:

SQL

CREATE INDEX idx_orders_user_id ON orders(user_id);

Золотое правило¶

Мало данных → почти все быстро.
Много данных → важны индексы, фильтры, правильные JOIN и количество читаемых строк.

Частые ошибки новичков¶

Путать WHERE и HAVING
WHERE фильтрует строки
HAVING фильтрует группы после GROUP BY
Сравнивать с NULL через =
нужно IS NULL / IS NOT NULL
JOIN "размножает" строки
если связь один-ко-многим, строк станет больше — это нормально
проверяйте, что join-ите по правильному ключу
UPDATE/DELETE без WHERE
введите привычку: сначала писать WHERE, потом остальное
Слишком рано бояться сложных запросов
часто сложность решается CTE: "сделай в 2 шага"

Практика (мини-задания)¶

Уровень 1¶

Выведите всех пользователей по алфавиту.
Выведите товары дороже 20.
Выведите 2 самых дорогих товара.

Уровень 2¶

Выведите заказы вместе с именем пользователя (JOIN).
Посчитайте количество заказов по статусам (GROUP BY).
Посчитайте сумму каждого заказа (JOIN + GROUP BY).

Уровень 3¶

Найдите пользователей, у которых есть хотя бы один заказ (EXISTS).
Найдите самый новый заказ каждого пользователя (ROW_NUMBER).
Сделайте отчет: сколько всего потратил каждый пользователь (JOIN + SUM).