需求说明 #

我有一个名为logins的表，包含user_id和login_date两列。需要按照以下规则计算2017、2018、2019和2020年的活跃用户数量：

对于任意日期，如果某个user_id在过去90天内至少有1次登录，则该user_id在当日被视为活跃用户。例如，若某个user_id的login_date为2017-01-01，那么该用户在2017-01-01至2017-04-01的每一天都被视为活跃用户；若该用户在2017-02-01另有一次登录，则其活跃期将延续至2017-02-01至2017-05-01的每一天。

示例输入数据 #

+-----------+------------+
| user_id   | login_date |
+-----------+------------+
| 0000000   | 2017-01-01 |
| 0000000   | 2017-02-01 |
| 0000001   | 2017-01-02 |
+-----------+------------+

我尝试的错误逻辑 #

WITH all_missions AS (
 SELECT user_id,
 format_datetime(login_date, 'yyyy-MM') AS first_active_date,
 format_datetime(date_add('month', 1, login_date), 'yyyy-MM-dd') AS active_m1,
 format_datetime(date_add('month', 2, login_date), 'yyyy-MM-dd') AS active_m2,
 format_datetime(date_add('month', 3, login_date), 'yyyy-MM-dd') AS active_m3
 FROM logins
),
active_months AS (
 SELECT l.user_id, t2.active_month, t2.month_number
 FROM logins l
 CROSS JOIN unnest (
 array['active_m1', 'active_m2', 'active_m3'],
 array[active_m1, active_m2, active_m3]
 ) t2 (active_month, month_number)
),
SELECT substring(month_number, 1, 4) AS year_number,
 count(DISTINCT user_id) AS num_active_users
FROM active_months
GROUP BY 1

先分析你之前思路的问题 #

你的方法存在两个核心问题：

1. 用整月划分活跃期不准确：活跃期是连续的90天，不是整月。比如用户在2017-03-15登录，活跃期到2017-06-13，这覆盖了3月的后半段、4月、5月和6月的前半段，但你的方法只生成了4、5、6月的记录，会漏掉用户在3月的活跃天数，导致2017年的统计可能少算该用户；同时如果活跃期跨年份（比如2017-12-15登录，活跃期到2018-03-15），你的方法只会把用户计入2018年，漏掉2017年的活跃天数。
2. 未合并重叠活跃区间：同一个用户多次登录会生成重复的活跃月份记录，虽然最后用count(DISTINCT)能去重，但逻辑上不够严谨，且无法准确处理跨期的情况。

正确的SQL实现 #

我们可以通过合并用户的活跃区间，再和目标年份的时间范围做交集判断，来准确统计每个年份的活跃用户：

WITH user_login_intervals AS (
    -- 为每条登录记录生成对应的90天活跃区间
    SELECT 
        user_id,
        login_date AS start_date,
        date_add(login_date, 90) AS end_date
    FROM logins
),
merged_intervals AS (
    -- 标记用户的活跃区间是否需要合并（处理重叠/连续的情况）
    SELECT 
        user_id,
        start_date,
        -- 计算当前及之前所有区间的最大结束日期
        MAX(end_date) OVER (
            PARTITION BY user_id 
            ORDER BY start_date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS current_end,
        -- 判断当前区间是否是新的独立区间
        CASE 
            WHEN start_date <= LAG(MAX(end_date) OVER (PARTITION BY user_id ORDER BY start_date)) OVER (PARTITION BY user_id ORDER BY start_date)
            THEN 0
            ELSE 1
        END AS is_new_interval
    FROM user_login_intervals
    GROUP BY user_id, start_date
),
final_intervals AS (
    -- 合并重叠/连续的活跃区间，得到用户的最终活跃时间段
    SELECT 
        user_id,
        MIN(start_date) AS interval_start,
        MAX(current_end) AS interval_end
    FROM (
        SELECT 
            *,
            -- 为每个用户的活跃区间分组
            SUM(is_new_interval) OVER (PARTITION BY user_id ORDER BY start_date) AS interval_group
        FROM merged_intervals
    ) t
    GROUP BY user_id, interval_group
),
target_years AS (
    -- 定义需要统计的4个年份的时间范围
    SELECT 
        '2017' AS year,
        DATE '2017-01-01' AS year_start,
        DATE '2017-12-31' AS year_end
    UNION ALL
    SELECT 
        '2018' AS year,
        DATE '2018-01-01' AS year_start,
        DATE '2018-12-31' AS year_end
    UNION ALL
    SELECT 
        '2019' AS year,
        DATE '2019-01-01' AS year_start,
        DATE '2019-12-31' AS year_end
    UNION ALL
    SELECT 
        '2020' AS year,
        DATE '2020-01-01' AS year_start,
        DATE '2020-12-31' AS year_end
)
-- 统计每个年份的活跃用户：只要用户的活跃区间和年份有交集，就计入该年份
SELECT 
    ty.year,
    COUNT(DISTINCT fi.user_id) AS num_active_users
FROM target_years ty
LEFT JOIN final_intervals fi
ON fi.interval_start <= ty.year_end 
AND fi.interval_end >= ty.year_start
GROUP BY ty.year
ORDER BY ty.year;

逻辑解释 #

1. 生成基础活跃区间：为每条登录记录生成login_date到login_date+90天的区间。
2. 合并重叠区间：通过窗口函数合并用户所有重叠或连续的活跃区间，避免重复统计。比如用户在2017-01-01和2017-02-01登录，合并后的活跃区间是2017-01-01到2017-05-01。
3. 定义目标年份：明确2017-2020每个年份的起始和结束日期。
4. 交集判断统计：判断用户的活跃区间是否和目标年份有重叠，只要有重叠，该用户就属于该年份的活跃用户，最后按年份去重计数。

这个方法能准确处理所有边界情况，包括跨年份的活跃期，以及用户多次登录的重叠活跃区间。

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