# 一次数据分析的全过程

2011-08-08

2008/1/11 02:14:33:181 181 00001c68 SeqID 418370 ToBack()=TRUE Len=154 MsgID=x00000202

2008/1/11 02:14:33:181 181 00001c68 SeqID 418370 ToFront()=TRUE Len=260 MsgID=x08000202 BEIP=192.168.1.162 BEPort=22049

2008/1/11 03:05:42:330 330 00004110 SeqID 418370 ToBack()=TRUE Len=154 MsgID=x00000202

2008/1/11 03:05:42:346 346 00004110 SeqID 418370 ToFront()=TRUE Len=261 MsgID=x08000202 BEIP=192.168.1.163 BEPort=22049

select b.hours,

case when overlap<10 then &#39;<10ms&#39;

when overlap<20 then &#39;10-20&#39;

when overlap<30 then &#39;20-30&#39;

when overlap<40 then &#39;30-40&#39;

when overlap<50 then &#39;40-50&#39;

when overlap<60 then &#39;50-60&#39;

when overlap<70 then &#39;60-70&#39;

when overlap<80 then &#39;70-80&#39;

when overlap<90 then &#39;80-90&#39;

else &#39;>90ms&#39;

end tt,

count(*)

from

(

select a.f,a.d from

(

select k,a,b,f,d,g,c,

LAG(c, 1, 0) OVER (partition by f,d ORDER BY B,g) lastc,

LAG(b, 1, 0) OVER (partition by f,d ORDER BY B,g) lastb,

case when c - LAG(c, 1, 0) OVER (ORDER BY tt)>=0 then c - LAG(c, 1, 0) OVER (ORDER BY tt)

else c - LAG(c, 1, 0) OVER (ORDER BY tt)+1000 end aa

from test6 t

) a

where a.g=&#39;ToFront()=TRUE&#39; and a.aa>90 )

order by f,d,b,g

) b

group by b.hours,

case when overlap<10 then &#39;<10ms&#39;

when overlap<20 then &#39;10-20&#39;

when overlap<30 then &#39;20-30&#39;

when overlap<40 then &#39;30-40&#39;

when overlap<50 then &#39;40-50&#39;

when overlap<60 then &#39;50-60&#39;

when overlap<70 then &#39;60-70&#39;

when overlap<80 then &#39;70-80&#39;

when overlap<90 then &#39;80-90&#39;

else &#39;>90ms&#39;

end

create or replace trigger trigger_test6

before insert on test6

for each row

declare

begin

select tt.nextval into :new.tt from dual;

end trigger_test6;

select rr,k,a,b,f,d,g,c,

LAG(c, 1, 0) OVER (ORDER BY tt) lastc,

LAG(b, 1, 0) OVER (ORDER BY tt) lastb

from test6 t