使用遞歸 CTE 和/或視窗函式簡化函式

我試圖想出一個遞歸 CTE 和/或視窗函式來創建一個函式。

幾天后，我將函式歸結為（虛擬碼），我擁有Nand B，並且需要生成E：

E n = B n * (1 - SUM ( E 1 , E 2 , … E n-1 ))

例子：

╔═══╦═════════════╦═════════════╗
║ N ║ B           ║ E           ║
╠═══╬═════════════╬═════════════╣
║ 0 ║ 0.142857143 ║ 0.142857143 ║
║ 1 ║ 0.285714286 ║ 0.244897959 ║
║ 2 ║ 0.285714286 ║ 0.174927114 ║
║ 3 ║ 0.285714286 ║ 0.124947938 ║
║ 4 ║ 0.285714286 ║ 0.089248527 ║
║ 5 ║ 0.4         ║ 0.089248527 ║
║ 6 ║ 0.666666667 ║ 0.089248527 ║
║ 7 ║ 1           ║ 0.044624264 ║
╚═══╩═════════════╩═════════════╝

E 0 = 0.143 * (1 - 0) = 0.143

E 1 = 0.286 * (1 - 0.143) = 0.245

E 2 = 0.286 * (1 - (0.143 + 0.245)) = 0.175

E 3 = 0.286 * (1 - (0.143 ) ) + 0.245 + 0.175)) = 0.125

E 4 = 0.286 * (1 - (0.143 + 0.245 + 0.175 + 0.125)) = 0.089

E 5 = 0.400 * (1 - (0.143 + 0.245 + 0.175 + 0.125 + 0.089)) = 0.089

E 6 = 0.667 * (1 - (0.143 + 0.245 + 0.175 + 0.125 + 0.089 + 0.089)) = 0.089

E 7 = 1.000 * (1 - (0.143 + 0.245 + 0.175 + 0.125 + 0.089 + 0.089) = 0.089) + 0.044

如果上表在 Excel 中，則C2 = B2 * (1 - 0)(base) 和C3 = B3 * (1 - SUM(C$2:C2))(recursive)

我試過的：

視窗函式

嘗試過SUM(...) OVER(ORDER BY [N] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)，但無法遞歸引用該列。

遞歸 CTE

嘗試了幾次迭代：

WITH B AS ([Num], [Best], [Effective Rate]) AS (
   SELECT *
       , [Best]
   FROM A
   WHERE [Num] = 0
   UNION ALL
   SELECT A.*
       , (1 - [Effective Rate]) * A.[Best]
   FROM B
   JOIN A ON A.[Num] = B.[Num] + 1 
)

有些在 CTE 中有一個額外的列，但它只涵蓋前 1 行，並且第 2 行之後的結果是錯誤的。

具有視窗函式的遞歸 CTE

從我嘗試過的所有內容來看，似乎 CTE 的遞歸段是獨立於其他結果計算的，並且SUM(...) OVER(...)僅適用於目前行。（關於上表， 的所有值E都是0.142857143）。

我認為這是因為這UNION ALL一切都是同時發生的，而不是逐漸發生的。

替代解決方案

我真正希望發生的是簡化上述方程，和/或將其轉換為迭代函式。

**獎勵：**如果有人想知道此資訊的來源，它用於計算MACRS 折舊以用於稅收目的。

您需要一個額外的列來攜帶執行總計（小提琴）。

在下面的 CTE 的遞歸部分中，R指的是“上一個”行，A而目前行是指引用該列R的您的SUM(E1, E2, ... En-1).

WITH R
    AS (SELECT N,
               B,
               E = B,
               RunningTotalE = B
        FROM   A
        WHERE  N = 0
        UNION ALL
        SELECT A.N,
               A.B,
               E = A.B * ( 1 - R.RunningTotalE ),
               RunningTotalE = A.B * ( 1 - R.RunningTotalE ) + R.RunningTotalE
        FROM   R
               JOIN A
                 ON A.N = R.N + 1)
SELECT N,
      B,
      E = CAST(E AS DECIMAL(10,9))
FROM   R 

引用自：https://dba.stackexchange.com/questions/268697