Redshift数据库中使用SQL计算用户多个活跃日期间的最长不活跃周期

实现逻辑 #

• 用LAG()窗口函数按用户ID分区，按活跃日期升序排序，获取同用户上一次的活跃日期
• 调用Redshift内置DATEDIFF()函数计算相邻两次活跃的间隔天数
• 按用户ID分组取间隔天数的最大值，即为该用户的最长不活跃周期

可用SQL语句 #

假设你的源表名为user_active_log，字段为id（用户ID）、DATE（活跃日期，因是关键字使用时需用双引号包裹），代码如下：

WITH activity_with_prev_date AS (
    SELECT
        id,
        "DATE",
        LAG("DATE") OVER (PARTITION BY id ORDER BY "DATE") AS last_active_date
    FROM user_active_log
)
SELECT
    id AS "ID",
    MAX(DATEDIFF(day, last_active_date, "DATE")) AS "Maximum inactivity (in days)"
FROM activity_with_prev_date
WHERE last_active_date IS NOT NULL
GROUP BY id
ORDER BY id;

补充说明 #

如果你的业务需要保留仅产生过1次活跃记录的用户，可以去掉WHERE last_active_date IS NOT NULL过滤条件，同时用COALESCE函数给这类用户设置默认值，比如COALESCE(MAX(DATEDIFF(day, last_active_date, "DATE")), 0)代表无相邻活跃的用户最长不活跃周期记为0。

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