Ms sql битовые операции: Побитовые операторы (Transact-SQL) — SQL Server

Если биты в любой позиции являются обоими 1, результатом будет 1.

Если любой бит в любой позиции равен 1, результатом будет 1.

Меняет значение бита в обратном направлении в каждой позиции бита. = (присваивание побитового исключающего ИЛИ)

В SQL Server 2022 г. (16.x) появились следующие побитовые операторы:

• >> (SHIFT ВПРАВО)
• << (Shift влево)

Операнды для побитовых операторов могут быть любым из типов данных категорий типа данных integer или binary string (за исключением типа данных image ), за исключением того, что оба операнда не могут быть ни одним из типов данных категории типов данных двоичной строки. Следующая таблица показывает поддерживаемые типы данных операндов.

• Операторы (Transact-SQL)
• Типы данных (Transact-SQL)
• Составные операторы (Transact-SQL)

SQL.

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

Агрегатные (статические) функции выполняют вычисление на наборе значений и возвращают одиночное значение. Статистические функции, за исключением COUNT, не учитывают значения NULL. Статистические функции часто используются в выражении GROUP BY инструкции SELECT.

Все статистические функции являются детерминированными. Это означает, что статистические функции возвращают одну и ту же величину при каждом их вызове на одном и том же наборе входных значений. Дополнительные сведения о детерминизме функций см. в разделе Детерминированные и недетерминированные функции. Предложение OVER может следовать за всеми статистическими функциями, кроме CHECKSUM.

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

• Список выбора инструкции SELECT (вложенный или внешний запрос).
• Предложение COMPUTE или COMPUTE BY.
• Предложение HAVING.

Transact-SQL предоставляет следующие статистические функции:

• AVG
• CHECKSUM_AGG
• COUNT
• COUNT_BIG
• GROUPING
• MAX
• MIN
• SUM
• STDEV
• STDEVP
• VAR
• VARP

К сожалению это весь список, но что делать, когда нам нужна агрегатная функция, которой нет в 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)

побитовых операторов (Transact-SQL) — SQL Server

Редактировать

Твиттер

LinkedIn

Фейсбук

Электронная почта

• Статья
• 18.11.2022

Применяется к: SQL Server Azure SQL База данных Azure SQL Управляемый экземпляр Azure Synapse Analytics Analytics Platform System (PDW)

Побитовые операторы выполняют битовые манипуляции между двумя выражениями любого из типов данных категории целочисленных типов данных.

Побитовые операторы преобразуют два целых значения в двоичные биты, выполняют операцию И , ИЛИ или НЕ для каждого бита, получая результат. Затем преобразует результат в целое число.

Например, целое число 170 преобразуется в двоичное число 1010 1010 .

Целое число 75 преобразуется в двоичное число 0100 1011 .

В следующих статьях содержится дополнительная информация о побитовых операторах, доступных в компоненте Database Engine:

• & (Побитовое И)
• &= (побитовое И присваивание)
• | (Побитовое ИЛИ) 9= (Побитовое исключающее ИЛИ)
• ~ (побитовое НЕ)

Следующие побитовые операторы были введены в SQL Server 2022 (16.x):

• >> (сдвиг вправо)
• << (Сдвиг влево)

Операнды для побитовых операций могут быть любым из типов данных целочисленных или двоичных строковых типов данных (за исключением типа данных изображение ), за исключением того, что оба операнда не могут быть ни одним из типов данных категории категория двоичных строковых типов данных. В следующей таблице показаны поддерживаемые типы данных операндов.

• Операторы (Transact-SQL)
• Типы данных (Transact-SQL)
• Составные операторы (Transact-SQL)

Манипуляции с битами

— SQL What is &

спросил
3 года, 2 месяца назад

Изменено
3 года, 2 месяца назад

Просмотрено
633 раза

Я хочу знать функцию оператора и . Например:

 ВЫБЕРИТЕ (8 и 16)
 

В последнем коде, если я изменю второе значение ( 16 ) на другое, например 10 , результат изменится.

Читал об этом, но точного ответа не нашел.

Спасибо за помощь.

• sql
• битовые манипуляции
• побитовые операторы
• побитовые и

3

Для SQL Server вы можете найти ответ здесь https://learn.microsoft.com/en-us/sql/t-sql/language-elements/bitwise-and-transact-sql?view=sql-server-ver15

Побитовый оператор & выполняет побитовое логическое И между двумя выражениями, беря каждый соответствующий бит для обоих выражений. Биты в результате устанавливаются в 1 тогда и только тогда, когда оба бита (для текущего разрешаемого бита) во входных выражениях имеют значение 1; в противном случае бит в результате устанавливается на 0,

Если левое и правое выражения имеют разные целочисленные типы данных (например, левое выражение — smallint, а правое — int), аргумент меньшего типа данных преобразуется в больший тип данных.

Назад: Основные языки: Самые распространенные языки мира — Коммерсантъ

Вперед: Сменить пароль в виндовс 10: Как изменить пароль в Windows 10