如何用pandas或SQL统计用户第二频繁登录地点等多维度登录指标

需求说明 #

• 基于用户登录行为数据集，统计每个用户的5个字段：user_id、最近登录日期、最近登录地点、最频繁登录地点、第二频繁登录地点
• 数据集结构样例：

event_name  event_date  user_id     user_city   user_state
exit_click  06-09-2021  10795552    Kayamkulam  Kerala
exit_click  06-09-2021  11129909    Tiruppur    Tamil Nadu
exit_click  06-09-2021  11028532    Thrissur    Kerala
-- 其余样例数据省略

现有SQL局限 #

你当前编写的SQL逻辑存在规范问题，group by user_id后直接取非聚合字段event_date在严格SQL模式下会报错，且max(user_city)取的是城市名称的最大值而非登录最频繁的城市，也无法实现第二频繁登录地点的计算。
现有代码：

select bq.user_id as user_id, 
bq.event_date as Date_of_Last_Login,
bq.user_city as Location_of_Latest_Login,
max(user_city) as Location_of_Max_Logins from bq 

group by user_id
order by event_date DESC ;

方案实现 #

SQL实现（支持MySQL8.0+/PostgreSQL/BigQuery等所有支持窗口函数的数据库） #

核心逻辑：通过CTE分步骤计算最近登录排名、城市登录次数排名，再聚合得到最终结果

WITH 
-- 步骤1：计算每个用户最近登录的信息
latest_login AS (
    SELECT 
        user_id,
        event_date,
        user_city,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY STR_TO_DATE(event_date, '%d-%m-%Y') DESC) AS rn
    FROM bq
),
-- 步骤2：统计每个用户各城市的登录次数并排序
city_rank AS (
    SELECT 
        user_id,
        user_city,
        COUNT(*) AS login_cnt,
        -- 次数相同的情况下默认取最近有登录的城市，可根据业务调整排序规则
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(*) DESC, STR_TO_DATE(MAX(event_date), '%d-%m-%Y') DESC) AS city_rn
    FROM bq
    GROUP BY user_id, user_city
)
-- 步骤3：关联聚合得到所有指标
SELECT 
    l.user_id,
    l.event_date AS Date_of_Last_Login,
    l.user_city AS Location_of_Latest_Login,
    MAX(CASE WHEN c.city_rn = 1 THEN c.user_city END) AS Location_of_Max_Logins,
    MAX(CASE WHEN c.city_rn = 2 THEN c.user_city END) AS Location_of_2nd_Max_Logins
FROM latest_login l
LEFT JOIN city_rank c ON l.user_id = c.user_id
WHERE l.rn = 1
GROUP BY l.user_id, l.event_date, l.user_city;

说明：如果用户只有1个登录城市，第二频繁登录地点会返回NULL，可通过IFNULL/COALESCE函数替换为业务需要的默认值。如果存在多个城市登录次数相同的场景，可将ROW_NUMBER()替换为RANK()或DENSE_RANK()适配业务规则。

Pandas实现 #

import pandas as pd

# 假设df为读取后的原始数据集，先转换日期格式方便排序
df['event_date'] = pd.to_datetime(df['event_date'], format='%d-%m-%Y')

# 1. 提取每个用户最近一次登录的信息
latest_df = df.sort_values('event_date', ascending=False).groupby('user_id').head(1)[['user_id', 'event_date', 'user_city']]
latest_df.columns = ['user_id', 'Date_of_Last_Login', 'Location_of_Latest_Login']

# 2. 统计每个用户各城市的登录次数，按次数倒序排名
city_cnt = df.groupby(['user_id', 'user_city']).size().reset_index(name='login_cnt')
city_cnt = city_cnt.sort_values(['user_id', 'login_cnt'], ascending=[True, False])
city_cnt['rank'] = city_cnt.groupby('user_id').cumcount() + 1

# 3. 行转列得到最频繁、第二频繁登录城市
city_pivot = city_cnt.pivot(index='user_id', columns='rank', values='user_city').reset_index()
city_pivot.columns = ['user_id', 'Location_of_Max_Logins', 'Location_of_2nd_Max_Logins']

# 4. 合并两个结果得到最终输出
result = pd.merge(latest_df, city_pivot, on='user_id', how='left')

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