SQL Oracle-将连续的行与过滤器组合 [英] SQL Oracle - Combining consecutive rows with filter

问题描述

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

在这里，我想构造一个数据库视图，该数据库视图结合了速度= 0的连续行.在这种情况下，DateFrom将是第一行&的DateFrom值. DateTo将是最后一行的DateTo值.结果如下表:

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:

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

要获得合并速度= 0的连续行的结果，我设计了如下用户sql查询的视图:

To get result with combining consecutive rows having speed = 0, I have designed view which users sql query as follows:

select foo_id, high_speed, speed, datefrom, dateto, dateto-datefrom period
  from (
    select recordid, foo_id, 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

现在，我必须应用与foo_id相同的结果.要获得此结果，需要将foo_id过滤器应用于内部嵌套查询.是否可以使用参数创建视图?或者我如何设计带有一个参数的任何函数，该参数将与foo_id列进行比较.前-

Now, I have to apply the same result as per foo_id. To get this result, need apply filter of foo_id to inner nested query. Is it possible to create view with parameter? or how I can design any function which takes one argument which will compare with foo_id column. ex -

select foo_id, high_speed, speed, datefrom, dateto, dateto-datefrom period
      from (
        select recordid, foo_id, 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 foo_id=12 )
          where tmp is not null)
       where tmp in (1, 2) order by recordid

推荐答案

自提出此问题以来已经过去了四年.但是，Oracle在Oracle 12c中添加了 MATCH_RECOGNIZE 子句，使解决方案更简单.

Four years have passed since this question was asked. But Oracle added the MATCH_RECOGNIZE clause to Oracle 12c, and this made the solution simpler.

SELECT
  foo_id, high_speed, speed,
  NVL(DateFromZ, DateFrom) DateFrom,
  NVL(DateToZ, DateTo) DateTo
FROM test
MATCH_RECOGNIZE (
  ORDER BY RecordId
  MEASURES
    FIRST(zeros.DateFrom) AS DateFromZ,
    FINAL LAST(zeros.DateTo) AS DateToZ,
    COUNT(*) AS cnt
  ALL ROWS PER MATCH WITH UNMATCHED ROWS
  PATTERN (zeros+)
  DEFINE
    zeros AS zeros.speed = 0
)
WHERE speed > 0 OR cnt = 1
ORDER BY RecordId;

输出:

+--------+------------+-------+------------+------------+
| FOO_ID | HIGH_SPEED | SPEED |  DATEFROM  |   DATETO   |
+--------+------------+-------+------------+------------+
|     12 |         60 |    10 | 09/11/2011 | 10/11/2011 |
|     13 |         20 |    20 | 11/11/2011 | 11/11/2011 |
|     12 |          0 |     0 | 13/11/2011 | 14/11/2011 |
|     12 |         70 |    50 | 15/11/2011 | 26/11/2011 |
|     12 |         40 |    40 | 09/11/2011 | 09/11/2011 |
|     13 |         25 |    20 | 09/11/2011 | 09/11/2011 |
|     12 |          0 |     0 | 15/11/2011 | 19/11/2011 |
|     12 |         20 |    10 | 12/11/2011 | 12/11/2011 |
+--------+------------+-------+------------+------------+

在 db<>小提琴.

这篇关于SQL Oracle-将连续的行与过滤器组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！