按ID分组将列值条件累加至指定阈值的实现方案

现有数据表格如下：

需要实现的逻辑：

• 按id分组处理
• 仅对amount大于$5的数值进行累加
• 当累加总和达到$50时，立即输出该组累加值，并对后续行重新开始累加
• 金额不大于$5的行直接保留，不参与累加
• 若遍历到分组末尾时累加总和未达$50，直接输出剩余累加值

预期输出结果：

解决方案 #

方案1：SQL实现（适用于PostgreSQL/MySQL 8.0+） #

通过递归CTE标记累加分组，再按分组求和：

WITH RECURSIVE processed AS (
    -- 初始化：给每行标记行号，初始化累加值和分组ID
    SELECT 
        id,
        name,
        amount,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT 1)) AS rn,
        CASE WHEN amount > 5 THEN amount ELSE 0 END AS running_total,
        CASE WHEN amount > 5 THEN 1 ELSE 0 END AS group_id
    FROM your_table
    UNION ALL
    -- 递归处理后续行，更新累加值和分组ID
    SELECT 
        p.id,
        p.name,
        t.amount,
        t.rn,
        CASE 
            WHEN t.amount <=5 THEN 0
            WHEN p.running_total + t.amount >=50 THEN t.amount
            ELSE p.running_total + t.amount
        END AS running_total,
        CASE 
            WHEN t.amount <=5 THEN p.group_id
            WHEN p.running_total + t.amount >=50 THEN p.group_id +1
            ELSE p.group_id
        END AS group_id
    FROM processed p
    JOIN (
        SELECT 
            id,
            name,
            amount,
            ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT 1)) AS rn
        FROM your_table
    ) t ON p.id = t.id AND p.rn = t.rn -1
),
-- 分组汇总，保留单独的小额行
final AS (
    SELECT 
        id,
        name,
        CASE 
            WHEN amount <=5 THEN amount
            ELSE SUM(amount)
        END AS "amount (in $)",
        CASE 
            WHEN amount <=5 THEN '跳过金额不大于$5的行'
            ELSE CONCAT('累计金额：', SUM(amount))
        END AS 说明
    FROM processed
    GROUP BY id, name, group_id, CASE WHEN amount <=5 THEN amount ELSE NULL END
    ORDER BY id, group_id
)
SELECT id, name, "amount (in $)", 说明 FROM final;

方案2：Python Pandas实现 #

通过遍历分组数据，手动维护累加器：

import pandas as pd

# 构造原始数据
df = pd.DataFrame({
    'id': [1,1,1,1,1,1,2,2,2,2],
    'name': ['A','A','A','A','A','A','B','B','B','B'],
    'amount': [10,5,20,20,40,30,25,20,30,30]
})

result = []
threshold = 50

# 按id分组处理
for id_val in df['id'].unique():
    group = df[df['id'] == id_val].reset_index(drop=True)
    current_sum = 0
    for _, row in group.iterrows():
        if row['amount'] <= 5:
            # 直接保留小额行
            result.append({
                'id': row['id'],
                'name': row['name'],
                'amount (in $)': row['amount'],
                '说明': '跳过金额不大于$5的行'
            })
            # 如果之前有未完成的累加，先输出
            if current_sum > 0:
                result.append({
                    'id': row['id'],
                    'name': row['name'],
                    'amount (in $)': current_sum,
                    '说明': f'累计金额：{current_sum}'
                })
                current_sum = 0
        else:
            current_sum += row['amount']
            # 达到阈值则输出并重置累加器
            if current_sum >= threshold:
                result.append({
                    'id': row['id'],
                    'name': row['name'],
                    'amount (in $)': current_sum,
                    '说明': f'累计金额：{current_sum}'
                })
                current_sum = 0
    # 处理分组末尾未完成的累加
    if current_sum > 0:
        result.append({
            'id': group.iloc[0]['id'],
            'name': group.iloc[0]['name'],
            'amount (in $)': current_sum,
            '说明': f'累计金额：{current_sum}'
        })

# 转换为DataFrame并打印
result_df = pd.DataFrame(result)
print(result_df.to_markdown(index=False))

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