MySQL按年和月计算总计并计算累计计数

Question

我需要帮助来优化我的 SQL 请求。

我有一个如下所示的表格:

id,resgier_date,action
1,'2011-01-01 04:28:21','signup'
2,'2011-01-05 04:28:21','signup'
3,'2011-02-02 04:28:21','signup'

如何选择和分组这些，以便输出为:

year,month,total,cumulative_sum
2011,1,2,2
2011,2,1,3

我想通过一个请求添加一列，其中包含累积的 count(id)。

我尝试提出这样的请求:

SELECT YEAR(REGISTER_DATE),
     count( * ),
     @running_total := @running_total + count( * ) AS cumulative_sum
FROM MYTABLE t
     JOIN (SELECT @running_total := 0) r
GROUP BY YEAR(REGISTER_DATE)
LIMIT 0 , 30

没有成功

感谢您的帮助

Answer 1

尝试

SELECT year, 
       month, 
       total, 
       @rt := @rt + total cumulative_sum
  FROM (
        SELECT YEAR(register_date) year,
               MONTH(register_date) month,
               COUNT(*) total
          FROM mytable
          GROUP BY YEAR(register_date), MONTH(register_date)
          LIMIT 0, 30
       ) n, (SELECT @rt := 0) r

输出

| YEAR | MONTH | TOTAL | CUMULATIVE_SUM |
-----------------------------------------
| 2011 |     1 |     2 |              2 |
| 2011 |     2 |     1 |              3 |

<强> SQLFiddle