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

Содержание

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

  • Чтение занимает 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

  • Чтение занимает 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

Возвращает максимальное значение выражения. 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

  • Чтение занимает 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

Возвращает статистическую дисперсию всех значений в указанном выражении. 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

  • Чтение занимает 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 ответа




14

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

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



4

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

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

Поделиться Brad     10 июня 2013 в 18:04



2

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

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 )) , первая зарплата вычисляется для каждого отдела, предполагая, что группировка выполняется по отделу, а затем выбирается максимальная из них . Теперь я не понимаю, как работает множественная агрегатная функция…



1

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

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

Поделиться David Aldridge     10 июня 2013 в 18:05


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


Вложенные агрегатные функции с группировкой в postgresql

Я пытаюсь получить среднее значение сумм, используя вложенные агрегатные функции и группировку. То, что я хотел бы сделать, это: SELECT AVG(SUM(x) GROUP BY y) WHERE … GROUP BY …; То есть для…


Агрегатные функции MAX и AVG не работают с DATEDIFF в Hibernate HQL

Я использую Hibernate3, пытаясь выполнить следующий запрос HQL, он выдает следующие ошибки. Запрос: select MAX(DATEDIFF(SECOND, startTime, endTime)) from Service_Master…


SQL: агрегатные функции со столбцами типа DATE

Я наткнулся на вопрос (в тесте) о том, какие агрегатные функции применимы к столбцам типа DATE. Итак, как я понимаю, COUNT будет просто подсчитывать количество строк, а MIN и MAX возвращают самую. ..


используйте агрегатные функции [ выполнить расчет ] для столбца unicode sql server

У меня есть один столбец unicode nvarchar в базе данных sql server. Я храню числовые значения в этом столбце В моем локализованном Indian Language GUJARATI. Как я могу использовать агрегатные…


Ошибка на Deployment из SQL server 2008 R2 сообщает SQL Server 2008, что агрегатные функции не могут быть вложены в другие агрегатные функции

Я использовал SQL Server 2008 R2 для разработки своих отчетов, и когда я попытался развернуть его на хост-сервере, который является SQL server 2008, он показал ошибку при развертывании отчета,…


Поддерживает ли DynamoDB агрегатные функции, такие как AVG, MAX, MIN?

Я попробовал DynamoDB docs и работать с примером, используя QueryResult , я не смог найти ни одного источника, как использовать min, max и avg в DynamoDB (я использую Java API).


Oracle sql вложенные агрегатные функции

Во вложенной агрегатной функции, использующей предложение group by ex max(avg (salary )) , первая зарплата вычисляется для каждого отдела, предполагая, что группировка выполняется по отделу, а затем. ..


Как имитировать агрегатные функции avg, sum, max, min и count on PouchDB?

Кто-нибудь знает, как создавать агрегатные функции, например avg, sum, max и min в базе данных PouchDB? Я создал простое приложение для тестирования PouchDB. Я до сих пор не понял, как выполнять эти…


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

Я понимаю, почему агрегатные функции должны использоваться в части наличия запроса, но не понимаю, почему два запроса ниже возвращают разные значения, т. е. что возвращает каждая из функций: SELECT…


Вложенные агрегатные функции

SELECT MAX(AVG(SYSDATE — inv_date)) FROM invoice; Что не так с этим запросом? Avg возвращает одно значение no Макс требует, чтобы группа работала над тем, чтобы она досент выполняла и выдавала…

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 0
12 22-06-2012 25-06-2012 1 2 0
13 22-06-2012 25-06-2012 3 2 0
14 21-06-2012 24-06-2012 2 2 0
15 23-06-2012 ЗНАЧЕНИЕ NULL 3 3 0

Давайте предположим, что мы хотим получить количество раз, когда фильм с идентификатором 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

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

В этой статье

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

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