SQL中按账户分区，基于日期间隔生成递增reactivated列

核心思路 #

通过窗口函数结合条件累计实现：按account_id分组，按时间顺序判断当前行与上一行的间隔是否超过1个月，将符合条件的情况标记为1，否则为0，最后对这些标记值进行累计求和，得到递增的reactivated列。

通用SQL示例 #

假设数据表名为account_intervals，基础SQL如下：

SELECT
    account_id,
    interval_start,
    interval_end,
    -- 累计符合间隔条件的次数，得到递增的reactivated值
    SUM(CASE
        -- 对比当前行start与上一行end的间隔是否超1个月
        WHEN DATEDIFF(month, LAG(interval_end) OVER (PARTITION BY account_id ORDER BY interval_start), interval_start) > 1
        THEN 1
        ELSE 0
    END) OVER (
        PARTITION BY account_id 
        ORDER BY interval_start 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS reactivated
FROM account_intervals;

数据库适配说明 #

不同数据库的日期差计算函数有差异，以下是常见数据库的适配写法：

• PostgreSQL：用EXTRACT计算间隔月份

  CASE
      WHEN EXTRACT(MONTH FROM interval_start - LAG(interval_end) OVER (PARTITION BY account_id ORDER BY interval_start)) > 1
      THEN 1
      ELSE 0
  END
  

• MySQL：用TIMESTAMPDIFF函数

  CASE
      WHEN TIMESTAMPDIFF(MONTH, LAG(interval_end) OVER (PARTITION BY account_id ORDER BY interval_start), interval_start) > 1
      THEN 1
      ELSE 0
  END
  

效果说明 #

• 每个account_id的第一行reactivated值为0（因为没有上一行，标记为0，累计和为0）
• 当某一行与上一行间隔超1个月时，标记为1，累计和递增，后续连续行保持该值，直到下一次触发间隔条件再次递增。

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