MySQL处理JSON类型state_history字段计算状态迁移耗时方案咨询

MySQL 8.0及以上版本可通过JSON_TABLE函数+窗口函数实现需求，具体实现如下： #

生成状态迁移明细结果 #

先将JSON字段中的时间、状态拆分为结构化行，再通过窗口函数匹配相邻状态对、计算时间差：

WITH state_detail AS (
    -- 拆解JSON为[时间,状态]的结构化行，按时间升序排列
    SELECT 
        t.row_id,
        STR_TO_DATE(jt_keys.state_time, '%Y-%m-%d %H:%i') AS state_time,
        jt.state_value
    FROM tableA t
    -- 提取所有时间键
    JOIN JSON_TABLE(
        JSON_KEYS(t.state_history),
        '$[*]' COLUMNS (
            state_time VARCHAR(50) PATH '$'
        )
    ) jt_keys
    -- 匹配每个时间键对应的状态值
    JOIN JSON_TABLE(
        JSON_ARRAY(JSON_EXTRACT(t.state_history, CONCAT('$."', jt_keys.state_time, '"'))),
        '$[*]' COLUMNS (
            state_value VARCHAR(50) PATH '$'
        )
    ) jt
),
state_transfer AS (
    -- 匹配相邻状态对，计算时间差（这里用小时为基础单位，可按需调整）
    SELECT
        row_id,
        state_value AS Initial_state,
        LEAD(state_value) OVER(PARTITION BY row_id ORDER BY state_time) AS Final_state,
        TIMESTAMPDIFF(HOUR, state_time, LEAD(state_time) OVER(PARTITION BY row_id ORDER BY state_time)) AS time_diff_hour
    FROM state_detail
)
-- 过滤掉没有下一个状态的最后一条无效记录
SELECT 
    row_id,
    Initial_state,
    Final_state,
    -- 可按需转换时间单位，转分钟用time_diff_hour*60，转天用time_diff_hour/24
    CONCAT(ROUND(time_diff_hour/24, 1), ' 天') AS Time_diff
FROM state_transfer
WHERE Final_state IS NOT NULL;

注意如果你的state_history中存储的时间格式和示例不同，需要调整STR_TO_DATE函数的格式参数匹配实际存储格式 #

统计状态对平均迁移耗时 #

基于上述明细结果，直接分组聚合即可得到各状态对的迁移统计指标：

WITH state_detail AS (
    SELECT 
        t.row_id,
        STR_TO_DATE(jt_keys.state_time, '%Y-%m-%d %H:%i') AS state_time,
        jt.state_value
    FROM tableA t
    JOIN JSON_TABLE(
        JSON_KEYS(t.state_history),
        '$[*]' COLUMNS (
            state_time VARCHAR(50) PATH '$'
        )
    ) jt_keys
    JOIN JSON_TABLE(
        JSON_ARRAY(JSON_EXTRACT(t.state_history, CONCAT('$."', jt_keys.state_time, '"'))),
        '$[*]' COLUMNS (
            state_value VARCHAR(50) PATH '$'
        )
    ) jt
),
state_transfer AS (
    SELECT
        state_value AS Initial_state,
        LEAD(state_value) OVER(PARTITION BY row_id ORDER BY state_time) AS Final_state,
        TIMESTAMPDIFF(HOUR, state_time, LEAD(state_time) OVER(PARTITION BY row_id ORDER BY state_time)) AS time_diff_hour
    FROM state_detail
)
SELECT 
    Initial_state,
    Final_state,
    COUNT(*) AS transfer_count,
    CONCAT(ROUND(AVG(time_diff_hour)/24, 1), ' 天') AS avg_transfer_time
FROM state_transfer
WHERE Final_state IS NOT NULL
GROUP BY Initial_state, Final_state;

版本兼容说明 #

如果你的MySQL版本低于8.0，不支持JSON_TABLE和CTE语法，该需求实现会非常繁琐，建议升级到MySQL 8.0及以上版本使用上述方案。

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