如何编写按国家分组的3天滚动日期范围去重计数SQL查询

我需要编写一个查询，统计每个国家在3天滚动日期范围内的去重邮箱数量。参考现有滚动日期去重计数的方案修改后报错，无法实现按国家拆分的统计需求。

示例输入表 #

预期输出结果 #

错误原因 #

你写的SQL报错核心有两点：

1. 你用来生成日期序列的派生表g里没有country字段，直接SELECT和GROUP BY引用country自然会识别失败
2. 子查询里没有加国家维度的过滤条件，就算拿到了country字段，统计的也是全国家的去重数，不是分国家的结果

修复方案 #

方案1：兼容旧版本PostgreSQL的子查询写法 #

先拿到日期和所有国家的笛卡尔积作为统计维度，再关联计算每个维度下的滚动去重数：

SELECT 
    g.date,
    c.country,
    (
        SELECT COUNT(DISTINCT email)
        FROM tbl
        WHERE 
            date BETWEEN g.date - 2 AND g.date
            AND country = c.country
    ) AS dist_emails
FROM 
    (SELECT generate_series(timestamp '2012-01-01', timestamp '2012-01-04', interval '1 day')::date AS date) AS g
CROSS JOIN 
    (SELECT DISTINCT country FROM tbl) AS c
-- 过滤无数据的组合，和预期输出对齐
WHERE EXISTS (
    SELECT 1 FROM tbl 
    WHERE country = c.country 
    AND date BETWEEN g.date -2 AND g.date
)
ORDER BY g.date, c.country;

方案2：PostgreSQL 11+ 支持的窗口函数写法（性能更优） #

WITH daily_unique AS (
    -- 先按天、国家、邮箱去重，减少后续计算量
    SELECT DISTINCT date, country, email FROM tbl
)
SELECT
    date,
    country,
    COUNT(DISTINCT email) OVER (
        PARTITION BY country
        ORDER BY date
        RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW
    ) AS dist_emails
FROM daily_unique
ORDER BY date, country;

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