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

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

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

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

  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 лекции
Чтобы Выполните следующие действия
Подсчитать записи в таблице SELECT COUNT(*) FROM <Table_Name>
Подсчитать количество записей, в которых значения в ячейках не равны 0 SELECT COUNT (<Field_Name>) FROM <Table_name>
Подсчитать количество записей, соответствующих определенному условию SELECT COUNT (*) FROM <Table_Name> WHERE <condition>
Подсчитать записи с одинаковыми значениями в одном из полей SELECT <Field_Name>, COUNT(*) FROM <Table_Name> GROUP BY <Field_Name>
Суммировать значения в столбце SELECT SUM(<Field_name>) FROM <Table_Name>
Получить наименьшее значение в столбце SELECT MIN(<Field_Name>) FROM <Table_Name>
Получить наибольшее значение в столбце SELECT MAX(<Field_Name>) FROM <Table_Name>
Получить среднее для значений в столбце SELECT AVG(<Field_Name>) FROM <Table_Name>
Получить промежуточные суммы и итоговые суммы для значений SELECT <Field_Name>, <FUNCTION_NAME>(<Field_Name>) FROM <Table_Name> GROUP BY <Field_Name> WITH ROLLUP
Получить результаты только для тех значений, которые не повторяются SELECT <FUNCTION_NAME> (DISTINCT <Field_Name>) FROM <Table_Name>
Определить свою агрегатную функцию Создайте агрегатную функцию CLR в Visual Studio
Лекция 1: 12345 || Лекция 2 >
Гаральд Егоркин
Гаральд Егоркин
Россия
Павел Шелякин
Павел Шелякин
Россия