Python、SQL Server、Stata按用户分组计算按天datediff生成连续天数列的方法

首先明确两种计算逻辑，可按需选择：

• 按实际自然日差计算：以每个用户的最早日期为基准，当前日期与基准日的天数差+1即为days值，若中间日期无数据行，差值仍按自然天计算
• 按数据出现顺序计算：按用户分组、日期排序后，直接按行序号生成1、2、3…的连续序列，不考虑中间是否缺日期

SQL Server 实现 #

假设表名为user_records，用户ID字段为user_id，日期字段为record_date：

-- 按自然日差计算
SELECT
  user_id,
  record_date,
  DATEDIFF(day, MIN(record_date) OVER (PARTITION BY user_id), record_date) + 1 AS days
FROM user_records
ORDER BY user_id, record_date;

如果需要按数据出现顺序生成连续序列，改用窗口函数排名即可：

-- 按出现顺序计算
SELECT
  user_id,
  record_date,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY record_date) AS days
FROM user_records
ORDER BY user_id, record_date;

注意：如果同用户同一天存在多条重复记录，ROW_NUMBER()会给同一天的不同行分配不同序号，需要按天去重排名的话可改用DENSE_RANK()。

Python（Pandas）实现 #

假设DataFrame为df，用户ID字段为user_id，日期字段为record_date：

import pandas as pd

# 先转换日期格式为datetime类型
df['record_date'] = pd.to_datetime(df['record_date'])

# 按自然日差计算
df['days'] = df.groupby('user_id')['record_date'].transform(
    lambda x: (x - x.min()).dt.days + 1
)

如果需要按数据出现顺序生成连续序列：

# 先按用户、日期排序
df = df.sort_values(['user_id', 'record_date'])
# 按分组生成累计序号
df['days'] = df.groupby('user_id').cumcount() + 1

Stata 实现 #

假设用户ID变量为user_id，日期变量为record_date（Stata内置数值日期类型）：

* 先按用户、日期排序
sort user_id record_date

* 按自然日差计算
bysort user_id: egen min_record_date = min(record_date)
gen days = record_date - min_record_date + 1
* 删除中间临时变量
drop min_record_date

如果需要按数据出现顺序生成连续序列，在已完成排序的前提下直接用分组行号即可：

* 按出现顺序计算
bysort user_id: gen days = _n

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