mysql中更复杂的累积和列

发布于 2024-11-01 21:03:39 字数 1018 浏览 1 评论 0原文

我阅读了在 MySQL 中创建累积和列，并尝试适应它适合我正在做的事情，但我似乎无法做对。

该表：

Id (primary key)
AcctId
CommodId
Date
PnL

有一个唯一索引，其中包含 AcctId、CommodId、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

将正确计算所有记录的运行总计，以格式显示数据

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
...

（每个日期可以有许多记录）。我想要的是

date        cum_pnl
========    =======
2011-01-01       4
2011-01-02       6
...

但我尝试过的都不起作用。 TIA。

原文

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.

The table:

Id (primary key)
AcctId
CommodId
Date
PnL

There is a unique index which contains AcctId, CommodId, Date. I want to get a cumulative total grouped by date.

This query

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
...

But nothing I've tried works. TIA.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

咿呀咿呀哟 2024-11-08 21:03:39

或者，我认为您可以将所有 pnl 替换为 sum(pnl)，并让您的 @cum 运行这些。我认为它看起来像这样：

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

我只是想弄清楚，当 SQL 不是按表达式分组时，SQL 是否会让您因选择 cum_pnl 而感到悲伤...也许您也可以尝试按它分组？

编辑新想法，如果您确实不反对嵌套查询，请将commoddata替换为求和分组查询

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

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

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?

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

回复收藏 0 原文