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

问题描述

我知道有一些帖子与此相关，但我的情况有点不同，我想就此获得一些帮助.

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.

我遇到的问题是日期与发生交互的小时、分钟和秒一起存储，因此 group by 没有将日期分组在一起.

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.

这是输出的样子.

在 12-23 日有 5 次交互，但由于时间戳不同，因此未分组.所以我需要想办法忽略时间戳，只看当天.

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.

如果我尝试 GROUP BY DAY(e.Date) 它只按天对数据进行分组(即任何一个月的 1 号发生的所有事情都被分组到一行中)并且输出是完全不是我想要的.

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.

GROUP BY DAY(e.Date), MONTH(e.Date) 正在按月份和月份中的某天将其拆分，但计数再次关闭.

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.

我根本不是 MySQL 专家，所以我对自己缺少的东西感到困惑

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

推荐答案

新答案

一开始，我不明白你是想统计总数.这是它的外观:

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;

原答案

由于您的加入，您可能会收到重复项.也许 e1 对某些行有不止一个匹配项，这会增加您的计数.无论是那个还是您加入中的比较也在比较秒数，这不是您所期望的.

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.

无论如何，不​​要将日期时间字段分成几天和几个月，只需从中剥离时间即可.这是你如何做到的.

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);

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