从changelog表中确定给定日期给定状态下的订单数

Question

我正在使用MySQL版本8。x数据库。下面是changelog表中的一些示例数据:

|client_id | order_id | status_changed_from | status_changed_to | status_changed_date |
+----------+----------+---------------------+-------------------+---------------------+
|  1004023 |   220840 | Open                | In Progress       | 2015-01-10 11:59:09 |
|  1004023 |   220840 | In Progress         | Done              | 2015-01-31 12:23:28 |
|  1004023 |   221080 | Open                | In Progress       | 2015-01-11 02:34:32 |
|  1004023 |   221080 | In Progress         | Done              | 2015-02-11 02:34:32 |
|  1374019 |   221665 | Open                | In Progress       | 2015-01-18 18:23:20 |
|  1374019 |   221665 | In Progress         | Done              | 2015-02-19 22:12:03 |
|  1004023 |   221971 | Open                | In Progress       | 2015-01-24 19:20:12 |
|  1004023 |   221971 | In Progress         | Done              | 2015-02-20 12:19:43 |
|  1374019 |   230924 | Open                | In Progress       | 2015-03-03 00:20:32 |
|  1374019 |   230924 | In Progress         | Done              | 2015-03-14 21:53:57 |
|  1374019 |   234361 | Open                | In Progress       | 2015-03-05 10:57:43 |
|  1374019 |   234361 | In Progress         | Done              | 2015-03-15 18:29:03 |
|  1497671 |   238360 | Open                | In Progress       | 2015-03-09 06:57:53 |
|  1497671 |   238360 | In Progress         | Done              | 2015-03-20 11:17:42 |
|  1374019 |   266924 | Open                | In Progress       | 2015-03-10 17:05:34 |
|  1374019 |   266924 | In Progress         | Done              | 2015-03-25 22:05:39 |
|  1497671 |   274617 | Open                | In Progress       | 2015-03-13 17:12:15 |
|  1497671 |   274617 | In Progress         | Done              | 2015-03-26 16:40:02 |

给定这些数据，我试图确定"正在进行中"的数量。给定客户在给定日期的订单。例如，2015年1月25日正在处理的订单数量:

| on_date    | orders_in_progress |
+------------+--------------------+
| 2015-01-25 | 4                  |

客户1004023在2015-01-25的订单数量:

| on_date    | client_id   | orders_in_progress |
+------------+-------------+--------------------+
| 2015-01-25 | 1004023     | 3                  |

Answer 1

基本上，您需要所有客户端或给定客户端在给定日期之前的最后状态日期。思路如下:

select cl.client_id, '2015-01-25' as on_date, count(*)
from changelog cl
where cl.status_changed_date = (select max(cl2.status_changed_date)
from changelog cl2
where cl2.client_id = cl.client_id and
cl2.status_changed_date <= '2015-01-25'
) and
cl.status_changed_to = 'In Progress'
group by cl.client_id;

你也可以使用窗口函数:

select cl.client_id, '2015-01-25', count(*)
from (select cl.*,
row_number() over (partition by client_id order by  status_changed_date desc) as seqnum
from changelog cl
where status_changed_date <= '2015-01-25'
) cl
where seqnum = 1
group by cl.client_id;

在这两个查询中，如果您愿意，您可以过滤到一个特定的客户端。