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

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

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

Как видите, значения в столбце 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 )

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

1. Воспользуйтесь подзапросом, чтобы получить все значения в одном результирующем наборе. Сохраните этот запрос как SubQueryMethod.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
2. Посмотрим предполагаемый план выполнения этого запроса в SQL Server Management Studio. Мы видим два запроса, выполняющихся параллельно до получения результирующих наборов.

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

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

1. Сначала создайте определяемую пользователем функцию, выполнив следующий сценарий.

   CREATE FUNCTION SalesToDate 
      ( 
       @ThisDate datetime 
      ) 
   RETURNS money 
   AS
   BEGIN
   RETURN (SELECT SUM(TotalDue) AS Expr1 
   FROM Sales.SalesOrderHeader 
   WHERE (OrderDate <= @ThisDate)) 
   END
2. Затем выполните следующий запрос. Сохраните оба запроса в файл UserDefFunctionMethod.sql.

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

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

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

1. В SQL Server Management Studio откройте меню Query (Запрос).
2. Выберите команду Include Client Statistics (Включить статистику клиента).
3. Выполните два набора запросов, которые вы сохранили во второй раз. Теперь вы увидите, что наряду с результатом доступна вкладка Client Statistics (Статистика клиента). Используйте предоставленную информацию для того, чтобы узнать стоимость двух различных видов запросов.

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

Примечание. В таблице показана только часть доступной статистики.

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

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

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

Эти функции можно использовать тем же способом, что и функцию 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