如何基于其他列数值和日期条件实现符合指定规则的月度增量值计算

核心逻辑 #

需求的核心是对符合递增规则的记录做分组：每触发一次条件1（col2>0）或条件2（col1=0），后续符合条件3（col2=0且col1>0）的记录归属新的分组，每个分组内按时间顺序从1开始递增计数即可。

1. SQL 实现（支持所有带窗口函数的主流数据库：MySQL8.0+、PostgreSQL、Hive、Spark SQL等） #

WITH step1 AS (
    -- 标记当前行是否为重置节点（触发条件1或条件2）
    SELECT 
        *,
        CASE WHEN col2 > 0 OR col1 = 0 THEN 1 ELSE 0 END AS reset_flag
    FROM 你的表名
),
step2 AS (
    -- 累加重置标记生成分组ID，连续符合条件3的记录会归属同一分组
    SELECT 
        *,
        SUM(reset_flag) OVER (PARTITION BY Id ORDER BY Date ASC) AS group_id
    FROM step1
),
step3 AS (
    -- 对每个分组内的记录按时间排序生成递增序号
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Id, group_id ORDER BY Date ASC) AS group_incr
    FROM step2
)
-- 按规则输出最终结果
SELECT 
    Id, Date, Col1, col2,
    CASE 
        WHEN col2 > 0 THEN 0
        WHEN col1 = 0 THEN 999
        ELSE group_incr 
    END AS expected_output
FROM step3
ORDER BY Id, Date ASC;

2. Python Pandas 实现 #

import pandas as pd

# 示例数据，可替换为自己的数据源读取逻辑
df = pd.DataFrame({
    'Id': [101]*10,
    'Date': ['01/01','01/02','01/03','01/04','01/05','01/06','01/07','01/08','01/09','01/10'],
    'Col1': [28,43,46,0,56,95,0,65,1,2],
    'col2': [1,0,0,0,0,5,0,0,0,0]
})

# 按ID分组、日期排序
df = df.sort_values(['Id', 'Date']).reset_index(drop=True)
# 生成重置标记
df['reset_flag'] = ((df['col2'] > 0) | (df['Col1'] == 0)).astype(int)
# 生成组ID
df['group_id'] = df.groupby('Id')['reset_flag'].cumsum()
# 生成分组内递增计数
df['group_incr'] = df.groupby(['Id', 'group_id']).cumcount() + 1
# 输出最终结果
df['expected_output'] = df.apply(
    lambda x: 0 if x['col2']>0 else 999 if x['Col1']==0 else x['group_incr'],
    axis=1
)

# 打印结果校验
print(df[['Id', 'Date', 'Col1', 'col2', 'expected_output']])

两种方案的输出结果完全匹配示例给出的预期值，可直接替换数据源使用。

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