MySQL: 划分具有连续相同值的区间

需求#

有一组数据，第一列是类型A/B，第二列是操作时间，怎么取出不同类型不同时间段的最大、最小操作时间？
输入：

输出：

建表语句：

create table test2 (
	`type` varchar(5),
	`time` timestamp
)engine=innodb;

insert into test2
values
('A', '2021-08-09 10:00:00'),
('A', '2021-08-09 10:04:00'),
('A', '2021-08-09 10:04:06'),
('B', '2021-08-12 10:04:00'),
('B', '2021-08-13 10:04:00'),
('A', '2021-08-14 10:04:00'),
('A', '2021-08-15 10:08:00'),
('B', '2021-08-16 10:04:00'),
('B', '2021-08-16 10:04:00'),
('B', '2021-08-18 10:04:00')

解决#

select distinct
    type,
    min_type,
    max_type
from (
    select
        `type`,
        `time`,
        max(`time`) over (partition by flag, `type`) max_type,
        min(`time`) over (partition by flag, `type`) min_type
    from (
        select
            a.*,
            cast(rn2_type as signed) - cast(rn_time as signed) as flag
        from (
            select
                `type`,
                `time`,
                row_number() over (order by `time`) rn_time,
                row_number() over (partition by `type` order by `time`) rn2_type
            from test2
        ) a
    ) a
) a

过程解析#

嵌套sql从最里层开始看，非数值连续类型铺垫，先设置两个标签rn_time,rn_type：

select 
	`type`,
	`time`,
	row_number() over(order by time ) rn_time,
	row_number() over(partition by type order by time) rn2_type
from test2

rn_time,rn_type相减得到连续类型分组:

select
    a.*,
    cast(rn2_type as signed) - cast(rn_time as signed) as flag
from (
    select
        `type`,
        `time`,
        row_number() over (order by `time`) rn_time,
        row_number() over (partition by type order by `time`) rn2_type
    from test2
) a

求分组最大值和最小值（注意不同type的flag列可能会相同，因此需要 partition by flag, type，参考的原文中这里有bug）:

select
    `type`,
    `time`,
    max(`time`) over (partition by flag, `type`) max_type,
    min(`time`) over (partition by flag, `type`) min_type
from (
    select
        a.*,
        cast(rn2_type as signed) - cast(rn_time as signed) as flag
    from (
        select
            `type`,
            `time`,
            row_number() over (order by `time`) rn_time,
            row_number() over (partition by type order by `time`) rn2_type
        from test2
    ) a
) a

最后一步：去重:

select distinct
    type,
    min_type,
    max_type
from (
    select
        `type`,
        `time`,
        max(`time`) over (partition by flag, `type`) max_type,
        min(`time`) over (partition by flag, `type`) min_type
    from (
        select
            a.*,
            cast(rn2_type as signed) - cast(rn_time as signed) as flag
        from (
            select
                `type`,
                `time`,
                row_number() over (order by `time`) rn_time,
                row_number() over (partition by `type` order by `time`) rn2_type
            from test2
        ) a
    ) a
) a