GROUP BY GROUPING SETS — несколько отчётов за один SQL-запрос!
В аналитике часто нужно получить статистику сразу на нескольких уровнях: по пользователям, по статусам и общий итог. Обычно для этого пишут несколько
Но в PostgreSQL есть малоиспользуемая возможность —
Представим таблицу заказов:
orders(id, user_id, status, amount)
Обычная группировка по пользователям:
Она возвращает суммы по каждому пользователю, но не показывает итоги по статусам или общую выручку. Для них пришлось бы писать дополнительные запросы.
Вместо этого можно задать сразу несколько уровней агрегации:
PostgreSQL сформирует три набора результатов: суммы по пользователям, суммы по статусам и общий итог по всей таблице.
Пустая группировка
Но в результирующих строках отсутствующие измерения заполняются NULL. Как понять, где общий итог, а где значение действительно отсутствовало в данных?
Для этого существует функция
В нашем случае level = 1 означает группировку по user_id, level = 2 — по status, а level = 3 — общий итог.
Есть ещё более короткий синтаксис для иерархических отчётов:
Это удобно для финансовых отчётов, аналитических витрин и дашбордов, где одни и те же данные нужно агрегировать на нескольких уровнях.
🔥
➡️ SQL Ready | #практика
В аналитике часто нужно получить статистику сразу на нескольких уровнях: по пользователям, по статусам и общий итог. Обычно для этого пишут несколько
GROUP BY и объединяют результаты через UNION ALL.Но в PostgreSQL есть малоиспользуемая возможность —
GROUPING SETS, которая позволяет рассчитывать разные группировки в одном запросе.Представим таблицу заказов:
orders(id, user_id, status, amount)
Обычная группировка по пользователям:
SELECT user_id, SUM(amount)
FROM orders
GROUP BY user_id;
Она возвращает суммы по каждому пользователю, но не показывает итоги по статусам или общую выручку. Для них пришлось бы писать дополнительные запросы.
Вместо этого можно задать сразу несколько уровней агрегации:
SELECT user_id, status, SUM(amount)
FROM orders
GROUP BY GROUPING SETS (
(user_id),
(status),
()
);
PostgreSQL сформирует три набора результатов: суммы по пользователям, суммы по статусам и общий итог по всей таблице.
Пустая группировка
() означает агрегацию всех строк без разделения на группы.Но в результирующих строках отсутствующие измерения заполняются NULL. Как понять, где общий итог, а где значение действительно отсутствовало в данных?
Для этого существует функция
GROUPING():SELECT
user_id, status,
GROUPING(user_id, status) AS level,
SUM(amount) AS total
FROM orders
GROUP BY GROUPING SETS (
(user_id), (status), ()
);
GROUPING возвращает битовую маску, показывающую, какие колонки исключены из текущей группировки.В нашем случае level = 1 означает группировку по user_id, level = 2 — по status, а level = 3 — общий итог.
Есть ещё более короткий синтаксис для иерархических отчётов:
SELECT user_id, status, SUM(amount)
FROM orders
GROUP BY ROLLUP (user_id, status);
ROLLUP автоматически рассчитает суммы по каждой паре user_id и status, промежуточные итоги по user_id и общий итог.Это удобно для финансовых отчётов, аналитических витрин и дашбордов, где одни и те же данные нужно агрегировать на нескольких уровнях.
GROUPING SETS и ROLLUP позволяют заменить несколько отдельных агрегаций одним SQL-запросом. Это сокращает дублирование кода и может уменьшить объём работы с исходными данными.Please open Telegram to view this post
VIEW IN TELEGRAM
❤12🔥8👍5🤝4
После миграции или обновления ETL-пайплайна количество строк совпало. Но означает ли это, что данные идентичны? Далеко не всегда.
Сегодня в гайде:
• Как находить различия между двумя наборами данных через EXCEPT;
• Как проверять расхождения в обоих направлениях;
• Зачем нужен EXCEPT ALL и как учитывать дубликаты;
• Как автоматизировать проверку совпадения данных.
Полезный приём для проверки миграций, сверки витрин и тестирования ETL — когда важно убедиться, что совпадают сами данные, а не только количество строк.
Please open Telegram to view this post
VIEW IN TELEGRAM
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥10👍8🤝4❤2
Получайте SQL-определения объектов из PostgreSQL!
Когда нужно перенести индекс, проверить ограничение или восстановить определение объекта, необязательно искать исходную миграцию или вручную собирать SQL из системных каталогов.
PostgreSQL умеет самостоятельно восстанавливать определения объектов через встроенные функции.
Например, получить SQL всех индексов таблицы:
Аналогично можно получить определения ограничений:
Для представлений есть отдельная функция:
И даже тело пользовательской функции можно получить непосредственно из базы:
Это удобно при анализе чужой базы, восстановлении миграций и проверке расхождений между окружениями.
🔥
➡️ SQL Ready | #совет
Когда нужно перенести индекс, проверить ограничение или восстановить определение объекта, необязательно искать исходную миграцию или вручную собирать SQL из системных каталогов.
PostgreSQL умеет самостоятельно восстанавливать определения объектов через встроенные функции.
Например, получить SQL всех индексов таблицы:
SELECT pg_get_indexdef(indexrelid)
FROM pg_index
WHERE indrelid = 'public.orders'::regclass;
Аналогично можно получить определения ограничений:
SELECT conname,
pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'public.orders'::regclass;
Для представлений есть отдельная функция:
SELECT pg_get_viewdef('public.active_orders'::regclass, true);И даже тело пользовательской функции можно получить непосредственно из базы:
SELECT pg_get_functiondef(
'public.calculate_discount(numeric)'::regprocedure
);
Это удобно при анализе чужой базы, восстановлении миграций и проверке расхождений между окружениями.
pg_get_*def() позволяет получать SQL-определения объектов прямо из PostgreSQL, не разбирая системные каталоги вручную.Please open Telegram to view this post
VIEW IN TELEGRAM
👍12🔥10🤝4❤2
В этой статье:
• Узнаете, как инженеры Авито автоматизировали миграцию SQL-витрин с Vertica на Trino с помощью локальной LLM, не передавая данные в облако;• Разберётесь, как DSPy, разбиение SQL на части и многоэтапная проверка помогают бороться с галлюцинациями и ошибками небольших моделей;• Посмотрите, как удалось сократить время переноса одной витрины с 3–5 часов до 30–60 минут, сохранив контроль над корректностью данных.🔊 Продолжай читать на Habr!
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥10👍5🤝5❤1
Как сохранить исходные ID при переносе данных в PostgreSQL?
Если идентификатор объявлен как GENERATED ALWAYS AS IDENTITY, PostgreSQL не разрешает напрямую указывать его значение.
Например, обычная вставка завершится ошибкой:
Но при миграции иногда необходимо сохранить исходные ID, чтобы не нарушить связи между таблицами.
Для этого существует
PostgreSQL разрешит явно передать идентификатор, не меняя определение колонки и не отключая генерацию значений.
Есть и обратная возможность —
После переноса с сохранением ID не забудьте проверить состояние последовательности: явная вставка не сдвигает её автоматически.
🔥
➡️ SQL Ready | #совет
Если идентификатор объявлен как GENERATED ALWAYS AS IDENTITY, PostgreSQL не разрешает напрямую указывать его значение.
Например, обычная вставка завершится ошибкой:
INSERT INTO users (id, name)
VALUES (1001, 'Alex');
Но при миграции иногда необходимо сохранить исходные ID, чтобы не нарушить связи между таблицами.
Для этого существует
OVERRIDING SYSTEM VALUE:INSERT INTO users (id, name)
OVERRIDING SYSTEM VALUE
VALUES (1001, 'Alex');
PostgreSQL разрешит явно передать идентификатор, не меняя определение колонки и не отключая генерацию значений.
Есть и обратная возможность —
OVERRIDING USER VALUE. Она заставляет PostgreSQL игнорировать переданные значения identity-колонок и генерировать новые:INSERT INTO users (id, name)
OVERRIDING USER VALUE
SELECT id, name
FROM users_backup;
После переноса с сохранением ID не забудьте проверить состояние последовательности: явная вставка не сдвигает её автоматически.
OVERRIDING SYSTEM VALUE позволяет переносить записи с исходными идентификаторами без изменения схемы таблицы и временного отключения генерации ID.Please open Telegram to view this post
VIEW IN TELEGRAM
🔥16👍8❤7
This media is not supported in your browser
VIEW IN TELEGRAM
Курс рассчитан на разработчиков и администраторов баз данных, уже имеющих опыт работы с PostgreSQL. Программа охватывает установку и обновление СУБД, миграцию данных, оптимизацию SQL-запросов, анализ производительности, безопасность, резервное копирование и отказоустойчивость. Отдельное внимание уделяется инструментам и механизмам, специфичным для редакции Enterprise.
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥10❤5🤝4
Условный UPDATE в SQL — как избежать потери изменений при конкурентных запросах?
При конкурентном изменении одной записи возникает риск
Рассмотрим таблицу товаров в MySQL/InnoDB с остатками, ценами и версией записи.
Предположим, два запроса читают
Чтобы исключить такую ситуацию, проверку остатка и изменение выполняют одним SQL-оператором.
В
Если первый запрос зафиксирует изменение, остаток станет равен 3, а версия — 6. Второй запрос после ожидания блокировки проверит актуальный остаток и не выполнит списание.
Количество затронутых строк позволяет определить результат: одна — списание выполнено, ноль — запись отсутствует или остатка недостаточно.
В MySQL результат можно проверить сразу после
Однако условный
Рассмотрим отдельный пример. Сначала восстановим исходное состояние записи:
При изменении цены ранее прочитанная версия включается в условие обновления.
Если другая транзакция уже увеличила
Все изменения, участвующие в контроле версий, должны увеличивать
Для проверки конкурентного поведения запросы необходимо выполнять в двух независимых сессиях MySQL.
🔥 Итог: атомарный
➡️ SQL Ready | #практика
При конкурентном изменении одной записи возникает риск
lost update — когда одно обновление перезаписывает результат другого.Рассмотрим таблицу товаров в MySQL/InnoDB с остатками, ценами и версией записи.
CREATE TABLE products (
id INT PRIMARY KEY,
stock INT NOT NULL,
price INT NOT NULL,
version INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;
INSERT INTO products VALUES (100, 10, 1000, 5);
Предположим, два запроса читают
stock = 10 и пытаются списать по 7 единиц. Если оба вычислят новое значение в приложении и запишут его без проверки исходного состояния, они могут установить stock = 3, фактически потеряв одно списание.Чтобы исключить такую ситуацию, проверку остатка и изменение выполняют одним SQL-оператором.
UPDATE products
SET stock = stock - 7,
version = version + 1
WHERE id = 100
AND stock >= 7;
В
InnoDB при стандартном REPEATABLE READ конкурентные UPDATE одной строки координируются блокировками.Если первый запрос зафиксирует изменение, остаток станет равен 3, а версия — 6. Второй запрос после ожидания блокировки проверит актуальный остаток и не выполнит списание.
Количество затронутых строк позволяет определить результат: одна — списание выполнено, ноль — запись отсутствует или остатка недостаточно.
В MySQL результат можно проверить сразу после
UPDATE:SELECT ROW_COUNT();
Однако условный
UPDATE защищает только инварианты, явно указанные в запросе. Для обнаружения конфликтующих изменений записи применяется optimistic locking с версионированием.Рассмотрим отдельный пример. Сначала восстановим исходное состояние записи:
DELETE FROM products WHERE id = 100;
INSERT INTO products VALUES (100, 10, 1000, 5);
Приложение получает данные и текущую версию.
SELECT price, version
FROM products
WHERE id = 100;
При изменении цены ранее прочитанная версия включается в условие обновления.
UPDATE products
SET price = 1500,
version = version + 1
WHERE id = 100
AND version = 5;
Если другая транзакция уже увеличила
version и зафиксировала изменение, запрос затронет ноль строк. Приложение обнаружит конфликт и сможет повторно прочитать данные, чтобы принять решение о повторном обновлении.Все изменения, участвующие в контроле версий, должны увеличивать
version. В явных транзакциях изменения фиксируются через COMMIT.Для проверки конкурентного поведения запросы необходимо выполнять в двух независимых сессиях MySQL.
UPDATE с проверкой условий защищает числовые инварианты, а optimistic locking позволяет обнаруживать конкурентные изменения записи. При необходимости оба механизма используются совместно.Please open Telegram to view this post
VIEW IN TELEGRAM
❤9🤝6👍5🔥3
Например, Vector Database позволяет искать данные по смыслу, а RAG помогает нейросетям получать актуальную информацию из баз данных, документов и других источников.
На картинке — 5 важных AI-технологий, включая AI-агентов, reasoning-модели и MCP, которые полезно знать при разработке современных приложений.
Сохрани, чтобы не потерять!
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥15❤7🤝3👍1
Псс, хочешь перестать мониторить Telegram в поисках работы? 👀
Ты листаешь каналы, видишь вакансию, сохраняешь и откладываешь отклик «на потом».
Потому что на каждую нужно потратить минут 20. В итоге из десятков сохранённых вакансий до отклика доходят единицы.
Софи забирает эту рутину на себя.
Она сама находит подходящие вакансии в 350+ Telegram-каналах, изучает их, пишет персональное сопроводительное и отправляет отклик.
А еще она умеет откликаться на:
▫️ hh ru
▫️ карьерных сайтах компаний - Авито, Сбер, Т-Банк
▫️ Geekjob
Тебе больше не нужно выбирать, где сегодня искать работу.
Софи будет искать везде.
В следующий раз доступ в Софи откроется 24 ноября.
Чтобы получить 3 дня бесплатного доступа - подписывайся на этот канал, все анонсы будут там.
Ты листаешь каналы, видишь вакансию, сохраняешь и откладываешь отклик «на потом».
Потому что на каждую нужно потратить минут 20. В итоге из десятков сохранённых вакансий до отклика доходят единицы.
Софи забирает эту рутину на себя.
Она сама находит подходящие вакансии в 350+ Telegram-каналах, изучает их, пишет персональное сопроводительное и отправляет отклик.
А еще она умеет откликаться на:
▫️ hh ru
▫️ карьерных сайтах компаний - Авито, Сбер, Т-Банк
▫️ Geekjob
Тебе больше не нужно выбирать, где сегодня искать работу.
Софи будет искать везде.
В следующий раз доступ в Софи откроется 24 ноября.
Чтобы получить 3 дня бесплатного доступа - подписывайся на этот канал, все анонсы будут там.
This media is not supported in your browser
VIEW IN TELEGRAM
В репозитории собраны задания, которые помогают закрепить работу с базами данных: от простых выборок и фильтрации до объединения таблиц, группировки и более сложных запросов. Хороший способ перейти от чтения теории к самостоятельному решению задач, проверить свои знания и подготовиться к техническим собеседованиям.
Оставляю ссылочку: GitHub📱
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥16❤15👍13