Post reply

Aggregate bit operator in TSQL

  • December 21, 2008 at 10:38 pm

    #197489

    Hello All:

    Do we have any aggregate bit operator in TSql?

    Consider the following scenario

    I have a table as follows

    Col1 Col2 Col3

    1 1 0

    0 0 1

    1 0 0

    Now I want to perform a select on the above table such that I get a single row where each column is a OR of that column for all rows

    For e.g

    SELECT func(Col1), func(Col2), func(Col3)

    Output

    1, 1, 1

    Thanks,

    Vishnu

  • December 21, 2008 at 10:58 pm

    #915961

    There is no such function. However, you can emulate this with the following:

    Select

    Case When Sum(Cast(Col1 as int)) > 1 Then 1 Else 0 End

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 22, 2008 at 4:08 pm

    #916460

    Here's a real cheater method...

    SELECT SIGN(SUM(Col1+0)), SIGN(SUM(Col2+0)), SIGN(SUM(Col3+0))

    FROM SomeTable

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 22, 2008 at 4:29 pm

    #916467

    Yeah, I always forget that T-SQL has the SIGN() function.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 22, 2008 at 5:18 pm

    #916486

    Heh... and it works so well for these types of bitwise ops.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 22, 2008 at 7:04 pm

    #916516

    Well it's shorter than CASE, but not much beyond that.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 23, 2008 at 5:11 am

    #916691

    I agree... CASE is almost as fast and bit easier to read.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 23, 2008 at 7:41 am

    #916754

    And it's so much more general. The reason that I keep forgetting that SIGN() is in SQL is not because I am not familiar with it. Heck, I used it often as SGN() back in the 70's in both FORTRAN and BASIC. Rather, I am sure that it's the very generality of the CASE function that leads me to subliminally lump SIGN() inot the category of "things that I don't have to remember".

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 23, 2008 at 8:13 am

    #916777

    Jeff Moden (12/22/2008)

    Here's a real cheater method...

    SELECT SIGN(SUM(Col1+0)), SIGN(SUM(Col2+0)), SIGN(SUM(Col3+0))

    FROM SomeTable

    That only works for the first bit. I have a similar problem where we use the first 5 bits as "permissions" on objects and I need to OR the values from multiple rows. Unfortunately, I'm performing RBAR, albeit without a cursor (using SQL Server's multiple row select into a scalar variable approach), in a UDF. E.g.,

    SELECT @v_permissions = ISNULL(@v_permissions, 0) | x.permissions FROM ...

    Any T-SQL tricks? E.g., the following values need to be OR'd together with the result being (decimal) 31.

    [font="Courier New"]Bit Mask Decimal Value

    0000000001 1

    0000000101 5

    0000000111 7

    0000010001 17

    0000010011 19

    0000011101 29[/font]

    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • December 23, 2008 at 8:25 am

    #916792

    JohnG; Actually, we've been talking about the BIT datatype. I think that you are talking about bit-masks encoded into INT's.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 23, 2008 at 8:31 am

    #916797

    RBarryYoung (12/23/2008)

    JohnG; Actually, we've been talking about the BIT datatype. I think that you are talking about bit-masks encoded into INT's.

    Exactly.

    I will add that the use of SIGN needs to be in one's arsenal. I've used it quite often.

    P.S. We're both dating ourselves. I started with FORTRAN IV, then COBOL, among others and have been "flipping bits" for quite some time. Note my avatar.

    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • December 23, 2008 at 8:55 am

    #916828

    Heh, I started with half-adder circuit diagrams when I was 13. Then FORTRAN II, then original BASIC, then COBOL, etc...

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 23, 2008 at 5:33 pm

    #917166

    JohnG (12/23/2008)

    Jeff Moden (12/22/2008)

    Here's a real cheater method...

    SELECT SIGN(SUM(Col1+0)), SIGN(SUM(Col2+0)), SIGN(SUM(Col3+0))

    FROM SomeTable

    That only works for the first bit. I have a similar problem where we use the first 5 bits as "permissions" on objects and I need to OR the values from multiple rows. Unfortunately, I'm performing RBAR, albeit without a cursor (using SQL Server's multiple row select into a scalar variable approach), in a UDF. E.g.,

    SELECT @v_permissions = ISNULL(@v_permissions, 0) | x.permissions FROM ...

    Any T-SQL tricks? E.g., the following values need to be OR'd together with the result being (decimal) 31.

    [font="Courier New"]Bit Mask Decimal Value

    0000000001 1

    0000000101 5

    0000000111 7

    0000010001 17

    0000010011 19

    0000011101 29[/font]

    Yep... "pseudo-cursor"... just like you had...

    --===== Create the test data

    -- (NOT PART OF THE SOLUTION)

    DECLARE @SomeTable TABLE

    (BitMask INT)

    INSERT INTO @SomeTable

    (BitMask)

    SELECT 1 UNION ALL

    SELECT 5 UNION ALL

    SELECT 7 UNION ALL

    SELECT 17 UNION ALL

    SELECT 19 UNION ALL

    SELECT 29

    --===== OR it all together

    DECLARE @OredData INT

    SELECT @OredData = ISNULL(@OredData,0)|BitMask

    FROM @SomeTable

    --===== Display the result

    -- (NOT PART OF THE SOLUTION)

    SELECT @OredData

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 24, 2008 at 7:23 am

    #917378

    Jeff Moden (12/23/2008)

    Yep... "pseudo-cursor"... just like you had...

    Thanks, Jeff for taking the time to look at the problem.

    The solution implemented (pseudo-cursor) is working quite well in our application. But I always had this thought in the back of my mind that there was some algorithm or technique out there that would solve the problem using set-based logic. Glad to know that I can sleep better.

    Side note: Oracle provides the capability to write your own Aggregate Functions, using the ODCIAggregate interface routines, which could be used here to solve the problem. However, I didn't pursue it as it didn't have the ROI for the portable (SQL Server and Oracle) implementation and the cursor solution was performing well.

    [font="Tahoma"]User-defined aggregate functions are used in SQL DML statements just like Oracle’s own built-in aggregates. Once such functions are registered with the server, Oracle simply invokes the aggregation routines that you supplied instead of the native ones.[/font]

    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • December 24, 2008 at 8:51 am

    #917439

    JohnG (12/24/2008)

    The solution implemented (pseudo-cursor) is working quite well in our application. But I always had this thought in the back of my mind that there was some algorithm or technique out there that would solve the problem using set-based logic.

    Oh there is, but it is way ugly and not necessarily any faster (or even as fast).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply