MySQL:获得累积计数和差异计数的任何好方法？

Question

假设我有以下数据集。

+--------------------+
| item_id count date |
+--------------------+
| 000 10 2017-11-01  |
| 001 15 2017-11-01  |
| 002 2 2017-11-01   |
| 003 100 2017-11-01 |
| 000 20 2017-11-02  |
| 001 20 2017-11-02  |
| 002 22 2017-11-02  |
| 003 101 2017-11-02 |
| 000 30 2017-11-03  |
| 001 25 2017-11-03  |
| 002 42 2017-11-03  |
| 003 102 2017-11-03 |
| 000 40 2017-11-04  |
| 001 30 2017-11-04  |
| 002 62 2017-11-04  |
| 003 103 2017-11-04 |
+--------------------+

有没有办法获取两个任意日期之间每一天的累计计数和差异计数？
(至于差异计数，如果选择的日期是 2017-11-01 和 2017-11-04，则第一个差异计数(2017-11-01)不必包含在最终 SQL 结果中，因为没有该日期之前可用的数据)

下面两张表是预期的SQL结果。

累计计数

+--------------------+
| item_id count date |
+--------------------+
| 000 10 2017-11-01  |
| 000 30 2017-11-02  |
| 000 60 2017-11-03  |
| 000 100 2017-11-03 |
| 001 15 2017-11-01  |
| 001 35 2017-11-02  |
| 001 60 2017-11-03  |
| 001 90 2017-11-04  |
| 002 2 2017-11-01   |
| 002 24 2017-11-02  |
| 002 66 2017-11-03  |
| 002 128 2017-11-04 |
| 003 100 2017-11-01 |
| 003 201 2017-11-02 |
| 003 303 2017-11-03 |
| 003 406 2017-11-04 |
+--------------------+

差异计数

+--------------------+
| item_id count date |
+--------------------+
| 000 10 2017-11-02  |
| 000 10 2017-11-03  |
| 000 10 2017-11-04  |
| 001 5 2017-11-02   |
| 001 5 2017-11-03   |
| 001 5 2017-11-04   |
| 002 20 2017-11-02  |
| 002 20 2017-11-03  |
| 002 20 2017-11-04  |
| 003 1 2017-11-02   |
| 003 1 2017-11-03   |
| 003 1 2017-11-04   |
+--------------------+

Answer 1

由于MySQL 不支持windows OLAP 函数(最新版本除外)，您可以使用变量。

累计总和:

set @item_id := 0;
set @val := 0;

select t.item_id, t.count, t.date, t.CSUM as CummulativeSUM
from (
select t1.*, 
       @val := if(@item_id=item_id, @val + t1.count, t1.count) as CSUM,
       @item_id := item_id
from table1 t1
order by t1.item_id, t1.date
    ) t;

结果:

+---------+-------+---------------------+----------------+
| item_id | count |        date         | CummulativeSUM |
+---------+-------+---------------------+----------------+
|       0 |    10 | 01.11.2017 00:00:00 |             10 |
|       0 |    20 | 02.11.2017 00:00:00 |             30 |
|       0 |    30 | 03.11.2017 00:00:00 |             60 |
|       0 |    40 | 04.11.2017 00:00:00 |            100 |
|       1 |    15 | 01.11.2017 00:00:00 |             15 |
|       1 |    20 | 02.11.2017 00:00:00 |             35 |
|       1 |    25 | 03.11.2017 00:00:00 |             60 |
|       1 |    30 | 04.11.2017 00:00:00 |             90 |
|       2 |     2 | 01.11.2017 00:00:00 |              2 |
|       2 |    22 | 02.11.2017 00:00:00 |             24 |
|       2 |    42 | 03.11.2017 00:00:00 |             66 |
|       2 |    62 | 04.11.2017 00:00:00 |            128 |
|       3 |   100 | 01.11.2017 00:00:00 |            100 |
|       3 |   101 | 02.11.2017 00:00:00 |            201 |
|       3 |   102 | 03.11.2017 00:00:00 |            303 |
|       3 |   103 | 04.11.2017 00:00:00 |            406 |
+---------+-------+---------------------+----------------+

Cumulative SUM DEMO

---

差异计数:

set @item_id1 := 0;
set @val1 := 0; 
set @count := 0;

select t.item_id, t.count, t.date, t.CSUM as diffSUM
from (
select t1.*, 
       @val1 := if(@item_id1=item_id, t1.count - @count, t1.count) as CSUM,
       @item_id1 := item_id,
       @count := count
from table1 t1
order by t1.item_id, t1.date
    ) t
where count <> csum;

结果:

+---------+-------+---------------------+-----------+
| item_id | count |        date         | diffCount |
+---------+-------+---------------------+-----------+
|       0 |    20 | 02.11.2017 00:00:00 |        10 |
|       0 |    30 | 03.11.2017 00:00:00 |        10 |
|       0 |    40 | 04.11.2017 00:00:00 |        10 |
|       1 |    20 | 02.11.2017 00:00:00 |         5 |
|       1 |    25 | 03.11.2017 00:00:00 |         5 |
|       1 |    30 | 04.11.2017 00:00:00 |         5 |
|       2 |    22 | 02.11.2017 00:00:00 |        20 |
|       2 |    42 | 03.11.2017 00:00:00 |        20 |
|       2 |    62 | 04.11.2017 00:00:00 |        20 |
|       3 |   101 | 02.11.2017 00:00:00 |         1 |
|       3 |   102 | 03.11.2017 00:00:00 |         1 |
|       3 |   103 | 04.11.2017 00:00:00 |         1 |
+---------+-------+---------------------+-----------+

DIFF Count DEMO