Take the first row from every time window

Question

I have 2 columns: one column is a time column and the other column is some sort of boolean type column:

GMT  VAL
2010-08-01 10:59:32   1
2010-08-01 10:59:33   0
2010-08-01 10:59:34   1
2010-08-01 10:59:36   0
2010-08-01 10:59:38   1
2010-08-01 10:59:41   1
2010-08-01 10:59:43   0
2010-08-01 10:59:45   1
2010-08-01 10:59:47   0
2010-08-01 10:59:53   1

I want to take the first row from every window of 10 seconds.

GMT  VAL
2010-08-01 10:59:32   1
2010-08-01 10:59:43   0

How do I do that ?

Answer 1

You can use row_number():

select t.*
from (select t.*,
             row_nubmer() over (partition by date_trunc('minute', gmt), floor(extract(seconds from gmt) / 6)
                                order by gmt) as seqnum
      from t
     ) t
where seqnum = 1;

You could also convert to a string:

select t.*
from (select t.*,
             row_nubmer() over (partition by left(to_char(gmt, 'YYYYMMDDHH24MMSS'), 13)
                                order by gmt) as seqnum
      from t
     ) t
where seqnum = 1;

Or using epoch:

select t.*
from (select t.*,
             row_nubmer() over (partition by floor(extract(epoch from gmt) / 10)                                   order by gmt) as seqnum
      from t
     ) t
where seqnum = 1;