В этом руководстве вы узнаете, как использовать функцию SQL Server AVG() для вычисления среднего значения из группы значений.
Введение в функцию SQL Server AVG()
Функция SQL Server AVG() — это агрегатная функция, которая возвращает среднее значение группы.
Ниже показан синтаксис функции AVG():
AVG([ALL | DISTINCT] expression)
В этом синтаксисе:
- ALL указывает функции AVG() принимать все значения для расчета. ALL используется по умолчанию.
- DISTINCT указывает функции AVG() работать только с уникальными значениями.
- выражение — это допустимое выражение, возвращающее числовое значение.
Функция AVG() игнорирует значения NULL.
Функция SQL Server AVG(): ALL против DISTINCT
Следующие операторы создают новую таблицу, вставляют в нее некоторые значения и запрашивают данные по ней:
CREATE TABLE t(
val dec(10,2)
);
INSERT INTO t(val)
VALUES(1),(2),(3),(4),(4),(5),(5),(6);
SELECT val FROM t;
Выход:
val ---- 1.00 2.00 3.00 4.00 4.00 5.00 5.00 6.00 (8 rows)
Следующий оператор использует функцию AVG() для вычисления среднего значения всех значений в таблице t:
SELECT AVG(ALL val) avg FROM t;
Выход:
avg -------- 3.750000 (1 row)
В этом примере мы используем модификатор ALL, поэтому функция average учитывает все восемь значений в столбце val при расчете:
(1 + 2 + 3 + 4 + 4 + 5 + 5 + 6) / 8 = 3.75
В следующем операторе используется функция AVG() с модификатором DISTINCT:
SELECT AVG(DISTINCT val) avg FROM t;
Вот результат:
avg -------- 3.500000 (1 row)
Благодаря модификатору DISTINCT функция AVG() выполняет вычисления для отдельных значений:
(1 + 2 + 3 + 4 + 5 + 6) / 6 = 3.5
Примеры функций SQL Server AVG()
Давайте рассмотрим несколько примеров, чтобы увидеть, как работает функция AVG().
1) Пример базовой функции SQL Server AVG()
В следующем примере возвращается средняя цена по прейскуранту всех продуктов:
SELECT
AVG(list_price) avg
FROM
production.products;
В этом примере функция AVG() возвращает одно значение для всей таблицы.
Выход:
avg ----------- 1520.591401
Чтобы сделать среднюю цену более удобной для чтения, вы можете округлить ее с помощью функции ROUND() и преобразовать результат в число с двумя десятичными знаками:
SELECT
CAST(ROUND(AVG(list_price),2) AS DEC(10,2)) avg
FROM
production.products;
Выход:
avg ------- 1520.59
2) Пример использования SQL Server AVG() с GROUP BY
Если вы используете функцию AVG() с предложением GROUP BY, функция AVG() возвращает одно значение для каждой группы вместо одного значения для всей таблицы.
В следующем примере функция AVG() используется с предложением GROUP BY для получения средней цены по прейскуранту для каждой категории продуктов:
SELECT
category_name,
CAST(ROUND(AVG(list_price),2) AS DEC(10,2)) avg_product_price
FROM
production.products p
INNER JOIN production.categories c
ON c.category_id = p.category_id
GROUP BY
category_name
ORDER BY
category_name;
Выход:
category_name | avg_product_price --------------------+------------------ Children Bicycles | 287.79 Comfort Bicycles | 682.12 Cruisers Bicycles | 730.41 Cyclocross Bicycles | 2542.79 Electric Bikes | 3281.66 Mountain Bikes | 1649.76 Road Bikes | 3175.36 (7 rows)
3) Пример использования SQL Server AVG() в предложении HAVING
В следующем примере функция AVG() используется в предложении HAVING для извлечения только тех брендов, средняя цена которых превышает 500:
SELECT
brand_name,
CAST(ROUND(AVG(list_price),2) AS DEC(10,2)) avg_product_price
FROM
production.products p
INNER JOIN production.brands c ON c.brand_id = p.brand_id
GROUP BY
brand_name
HAVING
AVG(list_price) > 500
ORDER BY
avg_product_price;
Выход:
brand_name | avg_product_price -------------+------------------- Sun Bicycles | 524.47 Haro | 621.99 Ritchey | 749.99 Electra | 761.01 Surly | 1331.75 Heller | 2173.00 Trek | 2500.06 (7 rows)
В этом примере:
- Во-первых, предложение GROUP BY делит продукты по брендам на группы.
- Во-вторых, функция AVG() вычисляет среднюю цену по прейскуранту для каждой группы.
- В-третьих, пункт HAVING исключает бренд, средняя цена которого составляет менее 500.
Краткое содержание
- Используйте функцию AVG() для вычисления среднего значения из группы значений.
- Используйте функцию AVG() с предложением GROUP BY для вычисления среднего значения для каждой группы.
