mysql - 按项目和周总计

我试图确定每个工作周结束时每种产品的库存水平。

我有一个记录所有产品交易的表格

+------------+-------------+------------+------------+
| ProductID  | Quantity    | Created_at |Cause       |
+------------+-------------+------------+------------+
|          1 | 200         | 2015-06-01 |Delivery    |
|          1 | -2          | 2015-06-02 |Order       |
|          1 | -1          | 2015-06-12 |Order       |
|          2 | 45          | 2015-06-15 |Delivery    |
|          2 | -5          | 2015-06-16 |Order       |
|          2 | -1          | 2015-06-17 |Broken      |
|          1 | 100         | 2015-06-21 |Delivery    |
+------------+-------------+------------+------------+

我只需要按特定产品累计到特定日期的数量，就可以显示出这样的内容

+------------+-------------+------------+
| ProductID  | Quantity    | Week       |
+------------+-------------+------------+
|          1 | 198         | 2015-06-05 |
|          1 | 197         | 2015-06-12 |
|          1 | 197         | 2015-06-19 |
|          2 | 39          | 2015-06-19 |
|          1 | 297         | 2015-06-26 |
|          2 | 39          | 2015-06-26 |
+------------+-------------+------------+

我尝试了With Rollup和@runtot：=的各种组合
但是到目前为止还没有成功。

最佳答案

因此，类似：

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(product_id INT NOT NULL
,created_at DATE NOT NULL
,quantity INT NOT NULL
,cause  VARCHAR(20) NOT NULL
,PRIMARY KEY(product_id,created_at)
);

INSERT INTO my_table VALUES
(1,'2015-06-01',200,'Delivery'),
(1,'2015-06-02', -2,'Order'),
(1,'2015-06-12', -1,'Order'),
(2,'2015-06-15', 45,'Delivery'),
(2,'2015-06-16', -5,'Order'),
(2,'2015-06-17', -1,'Broken'),
(1,'2015-06-21',100,'Delivery');

SELECT x.*
     , CASE WHEN @prev = product_id THEN @i:=@i+total ELSE @i:=total END running
     , @prev:=product_id prev
  FROM
     ( SELECT product_id
            , YEARWEEK(created_at) yw
            , SUM(quantity) total
         FROM my_table
        GROUP
           BY product_id
            , yw
     ) x
     , (SELECT @prev:=null,@i:=0) vars
 ORDER
    BY product_id
     , yw;
+------------+--------+-------+---------+------+
| product_id | yw     | total | running | prev |
+------------+--------+-------+---------+------+
|          1 | 201522 |   198 |     198 |    1 |
|          1 | 201523 |    -1 |     197 |    1 |
|          1 | 201525 |   100 |     297 |    1 |
|          2 | 201524 |    39 |      39 |    2 |
+------------+--------+-------+---------+------+

关于mysql - 按项目和周总计，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49156886/