求SQL查询语句：计算期刊历年发文量平均值

我的数据库包含journal_title、article_title、publish_year三个字段。我已经写出了如下SQL查询语句并得到对应结果：

SELECT journal_title, publish_year, COUNT(article_title) AS total_article 
FROM dumm_table 
GROUP BY journal_title, publish_year 
ORDER BY journal_title, publish_year

（结果截图：）

现在需要编写SQL查询，计算单本期刊历年发文量的平均值。示例：

• 期刊"Biophysical chemistry"在9年（1991、1992、1994、1995、1996、1997、1998、1999、2007）共发文315篇，平均值为315/9=35；
• 期刊"Biophysical journal"在2年（2006、2007）共发文54篇，平均值为54/2=27。

请帮忙编写对应SQL查询语句，感谢！

你可以基于已有的分组查询结果，通过嵌套查询或CTE来计算每本期刊的总发文量和有发文记录的年份数，进而得到平均值。以下是两种实用写法：

写法一：嵌套子查询 #

SELECT 
    journal_title,
    SUM(total_article) AS total_all_articles,
    COUNT(DISTINCT publish_year) AS total_years,
    ROUND(SUM(total_article) / COUNT(DISTINCT publish_year), 2) AS avg_articles_per_year
FROM (
    -- 复用你已有的按期刊+年份分组的查询
    SELECT 
        journal_title,
        publish_year,
        COUNT(article_title) AS total_article 
    FROM dumm_table 
    GROUP BY journal_title, publish_year
) AS yearly_article_counts
GROUP BY journal_title
ORDER BY journal_title;

写法二：CTE公共表表达式（可读性更高） #

WITH yearly_article_counts AS (
    -- 先得到每本期刊每年的发文量
    SELECT 
        journal_title,
        publish_year,
        COUNT(article_title) AS total_article 
    FROM dumm_table 
    GROUP BY journal_title, publish_year
)
SELECT 
    journal_title,
    SUM(total_article) AS total_all_articles,
    COUNT(DISTINCT publish_year) AS total_years,
    ROUND(SUM(total_article) / COUNT(DISTINCT publish_year), 2) AS avg_articles_per_year
FROM yearly_article_counts
GROUP BY journal_title
ORDER BY journal_title;

关键说明： #

• 内层查询/CTE部分就是你已经写好的逻辑，先按期刊和年份分组得到每年的发文量；
• 外层查询按期刊分组，SUM(total_article)计算该期刊的总发文量，COUNT(DISTINCT publish_year)统计该期刊有发文的年份数（和你示例中的统计逻辑一致，只算有发文的年份）；
• ROUND(..., 2)用于将平均值保留两位小数，你可以根据需求调整小数位数，或者去掉该函数保留原始精度；
• 最终结果会按期刊名称排序，方便查看。

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