Агрегатные функции в sql: Агрегатные функции (Transact-SQL)

Содержание

Агрегатные функции (Transact-SQL) — SQL Server

08/15/2018

Чтение занимает 2 мин

В этой статье

Применимо к:Applies to: SQL ServerSQL Server (все поддерживаемые версии) SQL ServerSQL Server (all supported versions) База данных SQL AzureAzure SQL DatabaseБаза данных SQL AzureAzure SQL Database Управляемый экземпляр SQL AzureAzure SQL Managed InstanceУправляемый экземпляр SQL AzureAzure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics Параллельное хранилище данныхParallel Data WarehouseПараллельное хранилище данныхParallel Data WarehouseПрименимо к:Applies to: SQL ServerSQL Server (все поддерживаемые версии) SQL ServerSQL Server (all supported versions) База данных SQL AzureAzure SQL DatabaseБаза данных SQL AzureAzure SQL Database Управляемый экземпляр SQL AzureAzure SQL Managed InstanceУправляемый экземпляр SQL AzureAzure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics Параллельное хранилище данныхParallel Data WarehouseПараллельное хранилище данныхParallel Data Warehouse

Агрегатная функция выполняет вычисление на наборе значений и возвращает одиночное значение. An aggregate function performs a calculation on a set of values, and returns a single value. Агрегатные функции, за исключением COUNT(*), не учитывают значения NULL.Except for COUNT(*), aggregate functions ignore null values. Агрегатные функции часто используются в выражении GROUP BY инструкции SELECT.Aggregate functions are often used with the GROUP BY clause of the SELECT statement.

Все агрегатные функции являются детерминированными.All aggregate functions are deterministic. Другими словами, агрегатные функции возвращают одну и ту же величину при каждом их вызове на одном и том же наборе входных значений.In other words, aggregate functions return the same value each time that they are called, when called with a specific set of input values. Дополнительные сведения о детерминированности функций см. в статье Детерминированные и недетерминированные функции.See Deterministic and Nondeterministic Functions for more information about function determinism. Предложение OVER может следовать за всеми агрегатными функциями, кроме STRING_AGG, GROUPING или GROUPING_ID. The OVER clause may follow all aggregate functions, except the STRING_AGG, GROUPING or GROUPING_ID functions.

Агрегатные функции можно использовать в качестве выражений только в следующих случаях.Use aggregate functions as expressions only in the following situations:

Список выбора инструкции SELECT (вложенный или внешний запрос).The select list of a SELECT statement (either a subquery or an outer query).
Предложение HAVING.A HAVING clause.

Transact-SQLTransact-SQL предоставляет следующие агрегатные функции.provides the following aggregate functions:

См. также разделSee also

Встроенные функции (Transact-SQL)Built-in Functions (Transact-SQL)
Предложение OVER (Transact-SQL)OVER Clause (Transact-SQL)

MAX (Transact-SQL) — SQL Server

000Z» data-article-date-source=»ms.date»>08/23/2017

Чтение занимает 2 мин

В этой статье

Возвращает максимальное значение выражения. Returns the maximum value in the expression.

Синтаксические обозначения в Transact-SQLTransact-SQL Syntax Conventions

СинтаксисSyntax

-- Aggregation Function Syntax  
MAX( [ ALL | DISTINCT ] expression )  
  
-- Analytic Function Syntax  
MAX ([ ALL ] expression) OVER ( [ <partition_by_clause> ] [ <order_by_clause> ] )

АргументыArguments

ALLALL
Применяет агрегатную функцию ко всем значениям.Applies the aggregate function to all values. ALL является параметром по умолчанию.ALL is the default.

DISTINCTDISTINCT
Указывает, что учитывается каждое уникальное значение.Specifies that each unique value is considered. Параметр DISTINCT не имеет смысла при использовании функцией MAX и доступен только для совместимости со стандартом ISO.DISTINCT is not meaningful with MAX and is available for ISO compatibility only.

expressionexpression
Может быть константой, именем столбца или функцией, а также любым сочетанием арифметических, побитовых и строковых операторов. Is a constant, column name, or function, and any combination of arithmetic, bitwise, and string operators. MAX можно использовать со столбцами numeric, character, uniqueidentifier и datetime, но не со столбцами bit.MAX can be used with numeric, character, uniqueidentifier, and datetime columns, but not with bit columns. Агрегатные функции и вложенные запросы не допускаются.Aggregate functions and subqueries are not permitted.

Дополнительные сведения см. в разделе Выражения (Transact-SQL).For more information, see Expressions (Transact-SQL).

OVER ( [ partition_by_clause ] order_by_clause )OVER ( [ partition_by_clause ] order_by_clause)
partition_by_clause делит результирующий набор, полученный с помощью предложения FROM, на секции, к которым применяется функция. partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. Если этот параметр не указан, функция обрабатывает все строки результирующего набора запроса как отдельные группы.If not specified, the function treats all rows of the query result set as a single group. order_by_clause определяет логический порядок, в котором выполняется операция.order_by_clause determines the logical order in which the operation is performed. Аргумент order_by_clause является обязательным.order_by_clause is required. Дополнительные сведения см. в статье Предложение OVER (Transact-SQL).For more information, see OVER Clause (Transact-SQL).

Типы возвращаемых данныхReturn Types

Возвращает то же значение, что и expression.Returns a value same as expression.

КомментарииRemarks

При выполнении функции MAX все значения NULL пропускаются.MAX ignores any null values.

MAX возвращает NULL, если нет строк для выбора.MAX returns NULL when there is no row to select.

При использовании со столбцами, содержащими символьные значения, функция MAX находит наибольшее значение в упорядоченной последовательности.For character columns, MAX finds the highest value in the collating sequence.

MAX — это детерминированная функция, если она используется без предложений OVER и ORDER BY.MAX is a deterministic function when used without the OVER and ORDER BY clauses. Она не детерминирована при использовании с предложениями OVER и ORDER BY.It is nondeterministic when specified with the OVER and ORDER BY clauses. Дополнительные сведения см. в разделе Deterministic and Nondeterministic Functions.For more information, see Deterministic and Nondeterministic Functions.

ПримерыExamples

A.A. Простой примерSimple example

В следующем примере возвращается наибольший (максимальный) размер налога в базе данных AdventureWorks2012AdventureWorks2012. The following example returns the highest (maximum) tax rate in the AdventureWorks2012AdventureWorks2012 database.

SELECT MAX(TaxRate)  
FROM Sales.SalesTaxRate;  
GO

Результирующий набор:Here is the result set.

-------------------  
19.60  
Warning, null value eliminated from aggregate.  
 
(1 row(s) affected)

Б.B. Использование предложения OVERUsing the OVER clause

В приведенном ниже примере рассматривается применение функций MIN, MAX, AVG и COUNT с предложением OVER для получения статистических значений для каждого из отделов в таблице HumanResources.Department в базе данных AdventureWorks2012AdventureWorks2012.The following example uses the MIN, MAX, AVG, and COUNT functions with the OVER clause to provide aggregated values for each department in the HumanResources.Department table in the AdventureWorks2012AdventureWorks2012 database.

SELECT DISTINCT Name  
       , MIN(Rate) OVER (PARTITION BY edh. DepartmentID) AS MinSalary  
       , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary  
       , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary  
       ,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept  
FROM HumanResources.EmployeePayHistory AS eph  
JOIN HumanResources.EmployeeDepartmentHistory AS edh  
     ON eph.BusinessEntityID = edh.BusinessEntityID  
JOIN HumanResources.Department AS d  
 ON d.DepartmentID = edh.DepartmentID  
WHERE edh.EndDate IS NULL  
ORDER BY Name;

Результирующий набор:Here is the result set.

Name                          MinSalary             MaxSalary             AvgSalary             EmployeesPerDept  
----------------------------- --------------------- --------------------- --------------------- ----------------  
Document Control              10.25                 17.7885               14.3884               5  
Engineering                   32.6923               63. 4615               40.1442               6  
Executive                     39.06                 125.50                68.3034               4  
Facilities and Maintenance    9.25                  24.0385               13.0316               7  
Finance                       13.4615               43.2692               23.935                10  
Human Resources               13.9423               27.1394               18.0248               6  
Information Services          27.4038               50.4808               34.1586               10  
Marketing                     13.4615               37.50                 18.4318               11  
Production                    6.50                  84.1346               13.5537               195  
Production Control            8.62                  24.5192               16.7746               8  
Purchasing                    9.86                  30.00                 18.0202               14  
Quality Assurance             10.5769               28. 8462               15.4647               6  
Research and Development      40.8654               50.4808               43.6731               4  
Sales                         23.0769               72.1154               29.9719               18  
Shipping and Receiving        9.00                  19.2308               10.8718               6  
Tool Design                   8.62                  29.8462               23.5054               6  
  
 (16 row(s) affected)

В.C. Использование MAX с символьными даннымиUsing MAX with character data

В приведенном ниже примере возвращается имя базы данных, которое является последним при сортировке по алфавиту.The following example returns the database name that sorts as the last name alphabetically. В нем используется WHERE database_id < 5 для обработки только системных баз данных.The example uses WHERE database_id < 5, to consider only the system databases.

SELECT MAX(name) FROM sys. databases WHERE database_id < 5;

Последняя системная база данных — tempdb.The last system database is tempdb.

См. также:See Also

Агрегатные функции (Transact-SQL) Aggregate Functions (Transact-SQL)
Предложение OVER (Transact-SQL)OVER Clause (Transact-SQL)

VAR (Transact-SQL) — SQL Server

03/13/2017

Чтение занимает 2 мин

В этой статье

Возвращает статистическую дисперсию всех значений в указанном выражении. Returns the statistical variance of all values in the specified expression. За функцией может следовать предложение OVER.May be followed by the OVER clause.

Синтаксические обозначения в Transact-SQLTransact-SQL Syntax Conventions

СинтаксисSyntax

-- Aggregate Function Syntax   
VAR ( [ ALL | DISTINCT ] expression )  
  
-- Analytic Function Syntax  
VAR ([ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause)

АргументыArguments

ALLALL
Применяет функцию ко всем значениям.Applies the function to all values. ALL является параметром по умолчанию.ALL is the default.

DISTINCTDISTINCT
Указывает, что учитывается каждое уникальное значение.Specifies that each unique value is considered.

expressionexpression
Выражение категории точного числового или приблизительного числового типа данных, за исключением типа данных bit.Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Агрегатные функции и вложенные запросы не допускаются.Aggregate functions and subqueries are not permitted.

OVER ( [ partition_by_clause ] order_by_clause )OVER ( [ partition_by_clause ] order_by_clause)
partition_by_clause делит результирующий набор, полученный с помощью предложения FROM, на секции, к которым применяется функция.partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. Если этот параметр не указан, функция обрабатывает все строки результирующего набора запроса как отдельные группы.If not specified, the function treats all rows of the query result set as a single group. order_by_clause определяет логический порядок, в котором выполняется операция.order_by_clause determines the logical order in which the operation is performed. order_by_clause — это обязательный элемент. order_by_clause is required. Дополнительные сведения см. в статье Предложение OVER (Transact-SQL).For more information, see OVER Clause (Transact-SQL).

Типы возвращаемых данныхReturn Types

floatfloat

КомментарииRemarks

Если в инструкции SELECT функция VAR используется для всех элементов, то при вычислении учитывается каждое значение результирующего набора.If VAR is used on all items in a SELECT statement, each value in the result set is included in the calculation. Функцию VAR можно использовать только для числовых столбцов.VAR can be used with numeric columns only. Значения NULL пропускаются.Null values are ignored.

VAR — это детерминированная функция, если она используется без предложений OVER и ORDER BY.VAR is a deterministic function when used without the OVER and ORDER BY clauses. Она не детерминирована при использовании с предложениями OVER и ORDER BY.It is nondeterministic when specified with the OVER and ORDER BY clauses. Дополнительные сведения см. в разделе Deterministic and Nondeterministic Functions.For more information, see Deterministic and Nondeterministic Functions.

ПримерыExamples

А. Использование функции VARA: Using VAR

В следующем примере возвращается дисперсия для заполнения всех значений премии в таблице SalesPerson в базе данных AdventureWorks2012AdventureWorks2012.The following example returns the variance for all bonus values in the SalesPerson table in the AdventureWorks2012AdventureWorks2012 database.

SELECT VAR(Bonus)  
FROM Sales.SalesPerson;  
GO

Примеры: Azure Synapse AnalyticsAzure Synapse Analytics и Параллельное хранилище данныхParallel Data WarehouseExamples: Azure Synapse AnalyticsAzure Synapse Analytics and Параллельное хранилище данныхParallel Data Warehouse

Б. Использование функции VARB: Using VAR

В приведенном ниже примере возвращается статистическая дисперсия для значений квот на продажу в таблице dbo. FactSalesQuota.The following example returns the statistical variance of the sales quota values in the table dbo.FactSalesQuota. Первый столбец содержит дисперсию всех уникальных значений, а второй — дисперсию всех значений, включая повторяющиеся.The first column contains the variance of all distinct values and the second column contains the variance of all values including any duplicates values.

-- Uses AdventureWorks  
  
SELECT VAR(DISTINCT SalesAmountQuota)AS Distinct_Values, VAR(SalesAmountQuota) AS All_Values  
FROM dbo.FactSalesQuota;

Результирующий набор:Here is the result set.

Distinct_Values   All_Values
----------------  ----------------
159180469909.18   158762853821.10

В.C. Использование функции VAR с предложением OVERUsing VAR with OVER

В приведенном ниже примере возвращается статистическая дисперсия для значений квот на продажу в каждом квартале календарного года.The following example returns the statistical variance of the sales quota values for each quarter in a calendar year. Обратите внимание на то, что ORDER BY в предложении OVER упорядочивает статистическую дисперсию, а ORDER BY в инструкции SELECT упорядочивает результирующий набор.Notice that the ORDER BY in the OVER clause orders the statistical variance and the ORDER BY of the SELECT statement orders the result set.

-- Uses AdventureWorks  
  
SELECT CalendarYear AS Year, CalendarQuarter AS Quarter, SalesAmountQuota AS SalesQuota,  
       VAR(SalesAmountQuota) OVER (ORDER BY CalendarYear, CalendarQuarter) AS Variance  
FROM dbo.FactSalesQuota  
WHERE EmployeeKey = 272 AND CalendarYear = 2002  
ORDER BY CalendarQuarter;

Результирующий набор:Here is the result set.

Year  Quarter  SalesQuota              Variance
----  -------  ----------------------  -------------------
2002  1         91000.0000             null
2002  2        140000.0000             1200500000.00
2002  3         70000.0000             1290333333.33
2002  4        154000.0000             1580250000. 00

См. также:See Also

Агрегатные функции (Transact-SQL) Aggregate Functions (Transact-SQL)
Предложение OVER (Transact-SQL)OVER Clause (Transact-SQL)

GROUPING (Transact-SQL) — SQL Server

12/03/2019

Чтение занимает 2 мин

В этой статье

Применимо к:Applies to: SQL ServerSQL Server (все поддерживаемые версии) SQL ServerSQL Server (all supported versions) База данных SQL AzureAzure SQL DatabaseБаза данных SQL AzureAzure SQL Database Управляемый экземпляр SQL AzureAzure SQL Managed InstanceУправляемый экземпляр SQL AzureAzure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsПрименимо к:Applies to: SQL ServerSQL Server (все поддерживаемые версии) SQL ServerSQL Server (all supported versions) База данных SQL AzureAzure SQL DatabaseБаза данных SQL AzureAzure SQL Database Управляемый экземпляр SQL AzureAzure SQL Managed InstanceУправляемый экземпляр SQL AzureAzure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics

Указывает, является ли указанное выражение столбца в списке GROUP BY статистическим или нет. Indicates whether a specified column expression in a GROUP BY list is aggregated or not. В результирующем наборе функция GROUPING возвращает 1 (статистическое выражение) или ноль (нестатистическое выражение).GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. Функция GROUPING может использоваться только в списке SELECT <select>, предложениях HAVING и ORDER BY, если указано предложение GROUP BY.GROUPING can be used only in the SELECT <select> list, HAVING, and ORDER BY clauses when GROUP BY is specified.

Синтаксические обозначения в Transact-SQLTransact-SQL Syntax Conventions

СинтаксисSyntax

GROUPING ( <column_expression> )

АргументыArguments

<column_expression>
Столбец или выражение, которое содержит столбец в предложении GROUP BY.Is a column or an expression that contains a column in a GROUP BY clause.

Типы возвращаемых данныхReturn Types

tinyinttinyint

RemarksRemarks

GROUPING используется, чтобы различать значения NULL, возвращаемые операторами ROLLUP, CUBE или GROUPING SETS, и стандартные значения NULL. GROUPING is used to distinguish the null values that are returned by ROLLUP, CUBE or GROUPING SETS from standard null values. Возвращение NULL в качестве результата операции ROLLUP, CUBE или GROUPING SETS является особым случаем использования NULL.The NULL returned as the result of a ROLLUP, CUBE or GROUPING SETS operation is a special use of NULL. Значение служит заполнителем столбца в результирующем наборе и означает «все».This acts as a column placeholder in the result set and means all.

ПримерыExamples

В следующем примере производится группирование SalesQuota и статистическая обработка сумм SaleYTD в базе данных AdventureWorks2012AdventureWorks2012.The following example groups SalesQuota and aggregates SaleYTD amounts in the AdventureWorks2012AdventureWorks2012 database. Функция GROUPING применяется к столбцу SalesQuota.The GROUPING function is applied to the SalesQuota column.

SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping'  
FROM Sales.SalesPerson  
GROUP BY SalesQuota WITH ROLLUP;  
GO

В результирующем наборе показаны два значения NULL в SalesQuota.The result set shows two null values under SalesQuota. Первый NULL представляет группу значений NULL из этого столбца в таблице.The first NULL represents the group of null values from this column in the table. Второй NULL находится в строке итогов, добавленной операцией ROLLUP.The second NULL is in the summary row added by the ROLLUP operation. Строка итогов содержит суммы TotalSalesYTD для всех групп SalesQuota и обозначается с помощью 1 в столбце Grouping.The summary row shows the TotalSalesYTD amounts for all SalesQuota groups and is indicated by 1 in the Grouping column.

Результирующий набор:Here is the result set.

SalesQuota     TotalSalesYTD       Grouping  
------------   -----------------   --------  
NULL           1533087.5999          0  
250000.00      33461260.59           0  
300000.00      9299677.9445          0  
NULL           44294026.1344         1  

(4 row(s) affected)

См. также:See Also

GROUPING_ID (Transact-SQL) GROUPING_ID (Transact-SQL)
GROUP BY (Transact-SQL)GROUP BY (Transact-SQL)

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

Всем доброго времени суток, и давайте c вами рассмотрим основные агрегатные функции SQL на примерах.

Допустим у нас есть стандартная таблица с товарами и их ценами:

id				tovar					summa
========================================================
1     				tovar1  				         1000
2    				tovar2  				        10000
3    				tovar3  				         2000
4   				 tovar4  				         1000
5    				tovar5  				         3000
6    				tovar6  				         3500
=========================================================

и первые функции которые мы с вами рассмотрим будут доставать из базы минимальное и максимальное значение поля summa:

SELECT MIN(`summa`), MAX(`summa`) FROM `tovar`

Как видите запрос нам вернул минимальное(1000) и максимальное(10000) значения поля summa из таблицы tovar. Это мы сделали благодаря использованию агрегатных функций MIN() и MAX(), то есть минимум и максимум, аргументом которых является поле из которого мы достаем значение.

С этим я думаю все понятно, поехали дальше.

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

SELECT SUM(`summa`) FROM `tovar`

Результатом данного запроса будет общая сумма значений(20500) поля summa. И здесь мы использовали функцию SUM() аргументом которой было поле summa.

Функция COUNT() считает количество строк и в качестве ее аргумента мы можем использовать либо название поля или ‘*’.Рассмотрим на следующем запросе:

SELECT COUNT(`summa`) FROM `tovar`
и 
SELECT COUNT(*) FROM `tovar`

как видите в обоих запросах нам вернулось значение 6, и это количество строк в таблице tovar.

Бывают ситуации когда нам необходимо получить среднее значение какого-нибудь поля.

Для этого существует функция AVG()

SELECT AVG(`summa`) FROM `tovar`

Результат 3416.6667 и мы можем это проверить используя уже полученные знания следующим запросом:

SELECT SUM(`summa`) / COUNT(`summa`) FROM `tovar`

Как видите результат у нас точно такой же 3416.6667 , то есть функция AVG() работает коректно.

И напоследок я хотел бы вам показать еще как избавиться от повторяющихся значений в полях. Допустим нам необходимо достать из таблицы tovar все категории цен , и как мы знаем у нас там есть повторяющиеся значения и для того чтобы их исключить. Существует специальное предложение DISTINCT, сразу оговорюсь это не функция.

Рассмотрим данное решение:

SELECT   DISTINCT  `summa` FROM `tovar`

Как видите нам из таблицы вернулись все наши категории цен товаров. И там нет повторяющихся значений!

Вот и все дорогие друзья! Думаю что данная статья была для вас полезна и вы узнали а ней что то новое, а если и нет, тогда освежили свои знания.

Всего доброго и удачи!

Вложенные агрегатные функции, Max (Avg()), in SQL

Я пишу этот запрос в SQL :

select MAX(AVG(salary) ) from employees group by department_id;

Сначала я получу группы по department_id , но что будет дальше ?

sql

Поделиться

Источник

Ala Aga

10 июня 2013 в 18:02

4 ответа

Если у вас есть что-то подобное

EmployeeId DepartmentId Salary
    1          1         10              
    2          1         30
    3          2         30
    4          2         40
    5          2         20
    6          3         40
    7          3         50

после группировки

DepartmentId    AVG(Salary) 
    1             (10+30)/2 = 20
    2             (30+40+20)/3 = 30
    3             (40+50)/2= 45

Таким образом, приведенный ниже запрос вернет 45 в качестве максимальной средней зарплаты для departmentId 3

SELECT MAX(x. avg) 
FROM ( SELECT AVG(salary)as avg FROM employees group by department_id)x;

Поделиться

Nikola Mitev

10 июня 2013 в 18:10

Скорее всего, в зависимости от вашего RDBMS, это нужно будет сделать с помощью подзапроса

select max(AveragesByDept.avgSalary) 
from ( 
    select avgSalary=avg(salary) 
    from employees
    group by department_id
    ) AveragesByDept

Поделиться

Brad

10 июня 2013 в 18:04

Вы можете сделать это, просто получив один ряд. Например:

select AVG(salary)
from employees
group by department_id
order by avg(salary) desc
limit 1

limit 1 может быть top 1 (SQL Server) или подзапросом с rownum = 1 (Oracle).

Поделиться

Gordon Linoff

10 июня 2013 в 18:12

Поддерживает ли DynamoDB агрегатные функции, такие как AVG, MAX, MIN?
Я попробовал DynamoDB docs и работать с примером, используя QueryResult , я не смог найти ни одного источника, как использовать min, max и avg в DynamoDB (я использую Java API).
Oracle sql вложенные агрегатные функции
Во вложенной агрегатной функции, использующей предложение group by ex max(avg (salary )) , первая зарплата вычисляется для каждого отдела, предполагая, что группировка выполняется по отделу, а затем выбирается максимальная из них . Теперь я не понимаю, как работает множественная агрегатная функция…

Это зависит от поддержки RDBMS, но если она будет поддержана, вы получите самую большую из средних по департаменту зарплату.

Группировка по применяется к внутреннему агрегату, а внешний агрегат не сгруппирован.

Поделиться

David Aldridge

10 июня 2013 в 18:05

17) Агрегатные функции — CoderLessons.com

Агрегатные функции — это все о

Выполнение расчетов в несколько рядов
Из одного столбца таблицы
И возвращая единственное значение.

Стандарт ISO определяет пять (5) агрегатных функций, а именно:

1) COUNT

2) SUM
3) AVG

4) MIN
5) MAX

Зачем использовать агрегатные функции.

С точки зрения бизнеса разные уровни организации предъявляют разные информационные требования. Менеджеры высшего уровня, как правило, заинтересованы в знании целых цифр и не нуждаются в отдельных деталях.

> Агрегатные функции позволяют нам легко получать сводные данные из нашей базы данных.

Например, из нашей базы данных myflix, руководство может потребовать следующих отчетов

Наименее арендованные фильмы.
Самые арендованные фильмы.
Среднее число, которое каждый фильм сдается в месяц.

Мы легко создаем вышеуказанные отчеты, используя агрегатные функции.

Давайте рассмотрим агрегатные функции подробно.

Функция COUNT

Функция COUNT возвращает общее количество значений в указанном поле. Он работает как с числовыми, так и с нечисловыми типами данных. Все агрегатные функции по умолчанию исключают нулевые значения перед работой с данными.

COUNT (*) — это специальная реализация функции COUNT, которая возвращает количество всех строк в указанной таблице. COUNT (*) также считает Null и дубликаты.

В приведенной ниже таблице показаны данные в таблице movierentals

номер ссылки	Дата сделки	Дата возвращения	членский номер	movie_id
11	20-06-2012	ЗНАЧЕНИЕ NULL	1	1
12	22-06-2012	25-06-2012	1	2
13	22-06-2012	25-06-2012	3	2
14	21-06-2012	24-06-2012	2	2
15	23-06-2012	ЗНАЧЕНИЕ NULL	3	3

Давайте предположим, что мы хотим получить количество раз, когда фильм с идентификатором 2 был сдан в аренду

SELECT COUNT(`movie_id`)  FROM `movierentals` WHERE `movie_id` = 2;

Выполнение вышеупомянутого запроса в MySQL Workbench против myflixdb дает нам следующие результаты.

DISTINCT Ключевое слово

Ключевое слово DISTINCT, которое позволяет нам исключать дубликаты из наших результатов. Это достигается путем группировки похожих значений вместе.

Чтобы оценить концепцию Distinct, давайте выполним простой запрос

SELECT `movie_id` FROM `movierentals`;

Теперь давайте выполним тот же запрос с отдельным ключевым словом —

SELECT DISTINCT `movie_id` FROM `movierentals`;

Как показано ниже, отчет об исключении повторяющихся записей из результатов.

Функция MIN

Функция MIN возвращает наименьшее значение в указанном поле таблицы .

В качестве примера, давайте предположим, что мы хотим знать год, в который был выпущен самый старый фильм в нашей библиотеке, мы можем использовать функцию MIN в MySQL, чтобы получить необходимую информацию.

Следующий запрос помогает нам достичь этого

SELECT MIN(`year_released`) FROM `movies`;

Выполнение вышеупомянутого запроса в MySQL Workbench против myflixdb дает нам следующие результаты.

Макс функция

Как следует из названия, функция MAX противоположна функции MIN. Он возвращает наибольшее значение из указанного поля таблицы .

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

В следующем примере возвращается последний год выпуска фильма.

SELECT MAX(`year_released`)  FROM `movies`;

Выполнение вышеуказанного запроса в MySQL Workbench с использованием myflixdb дает нам следующие результаты.

СУММА функция

Предположим, мы хотим получить отчет, в котором указана общая сумма выполненных платежей. Мы можем использовать функцию MySQL SUM, которая возвращает сумму всех значений в указанном столбце . SUM работает только с числовыми полями . Нулевые значения исключаются из возвращаемого результата.

В следующей таблице приведены данные в таблице платежей.

идентификатор платежа	членский номер	дата платежа	описание	выплаченная сумма	external_ reference_number
1	1	23.07.2012	Оплата проката фильмов	2500	11
2	1	25-07-2012	Оплата проката фильмов	2000	12
3	3	30-07-2012	Оплата проката фильмов	6000	ЗНАЧЕНИЕ NULL

Приведенный ниже запрос возвращает все выполненные платежи и суммирует их, чтобы получить единый результат.

SELECT SUM(`amount_paid`) FROM `payments`;

Выполнение вышеуказанного запроса в MySQL Workbench для myflixdb дает следующие результаты.

Функция AVG

Функция MySQL AVG возвращает среднее значение в указанном столбце . Как и функция SUM, она работает только с числовыми типами данных .

Предположим, мы хотим найти среднюю уплаченную сумму. Мы можем использовать следующий запрос —

SELECT AVG(`amount_paid`)  FROM `payments`;

Выполнение вышеуказанного запроса в MySQL Workbench дает нам следующие результаты.

Резюме

MySQL поддерживает все пять (5) стандартных агрегатных функций ISO COUNT, SUM, AVG, MIN и MAX.
Функции SUM и AVG работают только с числовыми данными.
Если вы хотите исключить повторяющиеся значения из результатов агрегатной функции, используйте ключевое слово DISTINCT. Ключевое слово ALL включает даже дубликаты. Если ничего не указано, по умолчанию принимается ALL.
Агрегатные функции могут использоваться в сочетании с другими предложениями SQL, такими как GROUP BY

Логические

Вы думаете, что агрегатные функции просты. Попробуй это!

В следующем примере группируются участники по имени, подсчитывается общее количество платежей, средняя сумма платежей и общая сумма сумм платежей.

SELECT m.`full_names`,COUNT(p.`payment_id`) AS  `paymentscount`,AVG(p.`amount_paid`)  AS `averagepaymentamount`,SUM(p.`amount_paid`)  AS `totalpayments` FROM members m, payments p WHERE m.`membership_number` = p.`membership_number` GROUP BY m.`full_names`;

Выполнение приведенного выше примера в MySQL Workbench дает нам следующие результаты.

ГРУППИРОВКА

(Transact-SQL) — SQL Server

03.12.2019
2 минуты на чтение

В этой статье

Применимо к: SQL Server (все поддерживаемые версии) База данных SQL AzureAzure SQL Managed InstanceAzure Synapse Analytics

Указывает, является ли указанное выражение столбца в списке GROUP BY агрегированным или нет.GROUPING возвращает 1 для агрегированного значения или 0 для неагрегированного в наборе результатов. GROUPING может использоваться только в списках SELECT

Имя *

Email *

Сайт

Октябрь 2024
Пн	Вт	Ср	Чт	Пт	Сб	Вс
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

Агрегатные функции в sql: Агрегатные функции (Transact-SQL) — SQL Server

Агрегатные функции (Transact-SQL) — SQL Server

В этой статье

См. также разделSee also

MAX (Transact-SQL) — SQL Server

В этой статье

СинтаксисSyntax

АргументыArguments

Типы возвращаемых данныхReturn Types

КомментарииRemarks

ПримерыExamples

A.A. Простой примерSimple example

Б.B. Использование предложения OVERUsing the OVER clause

В.C. Использование MAX с символьными даннымиUsing MAX with character data

См. также:See Also

VAR (Transact-SQL) — SQL Server

В этой статье

СинтаксисSyntax

АргументыArguments

Типы возвращаемых данныхReturn Types

КомментарииRemarks

ПримерыExamples

А. Использование функции VARA: Using VAR

Примеры: Azure Synapse AnalyticsAzure Synapse Analytics и Параллельное хранилище данныхParallel Data WarehouseExamples: Azure Synapse AnalyticsAzure Synapse Analytics and Параллельное хранилище данныхParallel Data Warehouse

Б. Использование функции VARB: Using VAR

В.C. Использование функции VAR с предложением OVERUsing VAR with OVER

См. также:See Also

GROUPING (Transact-SQL) — SQL Server

В этой статье

СинтаксисSyntax

АргументыArguments

Типы возвращаемых данныхReturn Types

RemarksRemarks

ПримерыExamples

См. также:See Also

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

Вложенные агрегатные функции, Max (Avg()), in SQL

4 ответа

Похожие вопросы:

17) Агрегатные функции — CoderLessons.com

Зачем использовать агрегатные функции.

Макс функция

Резюме

Логические

(Transact-SQL) — SQL Server

В этой статье