Tell me more ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

I am wondering if there is a better way to do this sql query it envolves multiple cross join and joins?

I have 3 tables, and want to compute cardinal product:

table N:
    N
    -
    4

table sums:
    i   S
    -----------
    1   22
    2   26
    3   22


table mults:
    i   j   M
    -------------------
    1   1   122
    1   2   144
    1   3   119
    2   1   144
    2   2   170
    2   3   141
    3   1   119
    3   2   141
    3   3   126

so here is a fiddle

SELECT  ((N.n*M1.m)-(S1.s*S2.s)) / (SQRT((N.n*M2.m)-(pow(S1.s,2))) * SQRT((N.n*M3.m)-(pow(S2.s,2))))
FROM N
CROSS JOIN sums as S1
CROSS JOIN sums as S2
JOIN mults as M1
     ON M1.i = S1.i
     AND M1.j = S2.i
JOIN mults as M2
     ON M2.i = S1.i
     AND M2.j = S1.i
JOIN mults as M3
     ON M3.i = S2.i
     AND M3.j = S2.i;
share|improve this question
Does it have to be done in SQL? I would suck the data out and do it in memory instead. There are some powerful matrix and numeric libraries out there. – Leonid Nov 17 '12 at 17:33
yeah, I know LAPACK for example but with sql queries? – cMinor Nov 17 '12 at 17:33
1  
I agreed with @Leonid. Using a database like this is likely to fall apart if you try to apply this approach on a little larger data sets, but another issue is that it looks so much like a math problem that you really should use a math tool just to make it easier to maintain and debug later. – Michael Zedeler Nov 18 '12 at 21:24

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.