如何用Pandas实现按日累计的国家-对象月度最大值滚动求和？

现有以record_date为索引的数据集，记录了2022年7月各国对象的每日使用情况（部分对象并非每日都有使用记录）：

country  objectid  objectuse
record_date
2022-07-20    chile         0          4
2022-07-01    chile         1          4
2022-07-02    chile         1          4
2022-07-03    chile         1          4
2022-07-04    chile         1          4
...             ...       ...        ...
2022-07-26     peru      3088          4
2022-07-27     peru      3088          4
2022-07-28     peru      3088          4
2022-07-30     peru      3088          4
2022-07-31     peru      3088          4

目前已通过以下代码实现各国所有对象月度最大值的总和计算：

df.groupby(['country', 'objectid']).max().groupby(level=0).sum()

输出结果：

objectuse
country
chile         1224
peru         17008

实际需求是生成按日期维度、各国累计到当日的所有对象月度最大值的滚动求和结果，示例如下：

country       sum  
record_date
2022-07-01    chile         1
2022-07-01     peru         1
2022-07-02    chile         2
2022-07-02     peru         3
...             ...       ...
2022-07-31    chile       1224
2022-07-31     peru      17008

要求避免循环过滤方式，同时需提供可转换为SQL的实现逻辑。

Pandas 实现方案 #

步骤1：计算每个对象的月度最大值及首次出现日期 #

先为每个country+objectid组合计算月度objectuse最大值，同时记录该对象首次出现的日期：

obj_max = df.reset_index().groupby(['country', 'objectid']).agg(
    max_use=('objectuse', 'max'),
    first_date=('record_date', 'min')
).reset_index()

步骤2：生成全量日期序列 #

生成2022年7月的所有日期，确保无日期遗漏：

import pandas as pd

date_range = pd.date_range(start='2022-07-01', end='2022-07-31', freq='D')

步骤3：生成日期与国家的全组合 #

为每个国家匹配所有日期，构建基础数据集：

countries = df['country'].unique()
date_country = pd.MultiIndex.from_product([date_range, countries], names=['record_date', 'country']).to_frame(index=False)

步骤4：计算滚动累计求和 #

关联对象数据与日期-国家组合，筛选出首次出现日期≤当前日期的对象，再按日期和国家累计求和：

result = pd.merge(date_country, obj_max, on='country', how='left')
result['max_use'] = result.apply(lambda x: x['max_use'] if x['first_date'] <= x['record_date'] else 0, axis=1)

final_result = result.groupby(['record_date', 'country'])['max_use'].sum().reset_index().set_index('record_date')
final_result = final_result.rename(columns={'max_use': 'sum'})

最终的final_result即为符合需求的滚动累计结果。

SQL 实现方案 #

假设数据集表名为daily_object_use，字段为record_date、country、objectid、objectuse，以下是SQL实现逻辑（以PostgreSQL为例）：

WITH obj_stats AS (
    -- 预计算每个对象的月度最大值和首次出现日期
    SELECT 
        country,
        objectid,
        MAX(objectuse) AS max_use,
        MIN(record_date) AS first_date
    FROM daily_object_use
    WHERE record_date BETWEEN '2022-07-01' AND '2022-07-31'
    GROUP BY country, objectid
),
date_series AS (
    -- 生成7月全量日期序列
    SELECT generate_series('2022-07-01'::DATE, '2022-07-31'::DATE, '1 day'::INTERVAL) AS record_date
),
date_country AS (
    -- 生成日期与国家的全组合
    SELECT 
        ds.record_date::DATE,
        c.country
    FROM date_series ds
    CROSS JOIN (SELECT DISTINCT country FROM daily_object_use) c
)
-- 计算滚动累计求和
SELECT 
    dc.record_date,
    dc.country,
    SUM(CASE WHEN os.first_date <= dc.record_date THEN os.max_use ELSE 0 END) AS sum
FROM date_country dc
LEFT JOIN obj_stats os ON dc.country = os.country
GROUP BY dc.record_date, dc.country
ORDER BY dc.record_date, dc.country;

不同数据库日期序列生成语法差异：

• MySQL：使用递归CTE生成日期序列
• SQL Server：结合DATEADD与递归CTE生成日期
• Oracle：使用CONNECT BY语法生成日期序列

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