在MYSQL，PHP或R中从mysql数据库制作共现矩阵 [英] Making a co-occurrence matrix from mysql database in MYSQL, PHP or 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

我想按ID分组并创建一个共现矩阵，如下所示:

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)

我愿意在MYSQL查询中或者使用R或PHP提出建议.

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

推荐答案

对自联接进行分组:

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

在 sqlfiddle 上查看.

这篇关于在MYSQL，PHP或R中从mysql数据库制作共现矩阵的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！