PostgreSQL:如何从标签数组生成边缘列表？

我有一些看起来如下的数据:

id, tags
1,{'A', 'B', 'C', 'D'}
2,{'A', 'C', 'D'}
3,{'A'}
4,{'B', 'D'}
5,{'A', 'D'}
6,{'D'}
7,{'D'}

我的目标是将其转换为边缘列表(或共现)表,如下所示:

tag1,tag2,count
'A', 'A', 1
'A', 'B', 1
'A', 'c', 2
'A', 'D', 3
'B', 'C', 1
'B', 'D', 2
'C', 'D', 2
'D', 'D', 2

请注意上表('A', 'A', 1&'D', 'D', 2)中的第一行和最后一行是因为A只出现在其中,而D出现两次 - 因此它们是自连接的.

如何使用PostgreSQL 9.3有效地完成这项工作？我有超过350K的标签和190万个文件.

样本数据:

create table tags( 
id int
,tagList text[]
);

insert into tags values (1,ARRAY['A', 'B', 'C', 'D']);
insert into tags values (2,ARRAY['A', 'C', 'D']);
insert into tags values (3,ARRAY['A']);
insert into tags values (4,ARRAY['B', 'D']);
insert into tags values (5,ARRAY['A', 'D']);
insert into tags values (6,ARRAY['D']);
insert into tags values (7,ARRAY['D']);

我尝试过的:

select a.tag, b.tag, count(*)
from
(select id, unnest(taglist) as tag
from  tags
) as a
inner join 
(select id, unnest(taglist) as tag
from  tags
) as b
on a.id = b.id and a.tag !=b.tag
group by a.tag, b.tag
order by a.tag, b.tag

哪个产生:

tag tag count
A   B   1
A   C   2
A   D   3
B   A   1
B   C   1
B   D   2
C   A   2
C   B   1
C   D   2
D   A   3
D   B   2
D   C   2

现在缺少在上面的表格是:它认为A->B与B->A作为独立的-我不希望这样的事情发生(我认为这里的术语,我与无向图的工作),另一件事是:它缺少自我连接顶点.即'A < - > A'和'D < - > D' - 我想这是因为a.tag!=b.tagjoin语句中的条件.

SQL小提琴演示

PS:我的数据集也很长,即每行一个标签,因此每个文档(id)​​可以分布在很多行上.