请求将多年期合同记录拆分为按年（365天）划分的多行

需求回顾 #

现有合同数据表包含Contract（合同编号）、start_date（起始日期）、End_date（结束日期）字段，需将每份合同拆分为以起始日期为基准、每365天一个周期的多行记录，直到覆盖原合同结束日期，最终结果新增S.No（序号）字段。

原始数据表：

目标结果示例：

方案1：SQL实现（数据库环境） #

思路 #

用递归CTE生成每个合同的年度周期记录，计算每个周期的起止日期，最后添加全局序号。

代码示例（MySQL） #

WITH RECURSIVE contract_periods AS (
    -- 初始行：生成第一个周期的起止日期
    SELECT 
        Contract,
        start_date,
        -- 第一个周期结束为起始日+364天（确保365天周期）
        DATE_ADD(start_date, INTERVAL 364 DAY) AS period_end,
        End_date AS original_end
    FROM contracts
    UNION ALL
    -- 递归生成后续周期
    SELECT 
        cp.Contract,
        DATE_ADD(cp.start_date, INTERVAL 365 DAY) AS new_start,
        DATE_ADD(DATE_ADD(cp.start_date, INTERVAL 365 DAY), INTERVAL 364 DAY) AS new_end,
        cp.original_end
    FROM contract_periods cp
    -- 终止条件：下一个周期的起始日不超过原始合同结束日
    WHERE DATE_ADD(cp.start_date, INTERVAL 365 DAY) <= cp.original_end
)
-- 最终结果：调整最后一个周期的结束日，添加序号
SELECT 
    ROW_NUMBER() OVER (ORDER BY Contract, start_date) AS `S.No`,
    Contract,
    start_date,
    -- 若周期结束日超过原始结束日，则取原始结束日
    CASE 
        WHEN period_end > original_end THEN original_end
        ELSE period_end
    END AS End_date
FROM contract_periods
ORDER BY `S.No`;

方案2：Excel实现（非数据库场景） #

手动操作步骤 #

1. 将原始数据放在A-C列（A:Contract，B:start_date，C:End_date）。
2. 生成周期起始日：
   • D2单元格输入=B2，D3输入=IF(D2+365<=C$2, D2+365, "")，下拉直到空值出现；对每个合同重复此操作。
3. 计算周期结束日：
   • E2单元格输入=IF(D2="","",MIN(D2+364,C2))，下拉填充对应D列非空单元格。
4. 合并所有合同的周期记录到新表，在新表首列输入=ROW()-ROW(新表首行)+1生成序号。

动态数组公式（Excel 365） #

一次性生成所有结果：

=LET(
    data, A2:C4,
    contracts, INDEX(data,,1),
    starts, INDEX(data,,2),
    ends, INDEX(data,,3),
    periods, CEILING((ends - starts +1)/365,1),
    all_starts, TOCOL(starts + SEQUENCE(MAX(periods))*365 -365,2),
    matched_contracts, TOCOL(IF(SEQUENCE(MAX(periods))<=periods,contracts,""),2),
    matched_ends, TOCOL(IF(SEQUENCE(MAX(periods))<=periods,ends,""),2),
    all_ends, MIN(all_starts+364,matched_ends),
    HSTACK(SEQUENCE(ROWS(all_starts)),matched_contracts,all_starts,all_ends)
)

方案3：Python实现（数据分析场景） #

思路 #

用Pandas库遍历每个合同，生成对应周期记录后合并结果，添加序号。

代码示例 #

import pandas as pd
from datetime import timedelta

# 加载原始数据
df = pd.DataFrame({
    'Contract': [1,23,26],
    'start_date': pd.to_datetime(['8/1/2022','8/7/2022','6/8/2022']),
    'End_date': pd.to_datetime(['7/31/2024','8/8/2023','6/9/2025'])
})

# 定义拆分单个合同的函数
def split_contract(row):
    contract = row['Contract']
    start = row['start_date']
    end = row['End_date']
    periods = []
    current_start = start
    
    while True:
        next_start = current_start + timedelta(days=365)
        if next_start > end:
            # 剩余天数不足365天，直接取原始结束日
            periods.append({'Contract': contract, 'start_date': current_start, 'End_date': end})
            break
        else:
            # 生成完整365天周期
            current_end = next_start - timedelta(days=1)
            periods.append({'Contract': contract, 'start_date': current_start, 'End_date': current_end})
            current_start = next_start
    
    return pd.DataFrame(periods)

# 应用函数到所有合同并合并结果
result = pd.concat([split_contract(row) for _, row in df.iterrows()], ignore_index=True)
# 添加序号
result.insert(0, 'S.No', range(1, len(result)+1))

# 格式化日期显示（可选）
result['start_date'] = result['start_date'].dt.strftime('%m/%d/%Y')
result['End_date'] = result['End_date'].dt.strftime('%m/%d/%Y')

print(result)

输出结果将与示例完全一致。

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