如何实现按连续同类型分组循环编号的identifier标识列生成需求

这个需求完全可以实现，属于典型的连续区间（孤岛）分组场景，用主流的SQL窗口函数或者Python数据处理工具都能快速实现。

核心逻辑 #

你要的identifier本质是连续相同type的分组编号，核心判断逻辑是：只要当前行的type和上一行的type不一致，就算新分组的起点，编号加1，和这个type历史有没有出现过无关。

1. SQL实现（支持MySQL8.0+/Hive/Spark SQL/Presto等所有支持窗口函数的数据库） #

假设你的表名为trx_table，按user_id分组、dt升序排序来判断行顺序，代码如下：

SELECT 
    dt,
    type,
    user_id,
    trx,
    SUM(change_flag) OVER(PARTITION BY user_id ORDER BY dt ASC) AS identifier
FROM (
    SELECT 
        *,
        CASE 
            -- 第一行没有上一行，标记为分组起点
            WHEN LAG(type, 1) OVER(PARTITION BY user_id ORDER BY dt ASC) IS NULL THEN 1
            -- 和上一行type不同，标记为分组起点
            WHEN type <> LAG(type, 1) OVER(PARTITION BY user_id ORDER BY dt ASC) THEN 1
            -- 和上一行type相同，不属于新分组
            ELSE 0 
        END AS change_flag
    FROM trx_table
) t

用你提供的样例数据执行上述代码，输出的identifier和你预期的结果完全一致。

2. Python Pandas实现 #

如果是用本地数据表处理，Pandas代码更简洁：

import pandas as pd

# 先确保数据按user_id、dt升序排序
df = df.sort_values(by=['user_id', 'dt'], ascending=True).reset_index(drop=True)
# 按user_id分组，标记type发生变化的行
df['is_new_group'] = df.groupby('user_id')['type'].shift(1) != df['type']
# 对分组标记做累计求和得到identifier
df['identifier'] = df.groupby('user_id')['is_new_group'].cumsum()
# 可选：删除临时标记列
df = df.drop(columns=['is_new_group'])

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