基于销售数据拆分月度预算剩余天数的SQL查询需求

现有表结构与初始数据 #

CREATE TABLE MasterData (
Business_Date DATE,
Sales float,
Budget float,
Difference_Budget float,
New_Budget float
);

INSERT INTO MasterData VALUES
('2023-03-01', 100, 150, 0, 0),
('2023-03-02', 200, 190, 0, 0),
('2023-03-03', 0, 180, 0, 0);

计算逻辑 #

• Difference_Budget：Budget - Sales
• New_Budget：当日Budget + 此前所有日期的差异值按对应日期当月剩余天数拆分后的累加值
  • 示例：
    • 2023-03-01的New_Budget = 当日Budget = 150
    • 2023-03-02的New_Budget = 190 + (50/30) ≈ 191.67（50是3月1日的差异，30是3月剩余天数）
    • 2023-03-03的New_Budget = 180 + (50/30)+(10/29) ≈ 182.01（10是3月2日的差异，29是3月2日的剩余天数）

适配C#的SQL SELECT查询语句 #

SELECT 
    md.Business_Date,
    md.Sales,
    md.Budget,
    -- 计算Difference_Budget
    (md.Budget - md.Sales) AS Difference_Budget,
    -- 计算New_Budget
    md.Budget + COALESCE(
        SUM(
            (prev.Budget - prev.Sales) 
            / (DATEPART(day, EOMONTH(prev.Business_Date)) - DATEPART(day, prev.Business_Date))
        ) OVER (ORDER BY md.Business_Date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
        0
    ) AS New_Budget
FROM MasterData md
ORDER BY md.Business_Date;

语句说明 #

1. Difference_Budget计算：直接通过Budget - Sales得到当日差异值
2. New_Budget计算：
   • 用窗口函数SUM() OVER()累加此前所有日期的差异拆分值
   • DATEPART(day, EOMONTH(prev.Business_Date))获取当前日期所在月份的总天数
   • DATEPART(day, prev.Business_Date)获取当前日期的日份，两者相减得到对应日期的当月剩余天数
   • COALESCE()处理第一个日期（无此前数据）的情况，返回0，确保New_Budget等于当日Budget
3. 适配C#：该语句可直接嵌入C#的SQL命令执行逻辑中，无需额外特殊处理，若后续有动态查询条件，建议使用参数化查询避免注入风险

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