按月对MySQL行进行分组，并计算数据直到每个月

I have a table called "results" with lots of rows and a column called "timestamp".

我有一个名为“results”的表，其中有很多行和一个名为“timestamp”的列。

I want to group the number of rows for each month. But for each one of them, I also want to consider the previous months rows.

我想对每个月的行数进行分组。但对于其中的每一个，我还想考虑前几个月的行数。

So if I have

所以如果我有

Jun/13
Jun/13
Jun/13
Jul/13
Jul/13
Jul/13
Jul/13
Jul/13
Aug/13
Aug/13
Aug/13
Aug/13
Aug/13
Aug/13
Aug/13

The result will be

结果将是

3  Jun/13
8  Jul/13
15 Aug/13

This is what I have right now, but it doesn't consider the previous months.

这是我现在所拥有的，但不考虑前几个月。

SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp), '%e %b %Y'), COUNT(1) FROM results
GROUP BY DATE_FORMAT(FROM_UNIXTIME(timestamp), '%e %b %Y');

3 个解决方案

#1

I got this to work:

我让它起作用:

SELECT month, count, @total:=@total+count AS total
FROM (SELECT @total:=0) t STRAIGHT_JOIN
  (SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp), '%e %b %Y') AS month, COUNT(*) AS count
  FROM results GROUP BY EXTRACT(YEAR_MONTH FROM FROM_UNIXTIME(timestamp))) AS m;

+------------+-------+-------+
| month      | count | total |
+------------+-------+-------+
| 1 Jun 2013 |     3 |     3 |
| 1 Jul 2013 |     5 |     8 |
| 1 Aug 2013 |     7 |    15 |
+------------+-------+-------+

#2

the following solution should be work ...

下面的解决方案应该是工作…

SELECT COUNT(1), DATE_FORMAT(timestamp, '%b/%y') FROM results GROUP BY YEAR(timestamp), MONTH(timestamp);

选择COUNT(1)， DATE_FORMAT(timestamp， '%b/%y')从结果组按年(时间戳)，月(时间戳);

good luck

祝你好运

Talki

#3

If I understand you correctly, this should work;

如果我理解正确，这应该是可行的;

SELECT COUNT(*) num, SUBSTR(ts, 1, 7) month
FROM results
JOIN (SELECT MAX(timestamp) ts FROM results
      GROUP BY YEAR(timestamp),Month(timestamp)) mm
  ON results.timestamp <= mm.ts
GROUP BY SUBSTR(ts, 1, 7);

An SQLfiddle to test with.

要测试的SQLfiddle。

#1