SQL实现任意非连续3个月聚合成本计算及90%占比验证

要判断是否存在任意3个非连续月份的成本总和占全年总成本≥90%，最优思路是直接取该成员成本最高的3个月求和——因为这3个月的和是所有3个月组合中最大的，如果这个最大和都达不到90%，其他组合肯定也不行；如果达标，那直接满足条件。这种方法不需要递归或循环，效率远高于遍历所有组合。

SQL 实现方案 #

步骤说明 #

1. 计算每个成员的全年总成本
2. 对每个成员的月度成本降序排序，取前3个求和
3. 比较Top3和与全年总成本的占比，输出判断结果

代码示例 #

WITH member_yearly_total AS (
    SELECT 
        MemberID,
        SUM(TotalCost) AS yearly_total
    FROM your_table_name -- 替换为你的实际表名
    GROUP BY MemberID
),
member_top3_costs AS (
    SELECT 
        MemberID,
        SUM(TotalCost) AS top3_sum
    FROM (
        SELECT 
            MemberID,
            TotalCost,
            ROW_NUMBER() OVER(PARTITION BY MemberID ORDER BY TotalCost DESC) AS rank_num
        FROM your_table_name
        WHERE TotalCost > 0 -- 过滤0成本月份，不影响最终判断
    ) ranked_costs
    WHERE rank_num <= 3
    GROUP BY MemberID
)
SELECT 
    m.MemberID,
    m.yearly_total,
    mt.top3_sum,
    ROUND((mt.top3_sum::FLOAT / m.yearly_total) * 100, 2) AS percentage,
    CASE 
        WHEN (mt.top3_sum::FLOAT / m.yearly_total) >= 0.9 THEN '存在'
        ELSE '不存在'
    END AS meets_condition
FROM member_yearly_total m
JOIN member_top3_costs mt ON m.MemberID = mt.MemberID;

示例数据验证 #

你的示例数据中，全年总成本为7380，Top3成本为5000+1000+330=6330，占比约85.77%，因此结果为不存在。

Python (Pandas) 实现方案 #

最优方案（Top3求和） #

import pandas as pd

# 加载示例数据（可替换为你的实际数据读取逻辑）
data = [
    (1, 'Jan2023', 100), (1, 'Feb2023', 100), (1, 'Mar2023', 1000),
    (1, 'Apr2023', 100), (1, 'May2023', 200), (1, 'Jun2023', 100),
    (1, 'Jul2023', 5000), (1, 'Aug2023', 300), (1, 'Sep2023', 330),
    (1, 'Oct2023', 50), (1, 'Nov2023', 0), (1, 'Dec2023', 100)
]
df = pd.DataFrame(data, columns=['MemberID', 'MonthYearofDateofService', 'TotalCost'])

# 按成员分组计算判断结果
result = df.groupby('MemberID').apply(
    lambda x: pd.Series({
        'yearly_total': x['TotalCost'].sum(),
        'top3_sum': x['TotalCost'].nlargest(3).sum(),
        'percentage': round((x['TotalCost'].nlargest(3).sum() / x['TotalCost'].sum()) * 100, 2),
        'meets_condition': x['TotalCost'].nlargest(3).sum() / x['TotalCost'].sum() >= 0.9
    })
).reset_index()

print(result)

备选：遍历所有3个月组合（效率较低） #

如果有特殊需求必须遍历所有可能的3个月组合，可以用itertools.combinations：

import pandas as pd
from itertools import combinations

def check_combination_condition(group):
    yearly_total = group['TotalCost'].sum()
    if yearly_total == 0:
        return False  # 避免除以0错误
    # 生成所有3个月的成本组合并判断
    for combo in combinations(group['TotalCost'], 3):
        if sum(combo) / yearly_total >= 0.9:
            return True
    return False

# 按成员分组判断
result = df.groupby('MemberID').apply(check_combination_condition).reset_index(name='meets_condition')
print(result)

注意事项 #

• 最优方案的核心逻辑是最大和优先判断，避免了不必要的计算，适合大数据量场景
• 若存在多个成员，上述方案会自动按成员分组处理
• 需单独处理总成本为0的情况，避免除以0错误

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