MySQL中更复杂的累积和列 [英] More complex cumulative sum column in mysql
问题描述
我阅读了在MySQL中创建累积总和列,然后试图使其适应我的工作,但我似乎无法正确地做到这一点.
I read Create a Cumulative Sum Column in MySQL, and tried to adapt it to what I'm doing, but I can't seem to get it right.
表格:
Id (primary key)
AcctId
CommodId
Date
PnL
有一个唯一索引,其中包含AcctId,CommodId,Date.我想获得按日期分组的累计总数.
There is a unique index which contains AcctId, CommodId, Date. I want to get a cumulative total grouped by date.
此查询
select c.date
, c.pnl
,(@cum := @cum + c.pnl) as "cum_pnl"
from commoddata c join (select @cum := 0) r
where
c.acctid = 2
and
c.date >= "2011-01-01"
and
c.date <= "2011-01-31"
order by c.date
将正确计算所有记录的运行总计,以格式显示数据
will correctly calculate the running total for all records, showing data in the format
date pnl cum_pnl
======== ====== =======
2011-01-01 1 1
2011-01-01 1 2
2011-01-01 1 3
2011-01-01 1 4
2011-01-02 1 5
2011-01-02 1 6
...
(每个日期可能有很多记录).我想要的是
(there can be many records per date). What I want is
date cum_pnl
======== =======
2011-01-01 4
2011-01-02 6
...
但是我没有尝试过. TIA.
But nothing I've tried works. TIA.
推荐答案
或者,我认为您可以将所有pnl
替换为sum(pnl)
,并让您的@cum在其中运行.我认为它看起来像这样:
Alternately I think you can replace all your pnl
with sum(pnl)
, and let your @cum run across those. I think it would look like this:
select c.date
,SUM(c.pnl)
,(@cum := @cum + SUM(c.pnl)) as "cum_pnl"
from commoddata c join (select @cum := 0) r
where
c.acctid = 2 and c.date >= "2011-01-01" and c.date <= "2011-01-31"
order by c.date
GROUP BY c.date
我只是想弄清楚,如果cum_pnl不是按表达式分组时,SQL是否会让您为选择cum_pnl感到烦恼...也许您也可以尝试按它分组?
I'm just trying to figure out if SQL will give you grief over selecting cum_pnl when it is not a group by expression... maybe you can try grouping by it as well?
编辑新思路,如果您真的不喜欢嵌套查询,请用汇总的分组查询替换commoddata
EDIT New Idea, if you're really not averse to nested queries, replace commoddata
with a summed grouped query
select c.date
,c.pnl
,(@cum := @cum + c.pnl) as "cum_pnl"
from
(SELECT date, sum(pnl) as pnl FROM commoddata WHERE [conditions] GROUP BY date) c
join (select @cum := 0) r
order by c.date
这篇关于MySQL中更复杂的累积和列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!