День 2791. #ЗаметкиНаПолях #SQL
10 Редких Возможностей SQL, Которые Стоит Знать Каждому. Часть 1
Большинство разработчиков используют лишь 20% возможностей SQL. SELECT, JOIN, GROUP BY — и на этом останавливаются. Однако у SQL есть и «второй уровень» — функции, позволяющие превратить страницу кода приложения или три отдельных запроса в одну лаконичную и понятную инструкцию. При этом они не являются новыми или экзотическими: они уже доступны в используемой вами БД.

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

1. Обобщённые табличные выражения (CTE)
Сложный запрос, оформленный как единая инструкция, труден для чтения, а вносить в него изменения ещё сложнее. CTE позволяет разбить его на последовательные именованные этапы с помощью ключевого слова WITH. Каждый этап представляет собой временный именованный набор данных, который можно использовать в дальнейшем ходе запроса:

WITH recent_shipments AS (
  SELECT id, number, carrier, status, created_at
  FROM shipments
  WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
),
shipment_details AS (
  SELECT rs.number, rs.carrier, rs.status,
   COUNT(si.id) AS total_items,
   SUM(si.quantity) AS total_quantity
  FROM recent_shipments rs
  LEFT JOIN shipment_items si ON rs.id = si.shipment_id
  GROUP BY rs.number, rs.carrier, rs.status
)
SELECT number, carrier, status, total_items, total_quantity
FROM shipment_details ORDER BY total_quantity DESC;

Здесь 2 именованные части:
- recent_shipments выбирает данные об поставках за последние 30 дней;
- shipment_details использует этот результат, присоединяя к нему данные о товарных позициях и выполняя агрегацию (подсчёт количества и объёмов). В итоговом операторе SELECT обращение ко второму CTE происходит так же, как к обычной таблице.
В результате получается запрос, который читается сверху вниз, в отличие от подхода с использованием вложенных подзапросов, которые приходится разбирать изнутри наружу.
CTE также поддерживают рекурсию (с помощью конструкции WITH RECURSIVE); это позволяет работать с иерархическими данными, такими как организационные структуры или деревья категорий.
CTE можно использовать в операторах SELECT, INSERT, UPDATE или DELETE.

2. Оконные функции
Иногда требуется выполнить вычисления по связанным строкам, сохранив при этом в результирующем наборе каждую отдельную строку. Оператор GROUP BY сворачивает строки, оставляя по одной записи на группу. Оконная функция же выполняет вычисления по набору строк (так называемому «окну»), не объединяя при этом сами строки:

SELECT number, carrier, created_at,
 ROW_NUMBER() OVER (PARTITION BY carrier ORDER BY created_at DESC) AS shipment_sequence,
 RANK() OVER (PARTITION BY carrier ORDER BY created_at DESC) AS shipment_rank
FROM shipments;

SELECT number, status, created_at,
 LAG(status) OVER (ORDER BY created_at) AS previous_status,
 LEAD(carrier) OVER (ORDER BY created_at) AS next_carrier
FROM shipments;

Первый запрос ранжирует отправления каждого перевозчика по дате. ROW_NUMBER() присваивает уникальный порядковый номер в рамках группы (заданной через PARTITION BY carrier), а RANK() делает то же самое, но при совпадении значений присваивает им одинаковый ранг.
Во втором запросе используются LAG и LEAD для обращения к предыдущей и следующей строкам (в данном случае — для получения предыдущего статуса и следующего перевозчика) без выполнения самосоединения (self-join).
Оконные функции позволяют вычислять нарастающие итоги и скользящие средние, определять ранги, а также сравнивать данные в разных строках.
Они являются стандартом SQL и поддерживаются в PostgreSQL, SQL Server, Oracle и MySQL 8+.

3. LATERAL-Соединения
Обычное соединение соединяет две таблицы на основе определённого условия. LATERAL-соединение позволяет подзапросу, расположенному справа, обращаться к столбцам таблицы, расположенной слева, и выполняется для каждой строки отдельно, что идеально подходит для задач типа «выбрать N лучших записей в каждой группе»:

-- Для каждого перевозчика выбираем одну последнюю отправку
SELECT c.carrier, s.number, s.status, s.created_at
FROM (SELECT DISTINCT carrier FROM shipments) c
CROSS JOIN LATERAL (
  SELECT number, status, created_at
  FROM shipments WHERE carrier = c.carrier
  ORDER BY created_at DESC LIMIT 1
) s;

Для каждого конкретного перевозчика коррелирующий подзапрос выбирает одну — самую свежую — поставку (с помощью ORDER BY created_at DESC LIMIT 1).
Ключевой момент — условие WHERE carrier = c.carrier: внутренний запрос «видит» значение carrier из строки внешнего запроса, чего обычный подзапрос сделать не может.
Это наиболее элегантный способ получить «самую свежую запись в группе» или «топ-3 записи в категории» без использования оконных функций.
Примечание: в SQL Server аналогичная задача решается с помощью CROSS APPLY (или OUTER APPLY для аналога LEFT JOIN), а в PostgreSQL используются CROSS JOIN LATERAL и LEFT JOIN LATERAL.

Продолжение следует…

Источник: https://antondevtips.com/blog/10-rare-sql-features-every-developer-should-know