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

Использование сложных статистических функций

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

анализа собранных данных. Среди более сложных статистических функций, которые вы можете использовать для будущих анализов данных - STDEV, STDEVP, VAR и VARP.

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

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

Функция VAR возвращает статистическое отклонение от численного значения определенного выражения.

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

Эта функция возвращает статистическое отклонения для совокупности значений в указанном выражении.

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

STDEV возвращает статистическое среднеквадратическое отклонение значения в указанном выражении, тогда как STDEVP возвращает статистическое среднеквадратическое отклонение совокупности значений в указанном выражении.

Рекомендуем ознакомиться с дополнительной информацией о значении этих статистических функций в литературе по статистике.

Использование ключевого слова DISTINCT

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

SELECT Production.Product.Name,
       MIN(DISTINCT Sales.SalesOrderDetail.UnitPrice) AS [Min Price],
       MAX(DISTINCT Sales.SalesOrderDetail.UnitPrice) AS [Max Price],
       AVG(DISTINCT 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

В следующей таблице можно сравнить результаты приведенного выше сценария с использованием и без использования ключевого слова DISTINCT. Обратите внимание на то, что значения MIN и MAX не изменяются, а значения AVG в большинстве случаев отличаются.

Проектирование собственных пользовательских агрегатов с использованием CLR

Совет. Чтобы выполнять упражнения в этом разделе, в SQL Server должна быть включена интеграция CLR. Узнать об этой настройке можно в Электронной документации по SQL Server 2005 в теме "Настройка конфигурации контактной зоны для функций".

SQL Server 2005 позволяет создавать новые агрегатные функции, написанные с использованием непосредственно языков CLR (общеязыковой среды выполнения). Например, представьте себе, что вам нужно рассчитать среднюю фактическую производительность по заказам. Это не особенно практический пример, но он покажет, как работать с типами данных Date, Time и TimeSpan, которыми трудно управлять, используя только T-SQL.

Создаем суррогат агрегатной функции

1. Из меню Start (Пуск) откройте All Programs, Microsoft Visual Studio 2005, Microsoft Visual Studio 2005 (Все программы, Microsoft Visual Studio 2005, Microsoft Visual Studio 2005).
2. В меню File (Файл) выберите команду New (Создать), затем Project (Проект). Откроется окно New Project (Новый проект), показанное ниже.
   

   
   
3. В панели Project Types (Типы проектов) разверните узел Visual Basic и выберите тип проекта Database (База данных). В панели Templates (Шаблоны) выделите SQL Server Project (Проект SQL Server). Укажите имя и путь к файлу для этого проекта, а затем нажмите кнопку ОК, чтобы создать его.
4. Откроется диалоговое окно Add Database Reference (Добавление ссылки на базу данных), показанное ниже. Выберите ссылку на базу данных, которую вы хотите использовать, и нажмите кнопку ОК, чтобы установить соединение. Если у вас не настроены ссылки на базы данных, то программа предложит создать новую ссылку. При выборе или создании ссылки, убедитесь, что указали в качестве базы данных, с которой нужно установить соединение, базу данных Adventure Works.
   

   
   
5. Если программа выведет приглашение включить отладку SQL/CLR, как показано на рисунке, нажмите кнопку Yes (Да), чтобы включить отладку.
   

   
   
6. В меню View (Вид) выберите команду Solution Explorer (Обозреватель решений). В панели Solution Explorer (Обозреватель решений) щелкните правой кнопкой мыши на своем проекте и выберите команду Add (Добавить), а затем Aggregate (Агрегат), как показано на рисунке.
   

   
   увеличить изображение
   
7. В диалоговом окне Add New Item (Добавление нового элемента) укажите имя для новой агрегатной функции WAVG.vb (сокращение от weighted average -взвешенное среднее), как показано на рисунке ниже, а затем нажмите кнопку Add (Добавить).
   

   
   увеличить изображение
   
8. Когда вы добавляете элемент WAVG.vb, Visual Studio добавляет в структуру агрегата следующий суррогат кода.

   <Serializable()> _
     <Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)> _ 
       Public Structure WAVG 
         Public Sub Init()
           " Введите сюда свой код 
         End Sub 
         Public Sub Accumulate(ByVal value As SqlString)
           " Введите сюда свой код 
         End Sub 
         Public Sub Merge(ByVal value As WAVG)
           " Введите сюда свой код 
         End Sub 
         Public Function Terminate() As SqlString
           " Введите сюда свой код
           Return New SqlString("") 
         End Function
         " Это заместитель элемента поля 
         Private var1 As Integer 
       End Structure

   В этой структуре определены четыре процедуры: Init, Accumulate, Merge и Terminate. Чтобы обеспечить правильное выполнение функций, вам нужно будет добавить соответствующий код.

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

   Поскольку работать мы будем с затраченным временем, вероятно, для возвращаемого значения подойдет тип данных TimeSpan. Однако этот тип данных в SQL Server нельзя использовать. Вместо него придется использовать строку с отображением промежутка времени.

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