sql - 连续分组依据

Question

我有下表:

SELECT * 
FROM mytable 
ORDER BY id;

id  name    code   time
1    A      111     1
2    A      111     2
3    A      888     3
4    A      888     4
5    A      888     5
6    A      888     6
7    A      888     7
8    A      111     8
9    A      111     9
10    A     111     10

我需要得到这样的结果:

name    code    times_between
A       111     1,2
A       888     3,7
A       111     8,10

是否可以按“ block ”分组？我需要根据时间进行区分，所以我不能只按名称分组，编码并只获取第一个和最后一个元素。

Answer 1

一种方法是这样的:

with the_table(id, name ,   code  ,  time) as(
select 1, 'A',111   ,  1 union all
select 2, 'A',111   ,  2 union all
select 3, 'A',888  ,   3 union all
select 4, 'A',888  ,   4 union all
select 5, 'A',888 ,    5 union all
select 6, 'A',888   ,  6 union all
select 7, 'A',888  ,   7 union all
select 8, 'A',111  ,   8 union all
select 9, 'A',111   ,  9 union all
select 10, 'A',111  ,   10
)


select name, code, min(time) ||','|| max(time)  from (
    select name, code, time, id,
    row_number() over(order  by id)   -
    row_number() over(partition by name , code order  by id) as grp
    from the_table
) t
group by name, code, grp
order by min(id)

(我忘记了，只是无法找到/记住创建组 grp 的技术名称)