如何编写SQL查询统计所有状态转换路径的对象数量？

表结构与示例数据 #

现有一张记录对象进入特定状态时间的数据库表，结构及数据如下：

ObjID Timestamp                        State
----- ----------                       -----
A     2022-09-14 09:00:00.000001       1
A     2022-09-14 09:00:00.000002       2
A     2022-09-14 09:00:00.000003       3
A     2022-09-14 09:00:00.000004       4
B     2022-09-14 10:00:00.000001       1
B     2022-09-14 10:00:00.000002       2
C     2022-09-14 11:00:00.000001       1
C     2022-09-14 11:00:00.000002       2

需求说明 #

需要编写SQL查询，统计所有状态转换路径对应的对象数量，期望输出如下：

State                Count
-----                ----
1->2->3->4           1
1->2                 2

现有方案的局限 #

此前尝试的查询仅能统计固定长度的路径（如1->2->3->4），无法处理任意长度的转换路径：

SELECT COUNT(*)
  from Table T1,
       Table T2,
       Table T3,
       Table T4
 WHERE T1.ObjId = T2.ObjId
   AND T1.ObjId = T3.ObjId
   AND T1.ObjId = T4.ObjId
   AND T1.Timestamp < T2.Timestamp
   AND T2.Timestamp < T3.Timestamp
   AND T3.Timestamp < T4.Timestamp
   AND T1.State = 1
   AND T2.State = 2
   AND T3.State = 3
   AND T4.State = 4

解决方案 #

可以通过窗口函数排序+字符串聚合的方式实现任意长度路径的统计，以下是不同数据库的实现示例：

SQL Server / PostgreSQL #

WITH OrderedStates AS (
    SELECT 
        ObjID,
        State,
        -- 按对象分组，根据时间给状态排序
        ROW_NUMBER() OVER (PARTITION BY ObjID ORDER BY Timestamp) AS rn
    FROM YourTable -- 替换为你的表名
)
SELECT 
    -- 按顺序拼接状态路径
    STRING_AGG(State, '->') WITHIN GROUP (ORDER BY rn) AS State,
    -- 统计对应路径的对象数量
    COUNT(DISTINCT ObjID) AS Count
FROM OrderedStates
GROUP BY ObjID
-- 按数量降序、路径升序排列结果
ORDER BY Count DESC, State;

MySQL #

MySQL使用GROUP_CONCAT实现字符串聚合：

SELECT 
    GROUP_CONCAT(State ORDER BY rn SEPARATOR '->') AS State,
    COUNT(DISTINCT ObjID) AS Count
FROM (
    SELECT 
        ObjID,
        State,
        ROW_NUMBER() OVER (PARTITION BY ObjID ORDER BY Timestamp) AS rn
    FROM YourTable -- 替换为你的表名
) AS OrderedStates
GROUP BY ObjID
ORDER BY Count DESC, State;

逻辑说明 #

1. 先通过窗口函数ROW_NUMBER()给每个对象的状态按时间戳排序，确保状态顺序正确；
2. 用字符串聚合函数将同一个对象的状态按顺序拼接成路径字符串；
3. 最后按路径分组，统计每个路径对应的对象数量。

内容的提问来源于stack exchange，提问作者rish0912