Oracle 迭代逻辑

嗨

我正在尝试编写一个SQL查询，该查询计算列 (mmwl) 的值，如下所示。

对于DDL和DML脚本，请检查livesql链接

create table t (y date, value_pvt int, mmwl int);

--sample data to generate a record
insert into t
select to_date('28/7/2018','dd/mm/yyyy')+level/24/60
    ,mod(level,3)
    ,null
from dual
connect by level<=5;

select to_char(y,'dd/mm/yyyy hh24:mi:ss') as y
       ,value_pvt
       ,mmwl 
   from t 
 order by 1;

Y             VALUE_PVT MMWL
28/07/2018 00:01:00 1  - 
28/07/2018 00:02:00 2  - 
28/07/2018 00:03:00 0  - 
28/07/2018 00:04:00 1  - 
28/07/2018 00:05:00 2  -

复制

对于y的5个值中的每一个，我需要计算mmwl的值

计算mmwl的公式如下

对于y的第一个值，即y (“28/7/2018 00:01”)

比较 (需要迭代直到y. ie的上界直到5。)

值 _ pvt(1) -值 _ pvt(2) => 1 - 2 = -1
值 _ pvt(2) -值 _ pvt(3) => 2 - 0 = 2
值 _ pvt(3) -值 _ pvt(4) => 0 - 1 = -1
值 _ pvt(4) -值 _ pvt(5) => 1 - 2 = -1

获取差异的最大值
max(-1,2，-1，-1) = 2

所以y('28/7/2018 00:01') 的mmwl值 = 2
-
同样对于y的下一个值
对于y (“28/7/2018 00:02”)
比较
值 _ pvt(1) -值 _ pvt(3) = 1 - 0 = 1
值 _ pvt(2) -值 _ pvt(4) = 2 - 1 = 1
值 _ pvt(3) -值 _ pvt(5) = 0 - 2 = -2

获取差异的最大值
最大 (1,1，-2) = 1
---
这需要持续到倒数第二个记录


最后一条记录即y('28/7/2018 00:05') 的mmwl值将设置为0)

为了获得所需的输出，我创建了一个查询，如下所示

with data
  as (select y /* generate a sequence ordered by the y*/
            ,value_pvt
            ,row_number() over(order by y) as rnk
            ,count(*) over(partition by 1) as tot_cnt
        from t
      )
    ,row_generator
     as (select level as lvl /* generate a rows up and till 5*/
          from dual
         connect by level<=(select tot_cnt 
                              from data 
                             where rownum=1)
         )
    , boundary_values
     as (select a.lvl /* generate records for levels and specify the max and min values of the boundaries. Eg:for lvl=4 low_val=1 and hi_val=5*/
               ,b.lvl as low_val
               ,a.lvl+b.lvl as hi_val
          from row_generator a
          join row_generator b
            on 1=1
         where b.lvl + a.lvl <=(select tot_cnt from data where rownum=1)            
         )    
    , computed_mmwl
     as ( select lvl,max(diff) as diff
           from (select m.lvl
                        ,n.value_pvt - o.value_pvt as diff
                   from boundary_values m
                   join data n
                     on m.low_val=n.rnk
                   join data o
                     on m.hi_val=o.rnk   
                 ) group by lvl
           union all
           select max(rnk), 0  /* this union all adds the last record ie y('28/07/2018 00:05:00')|0   */
             from data
        )
select to_char(a1.y,'dd/mm/yyyy hh24:mi:ss') as y
       ,a1.value_pvt
       ,b1.diff as new_mmwl
 from data a1
 join computed_mmwl b1
   on a1.rnk=b1.lvl
复制

这工作正常，并得到我的输出，但是我很想知道是否有一个更好的方法来做到这一点，使用模型子句或使用模式匹配或递归cte，甚至是一个更好的直sql解决方案。

(注意: t的实际表中会有500多行)

所以，对于位置N (按y排序) 的每一行，你是:

-查找每一行的当前val和之后的val N行之间的差异
-返回这些差异中最大的？

如果是这样，我相信您必须将表与另一个具有相同行数的表交叉连接。

但是有一种比递归更简单的方法来获得差异。

lead的第二个参数陈述了当前之后的多少行，以获得的值。所以使用这个，由N分区，生成所有的差异组合。

然后使用nth_value函数获取此N的Y和PVT值。

最后按nth_value输出分组，找出最大差值。这给出了:

with ranks as (
  select t.*,
         row_number () over ( order by y ) rn,
         count (*) over () c
  from   t
), grps as (
  select value_pvt - lead ( value_pvt, x ) over ( partition by x order by rn ) diff,
         nth_value ( y, x ) over ( partition by x ) nth_y,
         nth_value ( value_pvt, x ) over ( partition by x ) nth_pvt
  from   ranks r , lateral (
    select level x from dual
    connect by level <= c
  ) rws
)
  select nth_y, nth_pvt, nvl ( max ( diff ), 0 ) 
  from   grps
  group  by nth_y, nth_pvt
  order  by nth_y;

NTH_Y                  NTH_PVT   NVL(MAX(DIFF),0)   
28-JUL-2018 00:01:00           1                  2 
28-JUL-2018 00:02:00           2                  1 
28-JUL-2018 00:03:00           0                  0 
28-JUL-2018 00:04:00           1                 -1 
28-JUL-2018 00:05:00           2                  0 
复制