时间戳

首页 > 解决方案 > 递归 CTE 问题

sql-server - 递归 CTE 问题

问题描述

我在想出 CTE 完成任务的方式时遇到了一些麻烦。我已经有一个循环方法，它足够快，但我想以一种正确的方式来做，以更好地学习和理解 CTE 的使用。

SQL：

DECLARE @Income MONEY=125000.00,
    @Active INT=0,
    @Year CHAR(4)='2018'

DECLARE @T TABLE ([Year] CHAR(4),Active INT,UpperLimit MONEY,Factor DECIMAL(6,3))
INSERT INTO @T ([Year],Active,UpperLimit,Factor) SELECT '2018',0,5000.0,1.00;
INSERT INTO @T ([Year],Active,UpperLimit,Factor) SELECT '2018',0,100000.0,0.85;
INSERT INTO @T ([Year],Active,UpperLimit,Factor) SELECT '2018',0,500000.0,0.80;
INSERT INTO @T ([Year],Active,UpperLimit,Factor) SELECT '2018',0,999999999.0,0.75;

WITH GradientCTE ([Year], Active, UpperLimit, Factor,[Income],WeightedValue,[Row])  
AS  
(  
    SELECT [Year], Active, UpperLimit, Factor
        ,@Income AS [Income]
        ,CAST(0.0 AS DECIMAL(16,3))AS WeightedValue
        ,ROW_NUMBER() OVER(PARTITION BY [Year],Active ORDER BY UpperLimit ASC) AS [Row]
    From  @T
)
SELECT *
FROM GradientCTE
ORDER BY UpperLimit

TLDR 版本；电流输出：

Year    Active  UpperLimit     Factor   Income      WeightedValue   Row
2018    0       5000.000       1.000    125000.000  0.000           1
2018    0       100000.000     0.850    125000.000  0.000           2
2018    0       500000.000     0.800    125000.000  0.000           3
2018    0       999999999.000  0.750    125000.000  0.000           4

我想要什么：

Year    Active  UpperLimit    Factor    Income      WeightedValue   Row
2018    0       5000.000       1.000    125000.000  5000.000        1
2018    0       100000.000     0.850    120000.000  85000.000       2
2018    0       500000.000     0.800     20000.000  16000.000       3
2018    0       999999999.000  0.750         0.000  0.000           4

解释：

目前，循环逻辑逐行遍历设置，并将每行的 @Income 减少 UpperLimit，直到没有钱为止。它使用该数量乘以因子以获得加权数量。因此，在提供的示例中，起始收入为 125,000.00。前 5000 个是全权重 (1.00)，因此我们将收入减少 5000 并移动下一行以保存总加权值。这样做直到收入为 0。因此，125,000 应该是 (5000 * 1.0) + (100000 * 0.85) + (20000 * 0.80) + (0.00 * 0.75) 或总计 106,000（如果求和）。

标签： sql-servercommon-table-expression

解决方案

感谢罗斯布什的回答，它使我走上了解决问题的正确道路。从维护的角度来看，我认为循环模式更容易理解，所以我可能不会实现 CTE 版本，并且性能不是问题，因为数据集很小。

DECLARE @Income DECIMAL(18,3)=125000.00,
    @Active INT=0,
    @Year CHAR(4)='2018'

DECLARE @T TABLE ([Year] CHAR(4),Active INT,UpperLimit DECIMAL(18,3),Factor DECIMAL(18,3))
INSERT INTO @T ([Year],Active,UpperLimit,Factor) SELECT '2018',0,5000.0,1.00;
INSERT INTO @T ([Year],Active,UpperLimit,Factor) SELECT '2018',0,100000.0,0.85;
INSERT INTO @T ([Year],Active,UpperLimit,Factor) SELECT '2018',0,500000.0,0.80;
INSERT INTO @T ([Year],Active,UpperLimit,Factor) SELECT '2018',0,999999999.0,0.75;
;WITH GradientCTE 
AS  
(  
    SELECT DISTINCT
        [YEAR],Active,UpperLimit=0.00, Factor = 0.00, [Row] = 0
    FROM @T
    UNION ALL
    SELECT [Year],Active,UpperLimit, Factor
        ,ROW_NUMBER() OVER(PARTITION BY [Year],Active ORDER BY UpperLimit ASC) AS [Row]
    From  @T
)
,Reduce AS (
    SELECT 
        [YEAR],Active,CAST(@Income AS DECIMAL(18,3)) AS [RemainingIncome], 
        Row, 
        Factor
        ,UpperLimit
        ,CAST(0.00 AS DECIMAL(18,3)) AS WeightedValue
    FROM GradientCTE
    WHERE UpperLimit=0
    UNION ALL
    SELECT 
        g.[YEAR],g.Active,CASE WHEN CAST([RemainingIncome] - G.UpperLimit AS DECIMAL(18,3)) < 0 THEN 0 ELSE CAST([RemainingIncome] - G.UpperLimit AS DECIMAL(18,3))  END AS [RemainingIncome], 
        G.Row,
        g.Factor
        ,g.UpperLimit
        ,CAST(CASE WHEN [RemainingIncome]>G.UpperLimit  THEN G.UpperLimit * G.Factor ELSE R.[RemainingIncome] * G.Factor END AS DECIMAL(18,3)) AS WeightedValue
    FROM GradientCTE G
    INNER JOIN Reduce R ON R.Row = G.Row -1
       AND g.Year=r.Year
       AND g.Active=r.Active
)
SELECT
    *
    -- [Year],Active,SUM(WeightedValue)
FROM Reduce
WHERE [RemainingIncome] >= 0
--GROUP BY [Year],Active

推荐阅读