Postgres如何新增字段统计客户订阅的连续月份数

该需求属于典型的序列岛屿问题，核心逻辑是先将连续订阅的记录划到同一个分组，再对组内记录排序计数即可，实现代码如下：

-- 假设你的原始表名为subscription
WITH lag_calc AS (
  SELECT
    client,
    product,
    date,
    -- 取同客户同产品的上一条订阅日期
    LAG(date) OVER (PARTITION BY client, product ORDER BY date) AS prev_sub_date
  FROM subscription
),
group_flag AS (
  SELECT
    *,
    -- 订阅间隔超过1个月就标记为新分组的起点
    CASE WHEN date - prev_sub_date = INTERVAL '1 month' THEN 0 ELSE 1 END AS new_group_tag
  FROM lag_calc
),
group_id AS (
  SELECT
    *,
    -- 累加标记生成连续分组ID，同一个连续周期的记录group_id相同
    SUM(new_group_tag) OVER (PARTITION BY client, product ORDER BY date) AS group_id
  FROM group_flag
)
SELECT
  client,
  product,
  date,
  -- 同组内按日期排序的行号就是连续订阅月份数
  ROW_NUMBER() OVER (PARTITION BY client, product, group_id ORDER BY date) AS Consecutive_months
FROM group_id
ORDER BY date, client;

注意事项 #

如果你的date字段不是每月1号的格式，建议先统一转成月初值再计算，避免间隔判断出错，替换日期字段的写法为：date_trunc('month', date)::date。

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