В чём разница между агрегирующей и оконной функцией в SQL?

Обе функции выполняют вычисления над группами строк, но принципиально различаются в том, сворачивают ли они результирующий набор строк.

Агрегирующая функция (GROUP BY)

• Применяется к группам строк, определённым предложением GROUP BY.
• Результат: Каждая группа строк сворачивается в одну итоговую строку. Исходное количество строк в выводе сокращается.
• Пример: Подсчёт суммы продаж по каждому отделу.

  SELECT department_id, SUM(sale_amount) AS total_sales
  FROM sales
  GROUP BY department_id;
  -- В результате будет по одной строке на каждый department_id.

Оконная функция (OVER)

• Применяется к набору строк, связанных с текущей строкой ("окну"), определяемому предложением OVER().
• Результат: Вычисление производится для каждой строки отдельно, не сокращая общее количество строк в результате. К исходным данным добавляется новый столбец.
• Пример: Добавление к каждой строке продажи информации о средней цене в её отделе.

  SELECT employee_id, sale_amount, department_id,
     AVG(sale_amount) OVER (PARTITION BY department_id) AS avg_department_sale
  FROM sales;
  -- В результате будет столько же строк, сколько в таблице sales, но с новым столбцом avg_department_sale.

Практическое применение оконных функций: ROW_NUMBER(), RANK(), LAG()/LEAD() для анализа временных рядов, вычисления скользящего среднего.