Oracle窗口函数

Question

CREATED_DATE | ENTITY_ID | TYPE
02-MAY-17 | 1234 | A 
03-MAY-17 | 1234 | B
04-MAY-17 | 1234 | B
05-MAY-17 | 1234 | B

我正在编写一个查询，该查询将返回：

ENTITY_ID | DIFF_BETWEEN_A_B1 | DIFF_BETWEEN_A_B2
1234 | 1 | 2
...

我正在尝试编写一个查询，显示每个ENTITY_ID从第一次A类记录到第一次B类记录，到第一次A类记录和第二次B类记录之间所花费的时间。有两个以上的B类型记录，但只有一个类型A记录。我在想，也许有一种有效的方法可以用窗口函数来实现这一点，但是还没有对这些函数进行过多的处理。你怎么写这个？

Answer 1

下面是一种使用条件聚合的方法：

select t.entity_id,
       max(case when seqnum = 1 and type = 'B' then created_date end) 
        - max(a_created_date) as diff_1,
       max(case when seqnum = 2 and type = 'B' then created_date end) 
        - max(a_created_date) as diff_2
from (select t.*,
             min(case when type = 'A' then created_date end) over (partition by entity_id) as a_created_date,
             row_number() over (partition by entity_id, type order by created_date) as seqnum
      from t
      where type in ('A', 'B')
     ) t
group by entity_id;

Answer 2

下面是在Oracle 12.1和更高版本中如何做到这一点。

由于每个type = 'A'只有一行entity_id，所以MATCH_RECOGNIZE中的排序保证了计算所需的行将是每个分区中的前三行。这意味着不需要DEFINE子句；但是语法需要它(这就是为什么它只要求0 = 0)。

with
     test_data ( created_date, entity_id, tp ) as (
       select to_date('02-MAY-17', 'dd-MON-rr'), 1234, 'A' from dual union all 
       select to_date('03-MAY-17', 'dd-MON-rr'), 1234, 'B' from dual union all
       select to_date('04-MAY-17', 'dd-MON-rr'), 1234, 'B' from dual union all
       select to_date('05-MAY-17', 'dd-MON-rr'), 1234, 'B' from dual
     )
-- End of test data (not part of the solution). SQL query begins below this line
select entity_id, diff_between_a_b1, diff_between_a_b2
from   test_data
match_recognize (
  partition by entity_id
  order by     tp, created_date
  measures     b1.created_date - a.created_date as diff_between_a_b1,
               b2.created_date - a.created_date as diff_between_a_b2  
  pattern      ( ^ a b1 b2 )
  define       a as 0 = 0
)
;

ENTITY_ID  DIFF_BETWEEN_A_B1  DIFF_BETWEEN_A_B2
---------  -----------------  -----------------
     1234                  1                  2