如何在Redshift SQL中计算指定用户指定日期的7D-retention？

假设你的表名为user_visits，包含date（用户访问日期）和user_id（用户唯一标识）两个核心字段，需求是为每一行数据生成7D-retention字段：如果该用户在当前行日期的后7天内（即回访日期 > 当前日期，且回访日期 ≤ 当前日期+7天）有至少一次访问记录，标记为1，否则标记为0。

方案1：使用EXISTS子查询（兼容性最强） #

这是通用写法，几乎支持所有SQL数据库：

SELECT
    t1.date,
    t1.user_id,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM user_visits t2
            WHERE t2.user_id = t1.user_id
              -- 回访日期必须晚于当前行日期
              AND t2.date > t1.date
              -- 回访日期不超过当前日期+7天
              AND t2.date <= DATE_ADD(t1.date, INTERVAL 7 DAY)
        ) THEN 1
        ELSE 0
    END AS `7D-retention`
FROM user_visits t1
ORDER BY t1.user_id, t1.date;

逻辑说明：

通过自关联表检查当前用户的后续访问记录：

• 示例中2022-10-01的用户1，子查询会匹配到2022-10-05的记录（满足>2022-10-01且<=2022-10-08），因此返回1；
• 2022-10-05的用户1，子查询找不到该用户在2022-10-05至2022-10-12之间的访问记录，因此返回0。

方案2：使用窗口函数（大数据量更高效） #

如果你的数据库支持窗口函数（如PostgreSQL、BigQuery、MySQL 8.0+等），可以用窗口函数提升查询效率：

-- PostgreSQL/BigQuery版本
SELECT
    date,
    user_id,
    CASE
        WHEN MIN(date) OVER (
            PARTITION BY user_id
            ORDER BY date
            RANGE BETWEEN INTERVAL '1 day' FOLLOWING AND INTERVAL '7 days' FOLLOWING
        ) IS NOT NULL THEN 1
        ELSE 0
    END AS `7D-retention`
FROM user_visits;

-- MySQL 8.0+版本
SELECT
    date,
    user_id,
    CASE
        WHEN MIN(date) OVER (
            PARTITION BY user_id
            ORDER BY UNIX_TIMESTAMP(date)
            RANGE BETWEEN 86400 FOLLOWING AND 604800 FOLLOWING
        ) IS NOT NULL THEN 1
        ELSE 0
    END AS `7D-retention`
FROM user_visits;

逻辑说明：

按用户分组、日期排序，窗口范围限定为当前日期之后1天到7天，只要这个范围内存在访问日期，就标记为1。

注意事项 #

1. 确保date字段是日期类型，如果是字符串格式，需要先转换（比如MySQL用STR_TO_DATE(date, '%Y-%m-%d')）；
2. 如果需要将“当天重复访问”算作回访，可调整条件为t2.date >= t1.date，并通过主键（如id）排除当前行：AND (t2.date > t1.date OR (t2.date = t1.date AND t2.id != t1.id))。

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