主页 / dba / 问题 / 346036
Accepted
Daylon Hunt
Asked: 2025-04-05 02:56:29 +0800 CST

UNION 两个查询然后聚合

  • 772

我有两个不同的查询，需要将它们合并在一起，然后将每个费率类型组聚合到最终表中。对于每个费率组（Rate_001mo、Rate_003mo 等）

SELECT EffectiveDate, 
SUM(CASE WHEN RateName = 'FHLBCSOFR006M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_001mo,
SUM(CASE WHEN RateName = 'FHLBCSOFR006M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_003mo,
SUM(CASE WHEN RateName = 'FHLBCSOFR006M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_006mo,
SUM(CASE WHEN RateName = 'FHLBCSOFR012M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_012mo,
SUM(CASE WHEN RateName = 'FHLBCSOFR024M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_024mo,
SUM(CASE WHEN RateName = 'FHLBCSOFR036M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_036mo
FROM dbo.STAGING_FhlbRates WHERE EffectiveDate = '20250131'
group by EffectiveDate

UNION

SELECT EffectiveDate,
    SUM(CASE WHEN RateName = 'USOSFRA' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_001mo,
    SUM(CASE WHEN RateName = 'USOSFRC' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_003mo,
    SUM(CASE WHEN RateName = 'USOSFRF' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_006mo,
    SUM(CASE WHEN RateName = 'USOSFR1' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_012mo,
    SUM(CASE WHEN RateName = 'USOSFR2' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_024mo,
    SUM(CASE WHEN RateName = 'USOSFR3' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_036mo
    FROM dbo.STAGING_SofrOisRates WHERE EffectiveDate = '20250131'
    GROUP BY EffectiveDate

最终结果表的列应包括生效日期，然后是每个费率组（Rate_001mo、Rate_003mo 等）

aggregate

1 个回答

  1. Best Answer

    除非我从您的描述中遗漏了某些明显的内容，否则您只需嵌套这些并从结果中进行选择。

    SELECT
    x.EffectiveDate,
    SUM(x.Rate_001mo),
    SUM(x.Rate_003mo),
    SUM(x.Rate_006mo),
    SUM(x.Rate_012mo),
    SUM(x.Rate_024mo),
    SUM(x.Rate_036mo)
FROM
(
    SELECT 
        r.EffectiveDate, 
        SUM(CASE WHEN r.RateName = 'FHLBCSOFR006M' THEN r.InterestRate ELSE 0 END) / 100.0 AS Rate_001mo,
        SUM(CASE WHEN r.RateName = 'FHLBCSOFR006M' THEN r.InterestRate ELSE 0 END) / 100.0 AS Rate_003mo,
        SUM(CASE WHEN r.RateName = 'FHLBCSOFR006M' THEN r.InterestRate ELSE 0 END) / 100.0 AS Rate_006mo,
        SUM(CASE WHEN r.RateName = 'FHLBCSOFR012M' THEN r.InterestRate ELSE 0 END) / 100.0 AS Rate_012mo,
        SUM(CASE WHEN r.RateName = 'FHLBCSOFR024M' THEN r.InterestRate ELSE 0 END) / 100.0 AS Rate_024mo,
        SUM(CASE WHEN r.RateName = 'FHLBCSOFR036M' THEN r.InterestRate ELSE 0 END) / 100.0 AS Rate_036mo
    FROM dbo.STAGING_FhlbRates AS r
    WHERE r.EffectiveDate = '20250131'
    GROUP BY 
        r.EffectiveDate
    
    UNION
    
    SELECT 
        r.EffectiveDate,
        SUM(CASE WHEN r.RateName = 'USOSFRA' THEN r.InterestRate ELSE 0 END) / 100.0 AS Rate_001mo,
        SUM(CASE WHEN r.RateName = 'USOSFRC' THEN r.InterestRate ELSE 0 END) / 100.0 AS Rate_003mo,
        SUM(CASE WHEN r.RateName = 'USOSFRF' THEN r.InterestRate ELSE 0 END) / 100.0 AS Rate_006mo,
        SUM(CASE WHEN r.RateName = 'USOSFR1' THEN r.InterestRate ELSE 0 END) / 100.0 AS Rate_012mo,
        SUM(CASE WHEN r.RateName = 'USOSFR2' THEN r.InterestRate ELSE 0 END) / 100.0 AS Rate_024mo,
        SUM(CASE WHEN r.RateName = 'USOSFR3' THEN r.InterestRate ELSE 0 END) / 100.0 AS Rate_036mo
    FROM dbo.STAGING_SofrOisRates AS r
    WHERE r.EffectiveDate = '20250131'
    GROUP BY 
        r.EffectiveDate
) AS x
GROUP BY
    x.EffectiveDate;
    • 3

相关问题