将ID多次出现的连续日期记录分组为独立事件段

我来帮你搞定这个连续日期分组的需求！这种把同一用户（ID）的连续/重复日期记录打包成事件段的场景，在用户行为分析、业务事件追踪里特别常见，我经常处理这类问题。

先明确需求对应结果 #

根据你给出的输入表，最终应该得到这样的结果（把每个连续日期段合并为一个事件）：

核心思路 #

利用窗口函数+日期差值来识别连续日期段：同一ID下，连续的日期（包括同一天重复）会生成相同的「分组基准值」，我们就靠这个基准值来把同一段的记录归为一组。

具体SQL实现（支持MySQL/PostgreSQL等主流数据库） #

MySQL版本

WITH ranked_dates AS (
    SELECT 
        id,
        -- 先把字符串日期转成数据库可计算的日期类型
        STR_TO_DATE(date, '%m/%d/%Y') AS actual_date,
        -- 用DENSE_RANK给同一ID的相同日期分配相同排名，避免重复日期被拆分
        DENSE_RANK() OVER (PARTITION BY id ORDER BY STR_TO_DATE(date, '%m/%d/%Y')) AS rn
    FROM event_records
),
grouped_episodes AS (
    SELECT 
        id,
        actual_date,
        -- 日期减去排名天数，连续日期会得到相同的group_key
        DATE_SUB(actual_date, INTERVAL rn DAY) AS group_key
    FROM ranked_dates
)
SELECT 
    id,
    -- 转回原日期格式输出
    DATE_FORMAT(MIN(actual_date), '%m/%d/%Y') AS episode_start,
    DATE_FORMAT(MAX(actual_date), '%m/%d/%Y') AS episode_end,
    COUNT(*) AS record_count
FROM grouped_episodes
GROUP BY id, group_key
ORDER BY id, episode_start;

PostgreSQL版本

PostgreSQL的日期函数略有不同，调整一下即可：

WITH ranked_dates AS (
    SELECT 
        id,
        TO_DATE(date, 'MM/DD/YYYY') AS actual_date,
        DENSE_RANK() OVER (PARTITION BY id ORDER BY TO_DATE(date, 'MM/DD/YYYY')) AS rn
    FROM event_records
),
grouped_episodes AS (
    SELECT 
        id,
        actual_date,
        actual_date - rn * INTERVAL '1 day' AS group_key
    FROM ranked_dates
)
SELECT 
    id,
    TO_CHAR(MIN(actual_date), 'MM/DD/YYYY') AS episode_start,
    TO_CHAR(MAX(actual_date), 'MM/DD/YYYY') AS episode_end,
    COUNT(*) AS record_count
FROM grouped_episodes
GROUP BY id, group_key
ORDER BY id, episode_start;

代码解释 #

1. ranked_dates 公共表表达式：

   • 把输入的字符串日期转换为数据库能识别的日期类型，方便后续计算。
   • 用DENSE_RANK()按ID分组、日期排序，给同一ID下的相同日期分配相同的排名——这一步很关键，能确保同一天的多条记录不会被拆分到不同组。

2. grouped_episodes 公共表表达式：

   • 计算actual_date - rn（日期减排名对应的天数），连续的日期会得到相同的group_key。比如11/16（rn=1）减1天是11/15，11/17（rn=2）减2天也是11/15，这样就把连续日期绑定到同一个基准值上了。

3. 最终统计：

   • 按ID和group_key分组，取每组的最小日期（事件段开始）、最大日期（事件段结束），再统计该段的记录总数，最后按ID和事件开始日期排序。

验证效果 #

用你给出的测试数据跑这段代码，就能得到最开始展示的结果，完美匹配你想要的事件段分组需求。

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