获取历史累计排名前五国家最近7天的总count值

要实现这个需求，我们可以拆成两个清晰的步骤来完成：先筛选出历史累计count排名前五的国家，再计算这些国家最近7天的总count。下面是具体的SQL实现和细节说明：

完整SQL语句（基于表内最大日期计算最近7天） #

WITH top_countries AS (
    -- 第一步：计算每个国家的历史累计总count，取排名前5的国家
    SELECT 
        country,
        SUM(count) AS total_historical_count
    FROM events
    GROUP BY country
    ORDER BY total_historical_count DESC
    LIMIT 5
)
-- 第二步：计算这5个国家最近7天的总count
SELECT 
    tc.country,
    tc.total_historical_count,
    SUM(e.count) AS last_7_days_total_count
FROM top_countries tc
INNER JOIN events e 
    ON tc.country = e.country
-- 用表中的最大日期来确定"最近7天"，避免当前日期超出数据覆盖范围
WHERE e.date >= (SELECT MAX(date) FROM events) - INTERVAL '7 days'
GROUP BY tc.country, tc.total_historical_count
ORDER BY tc.total_historical_count DESC;

关键细节说明 #

1. 历史累计排名逻辑：通过GROUP BY country和SUM(count)得到每个国家的历史总count，再按降序取前5，这一步用CTE（top_countries）封装，让逻辑更清晰易读。
2. 最近7天的定义：
   • 如果你的业务需要基于当前系统日期计算最近7天，把(SELECT MAX(date) FROM events)替换成CURRENT_DATE即可。
   • 用表内最大日期的好处是，当表中数据不是实时更新时，不会出现筛选出无数据日期范围的情况。
3. 处理无数据的情况：如果某个top5国家在最近7天没有任何记录，上面的INNER JOIN会自动排除它。如果需要保留这些国家并显示总count为0，可以改成LEFT JOIN，并使用COALESCE函数处理空值：

WITH top_countries AS (
    SELECT 
        country,
        SUM(count) AS total_historical_count
    FROM events
    GROUP BY country
    ORDER BY total_historical_count DESC
    LIMIT 5
)
SELECT 
    tc.country,
    tc.total_historical_count,
    COALESCE(SUM(e.count), 0) AS last_7_days_total_count
FROM top_countries tc
LEFT JOIN events e 
    ON tc.country = e.country
    AND e.date >= (SELECT MAX(date) FROM events) - INTERVAL '7 days'
GROUP BY tc.country, tc.total_historical_count
ORDER BY tc.total_historical_count DESC;

内容的提问来源于stack exchange，提问作者J. Ivanovic