基于ID与Country计算Sales的Conditional fractioning需求求助

现有如下数据集：

需要新增衍生字段fraction，计算规则为：按ID分组，每个Country的Sales总和占该ID下所有Sales总和的比例，最终预期结果如下：

注：预期结果中ID=3的Germany记录未出现，推测为输入笔误，以下方案按ID分组内的Country-Sales总和占比逻辑实现。

1. Python Pandas 实现 #

import pandas as pd

# 构造原始数据
df = pd.DataFrame({
    'ID': [1,1,1,2,2,3,3],
    'Country': ['Austria','Austria','Belgium','Belgium','Czech','Denmark','Germany'],
    'Sales': [6,6,6,10,10,3,3]
})

# 计算每个ID+Country的Sales总和
country_total = df.groupby(['ID', 'Country'])['Sales'].sum().reset_index(name='Country_Sales')
# 计算每个ID的总Sales
id_total = df.groupby('ID')['Sales'].sum().reset_index(name='ID_Total')
# 合并并计算占比
merged = pd.merge(country_total, id_total, on='ID')
merged['fraction'] = merged['Country_Sales'] / merged['ID_Total']
# 合并回原数据并保留三位小数
result = pd.merge(df, merged[['ID','Country','fraction']], on=['ID','Country'])
result['fraction'] = result['fraction'].round(3)

print(result)

2. SQL 实现 #

假设数据存储在表sales_data中，使用窗口函数一步完成计算：

SELECT 
    ID,
    Country,
    Sales,
    ROUND(
        SUM(Sales) OVER (PARTITION BY ID, Country) / 
        SUM(Sales) OVER (PARTITION BY ID),
        3
    ) AS fraction
FROM sales_data;

• SUM(Sales) OVER (PARTITION BY ID, Country)：计算每个ID+Country分组的Sales总和
• SUM(Sales) OVER (PARTITION BY ID)：计算每个ID分组的总Sales
• 两者相除后保留三位小数得到目标字段

3. R dplyr 实现 #

library(dplyr)

# 构造原始数据
df <- data.frame(
    ID = c(1,1,1,2,2,3,3),
    Country = c('Austria','Austria','Belgium','Belgium','Czech','Denmark','Germany'),
    Sales = c(6,6,6,10,10,3,3)
)

result <- df %>%
    group_by(ID, Country) %>%
    mutate(Country_Sales = sum(Sales)) %>%
    group_by(ID) %>%
    mutate(fraction = round(Country_Sales / sum(Sales), 3)) %>%
    ungroup() %>%
    select(-Country_Sales)

print(result)

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