每行时间窗口不同时如何编写单SQL查询实现回溯聚合构建客户流失预测数据集

完全可以用单条SQL批量完成所有计算，性能会比逐行查询高几个量级。

核心思路 #

你之前逐行查询的逻辑本质是对每一行的account_no和ExtractDate，匹配符合区间条件的服务数。我们可以直接把待计算的所有行作为主表，和全量服务表做关联聚合，一次性算出所有结果。

具体实现 #

分两种场景处理：

场景1：待计算的数据集已经存储在数据库中

假设你的待计算表名为service_labels，包含account_no、service_no、ExtractDate三个关键字段，直接执行以下查询即可：

SELECT 
    t.account_no,
    t.service_no,
    t.ExtractDate,
    COUNT(DISTINCT s.ServiceNo) AS active_service_cnt
FROM service_labels t
LEFT JOIN ServicesAll s 
    ON t.account_no = s.AccountNo
    AND s.InstallationDate < t.ExtractDate
    AND (s.BillingStopDate IS NULL OR s.BillingStopDate >= t.ExtractDate)
GROUP BY t.account_no, t.service_no, t.ExtractDate

场景2：待计算的数据集仅存在于Python的DataFrame中

你可以先把需要用的字段批量写入数据库临时表，再执行关联查询，代码示例如下：

# 将待计算的关键字段写入临时表
iter_df[["account_no", "service_no", "ExtractDate"]].to_sql(
    name="temp_calc_labels",
    con=conn,
    if_exists="replace",
    index=False
)
# 执行批量计算查询
calc_sql = """
SELECT 
    t.account_no,
    t.service_no,
    t.ExtractDate,
    COUNT(DISTINCT s.ServiceNo) AS active_service_cnt
FROM temp_calc_labels t
LEFT JOIN ServicesAll s 
    ON t.account_no = s.AccountNo
    AND s.InstallationDate < t.ExtractDate
    AND (s.BillingStopDate IS NULL OR s.BillingStopDate >= t.ExtractDate)
GROUP BY t.account_no, t.service_no, t.ExtractDate
"""
# 读取计算结果并合并回原数据集
result_df = pd.read_sql(calc_sql, conn)
final_df = iter_df.merge(
    result_df,
    on=["account_no", "service_no", "ExtractDate"],
    how="left"
)

性能优化建议 #

给ServicesAll表建立(AccountNo, InstallationDate, BillingStopDate)联合索引，可以进一步大幅降低查询耗时。

结果验证 #

上述SQL的筛选逻辑和你原有的逐行查询逻辑完全一致，计算结果和你之前逐行遍历得到的结果完全相同，你可以拿示例中的S1、S3记录核对数值。

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