mysql - 按周分组的记录数的累积总和

我有以下数据库架构

ID  creation_date
1      2019-06-03
2      2019-06-04
3      2019-06-04
4      2019-06-10
5      2019-06-11

我需要按周找出表组的总大小。我正在寻找的输出类似于

year   week number_of_records
2019    23      3
2019    24      5

我正在编写以下查询，它只给我每周创建的记录数

> select year(creation_date) as year, weekofyear(creation_date) as week,
> count(id) from input group by year, week;

我得到的输出是

year   week number_of_records
2019    23      3
2019    24      2

最佳答案

对于 8.0 之前的版本...

架构 (MySQL v5.7)

CREATE TABLE my_table
(ID SERIAL PRIMARY KEY
,creation_date DATE NOT NULL
);

INSERT INTO my_table VALUES
(1    ,  '2019-06-03'),
(2     , '2019-06-04'),
(3     , '2019-06-04'),
(4      ,'2019-06-10'),
(5      ,'2019-06-11');

查询 #1

SELECT a.yearweek
, @i:=@i+a.total running
FROM
(SELECT DATE_FORMAT(x.creation_date,'%x-%v') yearweek
, COUNT(*) total
FROM my_table x
GROUP BY yearweek
)a
JOIN (SELECT @i:=0) vars
ORDER BY a.yearweek;

| yearweek | running |
| -------- | ------- |
| 2019-23  | 3       |
| 2019-24  | 5       |

---

View on DB Fiddle

关于mysql - 按周分组的记录数的累积总和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57155799/