MySQL累计总和按日期分组 [英] MySQL cumulative sum grouped by date

问题描述

我知道有一些与此相关的帖子，但是我的情况有所不同，我想在此获得一些帮助。

我需要从数据库中抽出一些数据，这是日间的交互累积计数。目前这是我有的

  SELECT 
 e.Date AS e_date，
 count（e.ID ）AS num_interactions 
 FROM example AS e 
 JOIN示例e1 ON e1.Date< = e.Date 
 GROUP BY e.Date; 
  

这个输出接近我想要的，但不完全是我需要的。
我遇到的问题是日期与小时和秒钟存储交互发生，所以组合不是将日子分组在一起。

这是输出的样子。 http://screencast.com/t/N1KFNFyil
在12-23 theres 5的互动，但它没有分组，因为时间戳是不同的。所以我需要找到一种方法来忽略时间戳，只要看一下。

如果我尝试 GROUP BY DAY（e.Date） 它将数据按日期分组（即，任何月份发生的所有事情都被分组为一行），输出不是我想要的输出http://screencast.com/t/HN6DH3GV63M

GROUP BY DAY e.Date），MONTH（e.Date）正在分月份和月份的日子，但再次计数是关闭的。

我不是一个MySQL专家，所以我对我错过的东西感到困惑

解决方案

我想我昨天晚上需要做什么...但是由于我是新来的，所以我不能发布，那么我所做的是这样的：

  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; 
  

会比您的查询效率低吗？我可能只是在python中进行计算，如果它的效率更高，那么它的数量就会越来越高，因为这将在数千到数十万行的范围内返回。

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.

this is what the output looks like. http://screencast.com/t/N1KFNFyil on 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.

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), MONTH(e.Date) is splitting it up by month and the day of the month, but again the count is off.

I'm not a MySQL expert at all so I'm puzzled on what i'm missing

解决方案

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.

这篇关于MySQL累计总和按日期分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！