mysql - 如何跟踪上一行状态计数

Question

我想计算不同状态下订单状态变化的次数。我的订单状态表:

| id |ordr_id|   status   |
|----|-------|------------|
| 1  |   1   | pending    |
| 2  |   1   | processing |
| 3  |   1   | complete   |
| 4  |   2   | pending    |
| 5  |   2   | cancelled  |
| 6  |   3   | processing |
| 7  |   3   | complete   |
| 8  |   4   | pending    |
| 9  |   4   | processing |

我想要的输出:

|        state         | count |
|----------------------|-------|
| pending->processing  |   2   |
| processing->complete |   2   |
| pending->cancelled   |   1   |

目前我正在通过 SELECT order_id,GROUP_CONCAT(status) as track FROM table group by order_id 获取结果，然后在 php 中处理数据以获得输出。但这在查询本身中可能吗？

Answer 1

使用lag():

select prev_status, status, count(*)
from (select t.*,
             lag(status) over (partition by order_id order by status) as prev_status
      from t
     ) t
group by prev_status, status;

LAG() 从版本 8 开始在 MySQL 中可用。

请注意，您可以通过在外部查询中放置 where prev_status is not null 来过滤掉每个订单的第一个状态。

您的版本不太正确，因为它没有强制执行排序。应该是:

SELECT order_id,
       GROUP_CONCAT(status ORDER BY id) as track

编辑:

在早期版本的 MySQL 中，您可以使用相关子查询:

select prev_status, status, count(*)
from (select t.*,
             (select t2.status
              from t t2
              where t2.order_id = t.order_id and t2.id < t.id
              order by t2.id desc
              limit 1
             ) as prev_status
      from t
     ) t
group by prev_status, status;