В чем разница между агрегатной и скалярной функцией?

В чем разница между агрегатной и скалярной функцией

Это вопрос о SQL функциях, который важен для фронтенд-разработчика, работающего с базами данных и писавшего запросы. Понимание этих типов функций помогает писать эффективные SQL запросы и оптимизировать данные, получаемые с бэкенда.

Скалярная функция (Scalar Function)

Скалярная функция принимает один или несколько входных параметров и возвращает ОДНО скалярное значение (число, строка, дата и т.д.). Она работает с одной строкой данных:

-- Встроенные скалярные функции
SELECT 
  UPPER('hello') AS upper_text,      -- 'HELLO'
  LOWER('HELLO') AS lower_text,      -- 'hello'
  LENGTH('hello') AS length,          -- 5
  ROUND(3.14159, 2) AS rounded,      -- 3.14
  DATE_FORMAT(NOW(), '%Y-%m-%d') AS today;

Скалярные функции обрабатывают значение одной ячейки и возвращают преобразованное значение:

-- Пример со скалярными функциями в SELECT
SELECT 
  id,
  UPPER(name) AS uppercase_name,     -- Скалярная функция
  EXTRACT(YEAR FROM birth_date) AS birth_year,  -- Скалярная функция
  COALESCE(phone, 'No phone') AS contact  -- Скалярная функция
FROM users;

-- Результат: по одной строке для каждой строки таблицы
-- id | uppercase_name | birth_year | contact
-- 1  | JOHN DOE       | 1990       | +7-900-123-45-67
-- 2  | JANE SMITH     | 1992       | No phone

Агрегатная функция (Aggregate Function)

Агрегатная функция берёт МНОЖЕСТВО значений (весь набор строк или группу строк) и возвращает ОДНО совокупное значение. Например: сумма, среднее, количество, минимум, максимум:

-- Встроенные агрегатные функции
SELECT 
  COUNT(*) AS total_users,            -- Количество строк
  SUM(salary) AS total_salary,        -- Сумма
  AVG(salary) AS avg_salary,          -- Среднее
  MIN(salary) AS min_salary,          -- Минимум
  MAX(salary) AS max_salary           -- Максимум
FROM employees;

-- Результат: ОДНА строка с агрегированными данными
-- total_users | total_salary | avg_salary | min_salary | max_salary
-- 150         | 7500000      | 50000      | 20000      | 250000

Ключевые отличия

Практические примеры

Пример 1: Скалярные функции — преобразование данных

-- Преобразование даты и строк для каждого пользователя
SELECT 
  id,
  CONCAT(first_name, ' ', last_name) AS full_name,  -- Скалярная
  UPPER(email) AS email_upper,                        -- Скалярная
  DATE_FORMAT(created_at, '%d.%m.%Y') AS sign_up_date -- Скалярная
FROM users
WHERE created_at > DATE_SUB(NOW(), INTERVAL 30 DAY);

Пример 2: Агрегатные функции — статистика по группам

-- Статистика по департаментам
SELECT 
  department,
  COUNT(*) AS employee_count,         -- Агрегатная
  AVG(salary) AS avg_salary,          -- Агрегатная
  MIN(salary) AS min_salary,          -- Агрегатная
  MAX(salary) AS max_salary           -- Агрегатная
FROM employees
GROUP BY department;

-- department | employee_count | avg_salary | min_salary | max_salary
-- IT         | 45             | 75000      | 40000      | 180000
-- HR         | 12             | 50000      | 35000      | 90000
-- Sales      | 38             | 60000      | 30000      | 150000

Пример 3: Комбинирование обоих типов

-- Скалярные функции применяются к результатам агрегатных
SELECT 
  department,
  COUNT(*) AS count,
  ROUND(AVG(salary), 2) AS avg_salary,         -- ROUND скалярная, AVG агрегатная
  CONCAT(department, ' (', COUNT(*), ')') AS dept_info  -- CONCAT скалярная, COUNT агрегатная
FROM employees
GROUP BY department
ORDER BY AVG(salary) DESC;  -- ORDER BY сначала вычисляет AVG (агрегатная)

Для Frontend разработчика

Понимание различия важно, когда:

1. Написание API запросов

Если вам нужно с бэкенда получить данные, нужно знать, какой тип функции использует API:

// API возвращает скалярные преобразования данных
const users = await fetch('/api/users').then(r => r.json());
// [{ id: 1, name: 'JOHN DOE', signup_date: '15.03.2024' }, ...]

// API возвращает агрегированные статистику
const stats = await fetch('/api/users/stats').then(r => r.json());
// { total_users: 150, avg_age: 32, new_this_month: 15 }

2. JavaScript функции как аналогия

В JavaScript есть похожие концепции через методы массивов:

const users = [
  { id: 1, name: 'John', age: 30 },
  { id: 2, name: 'Jane', age: 25 },
  { id: 3, name: 'Bob', age: 35 }
];

// Скалярные — преобразуют каждый элемент
const upperNames = users.map(u => u.name.toUpperCase()); // ['JOHN', 'JANE', 'BOB']
const ages = users.map(u => u.age); // [30, 25, 35]

// Агрегатные — объединяют все элементы в одно значение
const totalAge = users.reduce((sum, u) => sum + u.age, 0); // 90
const avgAge = totalAge / users.length; // 30
const count = users.length; // 3
const oldestUser = Math.max(...users.map(u => u.age)); // 35

3. Оптимизация запросов

Если нужна агрегированная статистика, лучше вычислить её в БД (агрегатная функция) чем в JavaScript:

// ПЛОХО: получить все данные и обработать в JS
const allOrders = await fetch('/api/orders').then(r => r.json());
const totalRevenue = allOrders.reduce((sum, o) => sum + o.amount, 0);
const avgOrderValue = totalRevenue / allOrders.length;

// ХОРОШО: вычислить в БД через агрегатную функцию
const stats = await fetch('/api/orders/stats').then(r => r.json());
const { totalRevenue, avgOrderValue } = stats;

4. Построение аналитики на фронтенде

// Скалярные трансформации — для подготовки отображения
const displayUsers = users.map(u => ({
  id: u.id,
  displayName: `${u.first_name} ${u.last_name}`.toUpperCase(),
  joinDate: new Date(u.created_at).toLocaleDateString('ru-RU'),
  isActive: u.status === 'active'
}));

// Агрегатные — для статистики
const userStats = {
  total: users.length,
  active: users.filter(u => u.status === 'active').length,
  avgAge: users.reduce((sum, u) => sum + u.age, 0) / users.length
};

Встроенные агрегатные функции SQL

COUNT(*) или COUNT(column)  -- Количество строк
SUM(column)                 -- Сумма
AVG(column)                 -- Среднее
MIN(column)                 -- Минимум
MAX(column)                 -- Максимум
GROUP_CONCAT(column)        -- Объединение строк
STDDEV(column)              -- Стандартное отклонение
VARIANCE(column)            -- Дисперсия

Встроенные скалярные функции SQL

-- Строковые
UPPER(string)               -- Заглавные буквы
LOWER(string)               -- Строчные буквы
LENGTH(string)              -- Длина строки
CONCAT(string1, string2)    -- Объединение
SUBSTRING(string, pos, len) -- Подстрока
REPLACE(string, old, new)   -- Замена
TRIM(string)                -- Удаление пробелов

-- Числовые
ROUND(number, decimals)     -- Округление
ABS(number)                 -- Абсолютное значение
SQRT(number)                -- Квадратный корень
POWER(base, exp)            -- Возведение в степень

-- Датовые
NOW()                       -- Текущая дата-время
DATE_FORMAT(date, format)   -- Форматирование даты
DATE_ADD(date, interval)    -- Добавление времени
EXTRACT(part FROM date)     -- Извлечение части даты

-- Логические
IF(condition, true_val, false_val)  -- Условие
CASE WHEN ... THEN ... END          -- Переключатель
COALESCE(val1, val2, ...)           -- Первое не-NULL значение

Вывод

Скалярная функция — преобразует значения построчно, возвращая значение для каждой входной строки.

Агрегатная функция — объединяет множество строк в одно итоговое значение (сумма, среднее, количество и т.д.).

Для frontend разработчика это важно для:

• Написания SQL запросов в бэкенде
• Понимания, какие данные запрашивать у API
• Оптимизации — лучше вычислять в БД, чем в JavaScript
• Работы с JavaScript методами массивов (map для скалярных, reduce для агрегатных)