在Snowflake SQL中如何将已赚保费分摊至保单起止日期间的各月份？

核心思路 #

按每个月内保单的实际生效天数占总生效天数的比例，分摊保单总成本，核心步骤如下：

• 生成保单覆盖的所有月份区间
• 计算每个月份内的实际生效天数
• 按天数比例计算当月分摊金额
• 可选：将行转列，把月份作为独立字段展示

SQL实现示例（以PostgreSQL为例） #

假设你的保单表名为policies，包含字段：policy_id（保单ID）、start_date（生效日期）、end_date（结束日期）、total_cost（保单总成本）。

1. 生成保单覆盖的所有月份 #

用递归CTE生成每个保单涉及的所有月份的起止日期：

WITH policy_months AS (
    SELECT
        policy_id,
        start_date,
        end_date,
        total_cost,
        DATE_TRUNC('month', start_date)::DATE AS month_start, -- 当月第一天
        (DATE_TRUNC('month', start_date) + INTERVAL '1 month' - INTERVAL '1 day')::DATE AS month_end -- 当月最后一天
    FROM policies
    UNION ALL
    SELECT
        policy_id,
        start_date,
        end_date,
        total_cost,
        (month_start + INTERVAL '1 month')::DATE AS month_start,
        (month_start + INTERVAL '2 months' - INTERVAL '1 day')::DATE AS month_end
    FROM policy_months
    WHERE month_start < DATE_TRUNC('month', end_date) -- 递归直到覆盖结束月份
)

2. 计算月度分摊金额 #

基于上面的CTE，计算每个保单在各月份的生效天数和分摊成本：

SELECT
    policy_id,
    TO_CHAR(month_start, 'MONYY') AS month, -- 格式化为JAN23样式
    -- 计算当月实际生效天数：取保单与月份区间的重叠天数
    GREATEST(LEAST(end_date, month_end), start_date) - GREATEST(start_date, month_start) + 1 AS days_in_month,
    end_date - start_date + 1 AS total_policy_days,
    -- 按比例计算分摊金额，保留两位小数
    ROUND(
        (GREATEST(LEAST(end_date, month_end), start_date) - GREATEST(start_date, month_start) + 1)::NUMERIC
        / (end_date - start_date + 1)::NUMERIC
        * total_cost,
        2
    ) AS allocated_cost
FROM policy_months
ORDER BY policy_id, month_start;

3. 行转列（将月份转为字段） #

如果需要把每个月份显示为单独列（如JAN23、FEB23），可以用CASE WHEN实现静态列：

WITH policy_months AS (
    SELECT
        policy_id,
        start_date,
        end_date,
        total_cost,
        DATE_TRUNC('month', start_date)::DATE AS month_start,
        (DATE_TRUNC('month', start_date) + INTERVAL '1 month' - INTERVAL '1 day')::DATE AS month_end
    FROM policies
    UNION ALL
    SELECT
        policy_id,
        start_date,
        end_date,
        total_cost,
        (month_start + INTERVAL '1 month')::DATE AS month_start,
        (month_start + INTERVAL '2 months' - INTERVAL '1 day')::DATE AS month_end
    FROM policy_months
    WHERE month_start < DATE_TRUNC('month', end_date)
),
monthly_allocations AS (
    SELECT
        policy_id,
        TO_CHAR(month_start, 'MONYY') AS month,
        ROUND(
            (GREATEST(LEAST(end_date, month_end), start_date) - GREATEST(start_date, month_start) + 1)::NUMERIC
            / (end_date - start_date + 1)::NUMERIC
            * total_cost,
            2
        ) AS allocated_cost
    FROM policy_months
)
SELECT
    policy_id,
    SUM(CASE WHEN month = 'JAN23' THEN allocated_cost ELSE 0 END) AS "JAN23",
    SUM(CASE WHEN month = 'FEB23' THEN allocated_cost ELSE 0 END) AS "FEB23",
    SUM(CASE WHEN month = 'MAR23' THEN allocated_cost ELSE 0 END) AS "MAR23"
    -- 按需添加更多月份字段
FROM monthly_allocations
GROUP BY policy_id;

适配不同数据库的注意点 #

• MySQL：替换DATE_TRUNC为DATE_FORMAT(start_date, '%Y-%m-01')，月份最后一天用LAST_DAY(start_date)，日期差用DATEDIFF(结束日期, 开始日期) + 1计算天数。
• SQL Server：用DATEFROMPARTS(YEAR(start_date), MONTH(start_date), 1)获取当月第一天，EOMONTH(start_date)获取当月最后一天，日期差用DATEDIFF(day, 开始日期, 结束日期) + 1。
• 所有数据库需注意日期类型一致性，避免隐式转换错误。

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