Avg (многомерные выражения)
Вычисляет набор и возвращает среднее непустых значений ячеек набора; среднее считается по мерам в наборе или по указанной мере.
Синтаксис
Аргументы
Set_Expression
Допустимое многомерное выражение, возвращающее набор.
Numeric_Expression
Допустимое числовое выражение (обычно многомерное выражение координат ячейки), возвращающее число.
Remarks
Если задан набор пустых кортежей или пустой набор, функция AVG возвращает пустое значение.
Функция AVG вычисляет среднее для непустых значений ячеек в заданном наборе, сначала вычисляя сумму значений по ячейкам в указанном наборе, а затем вычисляет вычисленную сумму по количеству непустых ячеек в указанном наборе.
Службы Службы Analysis Services отбрасывает неопределенные значения при вычислении среднего значения для набора чисел.
Если определенное числовое выражение (обычно мера) не указано, функция AVG усредняет среднюю величину в текущем контексте запроса. Если указана определенная мера, функция AVG сначала вычисляет меру по набору, а затем функция вычисляет среднее на основе указанной меры.
При использовании функции CurrentMember в операторе вычисляемого элемента необходимо указать числовое выражение, так как для текущей координаты в таком контексте запроса не существует меры по умолчанию.
Примеры
Следующий пример возвращает среднее значение для меры в указанном наборе. Обратите внимание, что заданная мера может быть либо мерой по умолчанию для элементов указанного набора, либо указанной мерой.
WITH SET [NW Region] AS
MEMBER [Geography].[Geography].[NW Region Avg] AS
—Uncomment the line below to get an average by Reseller Gross Profit Margin
—otherwise the average will be by whatever the default measure is in the cube,
—or whatever measure is specified in the query
—, [Measures].[Reseller Gross Profit Margin]
SELECT [Date].[Calendar Year].[Calendar Year].Members ON 0
FROM [Adventure Works]
WHERE ([Geography].[Geography].[NW Region Avg])
WITH MEMBER Measures.[Avg Gross Profit Margin] AS
Measures.[Gross Profit Margin]
MEMBER Measures.[Avg Gross Profit Margin Including Empty Days] AS
CoalesceEmpty(Measures.[Gross Profit Margin],0)
[Ship Date].[Fiscal].[Fiscal Year].Members ON ROWS
Функция Avg
Вычисляет среднее арифметическое набора значений, содержащихся в определенном поле запроса.
Синтаксис
Аргумент выражение представляет собой строковое выражение, определяющее поле с числовыми данными, для которых требуется найти среднее арифметическое, или выражение, с помощью которого производится расчет. Операндом в выражении может быть имя таблицы, константа или функция (встроенная или определяемая пользователем, но не другая агрегатная функция SQL).
Примечания
Значение, полученное с помощью функции Avg, является средним арифметическим (суммой значений, деленной на их количество). Например, вы можете использовать функцию Avg, чтобы подсчитать среднюю стоимость доставки.
Функция Avg не учитывает поля со значением Null.
Avg можно использовать в выражении запроса и в свойстве SQL объекта QueryDef или при создании набор записей на основе SQL запроса.
Примеры
SELECT Avg([UnitPrice]) AS Expr1 FROM ProductSales;
Возвращает среднее значение всех значений поля «Цена» из таблицы «ProductSales» и отображает его в столбце «Вырасть1».
SELECT Avg([SalePrice]) AS AvgSalePrice, Avg([Discount]) AS AvgDiscount FROM ProductSales;
Возвращает среднее значение полей «ЦенаПродажи» и «Скидка» из таблицы ProductSales. Результаты отображаются в столбце «AvgSalePrice» и «AvgDiscount» соответственно, возвращая среднее значение всех продаж «SalePrice», где проданное «Количество» больше 10. Результаты отображаются в столбце «AvgSalePrice».
SELECT Abs(Avg([Discount])) AS AbsAverageDiscount FROM ProductSales;
Возвращает абсолютную величину среднего значения поля Discount и отображает в столбце «AbsAverageDiscount».
Группировка
Агрегатные функции
Агрегатные функции выполняют вычисления над значениями в наборе строк. В T-SQL имеются следующие агрегатные функции:
AVG : находит среднее значение
SUM : находит сумму значений
MIN : находит наименьшее значение
MAX : находит наибольшее значение
COUNT : находит количество строк в запросе
В качестве аргумента все агрегатные функции принимают выражение, которое представляет критерий дя определения значений. Зачастую, в качестве выражения выступает название столбца, над значениями которого надо проводить вычисления.
Все агрегатные функции за исключением COUNT(*) игнорируют значения NULL.
Функция Avg возвращает среднее значение на диапазоне значений столбца таблицы.
Пусть в базе данных у нас есть таблица товаров Products, которая описывается следующими выражениями:
Найдем среднюю цену товаров из базы данных:
Для поиска среднего значения в качестве выражения в функцию передается столбец Price. Для получаемого значения устанавливается псевдоним Average_Price, хотя можно его и не устанавливать.
Также мы можем применить фильтрацию. Например, найти среднюю цену для товаров какого-то определенного производителя:
И, кроме того, мы можем находить среднее значение для более сложных выражений. Например, найдем среднюю сумму всех товаров, учитывая их количество:
Count
Функция Count вычисляет количество строк в выборке. Есть две формы этой функции. Первая форма COUNT(*) подсчитывает число строк в выборке:
Вторая форма функции вычисляет количество строк по определенному столбцу, при этом строки со значениями NULL игнорируются:
Min и Max
Функции Min и Max возвращают соответственно минимальное и максимальное значение по столбцу. Например, найдем минимальную цену среди товаров:
Поиск максимальной цены:
Данные функции также игнорируют значения NULL и не учитывают их при подсчете.
Функция Sum вычисляет сумму значений столбца. Например, подсчитаем общее количество товаров:
Также вместо имени столбца может передаваться вычисляемое выражение. Например, найдем общую стоимость всех имеющихся товаров:
All и Distinct
Так как этот оператор неявно подразумевается при отсутствии DISTINCT, то его можно не указывать.
Агрегатные функции в SQL: объяснение с примерами запросов
Перевод статьи «SQL Aggregate Functions – With Example Data Queries for Beginners».
Если вы когда-либо работали с таблицами Excel или Google Sheets, агрегатные функции SQL покажутся вам знакомыми. Например, вам случалось использовать SUM при работе с таблицами? Функция SUM есть и в SQL. Она как раз относится к агрегатным функциям.
Агрегатные функции выполняют конкретные действия со строками таблиц.
Допустим, вы каждый год запускаете сбор денег на какие-то нужды. У вас есть база жертвователей, где хранятся их имена, адреса электронной почты и перечисленные суммы (по годам).
При помощи функции COUNT вы можете определить, сколько всего пожертвований было сделано. А при помощи SUM можно вычислить общую сумму денег, которую удалось собрать в этом году.
В этой статье мы рассмотрим следующие агрегатные функции: COUNT, SUM, MIN/MAX и AVG. А для иллюстрации будем использовать небольшой набор данных:
| NAME | DONATION_2020 | DONATION_2021 | |
|---|---|---|---|
| Andrew Jones | ajones@someemail.com | 400 | 500 |
| Maria Rodriguez | maria77@someemail.com | 1000 | 350 |
| Gerry Ford | NULL | 25 | 25 |
| Isabella Munn | isamun91@someemail.com | 250 | NULL |
| Jennifer Ward | jjw1972@someemail.com | 2000 | 2300 |
| Rowan Parker | NULL | 5000 | 4000 |
Таблица donors
Функция COUNT
Функция COUNT возвращает количество строк. В самой простой форме COUNT подсчитывает общее количество строк в вашей таблице.
Вернется общее число жертвователей, в нашем случае это 6. Мы, конечно, и так видим, что их 6, но представьте, что таблица у нас очень большая.
Возможно, вам нужно сосчитать только какие-то определенные строки. Например, вывести число жертвователей, у которых указан адрес электронной почты.
Функция SUM
SUM — очень полезная агрегатная функция, с помощью которой можно складывать числовые значения в различных строках.
Поэтому в нашем примере при помощи функции SUM можно сложить все донаты всех жертвователей за определенный год, но не суммы, перечисленные каждым отдельным человеком за два года.
Функции MIN и MAX
Как вы наверняка догадываетесь, MIN и MAX используются для поиска минимального и максимального значений в определенном столбце базы данных.
Вернемся к нашему примеру. Допустим, нам нужно найти, каким было самое маленькое и самое большое пожертвование в 2021 году. Для этого мы можем запустить следующий запрос:
Обратите внимание, что здесь для возвращаемых столбцов мы добавили псевдонимы, при этом взяли их в кавычки. Если в вашем псевдониме нет пробелов, кавычки не обязательны, но у нас пробелы есть.
Любопытный факт: функции MIN и MAX можно использовать и для нечисловых значений.
MIN ищет самое маленькое число, букву, которая в алфавите стоит ближе всего к A, или самую раннюю дату. Функция MAX ищет самое большое число, букву, стоящую ближе всего к Z, или самую последнюю дату. Это очень полезная особенность!
Функция AVG
Чтобы получить средний размер пожертвований в 2020 году, можно запустить следующий запрос:
Итоги
SQL AVG () функция
Функция AVG ()
Функция SQL AVG () вычисляет среднее значение столбца числового типа. Возвращает среднее значение всех ненулевых значений
Синтаксис:
Поддержка СУБД: функция COUNT ()
| СУБД | команда |
| MySQL | поддержанный |
| PostgreSQL | поддержанный |
| SQL Server | поддержанный |
| оракул | поддержанный |
Синтаксис DB2 и Oracle:
Параметры:
| название | Описание |
|---|---|
| ВСЕ | Относится ко всем значениям. |
| DISTINCT | Вернуть сумму уникальных значений. |
| выражение | Выражение состоит из одной константы, переменной, скалярной функции или имени столбца. Выражение является выражением категории точных числовых или приблизительных числовых типов данных, за исключением типа битовых данных. Агрегатные функции и подзапросы не допускаются. |
Пример:
Чтобы получить данные среднего значения ‘advance_amount’ из таблицы ‘orders’, можно использовать следующий оператор SQL:
Пример таблицы: заказы
Иллюстрированная презентация:
SQL AVG () с нулевым
Чтобы получить данные «agent_code», сумму «creation_amt», количество клиентов для каждого агента и «receive_amt» из таблицы «customer», можно использовать следующую инструкцию SQL:
Образец таблицы: клиент
SQL AVG () с где
1. «ord_amount» должно быть больше 1000,
2. и значение ord_amount должно быть до 4500,
можно использовать следующий оператор SQL:
Пример таблицы: заказы
Иллюстрированная презентация:
SQL AVG () с SUM ()
Чтобы получить сумму ‘advance_amount’ и среднего значения ‘advance_amount’ из таблицы ‘orders’, можно использовать следующий оператор SQL:
Пример таблицы: заказы
Примечание. Выводы указанного оператора SQL, показанного здесь, взяты с использованием Oracle Database 10g Express Edition.
Вот слайд-презентация всех агрегатных функций.
Упражнения по SQL
Хотите улучшить вышеуказанную статью? Вносите свои заметки / комментарии / примеры через Disqus.
