Jake Jake - 9 months ago 51
SQL Question

SQL: transposing a time series table into a start-end time table if an event occur

I am trying to use a select statement to create a view, transposing a table with datetime into a table with records in each row, the start-end time when the consecutive values by time (partition by station) in 'record' field is not 0.

Here is a sample of the initial table.

enter image description here

And how it should look like after transposing.

enter image description here

Can anyone help?


You can use the conditional_change_event analytical function to create a special grouping identifier to split these out in a simple query:

select row_number() over () unique_id, 
       min(datetime) startdate, 
       max(datetime) enddate
from  (
  select t.*, CONDITIONAL_CHANGE_EVENT(decode(recording,0,0,1)) 
              over (partition by station order by datetime) chg
  from mytable t
) x
where recording > 0
group by station, chg
order by 1, 2

The decode is just to set up your islands and gaps (where gaps are recording <= 0 and islands are recording > 0). Then the change event on that will generate a new identifier for grouping. Also note that I am grouping on the change event even though it isn't part of the output.