如何在MySQL中计算去重值计数的7日滚动平均值？

现有如下数据表（示例数据）：

date       user_city 
2000-01-01 amsterdam
2000-01-01 copenhagen
2000-01-01 amsterdam
2000-01-01 vienna
2000-01-01 prague
2000-01-02 vienna
2000-01-02 amsterdam
2000-01-02 tokio
2000-01-03 copenhagen
2000-01-03 london
2000-01-03 prague
2000-01-03 amsterdam
...

需要计算user_city去重后的日计数的7日滚动平均值（例如2000-01-01的日计数为4，因当天amsterdam重复出现，去重后共4个不同城市）。

适配示例的3日滚动平均预期结果（供参考）：

window_start_date avg
2001-01-01        3.6666
2001-01-04        ?
2001-01-07        ?

已用Python实现该逻辑，代码如下：

df = original_table.groupby("date")["user_city"].nunique()
df = df.reset_index()
df["rolling_avg"] = df["user_city"].rolling(7).mean()

求对应的MySQL查询语句。

1. 先计算每日去重城市数 #

先按日期分组，统计每天的唯一城市数量，对应Python代码中的groupby("date")["user_city"].nunique()逻辑：

SELECT 
    date,
    COUNT(DISTINCT user_city) AS daily_unique_cities
FROM 
    original_table
GROUP BY 
    date
ORDER BY 
    date;

2. 基于日统计结果计算7日滚动平均 #

使用MySQL窗口函数AVG()结合时间范围窗口，实现滚动平均。窗口范围包含当前日期及往前推6天的记录（共7天）：

WITH daily_city_counts AS (
    SELECT 
        date,
        COUNT(DISTINCT user_city) AS daily_unique_cities
    FROM 
        original_table
    GROUP BY 
        date
)
SELECT 
    date AS window_start_date,
    AVG(daily_unique_cities) OVER (
        ORDER BY date
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
    ) AS avg_7day_rolling
FROM 
    daily_city_counts
ORDER BY 
    date;

补充：处理日期不连续的场景 #

如果数据表存在日期缺失（比如某天无任何记录），上述查询会跳过这些日期。若需将缺失日期的城市数计为0并纳入滚动计算，可先生成连续日期序列再左连接统计结果：

-- 生成连续日期序列（需根据实际数据的起止日期调整）
WITH date_series AS (
    SELECT ADDDATE('2000-01-01', INTERVAL seq DAY) AS date
    FROM (
        SELECT 0 seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
        UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 -- 可扩展更多日期
    ) seq_table
    WHERE ADDDATE('2000-01-01', INTERVAL seq DAY) <= (SELECT MAX(date) FROM original_table)
),
daily_city_counts AS (
    SELECT 
        date,
        COUNT(DISTINCT user_city) AS daily_unique_cities
    FROM 
        original_table
    GROUP BY 
        date
)
SELECT 
    ds.date AS window_start_date,
    AVG(COALESCE(dcc.daily_unique_cities, 0)) OVER (
        ORDER BY ds.date
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
    ) AS avg_7day_rolling
FROM 
    date_series ds
LEFT JOIN 
    daily_city_counts dcc ON ds.date = dcc.date
ORDER BY 
    ds.date;

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