| 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 |
在这里，我要构建数据库视图，该数据库视图将合并速度= 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 |
有没有可能在数据库视图或函数中获得结果的方法？
注意-
1.删除了devID列。这很令人困惑，没有添加另一列来理解这个问题。
2.另外，我还需要添加一个“ Period ”列，即与“DateFrom”和“DateTo”列不同的函数。
最佳答案
该查询使用解析函数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