Oracle 分析vs子查询

我有一个示例数据集，其中我想计算给定事件的RET状态数。

这是一个简单的查询，但现在他们希望计数重置时，有一个不同的created_by。
Barney在这个例子中会将event_id = 1的计数重置为1，而事件5仍然会返回2。

EVENT_ID CREATED_DATE CREATED_BY STATUS
1 18/SEP/2016 22:25:00 FRED S1
1 18/SEP/2016 22:25:47 FRED S2
1 21/SEP/2016 23:04:56 FRED S3
1 13/OCT/2016 16:21:52 FRED RET -- don't count me anymore
1 06/NOV/2016 13:46:15 FRED S1
1 07/NOV/2016 20:26:37 FRED S2
1 08/NOV/2016 14:26:05 FRED S3
1 13/NOV/2016 17:59:27 FRED RET -- don't count me anymore
1 31/DEC/2016 12:03:00 FRED S1
1 08/JAN/2017 15:08:31 FRED RET -- don't count me anymore
1 10/JAN/2017 10:09:00 BARNEY S1
1 19/FEB/2017 19:22:33 FRED RET -- count me

5 22/SEP/2016 05:37:00 FRED S1
5 22/SEP/2016 05:37:47 FRED S2
5 25/SEP/2016 06:16:56 FRED S3
5 16/OCT/2016 23:33:52 FRED RET -- count me
5 09/NOV/2016 20:58:15 FRED S1
5 11/NOV/2016 03:38:37 FRED S2
5 11/NOV/2016 21:38:05 FRED S3
5 17/NOV/2016 01:11:27 FRED RET -- count me

我认为下面的查询解决了问题

select count(*)
from recent_eg r
where r.status = 'RET'
and r.event_id = :event
and r.created_date > 
 coalesce( -- get most recent instance of differing name
   (select max(s.created_date)
    from recent_eg s
    where r.event_id = s.event_id
    and r.created_by != s.created_by
    )
 ,to_date(1,'j') -- dawn of time
 )

但是我开始探索分析的问题，但无法弄清楚如何做到这一点-或者if 应该这样做。它将取决于所需的输出吗？应用程序只需要计数，所以它可以应用业务规则，它不需要在行/col格式，但我认为也许在内联视图中使用的分析可能比子查询便宜？

select event_id, created_date, created_by, status
 ,count(case when status='RET' then 1 end) over (partition by event_id order by created_date) cumulative_return_count
 -- desired outcome, but need this to be dynamic
 ,case rownum when 20 then 2 when 12 then 1 end return_count_reset_from_barney
from recent_eg 
order by event_id, created_date;

实际表具有代理pk，event_id上的单独索引，created_date

livesql链接应该更好地显示数据？

(回复: 404链接地址准确，我不得不重新共享脚本。显然仍然习惯于关于编辑、运行、保持最新的livesql。我不太使用它。asktom测试用例需要共享给公众吗？)

您可以在同一事件上有多个RET事务created_by不同的人吗？

假设答案是否定的，您可以使用分析来解决这个问题!:)

以下是方法:

1.查找谁为事件创建了第一个RET事务
2.查找不是第1步找到的人提交的交易的最长日期
3.对created_date大于2的值的RET事务进行计数如果2返回null，则只需对事件使用第一个事务

那么这在代码中看起来如何？

那么你可以用first_value解决步骤1。确保你 “忽略空值”:

CREATE TABLE "RECENT_EG"  
   ("EVENT_ID" NUMBER NOT NULL ENABLE, 
  "CREATED_DATE" DATE NOT NULL ENABLE, 
  "CREATED_BY" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
  "STATUS" VARCHAR2(10 BYTE) 
   );

insert into recent_eg(event_id, created_date, created_by, status) values (1,to_date('18/9/2016 22:25:00','dd/mm/yyyy hh24:mi:ss'),'FRED','S1');
insert into recent_eg(event_id, created_date, created_by, status) values (1,to_date('18/9/2016 22:25:47','dd/mm/yyyy hh24:mi:ss'),'FRED','S2');
insert into recent_eg(event_id, created_date, created_by, status) values (1,to_date('21/9/2016 23:04:56','dd/mm/yyyy hh24:mi:ss'),'FRED','S3');
insert into recent_eg(event_id, created_date, created_by, status) values (1,to_date('13/10/2016 16:21:52','dd/mm/yyyy hh24:mi:ss'),'FRED','RET');
insert into recent_eg(event_id, created_date, created_by, status) values (1,to_date('06/11/2016 13:46:15','dd/mm/yyyy hh24:mi:ss'),'FRED','S1');
insert into recent_eg(event_id, created_date, created_by, status) values (1,to_date('07/11/2016 20:26:37','dd/mm/yyyy hh24:mi:ss'),'FRED','S2');
insert into recent_eg(event_id, created_date, created_by, status) values (1,to_date('08/11/2016 14:26:05','dd/mm/yyyy hh24:mi:ss'),'FRED','S3');
insert into recent_eg(event_id, created_date, created_by, status) values (1,to_date('13/11/2016 17:59:27','dd/mm/yyyy hh24:mi:ss'),'FRED','RET');
insert into recent_eg(event_id, created_date, created_by, status) values (1,to_date('31/12/2016 12:03:00','dd/mm/yyyy hh24:mi:ss'),'FRED','S1');
insert into recent_eg(event_id, created_date, created_by, status) values (1,to_date('08/1/2017 15:08:31','dd/mm/yyyy hh24:mi:ss'),'FRED','RET');
insert into recent_eg(event_id, created_date, created_by, status) values (1,to_date('10/1/2017 10:09:00','dd/mm/yyyy hh24:mi:ss'),'BARNEY','S1');
insert into recent_eg(event_id, created_date, created_by, status) values (1,to_date('19/2/2017 19:22:33','dd/mm/yyyy hh24:mi:ss'),'FRED','RET');
insert into recent_eg(event_id, created_date, created_by, status) values (5,to_date('22/9/2016 05:37:00','dd/mm/yyyy hh24:mi:ss'),'FRED','S1');
insert into recent_eg(event_id, created_date, created_by, status) values (5,to_date('22/9/2016 05:37:47','dd/mm/yyyy hh24:mi:ss'),'FRED','S2');
insert into recent_eg(event_id, created_date, created_by, status) values (5,to_date('25/9/2016 06:16:56','dd/mm/yyyy hh24:mi:ss'),'FRED','S3');
insert into recent_eg(event_id, created_date, created_by, status) values (5,to_date('16/10/2016 23:33:52','dd/mm/yyyy hh24:mi:ss'),'FRED','RET');
insert into recent_eg(event_id, created_date, created_by, status) values (5,to_date('09/11/2016 20:58:15','dd/mm/yyyy hh24:mi:ss'),'FRED','S1');
insert into recent_eg(event_id, created_date, created_by, status) values (5,to_date('11/11/2016 03:38:37','dd/mm/yyyy hh24:mi:ss'),'FRED','S2');
insert into recent_eg(event_id, created_date, created_by, status) values (5,to_date('11/11/2016 21:38:05','dd/mm/yyyy hh24:mi:ss'),'FRED','S3');
insert into recent_eg(event_id, created_date, created_by, status) values (5,to_date('17/11/2016 01:11:27','dd/mm/yyyy hh24:mi:ss'),'FRED','RET');

select event_id, created_date, created_by, status  
      ,first_value(case when status='RET' then created_by end) 
  ignore nulls over (partition by event_id order by created_date) fv
from recent_eg;

EVENT_ID  CREATED_DATE          CREATED_BY  STATUS  FV    
1         18-SEP-2016 22:25:00  FRED        S1            
1         18-SEP-2016 22:25:47  FRED        S2            
1         21-SEP-2016 23:04:56  FRED        S3            
1         13-OCT-2016 16:21:52  FRED        RET     FRED  
1         06-NOV-2016 13:46:15  FRED        S1      FRED  
1         07-NOV-2016 20:26:37  FRED        S2      FRED  
1         08-NOV-2016 14:26:05  FRED        S3      FRED  
1         13-NOV-2016 17:59:27  FRED        RET     FRED  
1         31-DEC-2016 12:03:00  FRED        S1      FRED  
1         08-JAN-2017 15:08:31  FRED        RET     FRED  
1         10-JAN-2017 10:09:00  BARNEY      S1      FRED  
1         19-FEB-2017 19:22:33  FRED        RET     FRED  
5         22-SEP-2016 05:37:00  FRED        S1            
5         22-SEP-2016 05:37:47  FRED        S2            
5         25-SEP-2016 06:16:56  FRED        S3            
5         16-OCT-2016 23:33:52  FRED        RET     FRED  
5         09-NOV-2016 20:58:15  FRED        S1      FRED  
5         11-NOV-2016 03:38:37  FRED        S2      FRED  
5         11-NOV-2016 21:38:05  FRED        S3      FRED  
5         17-NOV-2016 01:11:27  FRED        RET     FRED 

然后，您可以通过返回最大created_date在步骤2中找到日期 (如果存在)，其中:

-在步骤1中计算的created_by <> fv
-对于分区中的所有行 “之前无界和之后无界之间的行”

在此阶段还返回事件的最小日期:

with rws as (
  select event_id, created_date, created_by, status  
    ,first_value(case when status='RET' then created_by end) 
       ignore nulls over (partition by event_id order by created_date) fv
  from recent_eg  
)
  select r.*,
         max(case when created_by <> fv then created_date end) 
           over (partition by event_id order by created_date 
             rows between unbounded preceding and unbounded following) mx,
         min(created_date) over (partition by event_id order by created_date) mn
  from   rws r;

EVENT_ID  CREATED_DATE          CREATED_BY  STATUS  FV    MX                    MN                    
1         18-SEP-2016 22:25:00  FRED        S1            10-JAN-2017 10:09:00  18-SEP-2016 22:25:00  
1         18-SEP-2016 22:25:47  FRED        S2            10-JAN-2017 10:09:00  18-SEP-2016 22:25:00  
1         21-SEP-2016 23:04:56  FRED        S3            10-JAN-2017 10:09:00  18-SEP-2016 22:25:00  
1         13-OCT-2016 16:21:52  FRED        RET     FRED  10-JAN-2017 10:09:00  18-SEP-2016 22:25:00  
1         06-NOV-2016 13:46:15  FRED        S1      FRED  10-JAN-2017 10:09:00  18-SEP-2016 22:25:00  
1         07-NOV-2016 20:26:37  FRED        S2      FRED  10-JAN-2017 10:09:00  18-SEP-2016 22:25:00  
1         08-NOV-2016 14:26:05  FRED        S3      FRED  10-JAN-2017 10:09:00  18-SEP-2016 22:25:00  
1         13-NOV-2016 17:59:27  FRED        RET     FRED  10-JAN-2017 10:09:00  18-SEP-2016 22:25:00  
1         31-DEC-2016 12:03:00  FRED        S1      FRED  10-JAN-2017 10:09:00  18-SEP-2016 22:25:00  
1         08-JAN-2017 15:08:31  FRED        RET     FRED  10-JAN-2017 10:09:00  18-SEP-2016 22:25:00  
1         10-JAN-2017 10:09:00  BARNEY      S1      FRED  10-JAN-2017 10:09:00  18-SEP-2016 22:25:00  
1         19-FEB-2017 19:22:33  FRED        RET     FRED  10-JAN-2017 10:09:00  18-SEP-2016 22:25:00  
5         22-SEP-2016 05:37:00  FRED        S1                                  22-SEP-2016 05:37:00  
5         22-SEP-2016 05:37:47  FRED        S2                                  22-SEP-2016 05:37:00  
5         25-SEP-2016 06:16:56  FRED        S3                                  22-SEP-2016 05:37:00  
5         16-OCT-2016 23:33:52  FRED        RET     FRED                        22-SEP-2016 05:37:00  
5         09-NOV-2016 20:58:15  FRED        S1      FRED                        22-SEP-2016 05:37:00  
5         11-NOV-2016 03:38:37  FRED        S2      FRED                        22-SEP-2016 05:37:00  
5         11-NOV-2016 21:38:05  FRED        S3      FRED                        22-SEP-2016 05:37:00  
5         17-NOV-2016 01:11:27  FRED        RET     FRED                        22-SEP-2016 05:37:00  

您现在要做的就是返回日期大于或等于上面计算的最大日期 (如果为null，则返回最小值) 的RET事务。然后分组并计算结果:

with rws as (
  select event_id, created_date, created_by, status  
    ,first_value(case when status='RET' then created_by end) 
       ignore nulls over (partition by event_id order by created_date) fv
  from recent_eg  
), dates as (
  select r.*,
         max(case when created_by <> fv then created_date end) 
           over (partition by event_id order by created_date 
             rows between unbounded preceding and unbounded following) mx,
         min(created_date) over (partition by event_id order by created_date) mn
  from   rws r
)
  select event_id, count(*) from dates
  where  status = 'RET'
  and    created_date >= coalesce (mx, mn)
  group  by event_id
  order by event_id;

EVENT_ID  COUNT(*)  
1         1         
5         2    

说到哪个 “更好”:

解析解只引用该表一次。所以这应该做得更少。但这确实取决于您的数据和可用的索引。确保您对真实数据进行测试和验证!

在LiveSQL上-是的，请让您提交给AskTOM public的所有脚本!