如何添加TransactionOrder列标记用户交易发生顺序？

解决方案 #

1. SQL实现

使用窗口函数ROW_NUMBER()按User_ID分区，再按Transaction_Date排序得到交易序号，最后通过CASE WHEN将序号转换为对应的英文标识：

SELECT 
    Transaction_ID,
    User_ID,
    Transaction_Date,
    Amount,
    DayOfTheWeek,
    CASE 
        WHEN row_num = 1 THEN 'First'
        WHEN row_num = 2 THEN 'Second'
        WHEN row_num = 3 THEN 'Third'
        -- 可根据实际需求继续扩展更多序数词
        ELSE CAST(row_num as VARCHAR) || 'th'
    END AS TransactionOrder
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY Transaction_Date) AS row_num
    FROM transactions
) t;

2. Python Pandas实现

先对数据按User_ID分组，按Transaction_Date排序后生成组内序号，再映射为对应的英文标识：

import pandas as pd

# 加载示例数据（实际使用时可替换为你的数据源读取逻辑）
df = pd.DataFrame([
    ["Transaction_01", "User_01", "2021-09-06", 532, "Monday"],
    ["Transaction_02", "User_02", "2021-09-05", 631, "Sunday"],
    ["Transaction_03", "User_03", "2021-09-06", 7214, "Monday"],
    ["Transaction_04", "User_04", "2021-09-08", 131, "Wednesday"],
    ["Transaction_05", "User_01", "2021-09-08", 13, "Wednesday"],
    ["Transaction_06", "User_03", "2021-09-09", 72, "Thursday"],
    ["Transaction_07", "User_05", "2021-09-11", 139, "Saturday"],
    ["Transaction_08", "User_05", "2021-09-13", 214, "Monday"]
], columns=["Transaction_ID", "User_ID", "Transaction_Date", "Amount", "DayOfTheWeek"])

# 转换日期格式确保排序逻辑正确
df['Transaction_Date'] = pd.to_datetime(df['Transaction_Date'])

# 生成每个用户的交易序号
df['row_num'] = df.groupby('User_ID')['Transaction_Date'].rank(method='first', ascending=True).astype(int)

# 映射序号到英文标识
order_map = {1: 'First', 2: 'Second', 3: 'Third'}
df['TransactionOrder'] = df['row_num'].map(order_map).fillna(df['row_num'].astype(str) + 'th')

# 清理临时列并按原列顺序输出结果
df = df.drop('row_num', axis=1)[["Transaction_ID", "User_ID", "Transaction_Date", "Amount", "DayOfTheWeek", "TransactionOrder"]]

print(df)

如果需要支持更多交易次数，只需扩展CASE WHEN或order_map中的映射关系即可。

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