Рассмотрим задачу, в которой для таблицы с группами строк требуется найти максимальное по модулю значение вместе со знаком найденного элемента.
Пример:
Требуется написать запрос для получения таких результатов:
Чтобы получить такой результат максимально удобным способом, используя один запрос, создадим clr-агрегирующую функцию. Ниже представлен ее код.
В структуре имеется ряд методов. Методы Init и Terminate отвечают за первоначальную инициализацию и возврат значения для группы соответственно. Логика инкапсулирована в методе Accumulate. В случае если вычисления в группе распараллеливаются на несколько потоков, их результаты надо в конце собрать в одно значение. Этой цели служит метод Merge. Развернем сборку и создадим пользовательскую агрегирующую функцию:
Метод по созданию собственных агрегирующих функций необычайно полезен. Он дает возможность реализовать самые разнообразные вычисления в группе: конкатенацию строк, произведения чисел, битовые операции. Можно добавить логику для вычленения отдельных членов в группе и многое другое.
Пример:
if object_id ( N'tempdb..#data', N'U' ) is not null
begin
drop table #data
end
go
create table #data
(
iGrpId int not null,
iRowId int not null,
iAttr float not null,
primary key clustered ( iGrpId asc, iRowId asc ) on [primary]
) on [primary]
go
insert into #data
(
iGrpId,
iRowId,
iAttr
)
values
( 1, 1, 20 ),
( 1, 2, -20 ),
( 1, 3, 30 ),
( 2, 1, 10 ),
( 2, 2, 20 ),
( 2, 3, -10 ),
( 2, 4, -30 )
go
Чтобы получить такой результат максимально удобным способом, используя один запрос, создадим clr-агрегирующую функцию. Ниже представлен ее код.
using
System;
using
System.Data;
using
System.Data.SqlClient;
using
System.Data.SqlTypes;
using
Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregateAttribute
(
Microsoft.SqlServer.Server.Format.Native,
IsInvariantToNulls = true,
IsInvariantToDuplicates = false,
IsInvariantToOrder = false
)]
public
struct AbsOperations
{
private SqlDouble dMaxVal;
private bool bNullExists;
private bool bAllNull;
public void Init()
{
dMaxVal = 0;
bNullExists = false;
bAllNull = false;
}
public void Accumulate(SqlDouble
Value)
{
if
(Value.IsNull)
{
bNullExists = true;
}
else
{
double
dMaxVal_ = double.Parse(dMaxVal.ToString()),
Value_ = double.Parse(Value.ToString());
bNullExists = false;
if(Math.Abs(dMaxVal_) < Math.Abs(Value_))
{
dMaxVal = Value;
}
}
}
public void Merge(AbsOperations
grp)
{
if
(grp.dMaxVal.IsNull)
{
if
(grp.bNullExists)
{
bNullExists = true;
}
if
(!grp.bAllNull)
{
bAllNull = false;
}
double
dMaxVal_ = double.Parse(dMaxVal.ToString()),
Value = double.Parse(grp.dMaxVal.ToString());
if
(Math.Abs(dMaxVal_) < Math.Abs(Value))
{
dMaxVal = grp.dMaxVal;
}
}
}
public SqlDouble Terminate()
{
if
(bAllNull)
{
return
SqlDouble.Null;
}
return dMaxVal;
}
}В структуре имеется ряд методов. Методы Init и Terminate отвечают за первоначальную инициализацию и возврат значения для группы соответственно. Логика инкапсулирована в методе Accumulate. В случае если вычисления в группе распараллеливаются на несколько потоков, их результаты надо в конце собрать в одно значение. Этой цели служит метод Merge. Развернем сборку и создадим пользовательскую агрегирующую функцию:
if
not exists
(
select *
from sys.assemblies
where name = N'AbsOperations'
)
begin
create assembly AbsOperations
from
'C:\В\Программирование\Проекты\tmp\SqlServerProject4\bin\Debug\SqlServerProject4.dll'
with
permission_set =
safe
end
go
if
object_id ( N'dbo.GetMaxAbs', N'AF' ) is null
begin
create aggregate dbo.GetMaxAbs ( @fRow float )
returns float
external name AbsOperations.AbsOperations
end
go
Теперь можно написать запрос для решения задачи;
select
iGrpId, AgrgrAttr = dbo.GetMaxAbs ( iAttr )
from #data
group by iGrpIdМетод по созданию собственных агрегирующих функций необычайно полезен. Он дает возможность реализовать самые разнообразные вычисления в группе: конкатенацию строк, произведения чисел, битовые операции. Можно добавить логику для вычленения отдельных членов в группе и многое другое.
