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

Добавляем код в суррогаты

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

   Public Structure WAVG
   Private Ticks As Long "Собирает интервалы между датами 
   Private Previous As SqlDateTime "Хранит предыдущие даты
                                   "чтобы получить затраченное время 
   Private Count As Integer "Количество обработанных записей
2. Агрегатные функции обычно выполняются на сгруппированных наборах записей. Для каждой группы модуль выполнения вызывает процедуру Init до обработки первой записи. В процедуре Init необходимо инициировать переменные при помощи следующего кода:

   Public Sub Init()
     Count = 0
     Previous = Nothing
     Ticks = -1 
     "To detect the first record 
   End Sub
3. Для каждой записи в группе исполнитель вызывает процедуру Accumulate, которая подробно описана ниже. Обратите внимание, что для данной процедуры не надо изменять аргумент с типа данных по умолчанию SqlString на SqlDateTime.

   Public Sub Accumulate(ByVal value As SqlString) 
     Dim span As New TimeSpan(0) 
   
     If Ticks > -1 Then
       span = New TimeSpan(Ticks)
       span = span.Add(value.Value.Subtract(Previous.Value))
     Else
       Ticks = 0 
     End If
     Previous = value 
     Count += 1 
     Ticks = span.Ticks 
   End Sub

   Когда исполнитель в первый раз вызывает эту процедуру, у нас нет предыдущей даты. Следовательно, количество Ticks между проверяемой в настоящий момент датой и предыдущей будет равно нулю. Код сохраняет актуальную дату в переменной Previous, чтобы выполнить вычисления при следующем вызове функции Accumulate. Кроме того, мы добавляем единицу в переменную Count, чтобы отслеживать количество уже проверенных записей.

   Последующие вызовы процедуры Accumulate вычисляют различные временные интервалы между датами. Поскольку переменная Ticks больше не равна -1, процедура создает столбец TimeSpan и добавляет к нему разность между текущей и предыдущей датами. Она также может сохранить дату в переменной Previous и прирост переменной Count, как и прежде.
4. Когда записи закончатся, исполнитель вызывает функцию Terminate. Введите код, как показано ниже.

   Public Function Terminate() As SqlString 
     If Ticks <= 0 Then
       Return New TimeSpan(0).ToString 
     Else
       Dim Resp As Long = CLng(Ticks / Count) 
       Dim RespDate As New System.TimeSpan(Math.Abs((Resp))) 
       Return RespDate.ToString 
     End If 
   End Function

   Если не было вычислено ни одного интервала, то переменная Ticks будет содержать 0. В этом случае было бы возвращено строковое представление новой переменной TimeSpan с количеством интервалов, равным 0.

   Если интервалы были вычислены, то мы разделим сумму этих разностей на количество обработанных записей, а затем создадим новую переменную TimeSpan для хранения результата. Затем мы возвращаем строковое представление этой переменной TimeSpan.

   Примечание. SQL Server может также разделить работу на меньшие фрагменты, результаты которых нужно будет объединить, вызвав метод Merge. В реальном приложении вам нужно будет соответствующим образом реализовать эту функцию.
5. Чтобы протестировать функцию, отредактируйте файл Test.sql, который Visual Studio автоматически добавила к проекту. Этот файл можно найти в папке Test Scripts в обозревателе Solution Explorer. В этом файле введите следующую инструкцию SELECT, которая использует функцию.

   SELECT CONVERT(nvarchar(7), OrderDate, 111) AS Period,
          dbo.WAVG(OrderDate) as Span 
     FROM Sales.SalesOrderHeader 
     GROUP BY CONVERT(nvarchar(7), OrderDate, 111)
6. Затем выберите команды Build (Построить) <ProjectName> из меню Build (Построение), а затем Start Debugging (Начать отладку) из меню Debug (Отладка), чтобы выполнить сценарий.

   Ниже вы видите фрагмент результирующего набора.

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

   Дата	Интервал
   2001/07	03:54:46.9565217
   2001/08	03:07:00.7792208
   2001/09	03:22:43.1067961
   2001/10	03:34:55.5223881
   2001/11	02:41:14.1312741
   2001/12	02:24:57.9865772
   2002/01	03:09:28.4210526
   2002/02	02:35:31.2000000
   2002/03	02:44:15.5133080
   2002/04	02:51:08.8524590
   2002/05	02:24:28.8963211
   2002/06	02:28:05.1063830
   2002/07	02:12:55.3846154
   2002/08	01:42:51.4285714
   2002/09	02:15:08.7378641
   2002/10	02:23:02.7814570
   2002/11	02:08:05.8895706
   ...	...

   Если внимательно посмотреть на процедуру Accumulate, то можно заметить, что она создает переменную TimeSpan при каждом своем выполнении, тогда как вычисленные значения хранит в переменной Ticks. Почему бы не использовать только переменную TimeSpan? Проблема заключается в том, что агрегатные функции CLR нуждаются в сериали-зации между вызовами. Способ, которым выполняется сериализация, определяется атрибутом в декларации функции. Например, посмотрим на следующую декларацию:

   <Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)> _ 
     Public Structure WAVG

   Аргумент Format устанавливает формат сериализации. В формате Native можно сериализовать только типы значений, но не ссылочные типы, такие, как классы CLR (в том числе, класс System.String ) или ваши пользовательские классы. В этом примере мы можем транслировать значение, которое нам нужно сохранить между вызовами, сохранив его представление в переменной Ticks с типом данных long. Однако если нужно использовать ссылочные типы, можно изменить аргумент Format на Format.UserDefined. Однако если такое изменение будет сделано, вам придется реализовать свой механизм сериализации. Дополнительную информацию о механизмах сериализации можно найти в Электронной документации по SQL Server 2005 в теме "Вызов определяемых пользователем агрегатных функций CLR".

   Полностью этот пример агрегатной функции CLR включен в файлы примеров этой лекции и размещен в папке WAVG.

Заключение

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

Краткий справочник по 1 лекции