SQL实现金融Gap analysis：季度交易数据转换为月度平滑金额

• 先将原表中每个季度的首月数据，展开为该季度对应的3个连续月度
• 给每个季度的3个月度都关联对应季度的总金额
• 按规则计算平滑金额：季度前2个月的平滑金额为季度总金额除以3后取整，第3个月的平滑金额为季度总金额减去前2个月平滑金额的总和
• 原始季度金额仅在季度首月展示，其余月份留空

WITH original_quarterly_data AS (
    -- 替换为你的原季度数据表查询逻辑
    SELECT 
        "Month"::date AS quarter_start_month,
        "Description",
        "Amount" AS quarterly_amt
    FROM your_quarterly_table
),
-- 展开每个季度为3个连续月度
expanded_month_list AS (
    SELECT
        quarter_start_month,
        "Description",
        quarterly_amt,
        generate_series(quarter_start_month, quarter_start_month + INTERVAL '2 months', INTERVAL '1 month') AS cur_month
    FROM original_quarterly_data
)
SELECT
    TO_CHAR(cur_month, 'YYYY-MM-DD') AS "Month",
    "Description",
    -- 计算平滑金额
    CASE
        -- 季度前2个月
        WHEN EXTRACT(MONTH FROM cur_month) IN (EXTRACT(MONTH FROM quarter_start_month), EXTRACT(MONTH FROM quarter_start_month)+1)
        THEN ROUND(quarterly_amt::NUMERIC / 3, 0)::INT
        -- 季度第3个月
        ELSE quarterly_amt - 2 * ROUND(quarterly_amt::NUMERIC / 3, 0)::INT
    END AS "smoothed amount",
    -- 仅季度首月展示原始金额
    CASE WHEN cur_month = quarter_start_month THEN quarterly_amt ELSE NULL END AS "Original quarterly Amt"
FROM expanded_month_list
ORDER BY cur_month;

• MySQL：可使用递归CTE生成连续3个月份，日期函数替换为DATE_ADD，取月份用MONTH()函数
• Hive/Spark SQL：可通过posexplode(split('0,1,2',','))生成3个月份偏移量，配合add_months函数生成连续月度
• 所有数据库适配时核心计算逻辑保持一致，仅日期生成、类型转换函数可根据数据库语法调整

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