sql - sql查询从其中其他值列相同的一列中提取值

我有一个数据集
论文作者：

paper_id author_id
   1      521630
   1      972575
   1      1528710
   1      1611750
   2      1682088
   2      1589667
   2      972575
   3      521630
   3      1589667

我需要得到所有作者的共同作者信息，这些作者和一个作者一起写同一篇论文，然后按作者分组

author_id  co_authors
 521630     972575,1528710,1611750,1589667
 972575     521630,1528710,1611750,1589667
 .......

在第一行中，作者id=521630，作者id=97257515287101611750，作者id=1589667，写了论文3，我写了一个R代码

co_author_id<-vector()
for(i in 1:length(paper_author))
{
  author_id_data<-paper_author[i,2]
  index1<-which(paper_author$author_id %in% author_id_data
  paper_ids<-paper_author$paper_id[index1]
  index2<-which(paper_author$paper_id %in% paper_ids)
  co_authors<-paper_author$author_id[index2]
  co_author_id[i]<-paste(co_authors,collapse=" ")
 }

但这是非常低效的，因为数据大小是1200万行，因此使用sql将非常简单和方便
谢谢

最佳答案

我就是这样理解你的问题的。SQL Fiddle

select
    pa1.author_id,
    array_agg(pa2.author_id order by pa2.author_id) as co_author
from
    paper_author pa1
    left join
    paper_author pa2 on
        pa1.paper_id = pa2.paper_id
        and pa1.author_id != pa2.author_id
group by pa1.author_id
order by pa1.author_id