Агрегирующее побитовое OR на T-SQL и в виде CLR-сборки

by Alexey Knyazev 21. сентября 2011 02:32

Агрегатные (статические) функции выполняют вычисление на наборе значений и возвращают одиночное значение. Статистические функции, за исключением 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.

Пример:

ИЛИ 0011
0101
= 0111



| (Побитовое ИЛИ) (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)

Tags: , ,

SQL Server

Комментарии (1) -

alexey knyazev
alexey knyazev Russia
01.04.2012 23:37:11 #

Ещё один вариант - недокументированная агрегирующая функция sys.ORMask

Reply

Добавить комментарий

  Country flag

biuquote
  • Комментарий
  • Предпросмотр
Loading