Вычисление агрегатов

Вычисление итоговых и промежуточных сумм

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

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

Вычисление итоговых сумм

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

Применяем функцию SUM

Функция SUM делает именно то, что вы ожидаете: она возвращает сумму значений в столбце. Эти значения имеют числовые типы данных. Кроме того, функция возвратит ошибку, если обнаружит значение NULL при попытке вычислить итоговую сумму. Давайте рассмотрим, какими способами можно использовать функцию SUM для получения различной полезной информации. (Сценарии из этого раздела можно найти в примерах в папке \SqlScripts в файле SumExamplesFromText.sql )

Генерируем итоговые суммы

1. Чтобы подсчитать итоговую сумму значений в столбце LineTotal таблицы SalesOrderDetail в базе данных Adventure Works, введите и выполните следующий запрос:

   SELECT SUM(LineTotal) AS [Grand Total] FROM Sales.SalesOrderDetail
2. Сделаем результат, возвращаемый этим сценарием, более полезным: пусть он отображает итоговое количество продаж по продуктам; для этого выполним следующий сценарий:

   SELECT ProductID, SUM(LineTotal) AS [Product Total] 
     FROM Sales.SalesOrderDetail
     GROUP BY ProductID
3. Уже лучше, но название столбца ProductID может быть недостаточно понятно для пользователей. Еще усовершенствуем запрос посредством выполнения соединения с таблицей товаров, чтобы вместо идентификатора товара отображалось соответствующее название товара; для этого выполним следующий запрос:

   SELECT Production.Product.Name, SUM(Sales.SalesOrderDetail.LineTotal) AS [Product Total]
   FROM Sales.SalesOrderDetail INNER JOIN Production.Product
   ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID GROUP BY
      Production.Product.Name ORDER BY Production.Product.Name
4. Теперь результаты более понятны и полезны для пользователей. Вероятно, мы могли бы предоставить пользователям некоторую информацию о категории и подкатегории каждого товара. С этой целью выполним следующий сценарий:

   SELECT C.Name AS Category, S.Name AS SubCategory,
          P.Name AS Product, SUM(O.LineTotal) AS [Product Total] 
       FROM Sales.SalesOrderDetail AS O
       INNER JOIN Production.Product AS P ON O.ProductID = P.ProductID
       INNER JOIN Production.ProductSubcategory AS S 
           ON P.ProductSubcategoryID = S.ProductSubcategoryID
       INNER JOIN Production.ProductCategory AS C 
           ON S.ProductCategoryID = C.ProductCategoryID 
       GROUP BY P.Name, C.Name, S.Name ORDER BY Category, SubCategory, Product

   Получаем следующие результаты.

   Таблица 1.3. Результаты

   Category	SubCategory	Product	Sales
   Accessories	Bike Racks	Hitch Rack - 4-Bike	237096.16
   Accessories	Bike Stands	All-Purpose Bike Stand	39591.00
   Accessories	Bottles and Cages	Mountain Bottle Cage	20229.75
   Accessories	Bottles and Cages	Road Bottle Cage	15390.88
   Accessories	Bottles and Cages	Water Bottle - 30 oz.	28654.16
   Accessories	Cleaners	Bike Wash - Dissolver	18406.97
   Accessories	Fenders	Fender Set - Mountain	46619.58
   Accessories	Helmets	Sport-100 Helmet, Black	16.869.52

   Вот теперь мы предоставляем пользователям очень полезный набор информации.

   Если элементов слишком много, то анализ результатов может быть затруднительным. Кроме того, возможно, пользователю потребуется итоговая сумма по полям SubCategory и Category. Чтобы выполнить эту задачу, можно использовать функцию ROLLUP.

Используем функцию ROLLUP для вычисления детализированных сумм

T-SQL предоставляет операторы для предложения GROUP BY, которые позволяют получить не только детализированную, но и сводную информацию для каждого из полей, которые указаны в аргументе предложения GROUP BY. (Сценарии из этого раздела можно найти в примерах в папке \SqlScripts в файле RollupExamplesFromText.sql )

Генерируем детализированные суммы

1. Измените предыдущую инструкцию SELECT так, чтобы она использовала только информацию столбцов Category и SubCategory. Это уменьшит количество возвращаемых строк и несколько упростит понимание данных. Мы также добавим в предложение GROUP BY оператор WITH ROLLUP, чтобы вывести промежуточную сумму для столбцов Category и SubCategory.

   SELECT C.Name AS Category, S.Name AS SubCategory,
          SUM(O.LineTotal) AS Sales 
     FROM Sales.SalesOrderDetail AS O
     INNER JOIN Production.Product AS P
        ON O.ProductID = P.ProductID
     INNER JOIN Production.ProductSubcategory AS S
        ON P.ProductSubcategoryID = S.ProductSubcategoryID
     INNER JOIN Production.ProductCategory AS C
        ON S.ProductCategoryID = C.ProductCategoryID
     GROUP BY C.Name, S.Name WITH ROLLUP
     ORDER BY Category, SubCategory

   Таблица 1.4. Результаты

   Category	Subcategory	Sales
   NULL	NULL	$ 109846381.40
   Accessories	NULL	1272072.88
   Accessories	Bike Racks	237096.16
   Accessories	Bike Stands	39591.00
   Accessories	Bottles and Cages	64274.79
   Accessories	Cleaners	18406.97
   Accessories	Fenders	46619.58
   Accessories	Helmets	484048.53
   Accessories	Hydration Packs	105826.42
   Accessories	Locks	16240.22
   Accessories	Pumps	13514.69
   Accessories	Tires and Tubes	246454.53
   Bikes	NULL	94651172.70
   Bikes	Mountain Bikes	36445443.94
   …	…	…

   Из этого результирующего набора видно, что общая итоговая сумма продаж составляет 109,846,381.40 долларов, общая итоговая сумма продаж для категории Accessories составляет 1,272,072.88 долларов, общая итоговая сумма продаж для подкатегории Bike Racks -237,096.16 долларов и т. д. Однако вывод общих итоговых сумм таким образом - не лучший способ представления информации.
2. Чтобы получить более удобный структурированный результирующий набор, можно воспользоваться особой агрегатной функцией, GROUPING. Эта функция помогает обозначить различия между строками, содержащими итоговые значения, и строками, содержащие промежуточные значения. Добавьте функцию GROUPING в свой сценарий для полей Category и SubCategory, чтобы итоговые строки в результате были нагляднее; для этого выполните следующий сценарий:

   SELECT C.Name AS Category,
          S.Name AS SubCategory,
          SUM(O.LineTotal) AS Sales,
     GROUPING(C.Name) AS IsCategoryGroup,
     GROUPING(S.Name) AS IsSubCategoryGroup 
     FROM	Sales.SalesOrderDetail AS O
     INNER JOIN Production.Product AS P ON O.ProductID = P.ProductID
     INNER JOIN Production.ProductSubcategory AS S
        ON P.ProductSubcategoryID = S.ProductSubcategoryID
     INNER JOIN Production.ProductCategory AS C 
        ON S.ProductCategoryID = C.ProductCategoryID GROUP BY C.Name, S.Name 
     WITH ROLLUP ORDER BY Category, SubCategory
3. Теперь изменим порядок строк в результирующем наборе, упорядочив по этим сгруппированным значениям. В результате информация будет отображаться в более подходящем для отчета стиле:

   SELECT C.Name AS Category,
          S.Name AS SubCategory,
          SUM(O.LineTotal) AS Sales,
     GROUPING(C.Name) AS IsCategoryGroup,
     GROUPING (S.Name) AS IsSubCategoryGroup 
     FROM Sales.SalesOrderDetail AS O
     INNER JOIN Production.Product AS P ON O.ProductID = P.ProductID
     INNER JOIN Production.ProductSubcategory AS S
       ON P.ProductSubcategoryID = S.ProductSubcategoryID
     INNER JOIN Production.ProductCategory AS C 
       ON S.ProductCategoryID = C.ProductCategoryID 
     GROUP BY C.Name, S.Name WITH ROLLUP 
     ORDER BY IsCategoryGroup, Category, IsSubCategoryGroup, SubCategory

   Результирующий набор для этого сценария показан в табл. 1.5. Обратите внимание на то, что некоторые строки были опущены для экономии места.

   Таблица 1.5. Результаты

   Category	SubCategory	Sales	IsCategoryGroup	IsSubCategoryGroup
   Accessories	Bike Racks	$ 237096.16	0	0
   Accessories	Bike Stands	39591.00	0	0
   Accessories	NULL	1272072.88	0	1
   Bikes	Mountain Bikes	36445443.94	0	0
   Bikes	Touring Bikes	14296291.26	0	0
   Bikes	NULL	94651172.70	0	1
   Clothing	Bib-Shorts	167558.62	0	0
   Clothing	Caps	51229.45	0	0
   Clothing	NULL	2120542.52	0	1
   Components	Bottom Brackets	51826.37	0	0
   Components	Wheels	680831.35	0	0
   Components	NULL	11802593.29	0	1
   …		…	…	…
   NULL	NULL	109846381.40	1	1
4. Наконец, можно изменить отображение значения NULL до более приемлемого для отчета вида при помощи добавления в запрос инструкции CASE:

   SELECT
     CASE GROUPING(C.Name)
       WHEN 1 THEN "Category Total"
            ELSE C.Name END AS Category, 
     CASE GROUPING(S.Name)
       WHEN 1 THEN "Subcategory Total"
              ELSE S.Name END AS SubCategory, 
           SUM(O.LineTotal) AS Sales, 
     GROUPING(C.Name) AS IsCategoryGroup, 
     GROUPING(S.Name) AS IsSubCategoryGroup 
     FROM Sales.SalesOrderDetail AS O 
     INNER JOIN Production.Product AS P
       ON O.ProductID = P.ProductID 
     INNER JOIN Production.ProductSubcategory AS S
       ON P.ProductSubcategoryID = S.ProductSubcategoryID 
     INNER JOIN Production.ProductCategory AS C
       ON S.ProductCategoryID = C.ProductCategoryID 
     GROUP BY C.Name, S.Name WITH ROLLUP 
     ORDER BY IsCategoryGroup, Category,
              IsSubCategoryGroup, SubCategory

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