CODEWITHSUNDEEP
sqlwindow-functionsgaps-and-islands
Yehuda Karlinsky
Yehuda Karlinsky

Reputation: 349

SQL break groups by a condition

Suppose I have this table:

timestamp | is_break | data
----------+----------+------
 0        | 1        | 10
 1        | 0        | 20
 2        | 1        | 5
 3        | 1        | 10
 4        | 0        | 20
 5        | 0        | 30

How can I separate the data into groups by the is_break column?

For example to get the AVG of the data by group such as:

min_timestamp | avg_data
--------------+---------
 0            | 15        
 2            | 5
 3            | 20

Upvotes: 0

Views: 130

Answers (1)

GMB
GMB

Reputation: 222402

You can use aggregation. How do we define the groups? With a window sum on is_break colum:

select min(timestamp), avg(date) avg_date
from (
    select t.*, sum(is_break) over(order by timestamp) grp
    from mytable t
)
group by grp

Upvotes: 1

Related Questions