Вопрос 6: Обработка запроса. Группировка данных. Агрегатные функции.

Порядок выполнения

Следующие действия демонстрируют логический порядок обработки или порядок привязки инструкции SELECT. Этот порядок определяет, когда объекты, определенные в одном шаге, становятся доступными для предложений в последующих шагах. Например, если обработчик запросов можно привязать (для доступа) к таблицам или представлениям, определенным в предложении FROM, эти объекты и их столбцы становятся доступными для всех последующих шагов. И наоборот, поскольку предложение SELECT является шагом 8, любые псевдонимы столбцов или производных столбцов, определенные в этом предложении, не могут быть объектом для ссылки предыдущих предложений. Вместе с тем к ним могут обращаться последующие предложения, например предложение ORDER BY. Фактическое физическое выполнение инструкции определяется обработчиком запросов и порядок из этого списка может значительно отличаться.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE или WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. В начало

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

Агрегатная функция выполняет вычисление на наборе значений и возвращает одиночное значение. Агрегатные функции, за исключением COUNT, не учитывают значения NULL. Агрегатные функции часто используются в выражении GROUP BY инструкции SELECT.Агрегатные функции можно использовать в качестве выражений только в следующих случаях:

• Список выбора инструкции SELECT (вложенный или внешний запрос).
• Предложение HAVING.

Transact-SQL предоставляет следующие агрегатные функции:

• APPROX_COUNT_DISTINCT Эта функция возвращает приблизительное количество уникальных значений, не равных NULL, в группе.
• MIN Возвращает минимальное значение выражения. За функцией может следовать предложение OVER.
• AVG Эта функция возвращает среднее арифметическое группы значений. Значения NULL она не учитывает.
• STDEV Возвращает статистическое стандартное отклонение всех значений в указанном выражении.
• CHECKSUM_AGG Эта функция возвращает контрольную сумму значений в группе. Значения NULL функция CHECKSUM_AGG не учитывает. Предложение OVER может следовать за функцией CHECKSUM_AGG.
• STDEVP Возвращает статистическое стандартное отклонение совокупности всех значений в указанном выражении.
• COUNT Эта функция возвращает количество элементов, найденных в группе. Функция COUNT работает подобно функции COUNT_BIG. Эти функции различаются только типами данных в возвращаемых значениях. Функция COUNT всегда возвращает значение типа данных int. Функция COUNT_BIG всегда возвращает значение типа данных bigint.
• STRING_AGG Сцепляет значения строковых выражений, помещая между ними значения-разделители. В конце строки разделитель не добавляется.
• COUNT_BIG Эта функция возвращает количество элементов, найденных в группе. Функция COUNT_BIG работает подобно функции COUNT. Эти функции различаются только типами данных в возвращаемых значениях. Функция COUNT_BIG всегда возвращает значение типа данных bigint. Функция COUNT всегда возвращает значение типа данных int.
• SUM Возвращает сумму всех, либо только уникальных, значений в выражении. Функция SUM может быть использована только для числовых столбцов. Значения NULL пропускаются.
• GROUPING Указывает, является ли указанное выражение столбца в списке GROUP BY статистическим или нет. В результирующем наборе функция GROUPING возвращает 1 (статистическое выражение) или ноль (нестатистическое выражение). Функция GROUPING может использоваться только в предложениях SELECT, HAVING и ORDER BY, если указано предложение GROUP BY.
• VAR Возвращает статистическую дисперсию всех значений в указанном выражении. За функцией может следовать предложение OVER.
• GROUPING_ID Представляет собой функцию, которая вычисляет уровень группирования. Если задано предложение GROUP BY, функция GROUPING_ID может использоваться только в предложениях SELECT, HAVING или ORDER BY.
• VARP Возвращает статистическую дисперсию для заполнения всех значений в указанном выражении.
• MAX Возвращает максимальное значение выражения.

GROUP BY

Предложение инструкции SELECT, которое разделяет результат запроса на группы строк обычно с целью выполнения одного или нескольких статистических вычислений в каждой группе. Инструкция SELECT возвращает одну строку для каждой группы.

Синтаксис

GROUP BY {
      column-expression  
    | ROLLUP ( <group_by_expression> [ ,...n ] )  
    | CUBE ( <group_by_expression> [ ,...n ] )  
    | GROUPING SETS ( <grouping_set> [ ,...n ]  )  
    | () --calculates the grand total 
} [ ,...n ] 

<group_by_expression> ::=  
      column-expression  
    | ( column-expression [ ,...n ] )    

<grouping_set> ::=  
      () --calculates the grand total  
    | <grouping_set_item>  
    | ( <grouping_set_item> [ ,...n ] )  

<grouping_set_item> ::=  
      <group_by_expression>  
    | ROLLUP ( <group_by_expression> [ ,...n ] )  
    | CUBE ( <group_by_expression> [ ,...n ] )

Аргументы

column-expression Указывает на столбец или на нестатистическое вычисление в столбце. Этот столбец может принадлежать таблице, производной таблице или представлению. Столбец должен быть указан в предложении FROM инструкции SELECT, но не обязательно должен присутствовать в списке SELECT.

GROUP BY column-expression [ ,...n ] Группирует результаты инструкции SELECT в соответствии со значениями в списке одного или нескольких выражений столбцов. Например, этот запрос создает таблицу Sales со столбцами Country, Region и Sales. Он вставляет четыре строки, и две строки имеют совпадающие значения для столбцов Country и Region.

CREATE TABLE Sales ( Country varchar(50), Region varchar(50), Sales int );

INSERT INTO sales VALUES (N'Canada', N'Alberta', 100);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 200);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 300);
INSERT INTO sales VALUES (N'United States', N'Montana', 100);

SELECT Country, Region, SUM(sales) AS TotalSales
FROM Sales
GROUP BY Country, Region;

Результат

GROUP BY ROLLUP

Создает группу для каждого сочетания выражений столбцов. Кроме того, выполняет сведение результатов в промежуточные и общие итоги. Для этого запрос перемещается справа налево, уменьшая количество выражений столбцов, по которым он создает группы и агрегаты. Порядок столбцов влияет на выходные данные ROLLUP и может отразиться на количестве строк в результирующем наборе. Например, GROUP BY ROLLUP (col1, col2, col3, col4) создает группы для каждой комбинации выражений столбцов в следующих списках.

• col1, col2, col3, col4
• col1, col2, col3, NULL
• col1, col2, NULL, NULL
• col1, NULL, NULL, NULL
• NULL, NULL, NULL, NULL — это общий итог.

GROUP BY CUBE

GROUP BY CUBE создает группы для всех возможных сочетаний столбцов. Для GROUP BY CUBE (a, b) результатами являются группы для уникальных значений (a, b) (NULL, b), (a, NULL) и (NULL, NULL). Принимая во внимание таблицу из предыдущего примера, этот код выполняет операцию GROUP BY CUBE по столбцам Country и Region.

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE (Country, Region);

Результат

Пример с агрегатной функцией

В следующем примере показано, как извлечь данные об общем объеме продаж за каждый год с помощью функции DATEPART. Одно и то же выражение должно присутствовать как в списке SELECT, так и в предложении GROUP BY.

SELECT DATEPART(yyyy,OrderDate) AS N'Year'  
    ,SUM(TotalDue) AS N'Total Order Amount'  
FROM Sales.SalesOrderHeader  
GROUP BY DATEPART(yyyy,OrderDate)  
ORDER BY DATEPART(yyyy,OrderDate);