Jake Jake - 11 months ago 54
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?

Answer Source

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, 
       station, 
       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.