mysql 按周分组并将总计添加到每周

Question

我正在尝试创建一个查询，该查询将返回每周的数字总计，以便我可以创建上升趋势折线图。在我的表中，我有许多记录，记录了完成日期(已完成)。我希望能够创建一个每周生成滚动总计的查询。因此，如果第 1 周完成了 10 个，第 2 周完成了 15 个，第 3 周完成了 5 个，则所需的结果将是:

第 1 周总数:10第 2 周总数:25第 3 周总计:30

示例数据:

 id status  sched
 12 Successful  2017-04-04 00:00:00.000
 15 Successful  2017-06-20 19:30:00.000
 18 Successful  2017-10-17 18:00:00.000
 26 Successful  2017-04-05 00:00:00.000
 29 Successful  2017-06-16 00:00:00.000
 30 Successful  2017-04-06 00:00:00.000
 31 Successful  2017-04-07 00:00:00.000
 32 Successful  2017-04-06 00:00:00.000
 34 Successful  2017-10-18 18:00:00.000
 35 Successful  2017-06-13 00:00:00.000

这是我用来成功生成按周数据而无需任何汇总的查询。我尝试添加“WITH ROLLUP”，但它只在最后给出总计，而不是每周。

select DATE_FORMAT(completed,'%d/%m/%Y') AS nd , wk, count(*)as totals
from
(
   select id, completed, yearweek(completed)as wk from w10_upgrades
   where status = 'Successful' and type = 'Normal'
   and yearweek(completed) is not null
) x
GROUP BY wk
ORDER BY wk;

期望的输出:

 wk     totals
 201714   10
 201715   25 (output would = week 201714 + 201715)
 201716   55 (output would = week 201714 + 201715 + 201716)
 ect...

任何方向都值得赞赏。我找不到任何与此相关的内容。

Answer 1

最终结果

SET @runtot:=0;
SELECT
   DATE_FORMAT(completed,'%d/%m/%Y') AS niceDate,
   wk,
   (@runtot := @runtot + c) AS rt
FROM
   (SELECT
        completed,
   yearweek(completed)as wk,
   COUNT(*) AS c
FROM  `w10_upgrades`
where status = 'Successful' and type = 'Normal'
and yearweek(completed) is not null
GROUP  BY wk
ORDER  BY wk) x