如何按独立分区为连续同名记录块添加计数列？

现有如下结构的DataFrame：

id   name     datetime
44   once     2022-11-22T15:41:00
44   once     2022-11-22T15:42:00
44   once     2022-11-22T15:43:00     
44   twice    2022-11-22T15:44:00
44   once     2022-11-22T16:41:00
55   thrice   2022-11-22T17:44:00
55   thrice   2022-11-22T17:46:00
55   once     2022-11-22T17:47:00
55   once     2022-11-22T17:51:00
55   twice    2022-11-22T18:41:00
55   thrice   2022-11-22T18:51:00

期望新增cnt列，该列表示每个连续同名记录块的行数，输出如下：

id   name     datetime                cnt
44   once     2022-11-22T15:41:00     3
44   once     2022-11-22T15:42:00     3
44   once     2022-11-22T15:43:00     3
44   twice    2022-11-22T15:44:00     1
44   once     2022-11-22T16:41:00     1
55   thrice   2022-11-22T17:44:00     2
55   thrice   2022-11-22T17:46:00     2    
55   once     2022-11-22T17:47:00     2
55   once     2022-11-22T17:51:00     2
55   twice    2022-11-22T18:41:00     1
55   thrice   2022-11-22T18:51:00     1

尝试了以下SQL语句但未得到预期结果：

select
id,
name,
datetime,
row_number() over (partition by id order by datetime) rn1,
row_number() over (partition by id, name order by name, datetime) rn2
from table

核心思路是先为每个连续的同名记录块分配唯一组ID，再基于组ID计算每个块的行数。以下是两种实现方式：

方式一：子查询+窗口函数直接计算 #

SELECT 
    id,
    name,
    datetime,
    COUNT(*) OVER (PARTITION BY id, group_id) AS cnt
FROM (
    SELECT 
        id,
        name,
        datetime,
        -- 生成连续同名块的组ID：当前行name与上一行不同时，标记为1，累加后得到唯一组ID
        SUM(CASE 
                WHEN name = LAG(name) OVER (PARTITION BY id ORDER BY datetime) THEN 0 
                ELSE 1 
            END) OVER (PARTITION BY id ORDER BY datetime) AS group_id
    FROM your_table
) t
ORDER BY id, datetime;

方式二：CTE分步实现 #

WITH grouped_data AS (
    SELECT 
        id,
        name,
        datetime,
        SUM(CASE 
                WHEN name = LAG(name) OVER (PARTITION BY id ORDER BY datetime) THEN 0 
                ELSE 1 
            END) OVER (PARTITION BY id ORDER BY datetime) AS group_id
    FROM your_table
),
counted_groups AS (
    SELECT 
        group_id,
        COUNT(*) AS cnt
    FROM grouped_data
    GROUP BY group_id
)
SELECT 
    gd.id,
    gd.name,
    gd.datetime,
    cg.cnt
FROM grouped_data gd
JOIN counted_groups cg ON gd.group_id = cg.group_id
ORDER BY gd.id, gd.datetime;

逻辑说明 #

1. 生成组ID：在每个id分组内，按datetime排序，用LAG(name)获取上一行的name。如果当前行name与上一行不同，标记为1，否则为0；通过累加这些标记值，为每个连续的同名记录块分配唯一的group_id。
2. 计算块行数：基于id和group_id分组，用COUNT()函数统计每个块的行数，即为cnt列的值。

为什么原SQL无效 #

原SQL中用row_number()按id和name分区，会把所有相同name的行归为一组，不管它们是否连续。比如id=44的两个once记录块会被分到同一个分区，无法区分连续和非连续的情况，因此得不到正确的cnt值。

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