时间戳

首页 > 解决方案 > postgresql 查询视图：对于每个作者，列出他们的共同作者总数

sql - postgresql 查询视图：对于每个作者，列出他们的共同作者总数

问题描述

样本数据和预期结果 试图创建查询以计算每个作者的共同作者总数。

我现有的数据库表将有 2 列 bookid 及其各自的作者，每个 bookid 可能有多个作者，每个作者可能写不止一本书。

我想创建一个包含 2 列作者姓名和共同作者总数的视图

例如，如果 book1 有 author1,author2,author3 而 book2 有 author1,author2,author4，那么作者 1 的合著者总数为 3

注意不要重复计算同一作者。

这个还是新的，试图把它写成一张桌子，但一团糟。谢谢！

样本数据：

（分贝<>小提琴）

person_id   person_name   book_id
2           B             1
3           C             2
4           D             2
5           E             2
3           C             3
4           D             3
6           F             3
4           D             4
8           H             4
9           I             4
10          J             4

预期结果：

person_name   coauthors
B             0
C             3
D             6
E             2
F             2
H             3
I             3
J             3

ps：我有大约 2000+ 行作者和 bookid

标签： sqlpostgresql

解决方案

假设Books(author1, ... author4, bookid, ...)某些author*值可能为空的表。或者，辅助BookAuthors(bookid, author)表允许任意数量的作者，以及更简单的查询

这看起来很乱，但利用UNION(not UNION ALL) 做 implicit DISTINCT。

select first, count(distinct second)
from (select author1 first, author2 second from Books union
      select author1, author3 from Books union
      select author1, author4 from Books union
      select author2, author1 from Books union
      select author2, author3 from Books union
      select author2, author4 from Books union
      select author3, author1 from Books union
      select author3, author2 from Books union
      select author3, author4 from Books union
      select author4, author1 from Books union
      select author4, author2 from Books union
      select author4, author3 from Books
) coauthors where first is not null and second is not null

推荐阅读