sql - 每年从零开始的累积栏

Question

我在比率表的第一列中按日期排序了从 2008 年 1 月 1 日到今天的数据。

我在第二列中有值。我可以使用以下代码进行累积第三列，但我不知道如何使其在每年 1 月 1 日重新启动以实现每年累积。

SELECT 
    t3.Date, 
    SUM(cumul) AS cumul 
FROM (
    SELECT 
        t1.Date, 
        t1.nb, 
        SUM(t2.nb) AS cumul   
    FROM (
        SELECT 
            Ratio.Date, 
            SUM(DailyValue) AS Nb
        FROM Ratio
        GROUP BY Ratio.Date
    )t1
    INNER JOIN (
        SELECT 
            Ratio.Date, 
            SUM(DailyValue) AS nb
        FROM Ratio
        GROUP BY Ratio.Date
    ) t2 
        ON t1.Date >= t2.Date
    GROUP BY t1.Date, t1.nb
)t3
GROUP BY PnLDate,nb
ORDER BY pnldate

Answer 1

有一种更好的方法使用窗口函数SUM

select Date,
    sum(sum(DailyValue)) over (
        partition by year(date) order by date
        ) as cumul
from Ratio
group by Date
order by Date;