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

В лекциях курса "Разработка и защита баз данных в Microsoft SQL Server 2005 " мы научились проектировать и создавать базы данных, защищать их от несанкционированного использования и непредвиденных потерь данных, а также переносить данные в другие места. В данном курсе рассказывается о том, как манипулировать данными, хранящимися в базе данных; в этой лекции мы начинаем с вычисления итогов и другой сводной информации о данных в таблицах.

Прежде, чем приступить к чтению этой лекции, запустите SQL Server Management Studio, установите соединение с сервером SQL Server и выбрав базу данных Adventure Works, откройте окно New Query (Новый запрос).

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

Подсчет строк

Иногда в приложениях нужно сообщить пользователю, сколько элементов имеется в базе данных.

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

Тот же пример, написанный на языке Visual Basic, выглядит так:

Dim Counter As Integer = 0 
While dataReader.Read
  Counter += 1 
End While 
dataReader.Close()

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

Существует другой способ получить количество строк в том же объекте DataReader:

Dim Counter As Integer = dataReader.RecordsAffected()

Однако эта процедура при соединении с базой данных использует столько же сетевых ресурсов, что и предыдущая.

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

Использование функций T-SQL для вычисления количества записей

Язык Transact SQL (T-SQL) имеет особые функции для агрегации (обобщения информации о данных), эти функции являются скалярными (возвращают только одно значение) и обычно оперируют набором данных.

Используем функцию COUNT

Если нужно подсчитать записи в таблице, можно использовать следующий сценарий:

SELECT COUNT(*) AS [Count] FROM Production.Product

Запускаем пример приложения, написанного на Visual Basic и использующего функцию Count

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

Функцию COUNT можно использовать несколькими способами. Первый способ просто считает записи. В этом случае функция COUNT в качестве аргумента получает символ звездочки ( * ).

Но иногда бывает нужно подсчитать только те записи, которые имеют ненулевое значение в определенных полях. В этом случае можно использовать имя поля как аргумент.

Следующее предложение возвращает два различных результата для одной и той же таблицы:

SELECT COUNT(*) AS [Count],
       COUNT(Class) AS [Classes with values] 
  FROM Production.Product

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

SELECT COUNT(DISTINCT CardType) AS [Different Credit Cards] FROM Sales.CreditCard

Фильтрация результатов

Иногда приходится выполнять такие запросы к базе данных, которые отвечали бы на вопрос вроде: "Сколько у нас покупателей из Сиэтла?" Чтобы ответить на этот вопрос, нельзя использовать просто COUNT(*) или COUNT(DISTINCT имя_поля). Процесс подсчета требует использования фильтра.

По сути, синтаксис агрегации - это просто модификация инструкции SELECT. Учитывая это, можно фильтровать запрос при помощи предложения WHERE так же, как и в любой другой инструкции SELECT.

SELECT COUNT(*) AS [Customers In Seattle] 
  FROM Person.Address 
  WHERE (City = "Seattle")

Однако было бы лучше, если можно было бы предоставить информацию о пользователях для нескольких городов, а не только для одного города. Конечно, можно повторить запрос с параметром в предложении WHERE, чтобы пользователи могли указать город, который их интересует. Но что, если нужно ответить на вопрос: "Сколько у нас покупателей в каждом городе?"

Чтобы ответить на этот вопрос, придется изменить инструкцию SELECT, добавив в нее предложение GROUP BY. Посмотрите на этот сценарий и возвращенный им результат.

SELECT City, COUNT(*) AS [Count of Customers] 
  FROM Person.Address GROUP BY City

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

Создаем итоговую сводку с сортировкой данных

SELECT City, COUNT(*) AS [Count of Customers] 
  FROM Person.Address GROUP BY City ORDER BY City

SELECT City, COUNT(*) AS [Count of Customers]
  FROM Person.Address
  GROUP BY City
  HAVING (COUNT(*) > 50)
  ORDER BY City

Различие между предложениями WHERE и HAVING заключается в типе информации, к которой применяется каждый фильтр. Предложение WHERE фильтрует оригинальный набор информации из таблицы. Предложение HAVING фильтрует информацию, полученную после выполнения агрегатных функций, при этом фильтры обычно основываются на результатах агрегации.

Если мы рассмотрим предполагаемый план выполнения для сценария, использующего предложение WHERE, и другого сценария, использующего предложение HAVING, разница между этими двумя сценариями станет очевидной: Ознакомьтесь с предполагаемыми планами выполнения для двух предыдущих запросов при помощи следующей процедуры. Эти два плана выполнения показаны на рисунках 1.1 и 1.2.

Просматриваем предполагаемый план выполнения

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

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

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

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

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

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

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

SELECT SUM(LineTotal) AS [Grand Total] FROM Sales.SalesOrderDetail

SELECT ProductID, SUM(LineTotal) AS [Product Total] 
  FROM Sales.SalesOrderDetail
  GROUP BY 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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT OrderDate,
       SUM(TotalDue) AS Sales
  FROM Sales.SalesOrderHeader AS A
  GROUP BY OrderDate
  ORDER BY OrderDate

Нам нужно найти промежуточный итог для одной конкретной даты, для чего теоретически можно составить примерно такой запрос:

-Этот запрос не является синтаксически правильным запросом SQL
SELECT SUM(TotalDue) AS Expr1
  FROM Sales.SalesOrderHeader
  WHERE (OrderDate <= <Specific_Date>)

Имейте в виду, что этот запрос не является корректным сценарием T-SQL, он приводится только для объяснения того, как можно было бы высчитать промежуточный итог на определенный момент времени. Элемент <Specific_Date> не является корректным для T-SQL, он просто представляет конкретную дату в нашем теоретическом запросе. Чтобы претворить теорию в реальный T-SQL, мы можем воспользоваться полем OrderDate в качестве конкретной даты для каждой строки. (Сценарии из этого раздела можно найти в примерах в папке \SqlScripts в файле RunningTotalsExamplesFromText.sql )

Вычисляем промежуточные итоги при помощи подзапросов

SELECT OrderDate, 
  SUM(TotalDue) AS Sales, 
      (
       SELECT SUM(TotalDue) FROM Sales.SalesOrderHeader 
       WHERE (OrderDate <= A.OrderDate) 
      )
      AS [Actual Sales] 
FROM Sales.SalesOrderHeader AS A 
GROUP BY OrderDate 
ORDER BY OrderDate

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

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

CREATE FUNCTION SalesToDate 
   ( 
    @ThisDate datetime 
   ) 
RETURNS money 
AS
BEGIN
RETURN (SELECT SUM(TotalDue) AS Expr1 
FROM Sales.SalesOrderHeader 
WHERE (OrderDate <= @ThisDate)) 
END

SELECT OrderDate,
       SUM(TotalDue) AS Sales,
       dbo.SalesToDate(A.OrderDate) AS [Actual sales] 
FROM Sales.SalesOrderHeader AS A 
GROUP BY OrderDate 
ORDER BY OrderDate

Какой метод лучше? Для сравнения выполните следующую процедуру.

Сравниваем производительность двух запросов

В следующей таблице показано подмножество статистических показателей двух запросов.

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

Вычисление статистических значений

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

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

Использование функции AVG

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

Функция AVG в качестве аргумента принимает любые выражения, возвращающие численные значения. Аргументом может быть имя столбца или вычисление. Однако подзапросы или другие агрегатные функции в аргументе недопустимы.

Следующий пример - инструкция SELECT, которая возвращает среднюю цену продаж для каждого изделия:

SELECT Production.Product.Name AS Product,
   AVG(Sales.SalesOrderDetail.UnitPrice) AS [Avg Price] 
  FROM Sales.SalesOrderDetail 
  INNER JOIN Production.Product 
    ON  Sales.SalesOrderDetail.ProductID = Production.Product.ProductID 
  GROUP BY Production.Product.Name 
  ORDER BY Production.Product.Name

И еще одна инструкция SQL, возвращающая среднюю стоимость каждого изделия по месяцам и общую среднюю стоимость для каждого продукта.

SELECT
  CASE GROUPING(Production. Product .Name) 
    WHEN 1 THEN "Global Average" 
           ELSE Production. Product .Name END AS Product, 
  CASE GROUPING( CONVERT(nvarchar(7), 
                 Sales.SalesOrderHeader.OrderDate, 111))
    WHEN 1 THEN "Average"
           ELSE CONVERT(nvarchar(7), Sales.SalesOrderHeader.OrderDate, 111) 
  END AS Period,
  AVG(Sales.SalesOrderDetail.UnitPrice) AS [Avg Price] 
  FROM Sales.SalesOrderDetail 
  INNER JOIN Production.Product
    ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID 
  INNER JOIN Sales.SalesOrderHeader
    ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID 
  GROUP BY Production.Product.Name,
           CONVERT(nvarchar(7), Sales.SalesOrderHeader.OrderDate, 111) 
  WITH ROLLUP ORDER BY GROUPING(Production.Product.Name),
                       Production.Product.Name, Period

Использование функций MIN и MAX

Эти две функции возвращают минимальное или максимальное значение выражения. И вновь, выражение, используемое в качестве аргумента для этих функций, может быть либо именем столбца, либо другим вычислением, кроме того, для форматирования результатов можно использовать операторы GROUP или ROLLUP. Выполните следующий сценарий, чтобы увидеть функции MIN и MAX в действии.

SELECT Production.Product.Name,
       MIN(Sales.SalesOrderDetail.UnitPrice) AS [Min Price],
       MAX(Sales.SalesOrderDetail.UnitPrice) AS [Max Price],
       AVG(Sales.SalesOrderDetail.UnitPrice) AS [Avg Price] 
  FROM Sales.SalesOrderDetail 
  INNER JOIN Production.Product
    ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID 
  GROUP BY Production.Product.Name 
  ORDER BY Production.Product.Name