如何使用窗口函数计算分城市的30日活跃用户基数？

针对你遇到的窗口函数不支持COUNT(DISTINCT)、CTE求和无法跨月统计的问题，这里提供两种通用且可行的解决方案：

方案一：非等值关联（通用SQL，适配多数数据库） #

核心思路是先构建城市+日期的完整维度表，再通过非等值关联匹配每个统计日期过去30天内的所有客户，最后按维度分组统计唯一客户数。

代码示例（PostgreSQL版本） #

WITH date_range AS (
    -- 生成2014-01-01至2014-06-30的所有日期
    SELECT generate_series('2014-01-01'::DATE, '2014-06-30'::DATE, '1 day'::INTERVAL)::DATE AS dt
),
city_dates AS (
    -- 生成每个城市与所有统计日期的组合（确保无日期遗漏）
    SELECT DISTINCT c.cityname, dr.dt AS stat_date
    FROM clients c
    CROSS JOIN date_range dr
)
SELECT 
    cd.cityname,
    cd.stat_date,
    COUNT(DISTINCT cl.clientid) AS rolling_30d_active_users
FROM city_dates cd
LEFT JOIN clients cl 
    ON cd.cityname = cl.cityname
    AND cl.date::DATE BETWEEN cd.stat_date - INTERVAL '29 days' AND cd.stat_date
-- 限定原表数据范围，避免关联无关数据
WHERE cl.date BETWEEN '2014-01-01' AND '2014-06-30'
GROUP BY cd.cityname, cd.stat_date
ORDER BY cd.cityname, cd.stat_date;

适配MySQL的日期生成语法 #

如果使用MySQL，替换date_range部分为递归CTE：

WITH RECURSIVE date_range AS (
    SELECT '2014-01-01' AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_range
    WHERE dt < '2014-06-30'
)

方案二：窗口函数+条件聚合（适配支持窗口函数的数据库） #

通过先去重每日客户活跃记录，再用窗口函数标记客户是否在统计窗口内，最后通过条件聚合实现“去重计数”，规避窗口函数不支持COUNT(DISTINCT)的限制。

代码示例 #

WITH client_daily AS (
    -- 去重：每个客户每日在同一城市仅保留一条记录
    SELECT DISTINCT 
        cityname, 
        date::DATE AS active_date, 
        clientid
    FROM clients
    WHERE date BETWEEN '2014-01-01' AND '2014-06-30'
),
date_range AS (
    SELECT generate_series('2014-01-01'::DATE, '2014-06-30'::DATE, '1 day'::INTERVAL)::DATE AS stat_date
),
city_dates AS (
    SELECT DISTINCT c.cityname, dr.stat_date
    FROM client_daily c
    CROSS JOIN date_range dr
),
client_window AS (
    SELECT 
        cd.cityname,
        cd.stat_date,
        cl.clientid,
        -- 判断客户活跃日期是否在当前统计日期的30天窗口内
        CASE WHEN cl.active_date BETWEEN cd.stat_date - INTERVAL '29 days' AND cd.stat_date THEN 1 ELSE 0 END AS is_in_window,
        -- 标记该客户在当前统计窗口内的首次活跃记录（用于去重）
        ROW_NUMBER() OVER (PARTITION BY cd.cityname, cd.stat_date, cl.clientid ORDER BY cl.active_date) AS rn
    FROM city_dates cd
    LEFT JOIN client_daily cl ON cd.cityname = cl.cityname
)
SELECT 
    cityname,
    stat_date,
    SUM(CASE WHEN is_in_window = 1 AND rn = 1 THEN 1 ELSE 0 END) AS rolling_30d_active_users
FROM client_window
GROUP BY cityname, stat_date
ORDER BY cityname, stat_date;

优化建议 #

• 若数据量较大，建议给clients表建立(cityname, date, clientid)联合索引，大幅提升非等值关联的查询性能。
• 部分数据库（如BigQuery、Snowflake）支持更简洁的滑动窗口语法，可直接使用COUNT(DISTINCT clientid) OVER (PARTITION BY cityname ORDER BY date RANGE BETWEEN INTERVAL 29 DAY PRECEDING AND CURRENT ROW)，但需确认数据库兼容性。

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