如何在相邻字段组合相同时创建带唯一标识符的activity_instance列

需求规则 #

• 以unique_id作为用户区分维度，每个用户的实例编号独立计算
• 判定新实例的规则：同个用户下，unique_id、activity、date三列值完全一致的记录归属同一个实例
• 编号规则：同个用户下，按数据原始排列顺序，首次出现的三列组合编号从1开始计数，每遇到一个从未出现过的新三列组合，编号加1；后续重复出现的历史组合，直接沿用第一次分配的编号
• 编号仅在单个用户下独立递增，跨用户不冲突，满足全局事件组唯一标识要求

目标结果示例 #

常用实现方案 #

1. Python Pandas 实现 #

适合本地小批量、中批量数据处理，代码简洁且逻辑稳定：

import pandas as pd

# 替换为实际数据读取逻辑，支持csv、excel、数据库读取等
df = pd.read_csv("your_source_data.csv")

# 核心逻辑：按用户分组，对组内(activity,date)组合按首次出现顺序编码
df["activity_instance"] = df.groupby("unique_id", group_keys=False).apply(
    lambda x: x[["activity", "date"]].agg("|".join, axis=1).factorize()[0] + 1
)

实现说明：factorize方法默认按值第一次出现的顺序生成从0开始的连续整数，加1后适配从1开始计数的要求，天然支持“重复值沿用原编码、新值顺序递增”的规则，不会因为后续出现历史组合打乱计数顺序。百万级以上数据处理时，用字符串拼接生成临时键的方式比转tuple性能高30%左右。

2. SQL 实现 #

适合数据库、数仓端直接计算，无需导出数据：
以下代码兼容MySQL 8.0+、PostgreSQL、Hive、Spark SQL等支持标准窗口函数的引擎：

WITH tmp_mark AS (
    SELECT
        *,
        -- 标记当前记录是否为该用户下对应(activity,date)组合的第一次出现
        CASE WHEN ROW_NUMBER() OVER(
            PARTITION BY unique_id, activity, date
            -- 必须替换为能确定原始数据顺序的字段，比如自增id、采集时间戳ts，否则顺序错乱会导致编号错误
            ORDER BY id
        ) = 1 THEN 1 ELSE 0 END AS is_new_instance
    FROM your_source_table
)
SELECT
    unique_id,
    activity,
    date,
    -- 对同用户下的新实例标记做累计求和，得到连续递增的实例编号
    SUM(is_new_instance) OVER(
        PARTITION BY unique_id
        -- 排序字段和上方保持一致
        ORDER BY id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS activity_instance
FROM tmp_mark;

关键注意点：SQL本身不保证表的默认返回顺序，必须指定明确的排序字段（比如自增主键、数据入库时间戳）才能保证实例编号和数据出现顺序一致，否则结果不可控。

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