如何查询每个user对应的首次event及该event发生的timestamp

前提说明 #

现有用户行为数据集包含3个字段：

• user：用户唯一标识
• timestamp：事件发生日期
• event：事件类型

样例数据如下：

方案1：SQL实现（支持MySQL、PostgreSQL等主流数据库） #

核心逻辑为按用户分组后取时间最早的一条记录，推荐用窗口函数实现，性能更稳定：

WITH user_event_rank AS (
    SELECT 
        user,
        timestamp,
        event,
        -- 按用户分组后按时间升序排序，序号为1的就是首次事件
        ROW_NUMBER() OVER (PARTITION BY user ORDER BY timestamp ASC) AS rn
    FROM user_behavior
)
SELECT user, timestamp AS first_event_time, event AS first_event
FROM user_event_rank
WHERE rn = 1;

如果是不支持CTE和窗口函数的低版本数据库，可以用关联子查询实现：

SELECT a.user, a.timestamp AS first_event_time, a.event AS first_event
FROM user_behavior a
INNER JOIN (
    SELECT user, MIN(timestamp) AS min_time
    FROM user_behavior
    GROUP BY user
) b ON a.user = b.user AND a.timestamp = b.min_time;

注意：如果同一用户同一时间点存在多条事件，第二种写法会返回该时间所有事件；如果需要保留所有首次时间的事件，可将窗口函数写法中的ROW_NUMBER()替换为RANK()

方案2：Python Pandas实现 #

适合离线处理中小型数据集：

import pandas as pd

# 假设df是读取后的源数据表
df['timestamp'] = pd.to_datetime(df['timestamp']) # 转换为时间类型保证排序准确性

# 按时间排序后按用户分组，保留每组第一条记录
first_event_df = df.sort_values('timestamp').groupby('user', as_index=False).first()
first_event_df.columns = ['user', 'first_event_time', 'first_event']

方案3：Spark实现（适合超大规模分布式数据集） #

Spark SQL写法和上述普通SQL完全一致，DataFrame写法如下：

from pyspark.sql import Window
from pyspark.sql.functions import row_number

window_spec = Window.partitionBy("user").orderBy("timestamp")
df = df.withColumn("rn", row_number().over(window_spec))
first_event_df = df.filter(df.rn == 1).select(
    "user", 
    df.timestamp.alias("first_event_time"), 
    df.event.alias("first_event")
)

样例输出结果 #

用上述方案跑样例数据得到的结果如下：

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