oracle 行模式匹配,PL/SQL Challenge 每日一题：2016-6-9 12C新功能：行模式匹配

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引：

http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处：

http://www.plsqlchallenge.com/

作者：Kim Berg Hansen

运行环境：SQLPLUS, SERVEROUTPUT已打开

注：本题给出答案时候要求给予简要说明才能得到奖品

注：本题执行环境为12c

当我们的员工进入或者离开办公大楼时，他们会在门锁系统中输入用户id和密码，然后把他们的进入和离开记录在这张表中：

create table plch_entry_log (

userid    varchar2(3) not null

, direction varchar2(1) not null check(direction in ('I', 'O'))

, datetime  date        not null

)

/

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'

/

insert into plch_entry_log values ('KBH', 'I', '2016-05-30 08:00:15')

/

insert into plch_entry_log values ('KBH', 'O', '2016-05-30 16:15:25')

/

insert into plch_entry_log values ('KBH', 'I', '2016-05-31 08:01:35')

/

insert into plch_entry_log values ('KBH', 'O', '2016-05-31 16:14:45')

/

insert into plch_entry_log values ('KBH', 'O', '2016-05-31 16:16:55')

/

insert into plch_entry_log values ('EVR', 'I', '2016-05-30 08:02:10')

/

insert into plch_entry_log values ('EVR', 'I', '2016-05-30 08:05:20')

/

insert into plch_entry_log values ('EVR', 'I', '2016-05-30 08:07:30')

/

insert into plch_entry_log values ('EVR', 'O', '2016-05-30 16:20:40')

/

insert into plch_entry_log values ('EVR', 'I', '2016-05-31 08:09:50')

/

insert into plch_entry_log values ('EVR', 'O', '2016-05-31 16:02:59')

/

commit

/

当他们进入的时候，在DIRECTION记录的值是'I'。当他们离开的时候，在DIRECTION记录的值是'O'。

给定一个用户，表中的行应该永远在切换值(I, O, I, O, I, ...)指示着进入或者离开。但是有时候员工会和同事一起走过这个门，同事输入了密码，只有同事被记录。

另一方面，连续的 'I' 或者'O'记录也表明这个员工把他的用户id和密码告诉了别人，这可能是一个安全漏洞。

对于每个用户ID，我们想要找出每组连续的(以DATETIME排序)在DIRECTION列有相同的值的记录，每组记录我们需要第一个和最后一个DATETIME的值，出现次数(组中的行数)，以及连续的DIRECTION值。

为此我们有这个未完成的查询：

select *

from plch_entry_log

match_recognize(

partition by userid

order by datetime

##REPLACE##

)

order by userid, fromtime

/

哪些选项可用来取代 ##REPLACE## 使得查询返回这个输出：

USE FROMTIME            TOTIME              OCCURRENCES D

--- ------------------- ------------------- ----------- -

EVR 2016-05-30 08:02:10 2016-05-30 08:07:30           3 I

KBH 2016-05-31 16:14:45 2016-05-31 16:16:55           2 O

(A)

measures

first(datetime) as fromtime

, last(datetime)  as totime

, final count(*)  as occurrences

, classifier()    as direction

one row per match

pattern ( i{2,} | o{2,} )

define

i as direction = 'I'

, o as direction = 'O'

(B)

measures

first(datetime) as fromtime

, last(datetime)  as totime

, final count(*)  as occurrences

, classifier()    as direction

one row per match

pattern ( i++ | o++ )

define

i as direction = 'I'

, o as direction = 'O'

(C)

measures

first(datetime) as fromtime

, last(datetime)  as totime

, final count(*)  as occurrences

, classifier()    as direction

one row per match

pattern ( i i+ | o o+ )

define

i as direction = 'I'

, o as direction = 'O'

(D)

measures

strt.datetime       as fromtime

, last(same.datetime) as totime

, final count(*)      as occurrences

, strt.direction      as direction

one row per match

pattern ( strt same+ )

define

same as direction = prev(direction)

(E)

measures

first(same.datetime) as fromtime

, last(same.datetime)  as totime

, final count(*) + 1   as occurrences

, same.direction       as direction

one row per match

pattern ( same+ )

define

same as direction = prev(direction)

(F)

measures

first(same.datetime) as fromtime

, last(same.datetime)  as totime

, final count(*) + 1   as occurrences

, same.direction       as direction

one row per match

pattern ( same+ )

define

same as next(direction) = direction

(G)

measures

first(same.datetime) as fromtime

, stop.datetime        as totime

, final count(*)       as occurrences

, same.direction       as direction

one row per match

pattern ( same+ stop)

define

same as next(direction) = direction