大量单独计算的瀑布结果

我会做类似的事情，以尽量减少对现有查询的更改。如果它确实应该是 0 而不是空，您可能需要更改条件检查以使用哪个指标，但这应该可以传达这个想法。

SELECT AccountNbr
    ,SUM(CASE WHEN MetricToUse = 'CurrSofrOn' THEN CurrSofrOn ELSE NULL END) AS CurrSofrOn
    ,SUM(CASE WHEN MetricToUse = 'CurrTlpNonStableBase' THEN CurrTlpNonStableBase ELSE NULL END) AS CurrTlpNonStableBase
    ,SUM(CASE WHEN MetricToUse = 'CurrTlpStableBase' THEN CurrTlpStableBase ELSE NULL END ) AS CurrTlpStableBase
    ,SUM(CASE WHEN MetricToUse = 'CurrBeta' THEN CurrBeta ELSE NULL END ) AS CurrBeta
    ,SUM(CASE WHEN MetricToUse = 'CurrFtp' THEN CurrFtp ELSE NULL END ) AS CurrFtp
    ,SUM(CASE WHEN MetricToUse = 'CurrBase' THEN CurrBase ELSE NULL END ) AS CurrBase
    ,SUM(CASE WHEN MetricToUse = 'CurrStabilityAdj' THEN CurrStabilityAdj ELSE NULL END ) AS CurrStabilityAdj
    ,SUM(CASE WHEN MetricToUse = 'CurrTlpAdjAge' THEN CurrTlpAdjAge ELSE NULL END ) AS CurrTlpAdjAge
    ,SUM(CASE WHEN MetricToUse = 'CurrModeled' THEN CurrModeled ELSE NULL END ) AS CurrModeled
FROM (
    SELECT COALESCE(l.AcctNbr_2, l.AcctNbr_1) AS AccountNbr
        ,
        --1-
        COALESCE(l.CurrAvgBook * l.SofrOnRate_2, 0) AS CurrSofrOn
        ,
        --2-
        COALESCE(l.CurrAvgBook * l.MovingAvgBaseRt_2, 0) AS CurrBase
        ,
        --3-
        COALESCE(l.CurrAvgBook * l.TlpNonStableBase_2, 0) AS CurrTlpNonStableBase
        ,
        --4-
        COALESCE(l.CurrAvgBook * l.TlpStableBase_2, 0) AS CurrTlpStableBase
        ,
        --5-
        COALESCE(l.CurrAvgBook * l.Beta_2, 0) AS CurrBeta
        ,
        --6-
        COALESCE(l.CurrAvgBook * l.StableAdjBal_2, 0) AS CurrStabilityAdj
        ,
        --7-
        COALESCE(l.CurrAvgBook * l.FtpRate_2, 0) AS CurrFtp
        ,
        --8-
        COALESCE(l.CurrAvgBook * l.TlpAdjAge_2, 0) AS CurrTlpAdjAge
        ,
        --9-
        COALESCE(l.CurrAvgBook * l.FtpRateModel_2, 0) AS CurrModeled
        , 
        --Metric To Use--
        , MetricToUse = CASE    WHEN l.SofrOnRate_2 IS NOT NULL THEN 'CurrSofrOn'
                                WHEN l.MovingAvgBaseRt_2 IS NOT NULL THEN 'CurrBase'
                                WHEN l.TlpNonStableBase_2 IS NOT NULL THEN 'CurrTlpNonStableBase'
                                WHEN l.TlpStableBase_2 IS NOT NULL THEN 'CurrTlpStableBase'
                                WHEN l.Beta_2 IS NOT NULL THEN 'CurrBeta'
                                WHEN l.StableAdjBal_2 IS NOT NULL THEN 'CurrStabilityAdj'
                                WHEN l.FtpRate_2 IS NOT NULL THEN 'CurrFtp'
                                WHEN l.TlpAdjAge_2 IS NOT NULL THEN 'CurrTlpAdjAge'
                                WHEN l.FtpRateModel_2 IS NOT NULL THEN 'CurrModeled'
                                ELSE NULL 
                                END
    FROM (
        SELECT n1.AcctNbr AS AcctNbr_1
            ,n2.AcctNbr AS AcctNbr_2
            ,n1.TotalBookValueEoM AS PrevCurrBook
            ,n2.TotalBookValueEoM AS CurrBook
            ,n1.TotalBookValueAvg AS PrevAvgBook
            ,n2.TotalBookValueAvg AS CurrAvgBook
            ,n1.FtpRate_Model AS FtpRateModel_1
            ,n2.FtpRate_Model AS FtpRateModel_2
            ,n1.Stable_Base AS StableBase_1
            ,n2.Stable_Base AS StableBase_2
            ,n1.SofrOnRate AS SofrOnRate_1
            ,n2.SofrOnRate AS SofrOnRate_2
            ,n1.StableAdjustment_Balance AS StableAdjBal_1
            ,n2.StableAdjustment_Balance AS StableAdjBal_2
            ,n1.FtpRate_Model_Floored AS FtpRateModelFloored_1
            ,n2.FtpRate_Model_Floored AS FtpRateModelFloored_2
            ,n1.TlpAdjustment_Age AS TlpAdjAge_1
            ,n2.TlpAdjustment_Age AS TlpAdjAge_2
            ,n1.MovingAverageBaseRate AS MovingAvgBaseRt_1
            ,n2.MovingAverageBaseRate AS MovingAvgBaseRt_2
            ,n1.FtpRate_Final AS FtpRate_1
            ,n2.FtpRate_Final AS FtpRate_2
            ,n1.Beta AS Beta_1
            ,n2.Beta AS Beta_2
            ,n1.TlpTermInMonths_NonStable AS TlpTermInMnthsNS_1
            ,n2.TlpTermInMonths_NonStable AS TlpTermInMnthsNS_2
            ,n1.BaseRateIndex AS BaseRateIndex_1
            ,n2.BaseRateIndex AS BaseRateIndex_2
            ,n1.TLP_Stable_Base AS TlpStableBase_1
            ,n2.TLP_Stable_Base AS TlpStableBase_2
            ,n1.TLP_NonStable_Base AS TlpNonStableBase_1
            ,n2.TLP_NonStable_Base AS TlpNonStableBase_2
        FROM (
            SELECT DISTINCT *
            FROM dbo.NonMaturityDepositResults n
            WHERE n.TapeDate = 2024 - 11 - 30
            ) n1
        FULL JOIN (
            SELECT DISTINCT *
            FROM dbo.NonMaturityDepositResults n
            WHERE n.TapeDate = '2024-10-31'
            ) n2 ON n2.account_key = n1.account_key
            AND n2.Channel = n1.Channel
            AND n2.DepositType = n1.DepositType
        ) l
    ) a
GROUP BY a.DepositChannel
    ,a.DepositType