Агрегатные (статические) функции выполняют вычисление на наборе значений и возвращают одиночное значение. Статистические функции, за исключением COUNT, не учитывают значения NULL. Статистические функции часто используются в выражении GROUP BY инструкции SELECT.
Все статистические функции являются детерминированными. Это означает, что статистические функции возвращают одну и ту же величину при каждом их вызове на одном и том же наборе входных значений. Дополнительные сведения о детерминизме функций см. в разделе Детерминированные и недетерминированные функции. Предложение OVER может следовать за всеми статистическими функциями, кроме CHECKSUM.
Статистические функции могут быть использованы в качестве выражений только в следующих случаях.
- Список выбора инструкции SELECT (вложенный или внешний запрос).
- Предложение COMPUTE или COMPUTE BY.
- Предложение HAVING.
Transact-SQL предоставляет следующие статистические функции:
К сожалению это весь список, но что делать, когда нам нужна агрегатная функция, которой нет в T-SQL? В этой статье я покажу, как создать свою агрегатную функцию на примере побитового OR (Побитовое ИЛИ). Варианты решения будут в виде классического t-sql и в виде CLR-сборки. Кроме демонстрации скриптов, проведу небольшие замеры и сравнения производительности вариантов на t-sql и clr.
Побитовое ИЛИ (OR) — это бинарная операция, действие которой эквивалентно применению логического ИЛИ к каждой паре битов, которые стоят на одинаковых позициях в двоичных представлениях операндов. Другими словами, если оба соответствующих бита операндов равны 0, двоичный разряд результата равен 0; если же хотя бы один бит из пары равен 1, двоичный разряд результата равен 1.
Пример:
| (Побитовое ИЛИ) (Transact-SQL)
Выполняет поразрядную логическую операцию OR для двух указанных целочисленных значений, которые преобразуются в двоичные выражения в инструкциях Transact-SQL.
Проблема возникает, когда нужна агрегатная функция побитового ИЛИ. Стандартной агрегатной функции нет, поэтому будем писать свой скрипт. Для демонстрации создадим таблицу с тестовыми данными.
--Создадим тестовую таблицу
if object_id ( 'dbo.TestTable', 'U' ) is not null
drop table dbo.TestTable
go
create table dbo.TestTable ( id int identity, group_id int, bitwise int )
go
--Сгенерим данные: 1 млн. групп, в каждой группе по 3 значения, над которыми мы и будем выполнять агрегирующее ИЛИ
with bitwise
as
(
select 0 as bit
union all
select bit + 1 from bitwise b
where bit < 100
), temp_groups
as
(
select 0 as group_id
union all
select group_id + 1 from temp_groups b
where group_id < 1000000
)
insert into dbo.TestTable
select group_id, bit
from temp_groups t
cross apply ( select top (3) bit
from bitwise
where bit != group_id
order by newid ()
) ca
option ( maxrecursion 0 )
--Добавим индекс
if exists ( select * from sys.indexes
where object_id = object_id ( 'dbo.TestTable', 'U' )
and name = 'cind_gr'
)
drop index dbo.TestTable.cind_gr
go
create clustered index cind_gr on dbo.TestTable ( group_id )
go
А теперь 2 варианта на T-SQL, как можно выполнить агрегирующее побитовое ИЛИ.
Вариант 1. Используем PIVOT, для выстраивания списка полей в строку, а дальше используем стандартную операцию побитового или |
select group_id
, isnull( ( select isnull( [1], 0 ) | isnull( [2], 0 ) | isnull( [4] ,0 )
| isnull( [8] ,0 ) | isnull( [16], 0 )
| isnull( [32], 0 ) | isnull( [64], 0 ) | isnull( [128] ,0 )
| isnull( [256] ,0 ) | isnull( [512], 0 )
| isnull( [1024], 0 ) | isnull( [2048], 0 ) | isnull( [4096] ,0 )
| isnull( [8192] ,0 ) | isnull( [16384], 0 )
| isnull( [32768], 0 )
from ( select dgr.i
from ( select bitwise i
from dbo.TestTable where group_id = t.group_id
) dsc
inner join ( values (1),(2),(4),(8),(16),(32),(64),(128),(256),(512),(1024),(2048),(4096),(8192),(16384),(32768) ) dgr(i)
on dsc.i & dgr.i = dgr.i
) a(i)
pivot ( max(i) for i in ( [1],[2],[4],[8],[16],[32],[64],[128],[256],[512],[1024],[2048],[4096],[8192],[16384],[32768] ) ) pv
), 0 ) as AggOr
from dbo.TestTable t
group by group_id
order by group_id
Вариант 2. Небольшая хитрость в виде стандартной агрегатной функции MAX и побитовой операции И (&)
select group_id
, max( bitwise&32768 ) + max( bitwise&16384 ) + max( bitwise&8192 ) + max( bitwise&4096 )
+ max( bitwise&2048 ) + max( bitwise&1024 ) + max( bitwise&512 ) + max( bitwise&256 )
+ max( bitwise&128 ) + max( bitwise&64 ) + max( bitwise&32 ) + max( bitwise&16 )
+ max( bitwise&8 ) + max( bitwise&4 ) + max( bitwise&2 ) + max( bitwise&1 ) as AggOR
from dbo.TestTable
group by group_id
order by group_id
Вариант 3. Создадим свою агрегатную функцию CLR
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct AggOR
{
private SqlInt32 AggDiscounts;
public void Init()
{
AggDiscounts = 0;
}
public void Accumulate(SqlInt32 Value)
{
AggDiscounts |= Value;
}
public void Merge(AggOR Group)
{
AggDiscounts |= Group.AggDiscounts;
}
public SqlInt32 Terminate()
{
return AggDiscounts;
}
}
Подключаем сборку:
exec sp_configure 'clr enabled', 1
reconfigure
go
alter database TestDB set Trustworthy on
go
if object_id( 'dbo.AggOr', 'AF' ) is not null
drop aggregate AggOr
go
if exists ( select * from sys.assemblies
where name = 'AggOr_Assemblie' )
drop assembly AggOr_Assemblie
go
create assembly AggOr_Assemblie
from 'C:\temp\AggOR.dll'
go
create aggregate dbo.AggOr ( @bitwise int )
returns int
external name AggOr_Assemblie.AggOR
go
Ну и собственно сам вариант работы с нашей новой агрегатной функцией:
select group_id
, dbo.AggOr (bitwise) as AggOR
from dbo.TestTable
group by group_id
order by group_id
А теперь несколько обещанных сравнений этих трёх запросов:
План запроса 1
План запроса 2
План запроса 3
Дальше больше, теперь общая
статистика по времени и по IO:
Статистика запроса 1
Статистика запроса 2
Статистика запроса 3
Как видно из статистик, 1 запрос самый тяжёлый и по времени и по количеству чтений, 2 и 3 запросы выглядят на много лучше, но у 3-его время работы самое лучшее, хоть он и более требовательный к CPU.
ЗЫ: Выкладываю CLR-сборку агрегатной функции побитового ИЛИ - AggOR.dll (4,00 kb)