Aggregate Bitfield Values With Binary Or

I have a table with int values being used as bitfields (where each bit is a flag). Now I would like to aggregate them with a binary operation (in my case OR) so that: SELECT 1 AS b

Solution 1:

On MySQL and PostgreSQL you can use BIT_OR.

I don't think SQL Server has this aggregate function.

You could do it with lots of MAX and & as you said:

MAX(x & 1) + MAX(x & 2) + ... + MAX(x & 128)

Solution 2:

If you're expecting the result 171, surely you mean binary OR not AND?

In any case, this solution aggregates the values into a variable:

SELECT1AS bitfield
INTO #TABLEUNIONALLSELECT1+2+8+32UNIONALLSELECT2+128UNIONALLSELECT2+32DECLARE@iint=0SELECT@i=@i| bitfield
FROM #TABLESELECT@iDROPTABLE  #table

This might not meet your requirements if you want to group the aggregation by another field.

It is also unlikely to perform well on a large table.

Solution 3:

In MS SQL Server

DECLARE@aggVARCHAR(MAX) ='0001,0011,0101,0101,0101'SELECTCONVERT(binary(4), VALUE, 2) , VALUEFROM STRING_SPLIT( @agg , ',')

DECLARE@sumASBIGINT=0DECLARE@mulASBIGINT=0xffffffffSELECT@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')

Prints

VALUE----------------------0x0001000000010x0011000000110x0101000001010x0101000001010x0101000001010111000000010000

In more complex word you need:

CREATEORALTERFUNCTION dbo.BOR( @aggVARCHAR(MAX))
RETURNSBIGINTASBEGINDECLARE@sumASBIGINT=0SELECT@sum|=CONVERT(BIGINT, VALUE)
FROM STRING_SPLIT( @agg , ',')
RETURN@sumEND

GO
CREATEORALTERFUNCTION dbo.BAND( @aggVARCHAR(MAX))
RETURNSBIGINTASBEGINDECLARE@mulASBIGINT=0xffffffffffffffffSELECT@mul&=CONVERT(BIGINT, VALUE)
FROM STRING_SPLIT( @agg , ',')
RETURN@mulEND
GO

when using bitmap of payment periods

;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 ASBIGINT), '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
      GROUPBY cuno
   ) s
)
SELECT cuno
   ,  FORMAT( CAST(sum_months ASBIGINT), 'X16') sum_months
   ,  FORMAT( CAST(bor_months ASBIGINT), 'X16') bor_months
   ,  delayState
FROM delayPattern

cuno    sum_months          bor_months          delayState
12300000*10112*00000100000*10111*000001  dalyad 3+monthinrow45600000001000101100000000100010110NULL

But sometimes just one need to think and you can do it with SUM

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

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

Will print

cuno    bor_months  delayState
1230000010111000001    dalyad 3+monthinrow4560000000100010110NULL

NOTE: I am using HEX format and POWER(16.0, X) , just to be lazy, POWER(2.0, X) will be correct, but then you need bin->string formatter. Something like this:

CREATE OR ALTER FUNCTIONdbo.toBinaryString(@p INT)
RETURNSVARCHAR(24)
ASBEGINRETURNREVERSE(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