基于DOI公共值统计出版社共现对数的SQL/R实现咨询

首先假设你存储数据的SQLite表名为doi_publisher，两个统计需求的SQL实现如下：

需求1：任意出版社配对在不同DOI下的共同出现频次 #

SELECT 
  t1.publishing_company AS publisher_a,
  t2.publishing_company AS publisher_b,
  COUNT(DISTINCT t1.article_doi_number) AS co_occurrence_count
FROM doi_publisher t1
INNER JOIN doi_publisher t2
  ON t1.article_doi_number = t2.article_doi_number
  AND t1.publishing_company < t2.publishing_company -- 避免重复配对，如A-B和B-A算同一组
GROUP BY t1.publishing_company, t2.publishing_company
ORDER BY co_occurrence_count DESC;

运行后你会得到elsevier和wiley and sons的共现频次为3，和你举的示例结果一致。

需求2：出版社配对在仅由二者出版的DOI下的共现频次 #

先筛选出仅包含2个出版社的DOI，再统计这些DOI下的出版社配对频次：

WITH doi_pub_count AS (
  -- 先统计每个DOI对应的出版社总数
  SELECT article_doi_number, COUNT(*) AS pub_total
  FROM doi_publisher
  GROUP BY article_doi_number
  HAVING pub_total = 2 -- 仅保留刚好有2个出版社的DOI
)
SELECT 
  t1.publishing_company AS publisher_a,
  t2.publishing_company AS publisher_b,
  COUNT(DISTINCT t1.article_doi_number) AS exclusive_co_count
FROM doi_publisher t1
INNER JOIN doi_publisher t2
  ON t1.article_doi_number = t2.article_doi_number
  AND t1.publishing_company < t2.publishing_company
INNER JOIN doi_pub_count c
  ON t1.article_doi_number = c.article_doi_number
GROUP BY t1.publishing_company, t2.publishing_company
ORDER BY exclusive_co_count DESC;

运行后你会得到harvard business review和proquest的共现频次为2，符合示例结果。

R语言实现方案（使用tidyverse套件） #

如果你的数据存储为数据框df，列名和需求一致，可参考以下代码：

library(tidyverse)

# 需求1实现
result1 <- df %>%
  inner_join(df, by = "article_doi_number", suffix = c("_a", "_b")) %>%
  filter(publishing_company_a < publishing_company_b) %>%
  group_by(publishing_company_a, publishing_company_b) %>%
  summarise(co_occurrence_count = n_distinct(article_doi_number), .groups = "drop") %>%
  arrange(desc(co_occurrence_count))

# 需求2实现
result2 <- df %>%
  group_by(article_doi_number) %>%
  filter(n() == 2) %>% # 仅保留刚好2个出版社的DOI
  ungroup() %>%
  inner_join(., ., by = "article_doi_number", suffix = c("_a", "_b")) %>%
  filter(publishing_company_a < publishing_company_b) %>%
  group_by(publishing_company_a, publishing_company_b) %>%
  summarise(exclusive_co_count = n_distinct(article_doi_number), .groups = "drop") %>%
  arrange(desc(exclusive_co_count))

内容的提问来源于stack exchange，提问作者killerstein