Novick Software
SQL Server Consulting Design Programming Tuning

  andy novick is a sql server mvp

 

 

A Note on SQL Aggregates

by Andrew Novick

In a recent article I showed how to create a User Aggregate function SQL Server 2005 using the .Net CLR.  Recently I got a note from Joe Celko about producing the aggregate with SQL instead of resorting to CLR programming.  Joe isn't enthusiastic about running non-SQL code inside of a relational engine and he explains some of the reasons in this article. When he read my article it responded with a way to create the Product aggregate that doesn't rely on using the CLR.  I wanted you to be able to read that and I've got another aggregate below that does a bit-wise OR operation.

Here are Joe Celko's remarks:

Here is a version of the aggregate product function in SQL. You will need to have the logarithm and exponential functions. They are not standards, but they are very common.

The idea is that there are three special cases - all positive numbers, one or more zeroes, and some negative numbers in the set. You can find out what your situation is with a quick test on the sign() of the minimum value in the set.

Within the case where you have negative numbers, there are two sub-cases: (1) an even number of negatives or (2) an odd number of negatives. You then need to apply some High School algebra to determine the sign of the final result.

SELECT CASE MIN (SIGN(nbr))
     WHEN 1 THEN EXP(SUM(LN(nbr))) -- all positive numbers
     WHEN 0 THEN 0.00 -- some zeroes
     WHEN -1 -- some negative numbers
    THEN (EXP(SUM(LN(ABS(nbr))))
                 * (CASE WHEN
                        MOD (SUM(ABS(SIGN(nbr)-1)/ 2)), 2) = 1
                              THEN -1.00 ELSE 1.00 END)
     ELSE NULL END AS big_pi
FROM NumberTable;

SELECT CASE MIN(ABS(SIGN(nbr)))
           WHEN 0 THEN 0.00 -- some zeroes
           ELSE -- no zeroes
                  EXP(SUM(LOG(ABS(NULLIF(nbr, 0)))))
                   * CASE WHEN MOD (CAST(SUM
                            ABS(SIGN(nbr)-1)/2) AS INTEGER), 2)
                       = 1
                       THEN -1.00 ELSE 1.00 END
            END AS big_pi

 

-- End of Joe Celko's remarks.

Microsoft's' Knowledge Base article Q89656: Simulating a PRODUCT() Aggregate Function also discusses how to achieve the Product aggregate functionality using the POWER function. 

Recently I had to simulate another aggregate function using SQL. This time the application is running SQL Server 7 and there's no prospect of an early upgrade to SQL Server 2005, much less 2000.  So here is a stored procedure that shows how to perform a bit-wise OR. The NewBits column is the bit-wise aggregate of the BITS column in the test data.

CREATE PROC ADHOC_ASN_TEST_FOR_BITWISE_OR AS  
/*
* Test/Demonstration of a bit-wise OR aggregation.  In the
* results, n is used to group rows in the data being aggregated.
* NewBits is the result where the column BITS has been
* aggregated.  B0, B1, ... through B7 are included for diagnosis.
*
* Example:
exec ADHOC_ASN_TEST_FOR_BITWISE_OR
****************************************************************/
SELECT N
        , CONVERT(VARBINARY(1), 
          CASE WHEN SUM(CASE WHEN 1 & BITS = 1
                         THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END  
        | CASE WHEN SUM(CASE WHEN 2 & BITS = 2
                         THEN 1 ELSE 0 END) > 0 THEN 2 ELSE 0 END  
        | CASE WHEN SUM(CASE WHEN 4 & BITS = 4
                         THEN 1 ELSE 0 END) > 0 THEN 4 ELSE 0 END  
        | CASE WHEN SUM(CASE WHEN 8 & BITS = 8
                         THEN 1 ELSE 0 END) > 0 THEN 8 ELSE 0 END  
        | CASE WHEN SUM(CASE WHEN 16 & BITS = 16
                         THEN 1 ELSE 0 END) > 0 THEN 16 ELSE 0 END  
        | CASE WHEN SUM(CASE WHEN 32 & BITS = 32
                         THEN 1 ELSE 0 END) > 0 THEN 32 ELSE 0 END  
        | CASE WHEN SUM(CASE WHEN 64 & BITS = 64
                         THEN 1 ELSE 0 END) > 0 THEN 64 ELSE 0 END  
        | CASE WHEN SUM(CASE WHEN 128 & BITS = 128
                         THEN 1 ELSE 0 END) > 0 THEN 128 ELSE 0 END  
            ) NewBits 
        , SUM(CASE WHEN 0X01 & BITS = 0X01 THEN 1 ELSE 0 END) B0 
        , SUM(CASE WHEN 0X02 & BITS = 0X02 THEN 1 ELSE 0 END) B1 
        , SUM(CASE WHEN 0X04 & BITS = 0X04 THEN 1 ELSE 0 END) B2 
        , SUM(CASE WHEN 0X08 & BITS = 0X08 THEN 1 ELSE 0 END) B3 
        , SUM(CASE WHEN 0X01 & BITS = 0X01 THEN 1 ELSE 0 END) B4 
        , SUM(CASE WHEN 0X02 & BITS = 0X02 THEN 1 ELSE 0 END) B5 
        , SUM(CASE WHEN 0X04 & BITS = 0X04 THEN 1 ELSE 0 END) B6 
        , SUM(CASE WHEN 0X08 & BITS = 0X08 THEN 1 ELSE 0 END) B7 

    FROM -- Test data for this example
                      SELECT 1 N,  CONVERT(TINYINT, 0X04) BITS 
            UNION ALL SELECT 1 N, CONVERT(TINYINT, 0XF8)  
            UNION ALL SELECT 2 N, CONVERT(TINYINT, 0X01)  
            UNION ALL SELECT 2 N, CONVERT(TINYINT, 0X41)    
         ) L 
    GROUP BY

Here are example results from running the stored procedure:

N   NewBits B0  B1  
			B2  B3  B4  B5  B6  B7 
--- ------- --- --- --- --- --- --- --- ---
  1 0xFC      0   0   1   1   0   0   1   1
  2 0x41      2   0   0   0   2   0   0   0
 

The expression in the NewBits column that must be reproduced in order to reuse this solution. While this works quite nicely, it does make obvious how difficult it might be to reuse the SQL version of these aggregates. 

Thanks to Joe Celko for his comments.

I'll be writing more about programming with the .Net CLR in SQL Server 2005 in coming months.  CLR programming will be the theme for my Coding-in-SQL Newsletter in  September and October so you might want to sign up.  Also in September I'm giving a presentation on the topic at Microsoft's Code Camp IV: Developers Gone Wild  September 24-25. If you're in the Boston area you might want to attend.


RSS as HTML

Personal Blog

 
New Tips:

Use dsinit to set the SQL Server instance for Windows Azure dev storage

Upcoming
Presentations:

SQL PASS
Nov 7, '12
Biggest
Loser: DB
Edition


Full Schedule