SQL Oracle: Combining consecutive rows

Question

    | RecordId | high_speed |   speed  | DateFrom   |  DateTo     |
    ---------------------------------------------------------------
    | 666542   |   60       |   10     | 09/11/2011 |  10/11/2011 |
    | 666986   |   20       |   20     | 11/11/2011 |  11/11/2011 |
    | 666996   |   0        |   0      | 13/11/2011 |  17/11/2011 |
    | 755485   |   0        |   0      | 01/11/2011 |  14/11/2011 |
    | 758545   |   70       |   50     | 15/11/2011 |  26/11/2011 |
    | 796956   |   40       |   40     | 09/11/2011 |  09/11/2011 |
    | 799656   |   25       |   20     | 09/11/2011 | 09/11/2011  |
    | 808845   |   0        |   0      | 15/11/2011 | 15/11/2011  |
    | 823323   |   0        |   0      | 15/11/2011 | 16/11/2011  |
    | 823669   |   0        |   0      | 17/11/2011 | 18/11/2011  |
    | 899555   |   0        |   0      | 18/11/2011 | 19/11/2011  |
    | 990990   |   20       |   10     | 12/11/2011 | 12/11/2011  |

Here, I want to construct database view which combines the consecutive rows having speed = 0. In that case, DateFrom will be the DateFrom value from first row & DateTo will be the DateTo value of last row. Which results into table as follows:

| high_speed |    speed  | DateFrom    |    DateTo    |
---------------------------------------------------
|  60        |     10    |  09/11/2011 |  10/11/2011  |
|  20        |     20    |  11/11/2011 |  11/11/2011  |
|  0         |     0     |  13/11/2011 |  14/11/2011  |
|  70        |     50    |  15/11/2011 |  26/11/2011  |
|  40        |     40    |  09/11/2011 |  09/11/2011  |
|  25        |     20    |  09/11/2011 |  09/11/2011  |
|  0         |     0     |  15/11/2011 |  19/11/2011  |
|  20        |     10    |  12/11/2011 |  12/11/2011  |

Is there any possible way to get result in database view or function?

Note - 1. Removed devID column. It was very confusing instead of it added another column for understanding of the question. 2. Also additionally, I need to add one "Period" column i.e function which is difference of "DateFrom" & "DateTo" column.

Answer 1

This query using analytic functions lag(), lead() and some logic with case ... when gives desired output:

select high_speed, speed, datefrom, dateto, dateto-datefrom period
  from (
    select recordid, high_speed, speed, datefrom, 
      case when tmp = 2 then lead(dateto) over (order by recordid) 
                        else dateto end dateto, tmp 
      from (
        select test.*, case when speed <> 0 then 1 
                       when lag(speed) over (order by recordid) <> 0 then 2
                       when lead(speed) over (order by recordid) <> 0 then 3 
                       end tmp
          from test )
      where tmp is not null)
   where tmp in (1, 2) order by recordid

^SQLFiddle

Answer 2

This is another approach for the same solution. It uses lag(), lead() and partition by

The difference to the previous solution is that this query binds rows by consecutive periods minding the gaps, i.e:

Consider all rows with speed 0.

• row 1: 01/11 to 14/11
• row 2: 15/11 to 18/11
• row 3: 20/11 to 22/11

Result:

• row 1: 01/11 to 18/11 (merged rows 1 and 2)
• row 2: 20/11 to 22/11 (row 3 is separated because of the gap 19/11)

Also note that periods that share the same days like 15/11-15/11 and 13/11-17/11 will break this query. The sample data provided has periods like this.

-- for better understanding, start reading from the most nested query to the outer

-- QUERY 4: Removes duplicates

-- this query removes duplicates, because both border-rows on a multiple-row period will be identical
-- after the query 3

select distinct
    high_speed,
    speed,
    datefrom,
    dateto,
    dateto-datefrom period
from
(

-- QUERY 3: Selects border-rows and builds datefrom and dateto.

-- this query selects all border-rows, which have the datefrom and dateto data that we need
-- to build the bigger period row.
--
-- this query also builds the bigger period datefrom and dateto

select
    high_speed,
    speed,
    CASE WHEN is_previous_a_border = 0 and is_next_a_border = 1 then lag(datefrom) over (partition by speed order by datefrom)
         WHEN is_previous_a_border = 1 and is_next_a_border = 0 then datefrom
         WHEN is_previous_a_border = 1 and is_next_a_border = 1 then datefrom
         ELSE null END datefrom,
    CASE WHEN is_previous_a_border = 0 and is_next_a_border = 1 then dateto
         WHEN is_previous_a_border = 1 and is_next_a_border = 0 then lead(dateto) over (partition by speed order by datefrom)
         WHEN is_previous_a_border = 1 and is_next_a_border = 1 then dateto
         ELSE null END dateto
from (

-- QUERY 2: Create syntax-sugar

-- this query creates some syntax-sugar properties:
--    - "is_previous_a_border": defines if previous row is a border
--    - "is_next_a_border": defines if previous row is a border

select 
    high_speed,
    speed,
    datefrom,
    dateto,
    is_border,
    nvl(lag(is_border) over (partition by speed order by datefrom), 1) as is_previous_a_border,
    nvl(lead(is_border) over (partition by speed order by datefrom), 1) as is_next_a_border
 from (

-- QUERY 1: Create "is_border" property

-- this query creates the "is_border" property, which defines if a row is a border of a bigger multiple-row period
-- we use partition by to group rows and lag/lead to flag rows with consecutive periods
--
-- note that both border-rows of a bigger multiple-row period will have is_border = 1, while all rows in between
-- them, will have is_border = 0.

select
    high_speed,
    speed,
    datefrom,
    dateto,
    case when lead(datefrom) over (partition by speed order by datefrom) between datefrom and dateto + interval '1' day
         and lag(dateto) over (partition by speed order by datefrom) between datefrom - interval '1' day and dateto then 0
    else 1 end is_border
from
    test))
where is_border = 1)
order by
  speed, datefrom;

SQL Fiddle (with altered sample data)