如何获取存在连续多月购买记录的user_id？

我需要从给定数据中，获取date列从指定起始日期开始、同一user_id连续多月出现的所有对应user_id值。

示例数据 #

需求示例 #

以获取2019-01-01之前（不含当日）连续多月有记录的user_id值为例，预期输出如下：

实现方案（基于窗口函数） #

实现思路 #

1. 先对原始数据按user_id+年月去重，同一个用户同一个月多次出现仅保留一条记录，排除重复数据干扰
2. 对每个user_id的月度记录按时间升序排序，用日期减去排序序号对应的月份数，得到分组基准值：连续月份的记录会得到相同的基准值，出现月份断档的话基准值会发生变化
3. 按user_id和分组基准值聚合，筛选出记录数符合连续月份要求的分组，输出对应记录即可

参考SQL代码（兼容MySQL 8.0+/PostgreSQL/Hive等支持窗口函数的引擎） #

WITH dedup_data AS (
    -- 去重得到每个用户每月唯一记录，同时添加日期过滤条件
    SELECT DISTINCT
        user_id,
        DATE_FORMAT(date, '%Y-%m') AS m_year,
        date
    FROM 替换为你的表名
    WHERE date < '2019-01-01' -- 可自行修改日期过滤规则
),
month_group AS (
    -- 计算连续月份分组基准
    SELECT
        user_id,
        m_year,
        DATE_SUB(date, INTERVAL (ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date)) MONTH) AS group_flag
    FROM dedup_data
)
-- 筛选符合连续月份要求的记录
SELECT user_id, m_year
FROM month_group
WHERE group_flag IN (
    SELECT group_flag
    FROM month_group
    GROUP BY user_id, group_flag
    HAVING COUNT(*) >= 2 -- 此处数值替换为你要求的最小连续月数，示例要求连续2个月及以上故填2
)
ORDER BY user_id, m_year;

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