Is there a way to improve this SQL query? It involves multiple cross join and joins.
I have 3 tables, and I want to compute the 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
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;