如何在90天窗口内仅筛选注册超90天用户的collect_set结果？

我明白你的需求了——你需要针对每个日期，在它往前推90天的回溯窗口里，只收集那些注册时间早于该日期90天前的去重用户ID。之前的查询没达到预期，核心问题是你在窗口函数里的case判断只对单日数据做了过滤，而没有针对整个窗口内的用户做统一的注册时间校验。

问题根源拆解 #

你之前的写法是在窗口的每一行数据里单独判断user_created_at < date - interval 90 day，但窗口是聚合90天内的所有用户数据。这会导致：只要某个用户在窗口内的某一天符合这个条件，就会被加入集合，但实际上我们需要的是这个用户的注册时间本身要早于当前日期的90天前，不管他们在窗口内哪一天出现。

正确解决方案 #

这里提供两种可行的写法，适配不同的SQL引擎场景：

方案一：预处理标记+窗口聚合

先给每条记录标记用户是否符合注册时间要求，再在窗口内收集符合条件的用户ID：

WITH user_validity AS (
    SELECT 
        date,
        feature,
        user_id,
        user_created_at,
        -- 标记当前用户在该日期下是否满足注册时间条件
        CASE WHEN user_created_at < date - INTERVAL 90 DAY THEN 1 ELSE 0 END AS is_valid
    FROM your_table
)
SELECT 
    date,
    feature,
    -- 在90天窗口内仅收集标记为有效用户的去重ID
    collect_set(CASE WHEN is_valid = 1 THEN user_id END) OVER (
        PARTITION BY feature
        ORDER BY CAST(timestamp(date) AS FLOAT)
        RANGE BETWEEN (-90*60*60*24) FOLLOWING AND 0 PRECEDING
    ) AS valid_user_ids
FROM user_validity

方案二：先聚合窗口用户再筛选注册时间

如果你的SQL引擎支持LATERAL VIEW explode（比如Hive、Spark SQL），可以先获取窗口内的所有用户，再关联注册信息筛选：

WITH window_users AS (
    SELECT 
        date,
        feature,
        -- 先收集90天窗口内的所有去重用户ID
        collect_set(user_id) OVER (
            PARTITION BY feature
            ORDER BY CAST(timestamp(date) AS FLOAT)
            RANGE BETWEEN (-90*60*60*24) FOLLOWING AND 0 PRECEDING
        ) AS window_user_ids
    FROM your_table
    GROUP BY date, feature -- 提前去重减少计算量
),
user_reg_info AS (
    SELECT DISTINCT user_id, user_created_at FROM your_table -- 提取唯一的用户注册信息
)
SELECT 
    w.date,
    w.feature,
    -- 筛选出窗口内注册时间早于当前日期90天前的用户
    collect_set(u.user_id) AS valid_user_ids
FROM window_users w
LATERAL VIEW explode(w.window_user_ids) exploded AS user_id
LEFT JOIN user_reg_info u ON exploded.user_id = u.user_id
WHERE u.user_created_at < w.date - INTERVAL 90 DAY
GROUP BY w.date, w.feature

注意事项 #

• 确保date和user_created_at是一致的日期/时间类型，避免因类型转换导致的判断错误；
• 如果你的原始表存在大量重复的date+feature+user_id记录，建议先做去重处理，能大幅提升窗口聚合的性能；
• 部分SQL引擎对窗口函数的RANGE区间支持可能有差异，若遇到问题可以尝试用ROWS结合日期函数来实现90天窗口（比如date_add(date, -90)）。

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