如何在SQL中计算非季度末两个月的加权平均余额并追加数据

需求说明 #

针对非季度末月份（排除3、6、9、12月），按照Excel的加权平均逻辑计算WtdAvgBal，将结果以新周期Period='X'的形式追加到原数据集。

示例数据集 #

核心计算逻辑 #

对应Excel的步骤：

• 先计算每行的AvgBal × 当月天数，比如7月有31天，ID1的数值是50×31=1550
• 统计目标非季度末月份的总天数（示例中7+8月合计62天）
• 按ID分组，用SUM(AvgBal×当月天数) ÷ 总天数得到WtdAvgBal，将结果绑定到最近月份的日期，标记周期为X

SQL实现方案 #

用CTE分步处理，逻辑清晰，兼容多数SQL方言（以下以SQL Server为例，其他方言仅需调整日期函数）：

WITH monthly_data AS (
    -- 转换日期格式，计算当月天数，提取月份
    SELECT 
        CONVERT(DATE, Date, 101) AS Date,
        ID,
        AvgBal,
        Period,
        DAY(EOMONTH(CONVERT(DATE, Date, 101))) AS month_days,
        MONTH(CONVERT(DATE, Date, 101)) AS month_num
    FROM your_table_name
),
non_quarter_end AS (
    -- 筛选出非季度末的月份数据
    SELECT *
    FROM monthly_data
    WHERE month_num NOT IN (3,6,9,12)
),
total_days AS (
    -- 计算目标月份的总天数
    SELECT SUM(month_days) AS total_days
    FROM non_quarter_end
),
wtd_avg_bal AS (
    -- 按ID计算加权平均，取最新月份的日期作为记录日期
    SELECT 
        MAX(n.Date) AS Date,
        n.ID,
        SUM(n.AvgBal * n.month_days) / td.total_days AS AvgBal,
        'X' AS Period
    FROM non_quarter_end n
    CROSS JOIN total_days td
    GROUP BY n.ID, td.total_days
)
-- 合并原数据和加权平均结果，按日期、周期、ID排序
SELECT Date, ID, AvgBal, Period FROM your_table_name
UNION ALL
SELECT Date, ID, AvgBal, Period FROM wtd_avg_bal
ORDER BY Date, Period, ID;

方言适配说明 #

如果用MySQL，需要调整日期相关函数：

• 用LAST_DAY(STR_TO_DATE(Date, '%m/%d/%Y'))替代EOMONTH
• 用DAY(LAST_DAY(...))获取当月天数
• 用MONTH(STR_TO_DATE(Date, '%m/%d/%Y'))提取月份

最终输出结果 #

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