ПостДень 2792. #ЗаметкиНаПолях #SQL

День 2792. #ЗаметкиНаПолях #SQL 10 Редких Возможностей SQL, Которые Стоит Знать Каждому. Часть 2 1-3 4. GROUPING SETS, ROLLUP и CUBE Для отчёта часто требуется получить сразу несколько уровней агрегации: итоговые значения по перевозчику и статусу, промежуточные итоги по перевозчику и общий итог. Наивный подход предполагает объединение нескольких запросов с помощью оператора UNION ALL. Конструкции GROUPING SETS, ROLLUP и CUBE позволяют получить все эти уровни в рамках одного запроса: SELECT carrier, status, COUNT(*) AS shipment_count, SUM(si.quantity) AS total_quantity FROM shipments s LEFT JOIN shipment_items si ON s.id = si.shipment_id GROUP BY GROUPING SETS ( (carrier, status), -- по поставщику и статусу (carrier), -- подытог по поставщику (status), -- подытог по статусу () -- общий итог ); SELECT carrier, status, DATE_TRUNC('month', created_at) AS month, COUNT(*) AS shipment_count FROM shipments GROUP BY ROLLUP (carrier, status, DATE_TRUNC('month', created_at) ); Первый запрос формирует группировки, которые вам нужны: по перевозчику и статусу, только по перевозчику, только по статусу, а также пустую группу () для получения общего итога. Оператор ROLLUP во втором запросе — это сокращённая запись для иерархических промежуточных итогов: сначала по перевозчику, затем по перевозчику и статусу, далее по перевозчику, статусу и месяцу — и наконец общий итог. Оператор CUBE создает все возможные комбинации столбцов. Один запрос заменяет 4, а БД вычисляет уровни за один проход, вместо того чтобы многократно сканировать таблицу. Эти средства являются частью стандарта SQL и поддерживаются в PostgreSQL, SQL Server и Oracle. 5. Предложение FILTER в агрегатных функциях Часто возникает необходимость подсчитать количество или сумму только для тех строк, которые удовлетворяют определённому условию, и вывести эти результаты рядом друг с другом. Предложение FILTER применяет условие к конкретной агрегатной функции, благодаря чему каждая из них обрабатывает своё подмножество данных — в рамках одной строки и за один проход по данным: SELECT carrier, COUNT(*) AS total_shipments, COUNT(*) FILTER (WHERE status = 'delivered') AS delivered_count, COUNT(*) FILTER (WHERE status = 'in_transit') AS in_transit_count, COUNT(*) FILTER (WHERE status = 'pending') AS pending_count, SUM(si.quantity) FILTER (WHERE status = 'delivered') AS delivered_quantity, SUM(si.quantity) FILTER (WHERE status = 'pending') AS pending_quantity FROM shipments s LEFT JOIN shipment_items si ON s.id = si.shipment_id GROUP BY carrier; Каждое выражение COUNT(*) FILTER (WHERE …) подсчитывает только соответствующие условию строки, благодаря чему вы получаете количество отправлений со статусами «доставлено», «в пути» и «в ожидании» в виде отдельных столбцов для каждого перевозчика. Запись COUNT(*) FILTER (WHERE status = 'delivered') читается легче, чем старый приём с использованием CASE: SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END). Назначение конструкции FILTER гораздо более очевидно. Примечание: FILTER поддерживается в PostgreSQL. В SQL Server и MySQL такой возможности нет — там приходится использовать CASE внутри агрегатной функции, например: COUNT(CASE WHEN status = 'delivered' THEN 1 END). 6. UPSERT (INSERT … ON CONFLICT) Вставка строки, если она новая, и её обновление, если она уже существует — распространённая задача, для решения которой обычно требуются SELECT, условие и две ветви выполнения кода. Операция UPSERT позволяет выполнить это одной атомарной командой, исключая риск возникновения состояния гонки между проверкой и записью: INSERT INTO shipments (id, number, order_id, address_street, address_city, address_zip, carrier, receiver_email, status, created_at, updated_at) VALUES ('550e8400-e29b-41d4-a716-446655440000', 'SH-2024-001', 'ORD-2024-001', '123 Main St', 'New York', '10001', 'FedEx', '[email protected]', 'pending', NOW(), NOW()) ON CONFLICT (number) DO UPDATE SET carrier = EXCLUDED.carrier, status = EXCLUDED.status, updated_at = GREATEST(shipments.updated_at, EXCLUDED.updated_at); Команда INSERT … ON CONFLICT (number) DO UPDATE пытается выполнить вставку; если строка с таким номером уже существует, вместо этого выполняется обновление. Псевдотаблица EXCLUDED содержит значения, которые вы пытались вставить, поэтому запись carrier = EXCLUDED.carrier означает «использовать нового перевозчика». Выражение GREATEST(shipments.updated_at, EXCLUDED.updated_at) позволяет сохранить более позднюю из двух временных меток. Одна команда, никаких дублирующихся строк и никаких проблем с состоянием гонки при одновременном выполнении запросов разными клиентами. Примечание: это синтаксис PostgreSQL. В стандарте SQL (и в таких СУБД, как SQL Server или Oracle) используется оператор MERGE, а в MySQL — INSERT … ON DUPLICATE KEY UPDATE. Замечание: поле, по которому будет отслеживаться конфликт (number) должно иметь ограничение уникальности. 7. Поддержка JSON Иногда требуется хранить гибкие, полуструктурированные данные — например, событие, тело веб-хука или блок настроек. PostgreSQL поддерживает JSON на уровне ядра (тип JSONB) и позволяет выполнять запросы к содержимому таких полей; благодаря этому вам не нужна отдельная документоориентированная БД для редких случаев использования JSON. Также отпадает необходимость хранить JSON в виде обычных строк и обрабатывать их на стороне бэкенда, теряя при этом все преимущества индексации: CREATE TABLE ship_events ( id SERIAL PRIMARY KEY, payload JSONB NOT NULL ); -- Пример данных INSERT INTO ship_events (payload) VALUES ('{"type":"click","coordinates":[{"x":10,"y":20},{"x":15,"y":25}]}'), ('{"type":"hover","coordinates":[{"x":5,"y":30}]}'), ('{"type":"scroll","coordinates":[{"x":0,"y":100},{"x":0,"y":200},{"x":0,"y":300}]}'); -- Выбираем поля JSON SELECT payload ->> 'type' AS event_type, payload -> 'coordinates' -> 0 ->> 'x' AS first_x, payload -> 'coordinates' -> 0 ->> 'y' AS first_y FROM ship_events; В таблице событий данные хранятся в формате JSONB. Запрос обращается к ним следующим образом: оператор ->> извлекает значение как текст, а оператор -> — вложенный JSON-объект или элемент массива; так, выражение payload -> 'coordinates' -> 0 ->> 'x' позволяет получить координату x первого элемента. Данные JSONB хранятся в разобранном бинарном виде и поддерживают индексацию, что позволяет выполнять фильтрацию и извлечение информации без сканирования документов целиком. Примечание: в SQL Server для работы с JSON используются функции JSON_VALUE и OPENJSON, а стандарт SQL предусматривает функцию JSON_TABLE (доступную в Oracle, MySQL и PostgreSQL 17+), которая преобразует JSON-массив непосредственно в строки реляционной таблицы. Окончание следует… Источник: https://antondevtips.com/blog/10-rare-sql-features-every-developer-should-know