Что такое агрегатные и скалярные функции?sql-77

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

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

Основные характеристики:

SELECT
    COUNT(*) AS total_orders,
    AVG(amount) AS avg_amount,
    MAX(order_date) AS latest_order
FROM orders;

• Обрабатывают группы строк
• Часто используются с GROUP BY
• Игнорируют NULL значения (кроме COUNT(*))

Популярные агрегатные функции:

1. COUNT() — подсчет строк
2. SUM() — сумма значений
3. AVG() — среднее значение
4. MIN()/MAX() — минимальное/максимальное значение
5. STDDEV() — стандартное отклонение

Особенности работы:

-- С GROUP BY
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

-- С HAVING для фильтрации групп
SELECT product_id, SUM(quantity)
FROM order_items
GROUP BY product_id
HAVING SUM(quantity) > 100;

🔢 Скалярные функции

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

Основные характеристики:

SELECT
    UPPER(name) AS uppercase_name,
    ROUND(price, 2) AS rounded_price,
    CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

• Выполняются для каждой строки отдельно
• Могут использоваться в WHERE, SELECT, ORDER BY
• Могут быть вложенными

Типы скалярных функций:

1. Строковые:

   SELECT SUBSTRING('SQL Expert', 5, 3) -- 'Exp'
   

2. Математические:

   SELECT POWER(2, 3) -- 8
   

3. Даты и времени:

   SELECT DATEDIFF(day, '2023-01-01', '2023-01-31') -- 30
   

4. Преобразования типов:

   SELECT CAST(price AS DECIMAL(10,2)) FROM products
   

🔍 Ключевые различия

Характеристика	Агрегатные функции	Скалярные функции
Обработка	Набор строк	Отдельные значения
Результат	Одно значение на группу	Значение для каждой строки
GROUP BY	Требуется для группировки	Не требуется
NULL-значения	Обычно игнорируются	Зависит от функции
Производительность	Ресурсоемкие для больших таблиц	Оптимизированы

💡 Продвинутые примеры

Комбинирование функций:

SELECT
    product_id,
    UPPER(product_name) AS name,
    ROUND(AVG(price), 2) AS avg_price,
    COUNT(*) OVER() AS total_products
FROM products
WHERE LENGTH(description) > 50
GROUP BY product_id, product_name;

Пользовательские функции:

-- Создание скалярной функции (SQL Server)
CREATE FUNCTION dbo.GetFormattedDate (@date DATETIME)
RETURNS VARCHAR(30)
AS
BEGIN
    RETURN FORMAT(@date, 'dd.MM.yyyy HH:mm');
END;

-- Создание агрегатной функции (PostgreSQL)
CREATE AGGREGATE percentile_cont(float8) (
    SFUNC = ordered_set_transition,
    STYPE = internal,
    FINALFUNC = percentile_cont_final,
    PARALLEL = SAFE
);

⚠️ Распространенные ошибки

1. Использование агрегатной функции без GROUP BY:

-- Ошибка: столбец не в GROUP BY
SELECT department, employee_name, AVG(salary)
FROM employees;

2. Путаница в обработке NULL:

-- COUNT(*) считает все строки, COUNT(column) только не-NULL значения
SELECT COUNT(*), COUNT(bonus) FROM employees;

3. Неоптимальное использование в WHERE:

-- Плохо (скалярная функция может помечать индексу)
SELECT * FROM orders WHERE YEAR(order_date) = 2023;

-- Лучше
SELECT * FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

Резюмируем

Агрегатные функции — это инструмент для: ✔ Анализа наборов данных
✔ Групповых вычислений
✔ Свертки информации в отчеты

Скалярные функции — это инструмент для: ✔ Преобразования отдельных значений
✔ Форматирования вывода
✔ Элементарных вычислений

Правила выбора:

1. Используйте агрегатные функции для статистики по группам
2. Применяйте скалярные функции для обработки отдельных значений
3. Помните о влиянии на производительность
4. Учитывайте особенности обработки NULL-значений

Оптимальное комбинирование этих функций — ключ к эффективным SQL-запросам.