mysql - 按月对 MySQL 行进行分组并统计数据直到每个月

Question

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

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

如果我有

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

结果会是

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

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

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

Answer 1

我让这个工作:

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