分析24小時日誌獲取交易時間分佈

 

1,建立按分鐘分佈的表
create table tmp_acq_min(m varchar2(10));

begin
  for x in 0..23 loop
    for y in 0..59 loop
      insert into tmp_acq_min(m) values(lpad(x,2,'0')||lpad(y,2,'0'));
    end loop;
  end loop;
end;
/

commit;     

2,載入日誌資料
--建立日誌表
create table tmp_acq(id int,d date,txt varchar2(4000),t timestamp);

--建立控制檔案
acq.ctl:
LOAD DATA
APPEND
INTO TABLE TMP_ACQ
(id recnum,
 txt char(2000)
)

--注：此處的recnum很有用，直接對應日誌檔案中的行號，對於skip，discard，reject的記錄，仍然保留記錄號；sequence，只在插入表的記錄按序編號，為reject的記錄保留需要。

--載入日誌
sqlldr bnet303/bnet303 control=acq.ctl data=acqlog.2013-10-31.txt log=1031.log discard=1031.dis bad=1031.bad errors=999999999

3，獲取記錄時間
--獲取時間
update tmp_acq
set t=to_timestamp(substr(txt, 1, 23), 'YYYY-MM-DD HH24:MI:SS,ff3')
 where regexp_like(substr(txt, 1, 23),'\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d,\d\d\d');
--注：使用正規表示式，只更新符合格式的記錄時間

create index idx_tmp_acq_1031 on tmp_acq_1031(id);
--對於部分報錯資訊，行首沒有時間，更新為前一行的時間
declare
   l_t timestamp;
begin
   for x in(select * from tmp_acq order by id) loop
     if(x.t is null) then
       update tmp_acq a set a.t = l_t,d=sysdate where a.id = x.id;
     end if;
     if(x.t is not null) then
       l_t := x.t;
     end if;
   end loop;
end;
/
commit;

4，查詢
--O999：產品層報無法獲取連線次數
select m.m, nvl(v.cnt, 0) cnt
  from tmp_acq_min m,
       (select to_char(t.t, 'HH24MI') t, count(*) cnt
          from tmp_acq t
         where t.txt like '%|ERROR|COMM|bnet.O999%'
         group by to_char(t.t, 'HH24MI')) v
 where m.m = v.t(+)
 order by 1;