Question

我正在努力取得以下成果:

https://i.stack.imgur.com/ELn79.png”rel=“nofollow noreferer”>。

合计数的计算公式(数值+1) * (1) - 1

我正试图在妇产科进行以下检查:

WITH cte AS (
    SELECT date, value, ROW_NUMBER() OVER (ORDER BY date) AS seqnum
    FROM table_name
), cte2 AS (
    SELECT cte.date, cte.value, cte.seqnum
    FROM cte
    WHERE seqnum = 1
    UNION ALL
    SELECT cte.date, (cte.value + 1) * (cte2.value + 1) - 1, cte.seqnum
    FROM cte2
    JOIN cte ON cte.seqnum = cte2.seqnum + 1
)
SELECT cte2.date, cte2.value
FROM cte2;

我正在犯以下错误:

本版本中不支持复读的共同表格表述。

Answer 1

你可能不需要再行。复合利息计算基本上是一个连续产品计算。如果房舍管理处将实施<代码>prod()功能类似于<代码>sum(<>>/代码>,但大多数(所有)没有。好消息是,你可以把产品操作改变为使用标识总操作。

(凡记住机械滑坡规则或熟悉WWII-era以计算机为攻击目标战斗的神经工作的人都知道:

相当于一种假设的<代码>prod(价值)的功能为exp(sum(log(价值)))。当处理相对百分比或分数时(以100%或1.0计),对应数为<代码>exp(((log(数值+1))-1。

你的问询可能会变得相对简单:

SELECT
    D.*,
    EXP(SUM(LOG(D.Interest + 1)) OVER(ORDER BY D.Id)) - 1 AS CummulativeInterest
FROM Data D
ORDER BY D.Id

I am not sure if this will work as written in Synapse, but your original posted query seemed to support windowed aggregates.

成果:

Id	Interest	Cummulative Interest (Rounded)	Cummulative Interest (Raw)
1	0	0	0
2	0.0606	0.0606	0.0606
3	0.0031	0.0639	0.0638878600000001
4	0.0002	0.0641	0.0641006375720001
5	0.0006	0.0647	0.0647390979545432
6	0	0.0647	0.0647390979545432
7	0	0.0647	0.0647390979545432
8	0.0439	0.1115	0.111481144354748
9	0	0.1115	0.111481144354748
10	0	0.1115	0.111481144354748
11	0.0001	0.1116	0.111592292469183
12	0	0.1116	0.111592292469183

See this db<>fiddle for a demo.

友情链接