Oracle SQL窗口函数，用于动态跳过和添加行

create table test1 (col1 number,col2 varchar2(5),col3 date,col4 number,col5 number,col6 number);

--Positive scenario sample data

insert into test1 values(1,'A',trunc(sysdate),0,5.5,2);

insert into test1 values(1,'A',trunc(sysdate)+1,0,3.5,3);

insert into test1 values(1,'A',trunc(sysdate)+2,0,2.5,0);

insert into test1 values(1,'A',trunc(sysdate)+3,0,6.5,0);

insert into test1 values(1,'A',trunc(sysdate)+4,0,8,0);

--Negative scenario Sample data (not working)

insert into test1 values(2,'B',trunc(sysdate),0,5.5,2);

insert into test1 values(2,'B',trunc(sysdate),1,3.5,3);

insert into test1 values(2,'B',trunc(sysdate)+1,0,3.5,5);

insert into test1 values(2,'B',trunc(sysdate)+2,0,2.5,2);

insert into test1 values(2,'B',trunc(sysdate)+3,0,6.5,0);

insert into test1 values(2,'B',trunc(sysdate)+4,0,8,0);

insert into test1 values(2,'B',trunc(sysdate)+4,1,7.9,0);

insert into test1 values(2,'B',trunc(sysdate)+5,0,10.5,0);

insert into test1 values(2,'B',trunc(sysdate)+6,0,1.5,0);

/*Working scenario */
--Query fetches for positive scenario where always col4 is 0 for a corresponding combination of col1 and col2. there is no issue
select test1.*,sum(col5) over (partition by col1,col2 order by col3 desc,col4 desc rows col6 preceding ) derived_field from test1
where col1=1 order by col1,col2,col3,col4;


/* Negative Scenario */
--This query needs to be modified so that it should consider only records where col4 is 0. i.e if col6 is 5 and there is a record in preceding 5 rows where col4 is 1
--then that record needs to be ignored and it has to consider the next available 0th record

select test1.*,sum(col5) over (partition by col1,col2 order by col3 desc,col4 desc rows col6 preceding )derived_field from test1
where col1=2 order by col1,col2,col3,col4;
复制

嗨，汤姆，

我有一个典型的场景，我们需要忽略中间的n条记录，并在执行窗口功能时添加n条记录

在上面的示例中，有两组数据被Col1 (值为1和2) 的值区分。对于第一种情况，其中col1为1的每行始终col4为0。SQL窗口函数按预期工作。而第二种情况是典型的。我已经尽力说明下面的问题陈述。如果需要更多信息，请告诉我

COL1 COL2 COL3 COL4 COL5 COL6
2 B 12-12月-18 0 5.5 2
2 B 12-12月-18 1 3.5 3
2 B 13-12月-18 0 3.5 5
2 B 14-12月-18 0 2.5 2
2 B 15-12月-18 0 6.5 0
2 B 16-12月-18 0 8 0
2 B 16-12月-18 1 7.9 0
2 B 17-12月-18 0 10.5 0
2 B 18-12月-18 0 1.5 0


从上表中我需要导出一个新的字段。在某些条件下，该字段将是前面col6行数的sum(col5)。就像下面显示的输出一样

COL1 COL2 COL3 COL4 COL5 COL6 Derived_field
2 B 12-DEC-18 0 5.5 2 11 -- (5.5 3.5 (日期13/12月) 2.5)
2 B 12-DEC-18 1 3.5 3 16- (3.5 3.5 2.5 6.5)
2 B 13-DEC-18 0 3.5 5 32.5
2 B 14-DEC-18 0 2.5 2 17
2 B 15-DEC-18 0 6.5
2 B 16-DEC-18 0 8
2 B 16-DEC-18 1 7.9 0 7.9
2 B 17-DEC-18 0 10.5
2 B 18-DEC-18 0 1.5

我已经尝试了以下查询，但不确定如何忽略col4 = 1的行，如果中间有任何被忽略的行，则动态添加另外1行。关键部分是可能存在col4将具有2,3、4等值的场景，所有这些值都需要忽略，并且需要添加col4 = 0的等效记录数

select test1.*,sum(col5) over (partition by col1,col2 order by col3 desc,col4 desc rows col6 preceding )derived_field from test1
where col1=2 order by col1,col2,col3,col4;
复制

好的，所以你想要的总和:

-当前行
-COL6在具有COL4 = 0的行之后？

如果是这样，您可以找到接下来的COL6行中有多少行具有COL4 = 1的值。然后将其添加到您需要向前看的行数中。

计算以下行的总和COL6以上行的计数。如果COL4 = 0，则仅通过返回COL5来排除要跳过的行。

坚持一些空处理，并将其添加到当前COL5值。

给出类似的东西:

with skips as (
  select t.*,
         col6 + count ( case when col4 <> 0 then 1 end ) over ( 
           partition by col1, col2
           order by col3, col4 
           rows between 1 following and col6 following
         ) rws
  from   test1 t
)
  select s.*,
         col5 +
           nvl ( sum ( case when col4 = 0 then col5 end ) over (
           partition by col1, col2
           order by col3, col4 
           rows between 1 following and rws following
         ), 0 ) d2
  from   skips s
  order  by col1, col2, col3;

COL1   COL2   COL3                   COL4   COL5   COL6   RWS   D2     
     1 A      12-DEC-2018 00:00:00        0    5.5      2     2   11.5 
     1 A      13-DEC-2018 00:00:00        0    3.5      3     3   20.5 
     1 A      14-DEC-2018 00:00:00        0    2.5      0     0    2.5 
     1 A      15-DEC-2018 00:00:00        0    6.5      0     0    6.5 
     1 A      16-DEC-2018 00:00:00        0      8      0     0      8 
     2 B      12-DEC-2018 00:00:00        0    5.5      2     3   11.5 
     2 B      12-DEC-2018 00:00:00        1    3.5      3     3     16 
     2 B      13-DEC-2018 00:00:00        0    3.5      5     6   32.5 
     2 B      14-DEC-2018 00:00:00        0    2.5      2     2     17 
     2 B      15-DEC-2018 00:00:00        0    6.5      0     0    6.5 
     2 B      16-DEC-2018 00:00:00        0      8      0     0      8 
     2 B      16-DEC-2018 00:00:00        1    7.9      0     0    7.9 
     2 B      17-DEC-2018 00:00:00        0   10.5      0     0   10.5 
     2 B      18-DEC-2018 00:00:00        0    1.5      0     0    1.5 
复制

注意: 这仅在没有COL4 = 1行相邻的情况下有效。或者至少，连续的COL4 = 1行数是 <= 您需要跳过的行数。

如果您可以具有COL4 = 1的连续行，则可能会出现这样的情况，即将COL6行向前看为COL4 = 1的行。但这在您需要跳过之后立即有N行。这个解决方案不考虑这些额外的行。