CODEWITHSUNDEEP
mysqlalgorithm
Tom Dawn
Tom Dawn

Reputation: 295

MySQL how to do permutation within one column and run a calculation?

Say I have a following table: table1

Now I want to have a permutation within cid column for each two ids (lets say id1=A and id2=B), and try to calculate the intersection of pid of A and B divided by the union of pid of A and B. For example, let A = cid 1 and B = cid 2, then the answer is intersection/union = 0/(1+1)=0 (notice B has purchased pid 3 for three times, but same product count only once).

The result should be like: result

There is going to be a permutation of any 2 different numbers in table1.cid, where smaller number goes before larger number, as well as the result of intersection/union. How can I write a query to perfome that?

Upvotes: 0

Views: 194

Answers (1)

yurib
yurib

Reputation: 8147

Both union and intersection can be achieved by joining the table with itself on the PID column:

SELECT a.cid a, b.cid b, COUNT(*) intersection
FROM table a
INNER JOIN table b
ON a.pid = b.pid AND a.cid < b.cid

if you use an OUTER join, that count(*) will return the size of the union.

you can execute both queries, then join on CIDs to have both intersection and union available for the computation:

SELECT a, b, intersection/union
FROM (<intersection query>) i
JOIN (<union query>) u
ON i.a = u.a AND i.b = u.b

You could of course optimize this a little bit since the inner join is a subset of the outer join, so there's no real need to perform both, but that's a matter for another question...

Upvotes: 0

Related Questions