I have a table where name, start time and duration.
I need a query that will give me the last 5 names, start time and duration. When if there is a sequence of several names - should get the beginning of the sequence, and its overall continuation.
If there are several sequences of the same name - should only get the last one.

for example:

Should be:

Would appreciate help! Thank you

CodePudding user response：

You need to definir when we start a new sequence with the same name.
I have 2 queries, one takes all occurences of a name as one sequence. The other keeps them together if they follow in the table.

create table table_name(
name varchar(10),
start_time int,
duration int);

✓

insert into table_name values
('a',79431,70),
('a',79420,11),
('a',79415,5),
('b',79413,1),
('c',79300,112),
('c',79298,2),
('b',79296,2),
('c',79288,6),
('c',79284,3),
('c',79278,347),
('d',78400,200),
('d',77764,636),
('e',77759,4),
('d',77741,17),
('f',77700,7),
('a',77642,10);

✓

select
  t.name,
  min(start_time) beginning,
  sum(duration) duration
from table_name t
join
(select distinct name
from table_name
order by start_time desc
limit 5) n
on t.name = n.name
group by t.name;

name | beginning | duration
:--- | --------: | -------:
a    |     77642 |       96
b    |     79296 |        3
c    |     79278 |      470
d    |     77741 |      853
e    |     77759 |        4

select
name,
min(start_time) beginning,
sum(duration) duration
from
(select 
  name,
  start_time,
  duration,
  row_number() over(order by start_time) rn,
  row_number() over(order by start_time desc)
   - row_number() over(partition by name order by start_time desc) as group_number
from table_name) t
group by name,group_number
order by group_number
limit 5;

name | beginning | duration
:--- | --------: | -------:
a    |     79415 |       86
b    |     79413 |        1
c    |     79298 |      114
b    |     79296 |        2
c    |     79278 |      356

db<>fiddle here