Is there a PRODUCT in Sql Server? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Is there a PRODUCT in Sql Server?

Author

Topic

Tigerite
Starting Member

30 Posts

Posted - 2003-05-29 : 10:36:06

Similar to SUM, but obviously to multiply rows together?

My situation is I have a table such:


PK  |Id  |  Product

0   |8   |  0.2
1   |7   |  0.2016
2   |5   |  0.2032
3   |2   |  0.2048
4   |10  |  0.2063

For each Id I need to work out the product of the factors beneath it, then to add on (1 - Product) of itself.. so in this case:


Result:

PK  |Id  |  Product

0   |8   |  0.8 + 0.001731
1   |7   |  0.7984 + 0.008585
2   |5   |  0.7968 + 0.04225
3   |2   |  0.7952 + 0.2063
4   |10  |  0.7937 + 0

Can it be done? If so, how? :)

PS If it helps, there is a correlation between PK and the Product, it's equal to (100 + PK) / (500 + PK)

Edited by - tigerite on 05/29/2003 10:40:05

Tigerite
Starting Member

30 Posts

Posted - 2003-05-29 : 10:45:09

Agh I made a mistake, it's meant to be multiply (1 - Product) to itself.

So:


PK  |Id  |  Product

0   |8   |  0.8 x 0.001731
1   |7   |  0.7984 x 0.008585
2   |5   |  0.7968 x 0.04225
3   |2   |  0.7952 x 0.2063
4   |10  |  0.7937 x 1

Note the 1 at the end instead of a 0, as well.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-29 : 10:52:25

See the article on running totals on the main page.

Edited by - ValterBorges on 05/29/2003 10:55:07

Tigerite
Starting Member

30 Posts

Posted - 2003-05-29 : 11:02:40

I can't seem to find it, am I missing something, sorry if so?

Tigerite
Starting Member

30 Posts

Posted - 2003-05-29 : 11:11:18

Do you mean this one:

http://www.sqlteam.com/item.asp?ItemID=3856

? If so, it still uses SUMs which I can't use, because I need to do a PRODUCT type, unless I use a cursor, which I really wanted to avoid :(

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-29 : 11:11:46

Never mind you're trying to MULTIPLY that was about summing. Working on a solution stay tunned.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-29 : 11:28:56

I have a start but it's not working right yet but maybe someone can pick up where I left off I have to go for now.

DECLARE @Product TABLE (PK INT, Id INT, Product decimal(10,6))

INSERT INTO @Product
SELECT 0, 8, .2 UNION ALL
SELECT 1, 7, .2016 UNION ALL
SELECT 2, 5, .2032 UNION ALL
SELECT 3, 2, .2048 UNION ALL
SELECT 4, 10, .2063

SELECT A.*, B.*, CAST(B.Product * C.Product AS decimal(10,5)) As MULT
FROM @Product A CROSS JOIN @Product B CROSS JOIN @Product C
WHERE A.PK < B.PK AND B.PK < C.PK
ORDER BY A.PK

This might not even be possible with cross joins might have to use correlated sub-queries. It's an interesting problem a running multiplication.

Tigerite
Starting Member

30 Posts

Posted - 2003-05-29 : 11:29:48

I think I worked it out, this seems to work:


select pk, id, (1 - product) * coalesce((select exp(sum(log(product)))
                                           from @temp_table ttb
                                          where ttb.pk > tt.pk), 1) as product
  from @temp_table tt
 order by pk

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-29 : 11:48:35

There it is ladies and gentleman an excellent excellent example of the power of mathematics.

http://www.mathsdirect.co.uk/pure/purtutloglaw.htm

The "SUM of the LOGS is equal to the LOG of the products"

Very nice Tigerite.

Graz maybe you can add this to the running totals article.

Edited by - ValterBorges on 05/29/2003 11:50:06

Tigerite
Starting Member

30 Posts

Posted - 2003-05-29 : 12:02:34

Thanks :) I'm not sure if it would work for zeros or negative numbers mind you. However I think this one will, and so would be better for the article: (I must admit, I didn't come up with this one completely on my own, bits of it were taken from another query I found)


select pk, id, (1 - product) * 
  coalesce((select case sum(case when sign(product) = -1 then 1 else 0 end)%2 
                   when 1 then -1 else 1 end
                   * exp(sum(log(abs(case when sign(product) <> 0 then product else 1 end))))
                   * min(case when product = 0 then 0 else 1 end)
              from @temp_table ttb
             where ttb.pk > tt.pk), 1) as product
  from @temp_table tt
 order by pk

EDIT: Spotted a very minor mistake which returns NULL as result set if only have one product of 0, fixed

Edited by - tigerite on 05/29/2003 15:06:28

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-29 : 14:13:41

quote:


select pk, id, (1 - product) * 
  coalesce((select case sum(case when sign(product) = -1 then 1 else 0 end)%2 
                   when 1 then -1 else 1 end
                   * exp(sum(log(abs(case when sign(product) <> 0 then product end))))
                   * min(case when product = 0 then 0 else 1 end)
              from @temp_table ttb
             where ttb.pk > tt.pk), 1) as product
  from @temp_table tt
 order by pk

Wow....it's like a u2 spy plane over my head....

Brett

8-)

Tigerite
Starting Member

30 Posts

Posted - 2003-05-29 : 14:51:04

Heh, it's not that bad really, the first case is because multiplying two negatives gives a positive, so it checks to see how many negatives there are, then mod 2's it to get 0 or 1, the last one simply says if the product is 0 then the multiple must be 0 as well, and the middle one is the same as before, sum of logarithms, except with an abs and a check for if 0, because log of 0 or a negative would generate an error.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-30 : 13:42:32

Okay so now we know how to do running sums, running multiplications.

ln(x1.x2.x3...xn) = ln x1 + ln x2 + ln x3 + .... + ln xn
e(ln(x1.x2.x3...xn) = x1.x2.x3...xn
Running subtraction could be done using the SIGN function and SUM.

One we have this then running division can use the second law of logarithms.

ln(x1/x2/x3/.../xn) = ln x1 - ln x2 - ln x3 - .... - ln xn
e(ln(x1/x2/x3/.../xn) = x1/x2/x3/.../xn

And we can do subtraction in the following way

a-b-c = a+-b+-c

SELECT a.field,
SELECT(
a.field + SUM(-1*b.field)
from tablex b
WHERE b.fieldorder > a.fieldorder
)
from tablex

Can anyone think of a better way to implement a SUBTRACTION FUNCTION.

Edited by - ValterBorges on 05/30/2003 18:26:18

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-31 : 05:19:04

I'd have thought that in virtually all cases where you might want to do running division or subtraction, the first term would be distinguished sufficiently well that it would be easiest to do term1 - sum(other_terms) or term1 / product(other_terms).

Edited by - Arnold Fribble on 05/31/2003 05:20:02

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources