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.

Can you please give link for reference or any sample code so that I can build view? — kd12, Jun 17 '15 at 08:25
is there a reason the 1123 devID isn't included in the result but the 1511 is? — Jeremy C., Jun 17 '15 at 08:40
@JeremyC. - There is no specific reason. In this we need to fetch coulmn values from first row except "DateTo" from last row when speed = 0 thats it. devID is just random number field. — kd12, Jun 17 '15 at 09:29
@a_horse_with_no_name - It will be better if expected solution for Oracle as I am using Oracle. — kd12, Jun 17 '15 at 09:31

score 3 · Accepted Answer · answered Jun 17 '15 at 10:39

3

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

answered Jun 17 '15 at 10:39

Ponder Stibbons

14,723
2
21
24

Thanks for query which is useful to us. Please check http://sqlfiddle.com/#!4/8c214/1 which fails when there is only one consecutive row with speed = 0. It is showing wrong value for DateTo. – kd12 Jun 18 '15 at 05:59
You need to modify conditions for `case` slightly, please check: [SQLFiddle](http://sqlfiddle.com/#!4/8c214/14). This also changes behavior of query when row(s) with speed 0 are at beginning or at the and of data. – Ponder Stibbons Jun 18 '15 at 07:21
Ohh.. Great !! Thanks a lot @Ponder Stibbons. Its now working properly for me. – kd12 Jun 18 '15 at 07:49
What would be the best way to send parameter to inner nested query? – kd12 Jun 19 '15 at 14:07
Is your last question directly connected to original? If no - the correct action is to ask new question in main forum - this way you have better chances for correct answer. And please specify, now I don't catch if you have ORA-00904 like in [this example](http://sqlfiddle.com/#!4/9eecb7/3275) or if you want to pass something from your client application, etc? Maybe [something like this](http://sqlfiddle.com/#!4/9eecb7/3280) is what you're after - but it's just guessing.If you are asking something else - please build full new question. – Ponder Stibbons Jun 19 '15 at 23:19
Created [new question| http://stackoverflow.com/questions/30958088/sql-oracle-combining-consecutive-rows-with-filter]. – kd12 Jun 20 '15 at 19:35
OK.You can add the "Oracle" tag, not only "Oracle11g" to your new question - in this way it will attract more attention. – Ponder Stibbons Jun 20 '15 at 20:40

score 1 · Answer 2 · answered Jul 10 '15 at 18:57

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)

SQL Oracle: Combining consecutive rows

2 Answers2