Prev: sql commands for datediff
Next: Diferent times for execution the same code
From: --CELKO-- on 14 Jan 2010 20:37
Here is a version of the aggregate product function in SQL. You will
need to have the logarithm and exponential functions. The idea is that
there are three special cases - all positive numbers, one or more
zeros, 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 Foobar;

Or another way;

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

As an aside, the book BYPASSES:A SIMPLE APPROACH TO COMPLEXITY by Z.
A. Melzak (Wiley-Interscience, 1983, ISBN 0-471-86854-X), is short
mathematical book on the general principle of conjugacy. This is the
method of using a transform and its inverse to reduce the complexity
of a calculation.
From: Tom Cooper on 14 Jan 2010 21:08
Once again, you are ignoring the fact that this is a Microsoft SQL Server
usergroup. The correct function is LOG(). LN() is not a valid function in
Microsoft SQL Server.

Tom

"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:bcdeb277-41a4-4cf4-be73-85fc01a00c87(a)m26g2000yqb.googlegroups.com...
> Here is a version of the aggregate product function in SQL. You will
> need to have the logarithm and exponential functions. The idea is that
> there are three special cases - all positive numbers, one or more
> zeros, 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 Foobar;
>
> Or another way;
>
> 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
>
> As an aside, the book BYPASSES:A SIMPLE APPROACH TO COMPLEXITY by Z.
> A. Melzak (Wiley-Interscience, 1983, ISBN 0-471-86854-X), is short
> mathematical book on the general principle of conjugacy. This is the
> method of using a transform and its inverse to reduce the complexity
> of a calculation.

First  |  Prev  | 
Pages: 1 2 3
Prev: sql commands for datediff
Next: Diferent times for execution the same code