如何重复week_diff为0的分组用户数据完成留存率计算

1. SQL 实现 #

直接用窗口函数即可完成基准值填充，不需要额外关联表，性能更好：

select 
    week_monday,
    users,
    week_diff,
    max(case when week_diff = 0 then users end) over(partition by week_monday) as users_2,
    round(users / (max(case when week_diff = 0 then users end) over(partition by week_monday)), 2) as retention_rate
from 你的表名

逻辑说明：窗口函数按week_monday分组，组内提取week_diff=0对应的唯一用户数，填充到组内所有行，再直接做除法计算留存率即可。

2. Python Pandas 实现 #

如果是用Python处理数据，两种常用写法都可以实现需求：

写法1：分组transform #

import pandas as pd

# 假设你的原始数据存在df变量中
df['users_2'] = df.groupby('week_monday')['users'].transform(lambda x: x[df.loc[x.index, 'week_diff'] == 0].iloc[0])
df['retention_rate'] = (df['users'] / df['users_2']).round(2)

写法2：基准值映射（更适合大数据量场景） #

# 先提取每周的基准用户数映射表
base_user_map = df[df['week_diff'] == 0].set_index('week_monday')['users'].to_dict()
# 按周匹配填充基准值
df['users_2'] = df['week_monday'].map(base_user_map)
df['retention_rate'] = (df['users'] / df['users_2']).round(2)

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