SQL 甲骨文 : 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  |

在这里，我想构建一个数据库 View ，它结合了 speed = 0 的连续行。在这种情况下，DateFrom 将是第一行的 DateFrom 值，而 DateTo 将是最后一行的 DateTo 值。结果入表如下:

| 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  |

是否有任何可能的方法在数据库 View 或函数中获取结果？

注意 -1. 删除了 devID 列。这非常令人困惑，而不是添加另一列来理解问题。2. 此外，我还需要添加一个“Period”列，即“DateFrom”和“DateTo”列的区别函数。

Answer 1

此查询使用分析函数 lag()、lead() 和一些逻辑 case ... when 给出所需的输出:

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