Oracle 显示运行总数为零的行以及前面所有行

嗨，团队，

数据设置:

create table test_item as 
with t1 as 
(
select 1 category, 'abc' Item_name,100 item_volume,12.3 item_price,to_date('04-may-2017 12:22:33 AM','dd-mon-yyyy hh:mi:ss AM') time from dual
union 
select 1,'abc' ,-100 ,12.3 ,to_date('04-may-2017 12:22:34 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 1,'abc' ,200 ,12.3 ,to_date('04-may-2017 12:22:34 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union 
select 1,'abc' ,-100 ,12.3 ,to_date('04-may-2017 12:22:35 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 2 category, 'abcd' Item_name,100 item_volume,12.32 item_price,to_date('04-may-2017 12:22:33 AM','dd-mon-yyyy hh:mi:ss AM') time from dual
union 
select 2,'abcd' ,-25 ,12.32 ,to_date('04-may-2017 12:22:34 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 2,'abcd' ,-75 ,12.32 ,to_date('04-may-2017 12:22:34 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union 
select 2,'abcd' ,100 ,12.32 ,to_date('04-may-2017 12:22:35 AM','dd-mon-yyyy hh:mi:ss AM') from dual
)
select * from t1;

-------------------------------------------------- 
  CATEGORY ITEM ITEM_VOLUME ITEM_PRICE TO_CHAR(TIME,'DD-MON-YY
---------- ---- ----------- ---------- -----------------------
         1 abc          100       12.3 04-may-2017 12:22:33 am
         1 abc         -100       12.3 04-may-2017 12:22:34 am
         1 abc          200       12.3 04-may-2017 12:22:34 am
         1 abc         -100       12.3 04-may-2017 12:22:35 am
         2 abcd         100      12.32 04-may-2017 12:22:33 am
         2 abcd         -75      12.32 04-may-2017 12:22:34 am
         2 abcd         -25      12.32 04-may-2017 12:22:34 am
         2 abcd         100      12.32 04-may-2017 12:22:35 am

问题: 需要一个SQL查询，它将只在下面的行，
要求: 分区按item_name，item_price和order按时间和总和item_volume，无论总和将变为零，这些记录需要打印，如果我们看到下面的预期输出100，-100结果零和100，-75，-25结果零，所以只有那些记录需要。

         1 abc          100       12.3 04-MAY-17
         1 abc         -100       12.3 04-MAY-17
         2 abcd         100      12.32 04-MAY-17
         2 abcd         -75      12.32 04-MAY-17
         2 abcd         -25      12.32 04-MAY-17

因此，您希望找到每个类别的运行总数为零的行。然后显示它和它前面的所有行？如果运行总数可以在两个不同的场合达到零，会发生什么？

无论如何，这里有一个基本的算法:

1.计算每个类别的运行总数
2.按类别分配行号
3.查找运行总数最小的行的最高行编号
4.运行总数的最小值

做第一遍的前两个步骤:

select ti.*, 
       sum(item_volume) over (partition by category order by time, item_volume) tot,
       row_number() over (partition by category order by time, item_volume) rn
from   test_item ti;

您可以使用以下方法找到步骤3的值:

max(rn) keep (dense_rank first order by tot) over (partition by category)

这是在说:

按 (运行) tot对行进行排序。然后，对于每个类别中运行总数最低的类别，找到最大行号。

在此之后，您需要做的就是确保最小运行总数为零，并找到行号等于或低于步骤3中的行号的所有行:

create table test_item as 
with t1 as 
(
select 1 category, 'abc' Item_name,100 item_volume,12.3 item_price,to_date('04-may-2017 12:22:33 AM','dd-mon-yyyy hh:mi:ss AM') time from dual
union 
select 1,'abc' ,-100 ,12.3 ,to_date('04-may-2017 12:22:34 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 1,'abc' ,200 ,12.3 ,to_date('04-may-2017 12:22:34 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union 
select 1,'abc' ,-100 ,12.3 ,to_date('04-may-2017 12:22:35 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 2 category, 'abcd' Item_name,100 item_volume,12.32 item_price,to_date('04-may-2017 12:22:33 AM','dd-mon-yyyy hh:mi:ss AM') time from dual
union 
select 2,'abcd' ,-25 ,12.32 ,to_date('04-may-2017 12:22:34 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 2,'abcd' ,-75 ,12.32 ,to_date('04-may-2017 12:22:34 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union 
select 2,'abcd' ,100 ,12.32 ,to_date('04-may-2017 12:22:35 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 3,'def' ,100 ,12.32 ,to_date('04-may-2017 12:22:34 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union 
select 3,'def' ,50 ,12.32 ,to_date('04-may-2017 12:22:35 AM','dd-mon-yyyy hh:mi:ss AM') from dual
)
select * from t1;

with tots as (
select ti.*, 
       sum(item_volume) over (partition by category order by time, item_volume) tot,
       row_number() over (partition by category order by time, item_volume) rn
from   test_item ti
), rws as (
  select t.*, max(rn) keep (dense_rank first order by tot) over (partition by category) mxrn ,
         min(tot) over (partition by category) mn
  from   tots t
)
  select * from rws
  where  rn <= mxrn
  and    mn = 0
  order  by 1, 5;

CATEGORY  ITEM_NAME  ITEM_VOLUME  ITEM_PRICE  TIME                  TOT  RN  MXRN  MN  
1         abc        100          12.3        04-MAY-2017 00:22:33  100  1   2     0   
1         abc        -100         12.3        04-MAY-2017 00:22:34  0    2   2     0   
2         abcd       100          12.32       04-MAY-2017 00:22:33  100  1   3     0   
2         abcd       -75          12.32       04-MAY-2017 00:22:34  25   2   3     0   
2         abcd       -25          12.32       04-MAY-2017 00:22:34  0    3   3     0

或者如果你想做12c风格，你可以使用match_recognize:

select * from test_item
match_recognize (
  partition by category order by time, item_volume
  measures classifier() c
  all rows per match 
  pattern (running* zero) 
  define 
    zero as sum(running.item_volume) = 0
)
where  c = 'RUNNING';

CATEGORY  TIME                  ITEM_VOLUME  C        ITEM_NAME  ITEM_PRICE  
1         04-MAY-2017 00:22:33  100          RUNNING  abc        12.3        
1         04-MAY-2017 00:22:34  -100         RUNNING  abc        12.3        
2         04-MAY-2017 00:22:33  100          RUNNING  abcd       12.32       
2         04-MAY-2017 00:22:34  -75          RUNNING  abcd       12.32       
2         04-MAY-2017 00:22:34  -25          RUNNING  abcd       12.32

不过，这只会在运行总数第一次达到零时匹配。解析方法将匹配多个:

insert into test_item
 select 1,'abc' ,-100 ,12.3 ,to_date('04-may-2017 12:22:35 AM','dd-mon-yyyy hh:mi:ss AM') from dual;

select * from test_item
match_recognize (
  partition by category order by time, item_volume
  measures classifier() c
  all rows per match 
  pattern (running* zero) 
  define 
    zero as sum(running.item_volume) = 0
)
where  c = 'RUNNING';

CATEGORY  TIME                  ITEM_VOLUME  C        ITEM_NAME  ITEM_PRICE  
1         04-MAY-2017 00:22:33  100          RUNNING  abc        12.3        
1         04-MAY-2017 00:22:34  -100         RUNNING  abc        12.3        
2         04-MAY-2017 00:22:33  100          RUNNING  abcd       12.32       
2         04-MAY-2017 00:22:34  -75          RUNNING  abcd       12.32       
2         04-MAY-2017 00:22:34  -25          RUNNING  abcd       12.32  

with tots as (
select ti.*, 
       sum(item_volume) over (partition by category order by time, item_volume) tot,
       row_number() over (partition by category order by time, item_volume) rn
from   test_item ti
), rws as (
  select t.*, max(rn) keep (dense_rank first order by tot) over (partition by category) mxrn ,
         min(tot) over (partition by category) mn
  from   tots t
)
  select * from rws
  where  rn <= mxrn
  and    mn = 0
  order  by 1, 5;

CATEGORY  ITEM_NAME  ITEM_VOLUME  ITEM_PRICE  TIME                  TOT  RN  MXRN  MN  
1         abc        100          12.3        04-MAY-2017 00:22:33  100  1   5     0   
1         abc        -100         12.3        04-MAY-2017 00:22:34  0    2   5     0   
1         abc        200          12.3        04-MAY-2017 00:22:34  200  3   5     0   
1         abc        -100         12.3        04-MAY-2017 00:22:35  0    4   5     0   
1         abc        -100         12.3        04-MAY-2017 00:22:35  0    5   5     0   
2         abcd       100          12.32       04-MAY-2017 00:22:33  100  1   3     0   
2         abcd       -75          12.32       04-MAY-2017 00:22:34  25   2   3     0   
2         abcd       -25          12.32       04-MAY-2017 00:22:34  0    3   3     0