Какие агрегирующие функции можно использовать с GROUP BY?

Агрегирующие функции в SQL при использовании GROUP BY

При использовании оператора GROUP BY в SQL, агрегирующие (агрегатные) функции выполняют вычисления над группами строк, объединённых по указанным критериям, возвращая одно значение для каждой группы. Эти функции являются фундаментальным инструментом для анализа данных, суммирования и статистики.

Основные агрегирующие функции

• COUNT(): Возвращает количество строк в группе. Часто используется с DISTINCT для подсчёта уникальных значений.

  SELECT department, COUNT(*) as employee_count
  FROM employees
  GROUP BY department;
  

• SUM(): Вычисляет общую сумму числового столбца для каждой группы.

  SELECT product_category, SUM(sales_amount) as total_sales
  FROM sales
  GROUP BY product_category;
  

• AVG(): Возвращает среднее значение числового столбца. Для точности часто используют CAST для контроля типа данных.

  SELECT team, AVG(CAST(score AS DECIMAL(10,2))) as average_score
  FROM game_results
  GROUP BY team;
  

• MIN() и MAX(): Определяют минимальное и максимальное значение в группе для указанного столбца (работают с числами, датами, строками).

  SELECT year, MIN(price) as lowest_price, MAX(price) as highest_price
  FROM commodity_prices
  GROUP BY year;
  

Специализированные и статистические агрегатные функции

• GROUP_CONCAT() / STRING_AGG() (нестандартные): Конкатенируют строковые значения из группы в одну строку. GROUP_CONCAT существует в MySQL, STRING_AGG — в PostgreSQL и SQL Server.

  -- MySQL
  SELECT project_id, GROUP_CONCAT(developer_name SEPARATOR ', ') as team
  FROM project_assignments
  GROUP BY project_id;
  

• STDDEV() / STDDEV_POP() / STDDEV_SAMP() и VARIANCE() / VAR_POP() / VAR_SAMP(): Вычисляют стандартное отклонение и дисперсию для числовых данных, показывая разброс значений в группе (POP — по всей популяции, SAMP — по выборке).

  SELECT process_id, STDDEV_POP(measurement) as deviation
  FROM quality_metrics
  GROUP BY process_id;
  

Ключевые особенности использования с GROUP BY

1. Все столбцы в SELECT, не являющиеся аргументами агрегатных функций, должны быть включены в GROUP BY. Это золотое правило.

   -- Правильно: region и city в GROUP BY
   SELECT region, city, SUM(revenue)
   FROM offices
   GROUP BY region, city;
   
   -- Ошибка: city не в GROUP BY
   SELECT region, city, SUM(revenue)
   FROM offices
   GROUP BY region;
   

2. Фильтрация результатов агрегации выполняется с HAVING, а не WHERE. WHERE фильтрует строки до группировки, HAVING — группы после агрегации.

   SELECT department, AVG(salary) as avg_salary
   FROM employees
   GROUP BY department
   HAVING AVG(salary) > 50000; -- Фильтр по результату агрегации
   

3. Возможность комбинирования нескольких функций в одном запросе для комплексного анализа.

   SELECT customer_id,
          COUNT(order_id) as orders_count,
          SUM(total_amount) as total_spent,
          AVG(total_amount) as average_order_value
   FROM orders
   GROUP BY customer_id;
   

4. Взаимодействие с DISTINCT внутри агрегатных функций позволяет учитывать только уникальные значения при вычислении.

   SELECT date, COUNT(DISTINCT user_id) as unique_users
   FROM site_visits
   GROUP BY date;
   

Практическое применение в контексте QA Automation

Для тестирования данных и валидации бизнес-логики в ETL-процессах или отчетах QA Automation Engineer часто использует агрегатные функции в автоматизированных проверках:

• Сравнение ожидаемых и фактических сумм (SUM) транзакций после миграции данных.
• Проверка корректности группировки и подсчёта (COUNT) записей по статусам.
• Валидация статистических показателей (AVG, STDDEV) в аналитических модулях.
• Использование GROUP_CONCAT для проверки корректности сборки текстовых списков.

Таким образом, агрегирующие функции с GROUP BY — это мощный инструмент для преобразования детализированных данных в сводную, аналитическую информацию, широко применяемый как в разработке, так и в тестировании программного обеспечения.