如何按ColA分组用2-3天滑动窗口分析ColD生成新列NewCol

方案1：Python（pandas）实现 #

适用于离线小批量数据处理场景，实现逻辑如下：

import pandas as pd

# 1、数据预处理，转换日期格式、按分组+日期排序确保顺序正确
df['ColB'] = pd.to_datetime(df['ColB'])
df = df.sort_values(['ColA', 'ColB']).reset_index(drop=True)

# 2、按ColA分组取当前行往前2行、往前3行的ColD值（对应2天前、3天前数据）
df['d2'] = df.groupby('ColA')['ColD'].shift(2)
df['d3'] = df.groupby('ColA')['ColD'].shift(3)

# 3、按规则计算NewCol
df['NewCol'] = df.apply(
    lambda x: 1 if (x['d2'] >= 200 or x['d3'] >= 200) 
    else 0 if pd.notna(x['d2']) and pd.notna(x['d3']) 
    else pd.NA, 
    axis=1
)

# 4、删除辅助计算列得到最终结果
df = df.drop(columns=['d2', 'd3'])

方案2：SQL实现 #

适用于大数据量离线数仓场景，支持MySQL8.0+、Hive SQL、Spark SQL、Flink SQL等所有支持窗口函数的SQL引擎：

SELECT 
    ColA,
    ColB,
    ColC,
    ColD,
    CASE 
        WHEN d2 IS NULL OR d3 IS NULL THEN NULL -- 不满足窗口长度返回NA
        WHEN d2 >= 200 OR d3 >= 200 THEN 1 -- 任意值≥200返回1
        ELSE 0 -- 所有值<200返回0
    END AS NewCol
FROM (
    SELECT 
        *,
        -- 取分组内当前行往前2行、往前3行的ColD值
        LAG(ColD, 2) OVER(PARTITION BY ColA ORDER BY ColB) AS d2,
        LAG(ColD, 3) OVER(PARTITION BY ColA ORDER BY ColB) AS d3
    FROM 你的表名
) t

注意事项 #

• 若数据中同一ColA分组下存在日期不连续的情况，需要先对每个分组生成完整的连续日期序列，补全缺失行后再执行上述逻辑，避免行数偏移和实际日期偏移不一致
• 若ColD存在空值，可根据业务需要补充空值处理逻辑，比如空值视为小于200，或者单独标记

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