如何在SQL中对相邻行值相同的数据进行分组？后续将计算采购时长

核心思路 #

针对相邻行相同值的分组需求（即SQL中的"岛屿问题"），通过窗口函数标记分组，再基于分组计算时长：

1. 用LAG()函数获取上一行的状态值，对比当前行判断是否需要切换分组
2. 累加分组切换标识生成唯一分组ID
3. 基于分组ID计算每组的首尾时间差，得到采购时长

示例场景 #

假设我们有采购日志表purchase_log，结构及数据如下：

需要实现：为每个订单内连续相同状态的行标记分组，并计算该分组的持续时长（分钟）。

具体实现（MySQL为例） #

步骤1：生成分组ID #

先通过子查询获取上一行状态，再累加生成分组ID：

SELECT 
    log_time,
    status,
    order_id,
    -- 状态变化时累加1，生成唯一分组ID
    SUM(CASE WHEN prev_status = status THEN 0 ELSE 1 END) OVER (PARTITION BY order_id ORDER BY log_time) AS group_id
FROM (
    SELECT 
        log_time,
        status,
        order_id,
        -- 获取同订单内上一行的状态
        LAG(status) OVER (PARTITION BY order_id ORDER BY log_time) AS prev_status
    FROM purchase_log
) AS t1

步骤2：计算分组时长并关联 #

用CTE封装分组数据，再通过窗口函数计算每组的首尾时间差：

WITH grouped_data AS (
    SELECT 
        log_time,
        status,
        order_id,
        SUM(CASE WHEN prev_status = status THEN 0 ELSE 1 END) OVER (PARTITION BY order_id ORDER BY log_time) AS group_id
    FROM (
        SELECT 
            log_time,
            status,
            order_id,
            LAG(status) OVER (PARTITION BY order_id ORDER BY log_time) AS prev_status
        FROM purchase_log
    ) AS t1
)
SELECT 
    gd.log_time,
    gd.status,
    gd.order_id,
    gd.group_id,
    -- 计算分组内的持续时长（分钟）
    TIMESTAMPDIFF(MINUTE, 
                  MIN(gd.log_time) OVER (PARTITION BY gd.order_id, gd.group_id), 
                  MAX(gd.log_time) OVER (PARTITION BY gd.order_id, gd.group_id)) AS duration_minutes
FROM grouped_data gd
ORDER BY gd.order_id, gd.log_time;

不同数据库适配说明 #

• SQL Server：用DATEDIFF(MINUTE, MIN(log_time), MAX(log_time))替代TIMESTAMPDIFF
• PostgreSQL：用EXTRACT(EPOCH FROM (MAX(log_time) - MIN(log_time))) / 60计算分钟数
• Oracle：用NUMTODSINTERVAL(MAX(log_time)-MIN(log_time), 'MINUTE')获取时长

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