mysql如何大矩阵,在MYSQL，PHP或R中从mysql数据库制作共现矩阵

I have a mysql table that looks like this:

id uid

1 a

1 b

1 c

1 d

2 a

2 b

2 c

2 e

3 b

3 c

3 e

3 f

And I would like to group by id and make a co-occurrence matrix like this:

a,b,2 -because a and b appear together in 2 id-groups (in 1 and 2)

a,c,2 -because a and c appear together in 2 id-groups (in 1 and 2)

b,c,3 -because b and c appear together in 3 id groups (in 1, 2 and 3)

I am open to suggestions either in MYSQL queries, or using R or PHP.

解决方案

Group a self-join:

SELECT a.uid a, b.uid b, COUNT(*) cnt

FROM my_table a JOIN my_table b ON b.id = a.id AND b.uid > a.uid

GROUP BY a.uid, b.uid

See it on sqlfiddle.