如何获取早于/等于邮件通知日期的最晚登录日期（SQL/Pandas）

Pandas 实现 #

假设你的DataFrame包含user_id（用户ID）、login_date（登录日期）、notification_date（邮件通知日期）三列，先确保日期列是datetime类型：

import pandas as pd

# 转换日期列类型（如果还不是datetime格式）
df['login_date'] = pd.to_datetime(df['login_date'])
df['notification_date'] = pd.to_datetime(df['notification_date'])

方法1：merge_asof（高效适配大数据量） #

先整理用户的登录记录（去重并排序），再和原表按用户、通知日期关联，匹配不晚于当前通知日期的最近登录日期：

# 提取去重后的用户登录记录并排序
login_records = df[['user_id', 'login_date']].drop_duplicates().sort_values(['user_id', 'login_date'])
# 对原表按用户和通知日期排序
df_sorted = df.sort_values(['user_id', 'notification_date'])
# 关联生成目标列
result = pd.merge_asof(
    df_sorted,
    login_records,
    by='user_id',
    left_on='notification_date',
    right_on='login_date',
    direction='backward'
).rename(columns={'login_date_y': 'Last Login Date'})

方法2：分组逐行计算（适合小数据集） #

按用户分组后，对每行的通知日期，筛选该用户所有早于等于它的登录日期并取最大值：

def get_last_login(group):
    group['Last Login Date'] = group.apply(
        lambda row: group[group['login_date'] <= row['notification_date']]['login_date'].max(),
        axis=1
    )
    return group

result = df.groupby('user_id').apply(get_last_login).reset_index(drop=True)

SQL 实现 #

假设你的表名为user_activity，包含user_id、login_date、notification_date字段：

方法1：关联子查询 #

SELECT 
    user_id,
    login_date,
    notification_date,
    (
        SELECT MAX(login_date)
        FROM user_activity u2
        WHERE u2.user_id = u1.user_id
          AND u2.login_date <= u1.notification_date
    ) AS `Last Login Date`
FROM user_activity u1;

方法2：窗口函数（性能更优） #

SELECT 
    user_id,
    login_date,
    notification_date,
    MAX(login_date) OVER (
        PARTITION BY user_id 
        ORDER BY notification_date
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS `Last Login Date`
FROM user_activity;

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