MySQL 累计计数未清项

Question

我有下表:

id      reference   created_at              closed_at
__      ______      ___________             __________
1       62506       2017-01-09 12:05:34     2017-01-09 16:14:55
2       62507       2017-01-09 12:09:47     NULL
3       62508       2017-01-10 12:09:48     NULL
4       62509       2017-01-10 12:11:15     NULL
5       62510       2017-01-10 12:12:41     2017-01-12 13:52:04
6       62511       2017-01-11 12:18:01     NULL
7       62512       2017-01-11 12:20:26     2017-01-15 11:39:31
8       62513       2017-01-11 12:29:19     NULL
9       62514       2017-01-12 12:37:11     NULL
10      62515       2017-01-12 12:43:31     NULL
11      62516       2017-01-12 13:20:49     NULL
12      62517       2017-01-12 13:30:58     2017-01-12 17:36:24

我想要一个查询，它返回项目的滚动总数以及我们当天“打开”的数量(打开在 closed_at 中为 null 或关闭日期大于结果行日期)

somedate     Total  Open
----------   ------ -----
2017-01-09   2      1      
2017-01-10   5      4
2017-01-11   8      7
2017-01-12   12     10

我知道我需要做一些累积计数，但我很垃圾，需要你的帮助:)

Answer 1

这是一种简单的方法。获取唯一日期，然后使用相关子查询:

select d.dte,
       (select count(*) from t where t.created_at < d.dte + interval 1 day
       ) as total,
       (select count(*) from t where t.closed_at < d.dte + interval 1 day
       ) as closed,
       (select count(*) from t
        where t.created_at < d.dte + interval 1 day and
              (t.closed_at is null or t.closed_at >= d.dte + interval 1 day)
       ) as total
from (select date(created_at) as dte from t
      union -- on purpose to remove duplicates
      select date(closed_at) from t
     ) d;

+ interval 1 day 所以“天”是当天午夜。

比较中缺少 date()，因此相关子查询可以使用索引。

子查询中的 union 是所有有事件的日期都被计算在内，即使没有新项目(只有关闭的项目)。