如何在PostgreSQL中获取时间序列各日期的符合条件活跃用户数？

哈喽～你已经搞定了单日期的活跃用户统计，现在要扩展成覆盖所有日期的时间序列结果对吧？咱们来一步步调整查询逻辑，完美解决这个需求！

首先明确下核心需求：对每个日期，统计在该日期过去30天内，有至少10个不同访问日期的用户数量。

原查询的局限 #

你现有的单日期查询逻辑是对的，但要批量处理所有日期，关键是要先生成需要统计的日期序列，再给每个日期套用你的筛选规则。

两种高效实现方案 #

方案一：生成日期序列 + 关联统计 #

先生成所有有访问记录的日期（也可以指定固定日期范围），再对每个日期计算符合条件的用户数：

WITH date_series AS (
    -- 生成visit表中所有出现过的日期，若需覆盖无访问的日期，可改用generate_series指定范围
    SELECT DISTINCT time::date AS stat_date
    FROM visit
    ORDER BY stat_date
),
user_daily_visits AS (
    -- 先去重：同一用户同一天的多次访问只算一次
    SELECT user_id, time::date AS visit_date
    FROM visit
    GROUP BY user_id, time::date
)
SELECT 
    ds.stat_date AS date,
    COUNT(DISTINCT udv.user_id) AS quantity
FROM date_series ds
LEFT JOIN user_daily_visits udv 
    ON udv.visit_date BETWEEN ds.stat_date - INTERVAL '30 days' AND ds.stat_date
GROUP BY ds.stat_date
-- 筛选出窗口内访问天数≥10的用户
HAVING COUNT(DISTINCT udv.visit_date) OVER (PARTITION BY udv.user_id) >= 10
ORDER BY ds.stat_date;

方案二：窗口函数 + 滚动统计 #

利用窗口函数预先计算每个用户在滚动30天内的访问日期数，再关联到统计日期，效率更高：

WITH user_rolling_stats AS (
    SELECT 
        user_id,
        visit_date,
        -- 计算当前用户在过去30天内的不同访问日期数量
        COUNT(DISTINCT visit_date) OVER (
            PARTITION BY user_id 
            ORDER BY visit_date 
            RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW
        ) AS rolling_days
    FROM (
        -- 先去重用户每日访问记录
        SELECT DISTINCT user_id, time::date AS visit_date
        FROM visit
    ) t
),
date_series AS (
    SELECT DISTINCT time::date AS stat_date
    FROM visit
    ORDER BY stat_date
)
SELECT 
    ds.stat_date AS date,
    COUNT(DISTINCT urs.user_id) AS quantity
FROM date_series ds
LEFT JOIN user_rolling_stats urs 
    ON urs.visit_date <= ds.stat_date 
    AND urs.visit_date >= ds.stat_date - INTERVAL '30 days'
    AND urs.rolling_days >= 10
GROUP BY ds.stat_date
ORDER BY ds.stat_date;

关键细节说明 #

• 日期序列生成：如果需要覆盖没有访问记录的日期（比如某一天没人访问但也要显示0），可以把date_series改成用generate_series指定固定范围，比如：

  SELECT generate_series('2018-05-01'::date, '2018-06-30'::date, '1 day') AS stat_date
  

• 去重处理：同一用户同一天可能有多次访问，必须先通过GROUP BY或DISTINCT合并成单条记录，否则会导致访问日期数统计错误。
• 滚动窗口逻辑：核心是对每个用户，统计其在目标日期的过去30天内的不同访问天数，筛选出≥10的用户后，再按统计日期汇总数量。

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