How to aggregate this rows in smart way?

Question

I have an table and rows like below minimum sample. I want smarter way that can get expected results. Does anyone have any good idea?

create table foo (
    id int,
    s datetime,
    e datetime,
    value float
);

insert foo values
    (1, '2019-1-1 1:00:00', '2019-1-1 3:00:00', 10.0),
    (1, '2019-1-1 1:30:00', '2019-1-1 3:00:00', 10.0),
    (1, '2019-1-1 4:00:00', '2019-1-1 5:00:00', 10.0),
    (1, '2019-1-1 4:30:00', '2019-1-1 6:00:00', 10.0),  
    (2, '2019-1-1 2:00:00', '2019-1-1 6:00:00', 15.0),  
    (2, '2019-1-1 2:00:00', '2019-1-1 6:00:00', 10.0);

I want results such like this.

1, '2019-1-1 1:00:00', '2019-1-1 3:00:00', 10.0
1, '2019-1-1 4:00:00', '2019-1-1 6:00:00', 10.0 
2, '2019-1-1 2:00:00', '2019-1-1 6:00:00', 15.0

They have the longer period, merged s and e that is going to be longer period, and larger value than the rows that have overlapping periods.

Additional Information

I thought that the "smart way" is more easy to understand (e.g. less sub-queries is better maybe) and more faster to execute.

Actually, I have an additional geography Point column to "foo" table in sample code, and I have to elect a Point from a row that have lowest "value" in the same time period group. I've thought my logic once. I have no idea to get result without many sub-queries or cursor. So I wanted make my issue more simple to get some idea for this case.

Merging intervals I guess. Look at https://stackoverflow.com/questions/8015482/how-to-merge-time-intervals-in-sql-server — Serg, May 21 '19 at 17:51
@Eric using the later versions of sqlserver with window functions, I suppose. They really perform well. — Serg, May 21 '19 at 18:10
I'm sorry for not showing my logic. I added details and the background for this topic. — Rita, May 22 '19 at 15:56

score 2 · Accepted Answer · answered May 21 '19 at 17:54

This is a type of gaps-and-islands problem. You can solve it by determining when the overlapping time periods start -- that is, where a time period does not overlap with any preceding time periods.

Then, a cumulative sum of the starts defines a group. And you can aggregate by the group:

select id, min(s) as s, max(e) as e, max(value)
from (select f.*,
             sum(case when max_e < s then 1 else 0 end) over (partition by id order by s) as grp
      from (select f.*,
                   max(e) over (partition by id order by s rows between unbounded preceding and 1 preceding) as max_e
            from foo f
           ) f
     ) f
group by id, grp;

Here is a db<>fiddle.

It took me a while to learn window frames. Their power is illustrated here by the brevity of the solution. — The Impaler, May 21 '19 at 18:56
It's awesome! I didn't know the gaps-and-islands problem. I'm trying to apply this pattern to my case. Thank you for your help. — Rita, May 22 '19 at 15:53

score -1 · Answer 2 · answered May 21 '19 at 17:47

-1

use max() aggregate function

select id,s,e,max(value) from table
group by id,s,e

answered May 21 '19 at 17:47

Zaynul Abadin Tuhin

31,407
5
33
63

That seems like a gross oversimplification of the issue – LittleBobbyTables - Au Revoir May 21 '19 at 17:53
This answer answers a different question. – The Impaler May 21 '19 at 18:03

How to aggregate this rows in smart way?

2 Answers2