SQL Server 运行百分比窗口函数

Question

我知道有几个使用 CTE 进行递归的示例等等，但是如何仅通过使用 SQL Server 2012 中的窗口函数来实现这一点:

CREATE TABLE #temp
(
       ID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
       Percentage INT NOT NULL
)

DECLARE @Calculated MONEY = 1000

INSERT INTO #temp ( Percentage ) VALUES  ( 100 )
INSERT INTO #temp ( Percentage ) VALUES  ( 90)
INSERT INTO #temp ( Percentage ) VALUES  ( 60)
INSERT INTO #temp ( Percentage ) VALUES  ( 50)
INSERT INTO #temp ( Percentage ) VALUES  ( 100)

结果将是一个像这样的运行百分比(我们从 1000 美元开始)

id    percentage   calculated 
--    --------     ---------
1     100          1000        
2     50           500
3     90           450
4     80           360  
5     100          360

因此下一行的值是百分比乘以该行上方的计算值。 LAG 可以用于计算别名吗？

谢谢

Answer 1

您需要百分比的运行乘积，而不是总是比较 2 个连续行，这就是 LEAD 和 LAG 在这里不起作用的原因。

您可以使用窗口总和来保持百分比与变量的运行乘积，以获得所需的计算结果:

SELECT
    ID,
    Expected,
    EXP(SUM(LOG(CONVERT(FLOAT, Percentage) / 100)) OVER (ORDER BY ID)) * @Calculated AS Actual
FROM #Temp

将此添加到您的示例代码中(我为您的预期输出添加了一列):

CREATE TABLE #temp
(
       ID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
       Percentage INT NOT NULL,
       Expected MONEY NOT NULL
)

DECLARE @Calculated MONEY = 1000

INSERT INTO #temp ( Percentage, Expected ) VALUES  ( 100 , 1000)
INSERT INTO #temp ( Percentage, Expected ) VALUES  ( 50, 500)
INSERT INTO #temp ( Percentage, Expected ) VALUES  ( 90, 450)
INSERT INTO #temp ( Percentage, Expected ) VALUES  ( 80, 360)
INSERT INTO #temp ( Percentage, Expected ) VALUES  ( 100, 360)

SELECT
    ID,
    Expected,
    EXP(SUM(LOG(CONVERT(FLOAT, Percentage) / 100)) OVER (ORDER BY ID)) * @Calculated AS Actual
FROM #Temp

这将产生您预期的输出:

ID          Expected              Actual
----------- --------------------- ----------------------
1           1000.00               1000
2           500.00                500
3           450.00                450
4           360.00                360
5           360.00                360