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

Применение агрегатных функций для фильтрации результатов в SQL

В SQL агрегатные функции (SUM, COUNT, AVG, MAX, MIN и др.) используются для вычисления сводных значений по группам строк. Для фильтрации результатов, полученных с помощью агрегатных функций, применяется специальное ключевое слово HAVING. Оно работает в сочетании с GROUP BY и позволяет отфильтровать группы данных после их агрегации, в отличие от WHERE, который фильтрует строки до группировки.

Основные секции применения агрегатных функций для фильтрации

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

   SELECT department_id, COUNT(*) as employee_count, AVG(salary) as avg_salary
   FROM employees
   GROUP BY department_id
   HAVING COUNT(*) > 5 AND AVG(salary) > 50000;
   

   Этот запрос вернёт только те отделы, где количество сотрудников больше 5 и средняя зарплата превышает 50 000.

2. Подзапросы в секции WHERE или FROM Хотя в WHERE нельзя напрямую использовать агрегатные функции для фильтрации сгруппированных данных, их можно применять в подзапросах, результат которых затем фильтруется.

   • В WHERE через подзапрос:

     SELECT * FROM employees e
     WHERE e.salary > (
         SELECT AVG(salary) FROM employees
         WHERE department_id = e.department_id
     );
     

     Здесь агрегатная функция `AVG` используется в коррелированном подзапросе для сравнения зарплаты каждого сотрудника со средней по его отделу.

• В FROM с использованием производной таблицы:

  SELECT dept_stats.department_id, dept_stats.avg_salary
  FROM (
      SELECT department_id, AVG(salary) as avg_salary
      FROM employees
      GROUP BY department_id
  ) AS dept_stats
  WHERE dept_stats.avg_salary > 60000;
  

     Агрегация выполняется во внутреннем запросе, а внешний `WHERE` фильтрует уже готовые агрегированные результаты.

1. Оконные функции (Window Functions) в сочетании с WHERE Оконные функции позволяют выполнять агрегатные вычисления без свёртки строк (без GROUP BY). Результаты таких функций доступны для каждой строки, поэтому их можно фильтровать в WHERE, но через подзапрос или CTE.

   WITH ranked_salaries AS (
       SELECT 
           employee_id, 
           salary,
           AVG(salary) OVER (PARTITION BY department_id) as dept_avg_salary
       FROM employees
   )
   SELECT employee_id, salary, dept_avg_salary
   FROM ranked_salaries
   WHERE salary > dept_avg_salary;
   

   Здесь AVG как оконная функция вычисляет среднюю зарплату по отделу для каждой строки, а затем WHERE фильтрует строки, где зарплата сотрудника выше этого среднего.

Сравнение HAVING и WHERE

Практический пример комплексного использования

SELECT 
    department_id,
    COUNT(*) as total_employees,
    SUM(CASE WHEN position = 'Manager' THEN 1 ELSE 0 END) as manager_count,
    AVG(salary) as average_salary
FROM employees
WHERE hire_date > '2020-01-01'  -- Фильтрация строк ДО группировки
GROUP BY department_id
HAVING AVG(salary) > 75000 AND COUNT(*) >= 3  -- Фильтрация групп ПОСЛЕ агрегации
ORDER BY average_salary DESC;

Ключевые выводы:

1. HAVING — единственная секция, где агрегатные функции можно использовать напрямую для фильтрации результатов группировки.
2. Для фильтрации на основе агрегаций без группировки или с более сложной логикой применяют подзапросы или CTE.
3. Оконные функции расширяют возможности, позволяя "присоединять" агрегированные значения к каждой строке для последующей фильтрации.
4. Всегда помните о порядке выполнения SQL: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Агрегация происходит после WHERE, поэтому для фильтрации её результатов нужен HAVING.

Для эффективной работы с агрегатными функциями и фильтрацией их результатов важно понимать эту последовательность и выбирать соответствующий инструмент (HAVING, подзапрос или оконную функцию) в зависимости от конкретной задачи.