Опубликован: 29.07.2008 | Доступ: свободный | Студентов: 1171 / 115 | Оценка: 4.49 / 4.15 | Длительность: 17:53:00
Лекция 1:

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

Лекция 1: 12345 || Лекция 2 >

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

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

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

Лекция 1: 12345 || Лекция 2 >
Гаральд Егоркин
Гаральд Егоркин
Россия
Павел Шелякин
Павел Шелякин
Россия