Что такое агрегатная функция?

Агрегатные функции в SQL

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

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

1. COUNT() — подсчет строк

-- Количество всех пользователей
SELECT COUNT(*) FROM users;

-- Количество пользователей которые заполнили email
SELECT COUNT(email) FROM users;

-- Количество уникальных городов
SELECT COUNT(DISTINCT city) FROM users;

Результат: одно число (целое число)

2. SUM() — сумма значений

-- Общая сумма всех заказов
SELECT SUM(total) FROM orders;

-- Сумма продаж по каждому продукту
SELECT product_id, SUM(quantity) as total_sold
FROM order_items
GROUP BY product_id;

Используется только с числовыми полями.

3. AVG() — среднее значение

-- Средняя цена заказа
SELECT AVG(total) FROM orders;

-- Средний рейтинг по товарам
SELECT product_id, AVG(rating) as avg_rating
FROM reviews
GROUP BY product_id;

4. MIN() и MAX() — минимум и максимум

-- Самый дорогой и самый дешевый товар
SELECT 
  MIN(price) as cheapest,
  MAX(price) as most_expensive
FROM products;

-- Дата первого и последнего заказа по пользователю
SELECT 
  user_id,
  MIN(created_at) as first_order,
  MAX(created_at) as last_order
FROM orders
GROUP BY user_id;

Более сложные примеры

GROUP BY с агрегатами

-- Количество и сумма заказов по каждому пользователю
SELECT 
  user_id,
  COUNT(*) as order_count,
  SUM(total) as total_spent,
  AVG(total) as avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY user_id
ORDER BY total_spent DESC;

Это вернет для каждого user_id одну строку с агрегированными данными.

HAVING — фильтрация после агрегации

-- Пользователи, потратившие больше 1000 рублей
SELECT 
  user_id,
  SUM(total) as total_spent
FROM orders
GROUP BY user_id
HAVING SUM(total) > 1000  -- Фильтруем после GROUP BY
ORDER BY total_spent DESC;

ОТЛИЧИЕ WHERE и HAVING:

• WHERE — фильтрует строки ДО агрегации
• HAVING — фильтрует результаты ПОСЛЕ агрегации

-- WHERE
SELECT user_id, COUNT(*) FROM orders
WHERE status = 'completed'  -- Берем только completed заказы
GROUP BY user_id;

-- HAVING
SELECT user_id, COUNT(*) FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;  -- Берем только тех, у кого > 5 заказов

GROUP_CONCAT / STRING_AGG

Этот агрегат объединяет значения в строку.

MySQL:

SELECT 
  user_id,
  GROUP_CONCAT(product_name SEPARATOR ', ') as products
FROM order_items
GROUP BY user_id;

PostgreSQL:

SELECT 
  user_id,
  STRING_AGG(product_name, ', ') as products
FROM order_items
GROUP BY user_id;

Результат: каждому пользователю показывает названия всех его заказанных товаров в одной строке.

Window функции vs агрегаты

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

-- АГРЕГАТНАЯ функция — одна строка результата
SELECT user_id, SUM(total) FROM orders GROUP BY user_id;

-- Результат:
-- user_id | sum
-- 1       | 500
-- 2       | 300

-- WINDOW функция — одна строка результата для каждой исходной строки
SELECT 
  id,
  user_id,
  total,
  SUM(total) OVER (PARTITION BY user_id) as user_total
FROM orders;

-- Результат:
-- id | user_id | total | user_total
-- 1  | 1       | 300   | 500
-- 2  | 1       | 200   | 500
-- 3  | 2       | 300   | 300

Практический пример: Аналитика заказов

SELECT 
  DATE_TRUNC('month', created_at) as month,
  COUNT(*) as total_orders,
  COUNT(DISTINCT user_id) as unique_users,
  SUM(total) as revenue,
  AVG(total) as avg_order_value,
  MIN(total) as min_order,
  MAX(total) as max_order,
  ROUND(AVG(total), 2) as avg_rounded
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month DESC;

Это вернет:

month      | total_orders | unique_users | revenue | avg_order_value
2024-03    | 150          | 120          | 45000   | 300
2024-02    | 130          | 110          | 39000   | 300
2024-01    | 140          | 115          | 42000   | 300

В Node.js

import { Query } from 'typeorm';

// Получить статистику
const stats = await repository
  .createQueryBuilder('order')
  .select('COUNT(order.id)', 'total_orders')
  .addSelect('SUM(order.total)', 'total_revenue')
  .addSelect('AVG(order.total)', 'avg_order')
  .where('order.status = :status', { status: 'completed' })
  .getRawOne();

console.log(stats);
// { total_orders: '150', total_revenue: '45000', avg_order: '300' }

// С GROUP BY
const byUser = await repository
  .createQueryBuilder('order')
  .select('order.user_id')
  .addSelect('COUNT(order.id)', 'order_count')
  .addSelect('SUM(order.total)', 'total_spent')
  .groupBy('order.user_id')
  .having('SUM(order.total) > :min', { min: 1000 })
  .getRawMany();

Важные моменты

1. NULL значения — COUNT(*) считает NULLs, COUNT(column) не считает
2. Производительность — агрегаты на больших таблицах могут быть медленными
3. Индексы — используй индексы на колонках, по которым агрегируешь
4. GROUP BY — обязателен если вместе с агрегатом есть неагрегированная колонка

Агрегатные функции — это мощный инструмент для анализа данных и составления отчетов.