忽略5秒时间差提取MySQL/DataFrame唯一记录的技术方案咨询

业务规则 #

同一手机号（mobile）和编码（code）的记录，若时间（dt）差小于5秒，视为同一条记录，需合并为一条（时间值可取该组内任意合理值）；时间差≥5秒则视为不同记录，需保留。

MySQL 场景示例 #

测试表与数据 #

create table test(dt bigint, mobile bigint, code int);

insert into test values (231019074114, 7819838466, 20);
insert into test values (231019074151, 7819838466, 20);
insert into test values (231019074154, 7819838466, 20);
insert into test values (231019074159, 8819838466, 20);
insert into test values (231019074159, 7819838466, 99);

原查询及结果 #

原查询直接按mobile, code分组取最大时间：

select mobile, code, max(dt) from test as t group by mobile, code order by mobile, code

执行结果：

mobile      code    max(dt)
7819838466  20      231019074154
7819838466  99      231019074159
8819838466  20      231019074159

问题与预期结果 #

原查询将7819838466+20的所有记录合并为一条，但其中231019074114与231019074151时间差37秒（≥5秒），属于不同记录；而231019074151与231019074154时间差3秒（<5秒），需合并为一条。

预期结果：

mobile      code    max(dt)
7819838466  20      231019074114
7819838466  20      231019074153
7819838466  99      231019074159
8819838466  20      231019074159

Pandas 场景：修复现有错误解决方案 #

现有错误代码 #

以下代码仅返回4条记录，缺失7819838466+20的其中一组记录：

from io import StringIO
import pandas as pd

audit_trail = StringIO('''
dt|mobile|code
231019074114|7819838466|20
231019074151|7819838466|20
231019074152|7819838466|20
231019074153|7819838466|20
231019074154|7819838466|20
231019074155|7819838466|20
231019074159|8819838466|20
231019074231|7819838466|99
231019074259|7819838466|99
''')

df = pd.read_csv(audit_trail, sep="|")

N = 5
df = df.sort_values(['mobile','code','dt'])
diff = df.groupby(['mobile','code'])['dt'].diff().clip(lower=N)
df[diff.isna() | diff.eq(N) & ~diff.duplicated()]

正确解决方案 #

通过分组标记连续时间组的方式，将时间差<5秒的记录归为同一组，再每组保留一条记录：

from io import StringIO
import pandas as pd

audit_trail = StringIO('''
dt|mobile|code
231019074114|7819838466|20
231019074151|7819838466|20
231019074152|7819838466|20
231019074153|7819838466|20
231019074154|7819838466|20
231019074155|7819838466|20
231019074159|8819838466|20
231019074231|7819838466|99
231019074259|7819838466|99
''')

df = pd.read_csv(audit_trail, sep="|")
N = 5

# 按mobile、code、dt排序
df = df.sort_values(['mobile', 'code', 'dt']).reset_index(drop=True)

# 分组计算与前一条的时间差，标记新组起始
df['time_diff'] = df.groupby(['mobile', 'code'])['dt'].diff()
df['new_group'] = df['time_diff'].fillna(N) >= N

# 生成连续时间组的分组ID
df['group_id'] = df.groupby(['mobile', 'code'])['new_group'].cumsum()

# 每组取中间位置的时间作为代表值，也可替换为首/尾值
result = df.groupby(['mobile', 'code', 'group_id']).agg(
    dt=('dt', lambda x: x.iloc[len(x)//2]),
    mobile=('mobile', 'first'),
    code=('code', 'first')
).reset_index(drop=True)[['dt', 'mobile', 'code']]

print(result)

预期输出 #

dt      mobile  code
0  231019074114  7819838466    20
1  231019074153  7819838466    20
2  231019074231  7819838466    99
3  231019074259  7819838466    99
4  231019074159  8819838466    20

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