按起止日期差值计算单账号每日账单的SQL查询需求

现有billing表数据如下：

需求说明 #

为账号ID 123计算每日账单金额，规则如下：

• 若记录的起止日期为同一天，全额计入当日；
• 若跨天，将金额按涉及天数均分至对应日期。

示例：

• 第一行记录起止日期间隔为0天，金额10全额计入2023-11-16；
• 第二行起止日期间隔为1天（共涉及2天），金额10均分后每天计入5，分别归属2023-11-16和2023-11-17；

预期计算结果 #

• 2023-11-16：10+5=15
• 2023-11-17：5+20=25
• 2023-11-18：10+10=20
• 2023-11-19：10+10=20

尝试的错误SQL #

select start_date, end_date, account_id, amount, datediff(day, start_date, end_date) as interval into #temp 
from billing where account_id = '123';

select * from #temp;
drop table if exists #result;
select account_id, start_date, end_date, interval, 
  CASE WHEN interval = 0 THEN amount ELSE amount/(interval + 1) END AS per_day_cost
INTO #result
UNION ALL
SELECT  account_id, start_date, end_date, interval, 
  CASE WHEN interval = 0 THEN amount ELSE amount/(interval + 1) END AS per_day_cost
FROM (VALUES (1), (2), (3)) as v(n) CROSS JOIN
  (SELECT account_id, start_date, end_date, interval, amount FROM #temp) s
where v.n <= interval
order by account_id;

select * from #result;

正确的SQL实现 #

以下是基于SQL Server的解决方案，通过生成日期序列拆分跨天记录，再按日期汇总金额：

WITH DateRange AS (
    -- 生成每条记录涉及的所有日期
    SELECT 
        account_id,
        amount,
        DATEADD(day, n, CAST(start_date AS DATE)) AS bill_date,
        -- 计算涉及的总天数
        DATEDIFF(day, start_date, end_date) + 1 AS total_days
    FROM billing
    -- 递归生成日期序列，覆盖从start_date到end_date的所有日期
    CROSS APPLY (
        SELECT TOP (DATEDIFF(day, start_date, end_date) + 1) 
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
        FROM master..spt_values
    ) AS Numbers
    WHERE account_id = '123'
),
DailyCost AS (
    -- 计算每条记录在对应日期的分摊金额
    SELECT 
        bill_date,
        CASE 
            WHEN total_days = 1 THEN amount 
            ELSE amount / total_days 
        END AS daily_amount
    FROM DateRange
)
-- 按日期汇总总金额
SELECT 
    bill_date,
    SUM(daily_amount) AS total_daily_cost
FROM DailyCost
GROUP BY bill_date
ORDER BY bill_date;

逻辑说明 #

1. DateRange CTE：通过CROSS APPLY结合系统表master..spt_values生成每条记录覆盖的所有日期，同时计算该记录涉及的总天数；
2. DailyCost CTE：根据总天数判断是全额计入还是均分金额；
3. 最后按日期分组汇总，得到每日的总账单金额。

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