如何在表格中按日期分组计算行值比值（C=A/B）

以下是针对不同工具场景的具体实现方法，满足给每个日期新增measure=C行（值为同日期A/B）的需求：

原始数据表格 #

目标结果表格 #

方法1：Excel/Google Sheets #

手动批量操作 #

1. 按date列排序，确保同一日期的A、B行相邻
2. 在第一个日期的B行下方插入空行，填写对应date，measure列输入C
3. value单元格输入公式（以第一个C行为例）：=B2/B3（假设A值在B2，B值在B3）
4. 选中新增的C行，复制到其他日期的B行下方，表格会自动调整单元格引用

Power Query 自动生成（更高效） #

1. 选中数据区域，进入「数据」选项卡，点击「从表格/区域」
2. 在Power Query编辑器中，选中date列，点击「转换」→「透视列」，设置透视列为measure，值列为value
3. 新增列命名为C，输入公式：=[A]/[B]
4. 选中date、A、B、C列，点击「转换」→「逆透视列」，取消勾选date列
5. 调整列顺序后，加载回表格即可

方法2：Python Pandas #

import pandas as pd

# 加载原始数据
df = pd.DataFrame({
    'date': ['2022-12-09', '2022-12-09', '2022-12-03', '2022-12-03'],
    'measure': ['A', 'B', 'A', 'B'],
    'value': [10, 2, 300, 30]
})

# 透视表格将A/B转为列，计算C值后逆透视回原格式
pivoted_df = df.pivot(index='date', columns='measure', values='value').reset_index()
pivoted_df['C'] = pivoted_df['A'] / pivoted_df['B']
result_df = pivoted_df.melt(id_vars='date', var_name='measure', value_name='value')

# 按日期排序，匹配目标格式
result_df = result_df.sort_values('date', ascending=False).reset_index(drop=True)
print(result_df)

方法3：SQL #

假设数据存储在measures表中，可通过以下语句生成结果：

-- 合并原始数据与新增的C行
SELECT date, measure, value
FROM measures
UNION ALL
SELECT 
    date,
    'C' AS measure,
    (SELECT value FROM measures m2 WHERE m2.date = m1.date AND m2.measure = 'A') /
    (SELECT value FROM measures m3 WHERE m3.date = m1.date AND m3.measure = 'B') AS value
FROM (SELECT DISTINCT date FROM measures) m1
ORDER BY date DESC, measure;

或者用窗口函数优化性能：

WITH date_values AS (
    SELECT 
        date,
        MAX(CASE WHEN measure = 'A' THEN value END) OVER (PARTITION BY date) AS a_val,
        MAX(CASE WHEN measure = 'B' THEN value END) OVER (PARTITION BY date) AS b_val
    FROM measures
)
SELECT date, measure, value
FROM measures
UNION ALL
SELECT DISTINCT date, 'C' AS measure, a_val / b_val AS value
FROM date_values
ORDER BY date DESC, measure;

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