基于合同起止日期按月统计总销售额（SQL/Python/R实现）

核心逻辑 #

统计规则：对每个自然月，汇总所有生效合同的销售额，生效判定标准为当月月初日期落在合同开始日期与合同结束日期区间内。最终输出Date（每月1号，格式为dd-mm-yyyy）、SALE（月度总销售额）两个字段。

若需将合同总金额平摊到每个生效月，只需将单条合同的SALE值替换为「合同总金额/合同总生效月数」再做聚合即可。

原始数据样例 #

参考输出样例 #

代码实现 #

SQL（兼容MySQL 8.0+、PostgreSQL 8.4+） #

核心思路：通过递归CTE生成覆盖全合同周期的连续月份，格式化合同日期后做关联聚合。

-- 假设原始表名为contracts
WITH RECURSIVE months AS (
    -- 生成序列起点：所有合同最早的开始月份
    SELECT DATE_FORMAT(MIN(STR_TO_DATE(`Contract Start Date`, '%d-%m-%Y')), '%Y-%m-01') AS month_start
    FROM contracts
    UNION ALL
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH)
    FROM months
    -- 生成序列终点：所有合同最晚的结束月份
    WHERE month_start < (SELECT DATE_FORMAT(MAX(STR_TO_DATE(`Contract End Date`, '%d-%m-%Y')), '%Y-%m-01') FROM contracts)
),
contracts_std AS (
    -- 格式化合同日期为月度第一天的标准格式
    SELECT
        SALE,
        DATE_FORMAT(STR_TO_DATE(`Contract Start Date`, '%d-%m-%Y'), '%Y-%m-01') AS start_m,
        DATE_FORMAT(STR_TO_DATE(`Contract End Date`, '%d-%m-%Y'), '%Y-%m-01') AS end_m
    FROM contracts
)
-- 关联聚合得到结果
SELECT
    DATE_FORMAT(m.month_start, '%d-%m-%Y') AS `Date`,
    ROUND(SUM(c.SALE), 2) AS SALE
FROM months m
LEFT JOIN contracts_std c
ON m.month_start BETWEEN c.start_m AND c.end_m
GROUP BY m.month_start
ORDER BY m.month_start;

PostgreSQL适配提示：将STR_TO_DATE替换为to_date(字段, 'DD-MM-YYYY')，DATE_ADD(month_start, INTERVAL 1 MONTH)替换为month_start + INTERVAL '1 month'，DATE_FORMAT替换为to_char即可。

Python（基于Pandas） #

核心思路：解析日期后生成连续月份序列，通过交叉连接匹配所有生效合同后聚合。

import pandas as pd

# 读取数据，以下为样例数据构造逻辑，实际使用替换为pd.read_csv等读取方法
df = pd.DataFrame([
    [253050, 134.00, '01-04-2019', '01-04-2026'],
    [222015, 142.00, '01-07-2017', '01-07-2022'],
    [253010, 134.00, '01-02-2018', '01-02-2024'],
    [251045, 110.00, '01-05-2015', '01-07-2028'],
    [345082, 100.00, '01-04-2012', '01-04-2030'],
    [208321, 110.00, '01-02-2018', '01-07-2027'],
    [780193, 99.00, '01-04-2015', '01-04-2026'],
    [222015, 142.00, '01-07-2017', '01-07-2022']
], columns=['Account Number', 'SALE', 'Contract Start Date', 'Contract End Date'])

# 日期格式化，统一取月度第一天
df['start_m'] = pd.to_datetime(df['Contract Start Date'], format='%d-%m-%Y').dt.to_period('M').dt.to_timestamp()
df['end_m'] = pd.to_datetime(df['Contract End Date'], format='%d-%m-%Y').dt.to_period('M').dt.to_timestamp()

# 生成连续月份序列
month_range = pd.date_range(start=df['start_m'].min(), end=df['end_m'].max(), freq='MS')
months = pd.DataFrame({'month': month_range})

# 交叉连接过滤生效合同，聚合得到结果
result = months.merge(df, how='cross')
result = result[result['month'].between(result['start_m'], result['end_m'])]
result = result.groupby('month', as_index=False)['SALE'].sum().round(2)
result['Date'] = result['month'].dt.strftime('%d-%m-%Y')
result = result[['Date', 'SALE']]

R（基于tidyverse/lubridate） #

核心思路与Python实现一致，通过日期处理、生成连续序列、关联过滤、聚合四步完成计算。

library(tidyverse)
library(lubridate)

# 读取数据，以下为样例数据构造逻辑，实际使用替换为read_csv等读取方法
df <- tribble(
  ~`Account Number`, ~SALE, ~`Contract Start Date`, ~`Contract End Date`,
  253050, 134.00, "01-04-2019", "01-04-2026",
  222015, 142.00, "01-07-2017", "01-07-2022",
  253010, 134.00, "01-02-2018", "01-02-2024",
  251045, 110.00, "01-05-2015", "01-07-2028",
  345082, 100.00, "01-04-2012", "01-04-2030",
  208321, 110.00, "01-02-2018", "01-07-2027",
  780193, 99.00, "01-04-2015", "01-04-2026",
  222015, 142.00, "01-07-2017", "01-07-2022"
)

# 日期格式化，统一取月度第一天
df <- df %>%
  mutate(
    start_m = floor_date(dmy(`Contract Start Date`), unit = "month"),
    end_m = floor_date(dmy(`Contract End Date`), unit = "month")
  )

# 生成连续月份序列
month_range <- seq.Date(min(df$start_m), max(df$end_m), by = "month")
months <- tibble(month = month_range)

# 关联过滤生效合同，聚合得到结果
result <- months %>%
  crossing(df) %>%
  filter(month >= start_m & month <= end_m) %>%
  group_by(month) %>%
  summarise(SALE = round(sum(SALE), 2), .groups = "drop") %>%
  mutate(Date = format(month, "%d-%m-%Y")) %>%
  select(Date, SALE)

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