Обединете стойностите на битовите полета с двоично ИЛИ

Имам таблица с int стойности, използвани като битови полета (където всеки бит е флаг).

Сега бих искал да ги агрегирам с двоична операция (в моя случай ИЛИ), така че:

SELECT 1 AS bitfield
INTO #TABLE
UNION ALL SELECT 1 + 2 + 8 + 32
UNION ALL SELECT 2 + 128
UNION ALL SELECT 2 + 32

SELECT AND_AGGR(bitfield) -- Invalid because AND_AGGR doesn't exist
FROM #TABLE

DROP #TABLE

ще доведе до стойността 171

Какъв би бил добър начин да направите това, който, надяваме се, не изисква много | и MAX (но ако трябва, трябва)?

Самият аз използвам MS SQL Server 2008, но решенията на други сървъри също представляват интерес.


sql-server aggregate-functions bit-manipulation bit-fields
person ANisus    schedule 19.04.2012    source източник
comment
Сега бих искал да ги агрегирам с двоична операция (в моя случай И). Сигурни ли сте, че имате предвид И, а не ИЛИ?   -  person Mark Byers    schedule 19.04.2012
comment
Да, разбира се. ще го сменя. Благодаря, че го посочи :)   -  person ANisus    schedule 19.04.2012

Отговори (3)


arrow_upward
1
arrow_downward

Ако очаквате резултата 171, със сигурност имате предвид двоичен OR, а не AND?

Във всеки случай това решение агрегира стойностите в променлива:

SELECT 1 AS bitfield
INTO #TABLE
UNION ALL SELECT 1 + 2 + 8 + 32
UNION ALL SELECT 2 + 128
UNION ALL SELECT 2 + 32

DECLARE @i int = 0

SELECT @i = @i | bitfield
FROM #TABLE

SELECT @i

DROP TABLE  #table

Това може да не отговаря на вашите изисквания, ако искате да групирате обобщението по друго поле.

Освен това е малко вероятно да се представи добре на голяма маса.

person Ed Harper    schedule 19.04.2012
comment
В моя пример можете да използвате това решение. В реалния случай обаче искам да групирам по някои други полета, но предполагам, че решението MAX(bitfield | 1) + ..., дадено от Марк Байърс, ще работи в този случай. - person ANisus; 19.04.2012

arrow_upward
2
arrow_downward

В MySQL и PostgreSQL можете да използвате BIT_OR< /a>.

Не мисля, че SQL Server има тази обобщена функция.

Можете да го направите с много MAX и &, както казахте:

MAX(x & 1) + MAX(x & 2) + ... + MAX(x & 128)
person Mark Byers    schedule 19.04.2012
comment
Благодаря за отговора! А, значи просто имаме грешно решение за база данни в този единствен случай. Но благодаря за отговора, тъй като помогна да докажа подозренията си. - person ANisus; 19.04.2012

arrow_upward
0
arrow_downward

В MS SQL Server

DECLARE @agg  VARCHAR(MAX) = '0001,0011,0101,0101,0101'
SELECT CONVERT(binary(4), VALUE, 2) , VALUE  FROM STRING_SPLIT( @agg , ',')

DECLARE @sum AS BIGINT = 0
DECLARE @mul AS BIGINT = 0xffffffff
SELECT  @sum |= v
   , @mul &= v
FROM STRING_SPLIT( @agg , ',')
CROSS APPLY (VALUES (CONVERT(binary(4), VALUE, 2))) _(v)

PRINT FORMAT(@sum,'X8')
PRINT FORMAT(@mul,'X8')

щампи

            VALUE
---------- ------------
0x00010000 0001
0x00110000 0011
0x01010000 0101
0x01010000 0101
0x01010000 0101

01110000
00010000

В по-сложна дума ви трябва:

CREATE OR ALTER FUNCTION dbo.BOR( @agg VARCHAR(MAX))
RETURNS BIGINT
AS 
BEGIN
DECLARE @sum AS BIGINT = 0
SELECT  @sum |= CONVERT(BIGINT, VALUE)
FROM STRING_SPLIT( @agg , ',')
RETURN @sum
END

GO
CREATE OR ALTER FUNCTION dbo.BAND( @agg VARCHAR(MAX))
RETURNS BIGINT
AS 
BEGIN
DECLARE @mul AS BIGINT = 0xffffffffffffffff
SELECT  @mul &= CONVERT(BIGINT, VALUE)
FROM STRING_SPLIT( @agg , ',')
RETURN @mul
END
GO

при използване на растерна карта на периодите на плащане

;WITH delayedPayment AS
(SELECT * FROM ( VALUES 
    ( 123, 67, '2020-2-1')
   ,( 123, 67, '2020-4-1')
   ,( 123, 67, '2020-5-1')
   ,( 123, 67, '2020-6-1')
   ,( 123, 68, '2020-6-1')  -- another agreement
   ,( 123, 67, '2020-12-1')
           
   ,( 456, 69, '2020-4-1')
   ,( 456, 69, '2020-8-1')
   ,( 456, 69, '2020-10-1')
   ,( 456, 69, '2020-11-1')) _(cuno, loan, missedDuedate)
)
, delayPattern AS
(SELECT cuno
   ,  sum_months
   ,  bor_months
   ,  IIF( FORMAT( CAST(bor_months AS BIGINT), 'X16') LIKE '%111%', 'dalyad 3+ month in row', NULL) delayState
   FROM (SELECT cuno
         , SUM(POWER( 16.0, CONVERT( BIGINT, DATEDIFF( month, missedDuedate, '2020-12-1')))) sum_months
         , dbo.BOR( STRING_AGG( CONVERT( BIGINT, POWER( 16.0, DATEDIFF( month, missedDuedate, '2020-12-1'))),',')) bor_months
      FROM delayedPayment
      GROUP BY cuno
   ) s
)
SELECT cuno
   ,  FORMAT( CAST(sum_months AS BIGINT), 'X16') sum_months
   ,  FORMAT( CAST(bor_months AS BIGINT), 'X16') bor_months
   ,  delayState
FROM delayPattern

cuno    sum_months          bor_months          delayState
123     00000*10112*000001  00000*10111*000001  dalyad 3+ month in row
456     0000000100010110    0000000100010110    NULL

Но понякога просто трябва да помислите и можете да го направите със SUM

, delayPattern AS -- optimal
(SELECT cuno
   ,  bor_months
   ,  IIF( FORMAT( CAST(bor_months AS BIGINT), 'X16') LIKE '%111%', 'dalyad 3+ month in row', NULL) delayState
   FROM (SELECT cuno
         , SUM(POWER( 16.0, missedmonth)) bor_months
      FROM ( SELECT DISTINCT cuno
               , missedmonth
            FROM delayedPayment
            CROSS APPLY (VALUES ( DATEDIFF( month, missedDuedate, '2020-12-1'))) _(missedmonth)
            GROUP BY cuno, missedmonth
            ) ss
      GROUP BY cuno
   ) s
)

SELECT cuno
   ,  FORMAT( CAST(bor_months AS BIGINT), 'X16') bor_months
   ,  delayState
FROM delayPattern

Ще се отпечата

cuno    bor_months  delayState
123 0000010111000001    dalyad 3+ month in row
456 0000000100010110    NULL

ЗАБЕЛЕЖКА: Използвам HEX формат и POWER(16.0, X), само за да бъда мързелив, POWER(2.0, X) ще бъде правилен, но тогава имате нужда от bin-›форматиране на низове. Нещо като това:

CREATE OR ALTER FUNCTION dbo.toBinaryString(@p INT)
RETURNS VARCHAR(24)
AS 
BEGIN 
RETURN  REVERSE(REPLACE( REPLACE( 
    REPLACE( REPLACE( REPLACE( REPLACE( 
    REPLACE( REPLACE( REPLACE( REPLACE( 
    REPLACE( REPLACE( REPLACE( REPLACE( 
    REPLACE( REPLACE( REPLACE( REPLACE( FORMAT(@p,'X8'), 
        '0', '....'), '1', '...x'),'2', '..x.'),'3', '..xx'),
        '4', '.x..'), '5', '.x.x'),'6', '.xx.'),'7', '.xxx'),
        '8', 'x...'), '9', 'x..x'),'A', 'x.x.'),'B', 'x.xx'),
        'C', 'xx..'), 'D', 'xx.x'),'E', 'xxx.'),'F', 'xxxx'),
        '.','0'),'x','1'))
END
person Kalju Pärn    schedule 27.06.2021