Оптимизация подсчета записей с GROUP BY в PostgreSQL

Быстрый ответ

Основание синтаксиса операторов GROUP BY и COUNT в PostgreSQL выглядит таким образом:

SELECT столбец, COUNT(*) AS количество
FROM таблица
GROUP BY столбец;

Рассмотрим пример, где производится подсчет количества заказов по их статусам:

SELECT статус, COUNT(*) AS количество
FROM заказы
GROUP BY статус;

Данный запрос извлечет количество каждого уникального статуса из таблицы заказы.

Подсчет уникальных значений: акцент на уникальность

Если вам необходимо подсчитать количество уникальных значений, следует использовать COUNT(DISTINCT столбец):

SELECT статус, COUNT(DISTINCT id_клиента) AS уникальные_клиенты
FROM заказы
GROUP BY статус;

Этот запрос рассчитает количество уникальных клиентов для каждого статуса заказа. Можно сказать, что каждый клиент – это уникальная снежинка.

Настройка производительности: ускорение при помощи индексов

Производительность выполнения запроса можно улучшить, создав индексы для столбцов, которые часто используются для выборки данных или сопоставления:

CREATE INDEX индекс_столбца ON таблица(столбец);

Другими словами, мы включаем турбо-режим для запросов в PostgreSQL.

Использование GROUP BY в разумных пределах

Избегайте чрезмерного использования GROUP BY при подсчете уникальных значений. В этих ситуациях предпочтительней использовать COUNT(DISTINCT):

SELECT COUNT(DISTINCT id_клиента) AS уникальные_клиенты
FROM заказы;

Такой подход сократит использование GROUP BY, сделав ваш запрос более простым и вероятно, более быстрым.

Визуализация

Данные можно представить как городской панорамный вид, где категории – это здания, а их количество определяет их высоту:

GROUP BY определяет контуры каждой группы зданий, разделяя различные районы.

До применения GROUP BY – все выглядит как единый городской пейзаж 🏙️:

🏠🏢🏬🏦🏭🏠🏢🏬🏦🏭

После применения GROUP BY с использованием COUNT – каждый район становится узнаваемым 🌆:

🏢🏢🏣       🏬🏬🏬🏬       🏦🏦
[ Район 1: 2 ][ Район 2: 4 ][ Район 3: 2 ]

Здесь важно скорее количество зданий в районе, а не его площадь.

Светодиодный меч: использование FILTER

С помощью оператора FILTER можно подсчитать строки, соответствующие определенным условиям:

SELECT статус,
       COUNT(*) FILTER (WHERE возврат) AS возвращенные_заказы,
       COUNT(*) FILTER (WHERE NOT возврат) AS завершенные_заказы
FROM заказы
GROUP BY статус;

Мы как бы маневрируем световым мечом, точно выбирая данные для подсчета количества возвращенных и выполненных заказов по каждому статусу.

CTE и оконные функции: универсальный инструмент

CTE (Common Table Expressions) помогают структурировать запросы, применяя оконные функции для подсчета данных внутри группы строк. Это своего рода швейцарский нож в PostgreSQL.

WITH OrderedStatuses AS (
    SELECT статус,
        ROW_NUMBER() OVER(PARTITION BY статус ORDER BY id_заказа) AS номер_строки
    FROM заказы
)
SELECT статус, COUNT(номер_строки) AS количество
FROM OrderedStatuses
GROUP BY статус;

Таким образом, SQL-запрос становится аналитическим инструментом, сортирующим заказы по статусам перед их подсчетом.

Использование EXISTS вместо агрегирующих функций: путь к оптимизации

Применяйте EXISTS, когда необходимо проверить наличие данных, а не подсчитывать их, так как это может обеспечить более высокую производительность:

SELECT статус
FROM заказы o
WHERE EXISTS (
    SELECT 1
    FROM клиенты c
    WHERE c.id = o.id_клиента
    AND c.регион = 'Европа'
)
GROUP BY статус;

В результате запрос будет выбирать только те группы заказов, для которых есть хоть один клиент из Европы. Это можно сравнить с выбором спелых фруктов, не обращая внимания на их количество.

Полезные материалы

1. PostgreSQL: Документация: 16: SELECT — Официальная документация по оператору GROUP BY.
2. PostgreSQL: Документация: 16: 9.21. Агрегатные функции — Руководство по использованию COUNT и других агрегатных функций.
3. Эффективное использование COUNT DISTINCT в PostgreSQL — Рекомендации по оптимизации запросов с использованием COUNT DISTINCT в PostgreSQL.