Início / dba / Perguntas / 346036
Accepted
Daylon Hunt
Asked: 2025-04-05 02:56:29 +0800 CST

UNION Duas consultas e depois agregação

  • 772

Tenho duas consultas diferentes que preciso unir em UNION e, então, agregar cada grupo de tipo de taxa em uma tabela final. Para cada grupo de taxa (Rate_001mo, Rate_003mo, etc.)

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

As colunas da tabela de resultados finais devem incluir a Data Efetiva e, em seguida, cada grupo de taxas (Taxa_001mês, Taxa_003mês, etc.)

aggregate

1 respostas

  1. Best Answer

    A menos que eu esteja esquecendo de algo óbvio na sua descrição, você só precisa aninhá-los e selecionar entre os resultados.

    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

relate perguntas