我想计算不同状态下订单状态变化的计数。我的订单状态表:
| 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 中处理数据以获得输出。但是这在查询本身中可能吗?
使用 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;
从版本 8 开始,MySQL 中LAG()
可用。
请注意,您可以通过在外部查询中放入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;
如果 id 列保证了记录的顺序,您可以使用自连接来实现您的要求,如下所示-
SELECT A.Status +'>'+ B.Status, COUNT(*)
FROM OrderStatus A
INNER JOIN OrderStatus B
ON A.id = B.id -1
WHERE B.Status IS NOT NULL
GROUP BY A.Status +'>'+ B.Status
将 3 种状态更改类型联接到您已经完成的表分组中:
select c.changetype, count(*) counter
from (
select 'pending->processing' changetype union all
select 'processing->complete' union all
select 'pending->cancelled'
) c inner join (
select
group_concat(status order by id separator '->') changestatus
from tablename
group by ordr_id
) t on concat('->', t.changestatus, '->') like concat('%->', changetype, '->%')
group by c.changetype
请参阅演示。
结果:
> changetype | counter
> :------------------- | ------:
> pending->cancelled | 1
> pending->processing | 2
> processing->complete | 2
...或者只是一个简单的连接...
SELECT CONCAT(a.status,'->',b.status) action
, COUNT(*) total
FROM my_table a
JOIN my_table b
ON b.ordr_id = a.ordr_id
AND b.id = a.id + 1
GROUP
BY action;
+----------------------+-------+
| action | total |
+----------------------+-------+
| pending->cancelled | 1 |
| pending->processing | 2 |
| processing->complete | 2 |
+----------------------+-------+
请注意,这依赖于 id 是连续的事实。