如何基于start_date等字段判断用户活跃状态并按规则赋值

实现方案 #

你可以用窗口函数LAG获取同用户上个月的active值，再按逻辑计算目标字段即可，参考SQL适配大部分支持窗口函数的数据库（MySQL8.0+、PostgreSQL、Hive、Spark SQL等）：

SELECT 
    start_date,
    client_id,
    active,
    CASE 
        WHEN last_month_active = 1 AND active = 0 THEN 1
        ELSE 0
    END AS target_value
FROM (
    SELECT 
        *,
        -- 按用户分组、按日期排序，取上一条记录的active值（即上个月的active状态）
        LAG(active, 1) OVER (PARTITION BY client_id ORDER BY start_date ASC) AS last_month_active
    FROM 你的表名
) t
-- 过滤仅保留2021年6月1日及之后的记录
WHERE start_date >= STR_TO_DATE('06.01.2021', '%m.%d.%Y')

注意事项 #

• 如果你的start_date已经是日期类型，WHERE条件可直接写start_date >= '2021-06-01'
• 如果同一用户同一个月存在多条记录，需要先按client_id和月份维度聚合去重，保证每个用户每个月仅对应一条active记录后再执行上述逻辑，避免取数错误
• 如果用的是不支持窗口函数的低版本数据库，可以用自关联的方式实现相同逻辑，参考如下：

SELECT 
    t1.start_date,
    t1.client_id,
    t1.active,
    CASE WHEN t2.active = 1 AND t1.active = 0 THEN 1 ELSE 0 END AS target_value
FROM 你的表名 t1
LEFT JOIN 你的表名 t2 
    ON t1.client_id = t2.client_id
    -- 关联上个月的记录，根据你的日期格式调整减法逻辑
    AND PERIOD_DIFF(DATE_FORMAT(t1.start_date, '%Y%m'), DATE_FORMAT(t2.start_date, '%Y%m')) = 1
WHERE t1.start_date >= STR_TO_DATE('06.01.2021', '%m.%d.%Y')

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