如何在Databricks SQL中实现按公司聚合多维度季度数据为年度分段汇总宽表

看起来你已经完成了第一步的聚合计算，现在需要把“长表”转成每个公司一行的“宽表”，同时把meas和volume的维度整合到列名里，还要新增Amort相关的汇总列（也就是Interest和Principal的对应分段总和）。我来一步步帮你实现这个需求：

思路拆解 #

1. 先完成基础分段聚合：计算每个(Company, Meas, Volume)组合下的1YR（Q1-Q4）、2YR（Q5-Q8）、3YR（Q9-Q12）总和，注意处理缺失的季度数据（比如你输入里的Q2，用COALESCE把NULL转成0避免求和出错）。
2. 行转列预处理：把1YR/2YR/3YR这三个分段字段拆成行，同时把meas和volume拼接成一个维度标识，方便后续转成宽表的列名。
3. 转成宽表：用PIVOT把预处理后的行数据转成你需要的宽表结构。
4. 计算Amort列：因为Amort是Interest和Principal的对应分段总和，直接把对应的列相加即可。

完整SQL代码 #

WITH base_agg AS (
    -- 第一步：计算每个(公司, 指标, 类型)的三个年度分段总和
    SELECT 
        company,
        CONCAT(meas, '_', volume) AS meas_volume,
        -- 1YR = Q1+Q2+Q3+Q4，用COALESCE处理缺失的季度值
        COALESCE(Q1, 0) + COALESCE(Q2, 0) + COALESCE(Q3, 0) + COALESCE(Q4, 0) AS 1YR,
        COALESCE(Q5, 0) + COALESCE(Q6, 0) + COALESCE(Q7, 0) + COALESCE(Q8, 0) AS 2YR,
        COALESCE(Q9, 0) + COALESCE(Q10, 0) + COALESCE(Q11, 0) + COALESCE(Q12, 0) AS 3YR
    FROM your_table_name -- 替换成你的实际表名
),
unpivoted AS (
    -- 第二步：把年度分段字段拆成行，生成"分段_指标_类型"的列名标识
    SELECT 
        company,
        CONCAT(period, '_', meas_volume) AS column_name,
        value
    FROM base_agg
    UNPIVOT (
        value FOR period IN (1YR, 2YR, 3YR)
    )
)
-- 第三步：转成宽表并计算Amort列
SELECT 
    company,
    -- 输出Interest相关列
    `1YR_Interest_Existing`,
    `2YR_Interest_Existing`,
    `3YR_Interest_Existing`,
    `1YR_Interest_New`,
    `2YR_Interest_New`,
    `3YR_Interest_New`,
    -- 输出Principal相关列
    `1YR_Principal_Existing`,
    `2YR_Principal_Existing`,
    `3YR_Principal_Existing`,
    `1YR_Principal_New`,
    `2YR_Principal_New`,
    `3YR_Principal_New`,
    -- 计算Amort相关列（Interest+Principal的对应分段总和）
    COALESCE(`1YR_Interest_Existing`, 0) + COALESCE(`1YR_Principal_Existing`, 0) AS `1YR_Amort_Existing`,
    COALESCE(`2YR_Interest_Existing`, 0) + COALESCE(`2YR_Principal_Existing`, 0) AS `2YR_Amort_Existing`,
    COALESCE(`3YR_Interest_Existing`, 0) + COALESCE(`3YR_Principal_Existing`, 0) AS `3YR_Amort_Existing`,
    COALESCE(`1YR_Interest_New`, 0) + COALESCE(`1YR_Principal_New`, 0) AS `1YR_Amort_New`,
    COALESCE(`2YR_Interest_New`, 0) + COALESCE(`2YR_Principal_New`, 0) AS `2YR_Amort_New`,
    COALESCE(`3YR_Interest_New`, 0) + COALESCE(`3YR_Principal_New`, 0) AS `3YR_Amort_New`
FROM unpivoted
PIVOT (
    MAX(value) FOR column_name IN (
        '1YR_Interest_Existing', '2YR_Interest_Existing', '3YR_Interest_Existing',
        '1YR_Interest_New', '2YR_Interest_New', '3YR_Interest_New',
        '1YR_Principal_Existing', '2YR_Principal_Existing', '3YR_Principal_Existing',
        '1YR_Principal_New', '2YR_Principal_New', '3YR_Principal_New'
    )
)
ORDER BY company;

关键细节说明 #

• 处理缺失季度：用COALESCE把NULL的季度值转成0，确保求和结果不会出现NULL。
• UNPIVOT的作用：把原来的1YR/2YR/3YR三个列拆成行，这样我们可以把“分段+指标+类型”组合成一个完整的列名标识，为后续PIVOT做准备。
• PIVOT的列名：需要明确列出所有要生成的列名（除了Amort，因为它是计算出来的），如果后续有新的meas或volume类型，只要在IN子句里添加对应的列名即可。
• Amort列计算：直接把Interest和Principal的对应分段列相加，同样用COALESCE处理可能的NULL值，避免结果为NULL。

内容来源于stack exchange