按日期分组的 MySQL 累计总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22276790/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL cumulative sum grouped by date
提问by John Ruddell
I know there have been a few posts related to this, but my case is a little bit different and I wanted to get some help on this.
我知道有一些与此相关的帖子，但我的情况有点不同，我想就此获得一些帮助。
I need to pull some data out of the database that is a cumulative count of interactions by day. currently this is what i have
我需要从数据库中提取一些数据，这些数据是每天交互的累积计数。目前这就是我所拥有的
SELECT
e.Date AS e_date,
count(e.ID) AS num_interactions
FROM example AS e
JOIN example e1 ON e1.Date <= e.Date
GROUP BY e.Date;
The output of this is close to what I want but not exactly what I need. the problem I'm having is the dates are stored with the hour minute and second that the interaction happened, so the group by is not grouping days together.
这个输出接近我想要的但不完全是我需要的。我遇到的问题是日期与发生交互的小时分钟和秒一起存储，因此 group by 不是将日期分组在一起。
this is what the output looks like. http://screencast.com/t/N1KFNFyilon 12-23 theres 5 interactions but its not grouped because the time stamp is different. so I need to find a way to ignore the timestamp and just look at the day.
这就是输出的样子。http://screencast.com/t/N1KFNFyil在 12-23 有 5 次交互，但由于时间戳不同，因此未分组。所以我需要找到一种方法来忽略时间戳并只查看当天。
if I try GROUP BY DAY(e.Date)it groups the data by the day only (i.e everything that happened on the 1st of any month is grouped into one row) and the output is not what i want at all http://screencast.com/t/HN6DH3GV63M
如果我尝试GROUP BY DAY(e.Date)只按天对数据进行分组（即任何一个月的 1 号发生的所有事情都被分组到一行中）并且输出根本不是我想要的http://screencast.com/t/HN6DH3GV63M
GROUP BY DAY(e.Date), MONTH(e.Date)is splitting it up by month and the day of the month, but again the count is off.
GROUP BY DAY(e.Date), MONTH(e.Date)正在按月和月中的某天将其拆分，但再次计数已关闭。
I'm not a MySQL expert at all so I'm puzzled on what i'm missing
我根本不是 MySQL 专家，所以我对自己缺少的东西感到困惑
回答by clhereistian
New Answer
新答案
At first, I didn't understand you were trying to do a running total. Here is how that would look:
一开始，我不明白你在尝试做一个总和。这是它的外观：
SET @runningTotal = 0;
SELECT
e_date,
num_interactions,
@runningTotal := @runningTotal + totals.num_interactions AS runningTotal
FROM
(SELECT
DATE(eDate) AS e_date,
COUNT(*) AS num_interactions
FROM example AS e
GROUP BY DATE(e.Date)) totals
ORDER BY e_date;
Original Answer
原答案
You could be getting duplicates because of your join. Maybe e1 has more than one match for some rows which is inflating your count. Either that or the comparison in your join is also comparing the seconds, which is not what you expect.
由于您的加入，您可能会收到重复项。也许 e1 对某些行有不止一个匹配项，这会增加您的计数。无论是那个还是您加入中的比较也在比较秒数，这不是您所期望的。
Anyhow, instead of chopping the datetime field into days and months, just strip the time from it. Here is how you do that.
无论如何，不要将日期时间字段分成几天和几个月，只需从中剥离时间即可。这是你如何做到的。
SELECT
DATE(e.Date) AS e_date,
count(e.ID) AS num_interactions
FROM example AS e
JOIN example e1 ON DATE(e1.Date) <= DATE(e.Date)
GROUP BY DATE(e.Date);
回答by John Ruddell
I figured out what I needed to do last night... but since I'm new to this I couldn't post it then... what I did that worked was this:
我想出了昨晚我需要做的事情......但由于我是新手，所以我无法发布它......我所做的工作是这样的：
SELECT
DATE(e.Date) AS e_date,
count(e.ID) AS num_daily_interactions,
(
SELECT
COUNT(id)
FROM example
WHERE DATE(Date) <= e_date
) as total_interactions_per_day
FROM example AS e
GROUP BY e_date;
Would that be less efficient than your query? I may just do the calculation in python after pulling out the count per day if its more efficient, because this will be on the scale of thousands to hundred of thousands of rows returned.
这会比您的查询效率低吗？如果效率更高，我可能会在每天提取计数后在 python 中进行计算，因为这将返回数千到数十万行。
