Slamdata:如何根据日期查找累计和

Question

我已经使用过SlamData中的查询功能。我的代码：

SELECT  
DATE_PART("year",thedate) AS year,DATE_PART("month",thedate) AS month,
SUM(runningPnL) AS PnL
FROM "/Mickey/testdb/sampledata3" AS c

GROUP BY DATE_PART("year", thedate) ,DATE_PART("month", thedate) order by DATE_PART("year", thedate) ,DATE_PART("month", thedate)

我的表的摘录：

PnL                 month      year  
-1651.8752           1         2001  
17180.4776           2         2001  
48207.54560000001    3         2001 

现在，我如何才能找到PnL的累积和？

第一个月的-1651.8752

第二个月的15528.6024

非常感谢你，>.<

Answer 1

我正在生成与您相同的累积总和样本数据。希望你从这篇文章中得到一些想法。

Create table tempData 
 (
   pnl float,
   [month] int,
   [year] int
 )
 Go
 insert into tempData values ( -1651.8752, 1,2001)
 insert into tempData values ( 17180.4776, 2,2001)
 insert into tempData values ( 48207.54560000001, 3,2001)

 Select * , (SELECT SUM(Alias.pnl)
         FROM tempData As Alias
         WHERE Alias.[Month] <= tempData.[Month]
         ) As CumulativSUM
 FROm tempData
 ORDER BY tempData.[MOnth]

Answer 2

我的代码是

SELECT a1.year, a1.month, a1.PnL, a1.PnL/(SUM(a2.PnL)+125000) as Running_Total 

FROM/Mickey/testdb/sampledata6as a1,/Mickey/testdb/sampledata6as a2 WHERE (a1.month > a2.month And a1.year=a2.year) or (a1.year>a2.year) GROUP BY a1.year, a1.month,a1.PnL ORDER BY a1.year,a1.month ASC;