按ID统计过去一年历史出现次数与有效响应数实现方案咨询

实现方案 #

以下提供两种常用场景的实现代码，计算逻辑完全匹配你的需求： #

1. SQL 实现 #

适用数据存储在关系型数据库的场景，通过窗口函数范围帧实现，假设你的表名为job_data，date字段为日期类型：

SELECT 
    job,
    id,
    date,
    response,
    -- 计算过去一年内同id、早于当前日期的历史记录数
    COUNT(*) OVER (
        PARTITION BY id 
        ORDER BY UNIX_TIMESTAMP(date)
        RANGE BETWEEN 31536000 PRECEDING AND 1 PRECEDING
    ) AS occurrences,
    -- 计算过去一年内同id、早于当前日期且响应为1的记录数
    SUM(CASE WHEN response = 1 THEN 1 ELSE 0 END) OVER (
        PARTITION BY id 
        ORDER BY UNIX_TIMESTAMP(date)
        RANGE BETWEEN 31536000 PRECEDING AND 1 PRECEDING
    ) AS response_count
FROM job_data
ORDER BY id, date DESC;

逻辑说明：将日期转成秒级时间戳计算，31536000为一年的秒数，上界设为1 PRECEDING自动排除所有同日期的记录，无需额外写过滤条件。

2. Python Pandas 实现 #

适用本地处理数据集的场景，逻辑直观易调整：

import pandas as pd

# 读取数据，根据你的实际存储格式调整读取逻辑
df = pd.read_csv("你的数据文件路径", sep="\s+")
df["date"] = pd.to_datetime(df["date"])

# 按id分组计算两个指标
def calc_metrics(group):
    group = group.sort_values("date", ascending=True).reset_index(drop=True)
    occurrences = []
    response_count = []
    for _, row in group.iterrows():
        cur_date = row["date"]
        # 筛选同id、日期早于当前行、且在过去1年内的记录
        mask = (group["date"] < cur_date) & (group["date"] >= cur_date - pd.Timedelta(days=365))
        filtered = group[mask]
        occurrences.append(len(filtered))
        response_count.append(filtered["response"].sum())
    group["occurrences"] = occurrences
    group["response_count"] = response_count
    return group

# 生成结果，可根据需要调整最终排序规则
result = df.groupby("id", group_keys=False).apply(calc_metrics).sort_values(["id", "date"], ascending=[True, False])
print(result)

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