AVG: примеры (SQL)

Простые примеры использования

Вычисление средней стоимости товаров в таблице Products:

SELECT AVG(UnitPrice) AS AvgPrice FROM Products;

AvgPrice
28.8663

Использование ключевого слова DISTINCT для вычисления средней цены только для уникальных значений:

SELECT AVG(DISTINCT UnitPrice) AS AvgDistinctPrice FROM Products;

AvgDistinctPrice
29.0421

Применение функции с группировкой данных. Пример вычисления средней зарплаты по отделам:

SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID;

DepartmentID   AvgSalary
1              55000.00
2              48000.00
3              62000.00

Функция корректно работает с целыми числами, возвращая целочисленный результат при делении нацело:

SELECT AVG(CAST(Quantity AS int)) AS AvgIntQuantity FROM OrderDetails;

AvgIntQuantity
23

Похожие функции в MS SQL

Для агрегации данных в MS SQL Server существуют альтернативные подходы, которые могут быть использованы в зависимости от задачи.

SUM() и COUNT(): Комбинация этих функций позволяет вручную вычислить среднее значение, что предоставляет больше контроля, особенно при необходимости обработки нулевых значений или особых условий. Например, можно явно исключить строки с нулевыми значениями или задать собственную логику округления.

PERCENTILE_CONT и PERCENTILE_DISC: Эти аналитические функции вычисляют процентиль и, в частности, медиану (50-й процентиль), которая является более устойчивым к выбросам показателем центра распределения, чем среднее арифметическое. Использование медианы предпочтительнее при работе с данными, содержащими аномальные значения.

Аналитическая форма AVG() с OVER(): Позволяет вычислять скользящее среднее или среднее в рамках окна без свертывания строк в одну, сохраняя детализацию исходного набора данных. Этот подход удобен для анализа временных рядов и сравнения индивидуальных значений со средним по группе.

Типичные ошибки

Деление на ноль: При ручном вычислении среднего через SUM и COUNT может возникнуть ошибка, если COUNT возвращает 0. AVG обрабатывает это корректно, возвращая NULL.

-- Ошибочный подход
SELECT SUM(Value) / COUNT(Value) AS ManualAvg FROM EmptyTable; -- Ошибка или NULL?
-- Безопасный подход с AVG
SELECT AVG(Value) AS SafeAvg FROM EmptyTable; -- Возвращает NULL

ManualAvg -> Возможна ошибка или NULL в зависимости от контекста.
SafeAvg -> NULL

Неправильное понимание DISTINCT: Использование AVG(DISTINCT column) вычисляет среднее только по уникальным значениям, что не всегда равно общему среднему. Это может привести к неверным результатам, если задача требует учета всех записей.

-- Данные: цены [10, 10, 20]
SELECT AVG(price) AS normal_avg, AVG(DISTINCT price) AS distinct_avg
FROM (VALUES (10), (10), (20)) AS t(price);

normal_avg   distinct_avg
13           15

Игнорирование типа данных: При работе с целыми числами результат AVG может быть усечен до целого, если не выполнить явное преобразование типа. Ожидание дробного результата может привести к путанице.

SELECT AVG(CAST(5 AS int) + CAST(2 AS int)) AS Result; -- Данные 5 и 2

Result
3

История изменений

В последних версиях MS SQL Server базовая функция AVG не претерпела значительных синтаксических изменений. Основные нововведения связаны с расширением контекстов ее использования.

Начиная с SQL Server 2005, была существенно расширена поддержка аналитических функций. Функция AVG получила возможность использоваться с предложением OVER, что позволило вычислять скользящие средние, средние по разделам без группировки и другие оконные агрегаты.

В SQL Server 2012 улучшена работа с предложением OVER, добавлены возможности для определения пользовательских рамок окна с помощью ROWS и RANGE. Это позволило более гибко задавать набор строк, участвующих в вычислении среднего для каждой текущей строки.

Отдельно стоит отметить постоянную работу Microsoft над оптимизацией производительности агрегатных функций, включая AVG, при обработке больших объемов данных и использовании колоночных индексов (In-Memory OLTP, Columnstore).

Расширенные примеры

Использование AVG в качестве аналитической функции для расчета скользящего среднего за последние 3 строки:

SELECT 
    OrderDate,
    DailySales,
    AVG(DailySales) OVER (ORDER BY OrderDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg3Days
FROM Sales.OrderStats
ORDER BY OrderDate;

OrderDate   DailySales   MovingAvg3Days
2023-10-01  100          100.00
2023-10-02  150          125.00
2023-10-03  200          150.00
2023-10-04  180          176.67

Вычисление среднего значения вместе с выводом детализированных строк и отклонением каждой строки от среднего по группе:

SELECT 
    DepartmentID,
    EmployeeID,
    Salary,
    AVG(Salary) OVER (PARTITION BY DepartmentID) AS DeptAvgSalary,
    Salary - AVG(Salary) OVER (PARTITION BY DepartmentID) AS DiffFromAvg
FROM Employees;

DepartmentID EmployeeID Salary DeptAvgSalary DiffFromAvg
1            101        60000  55000         5000
1            102        50000  55000         -5000
2            201        45000  48000         -3000
2            202        51000  48000         3000

Комбинирование AVG с другими агрегатными функциями в одном запросе для комплексного анализа:

SELECT 
    ProductCategoryID,
    COUNT(*) AS ProductCount,
    MIN(ListPrice) AS MinPrice,
    AVG(ListPrice) AS AvgPrice,
    MAX(ListPrice) AS MaxPrice,
    MAX(ListPrice) - AVG(ListPrice) AS MaxAboveAvg
FROM Production.Product
WHERE ListPrice > 0
GROUP BY ProductCategoryID
HAVING AVG(ListPrice) > 100;

Условное вычисление среднего с помощью выражения CASE внутри AVG. Пример расчета средней скидки только для товаров, на которые она распространяется:

SELECT 
    AVG(CASE WHEN DiscountPct > 0 THEN DiscountPct END) AS AvgDiscountForDiscountedItems
FROM Sales.SpecialOffer;

AvgDiscountForDiscountedItems
0.15

Использование в Common Table Expression (CTE) для многоступенчатых расчетов. Вычисление среднего по подгруппам, а затем общего среднего этих средних:

WITH DeptAvg AS (
    SELECT DepartmentID, AVG(Salary) AS AvgSal
    FROM Employees
    GROUP BY DepartmentID
)
SELECT AVG(AvgSal) AS CompanyWideAvgOfDeptAverages FROM DeptAvg;

Функции в других СУБД и языках

MySQL: Функция AVG работает аналогично, игнорируя NULL. Поддерживается использование с DISTINCT. Отличия минимальны и касаются в основном типизации результатов.

-- MySQL
SELECT AVG(salary) FROM employees;

65000.0000

PostgreSQL: Поведение функции AVG похоже, но возвращаемый тип всегда является числом с плавающей точкой (numeric или double precision) для целочисленных входных данных, в отличие от SQL Server, который может сохранить целый тип.

-- PostgreSQL
SELECT AVG(price) FROM products;

24.9875000000000000

Oracle: Синтаксис AVG([DISTINCT|ALL] expr) идентичен. Важное отличие — возможность использования функции как аналитической с предложением OVER без поддержки ORDER BY в окне для вычисления классического среднего (требуется Oracle 21c и выше для некоторых расширений).

-- Oracle
SELECT department_id, AVG(salary) OVER (PARTITION BY department_id) avg_dept_sal
FROM employees;

SQLite: Поддерживает функцию AVG, всегда возвращающую значение с плавающей точкой. Все значения NULL игнорируются.

-- SQLite
SELECT AVG(score) FROM test_results;

85.75

Языки программирования: В императивных языках вычисление среднего обычно требует ручного суммирования элементов и деления на их количество. Например, в Python: sum(list)/len(list) или использование специализированных библиотек, таких как NumPy (numpy.mean(array)), которые предлагают дополнительные параметры, такие как обработка осей в многомерных массивах и настройка поведения для NaN значений.