|
|
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 N
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.
|
|
