統計TIPTOP人數
寫一個PROCEDURE定時記錄目前TIPTOP GP上線人數
select * From ds.tc_zpa_file order by tc_zpa11,tc_zpa05,tc_zpa03
select * From ds.tc_zpb_file where substr(tc_zpb04,12,5) between '08:00' and '19:00';
--by 日、時、分、部門
select substr(tc_zpb04,1,16),tc_zpb01,sum(tc_zpb03) From ds.tc_zpb_file
where substr(tc_zpb04,1,10)>='2013/10/02'
and substr(tc_zpb04,12,5) between '09:00' and '18:00' and not(substr(tc_zpb04,12,5) between '12:00' and '13:00')
and substr(tc_zpb04,1,10) not in ('2013/10/05','2013/10/06','2013/10/10','2013/10/12','2013/10/13','2013/10/19','2013/10/20',
'2013/10/26','2013/10/27',
'2013/11/02','2013/11/03','2013/11/09','2013/11/10','2013/11/16','2013/11/17','2013/11/23',
'2013/11/24','2013/11/30',
'2013/12/01','2013/12/07','2013/12/08','2013/12/14','2013/12/15','2013/12/21','2013/12/22',
'2013/12/28','2013/12/29',
'2014/01/01','2014/01/04','2014/01/05','2014/01/11','2014/01/12','2014/01/18','2014/01/19',
'2014/01/25','2014/01/26','2014/01/29','2014/01/30','2014/01/31',
'2014/02/01','2014/02/02','2014/02/03','2014/02/04','2014/02/08','2014/02/09','2014/02/15',
'2014/02/16','2014/02/22','2014/02/23','2014/02/28',
'2014/03/01','2014/03/02','2014/03/08','2014/03/09','2014/03/15','2014/03/16','2014/03/22',
'2014/03/23','2014/03/29','2014/03/30',
'2014/04/04','2014/04/05','2014/04/06','2014/04/12','2014/04/13','2014/04/19','2014/04/20',
'2014/04/26','2014/04/27',
'2014/05/01','2014/05/03','2014/05/04','2014/05/10','2014/05/11','2014/05/17','2014/05/18',
'2014/05/24','2014/05/25','2014/05/31',
'2014/06/01','2014/06/02','2014/06/07','2014/06/08','2014/06/14','2014/06/15','2014/06/21',
'2014/06/22','2014/06/28','2014/06/29',
'2014/07/05','2014/07/06','2014/07/12','2014/07/13','2014/07/19','2014/07/20','2014/07/26',
'2014/07/27',
'2014/08/02','2014/08/03','2014/08/09','2014/08/10','2014/08/16','2014/08/17','2014/08/23',
'2014/08/24','2014/08/30','2014/08/31')
group by substr(tc_zpb04,1,16),tc_zpb01 order by 1
--by 日、時、分
select substr(tc_zpb04,1,16),sum(tc_zpb03) From ds.tc_zpb_file
where substr(tc_zpb04,1,10)>='2013/10/02'
and substr(tc_zpb04,12,5) between '09:00' and '18:00' and not(substr(tc_zpb04,12,5) between '12:00' and '13:00')
and substr(tc_zpb04,1,10) not in ('2013/10/05','2013/10/06','2013/10/10','2013/10/12','2013/10/13','2013/10/19','2013/10/20',
'2013/10/26','2013/10/27',
'2013/11/02','2013/11/03','2013/11/09','2013/11/10','2013/11/16','2013/11/17','2013/11/23',
'2013/11/24','2013/11/30',
'2013/12/01','2013/12/07','2013/12/08','2013/12/14','2013/12/15','2013/12/21','2013/12/22',
'2013/12/28','2013/12/29',
'2014/01/01','2014/01/04','2014/01/05','2014/01/11','2014/01/12','2014/01/18','2014/01/19',
'2014/01/25','2014/01/26','2014/01/29','2014/01/30','2014/01/31',
'2014/02/01','2014/02/02','2014/02/03','2014/02/04','2014/02/08','2014/02/09','2014/02/15',
'2014/02/16','2014/02/22','2014/02/23','2014/02/28',
'2014/03/01','2014/03/02','2014/03/08','2014/03/09','2014/03/15','2014/03/16','2014/03/22',
'2014/03/23','2014/03/29','2014/03/30',
'2014/04/04','2014/04/05','2014/04/06','2014/04/12','2014/04/13','2014/04/19','2014/04/20',
'2014/04/26','2014/04/27',
'2014/05/01','2014/05/03','2014/05/04','2014/05/10','2014/05/11','2014/05/17','2014/05/18',
'2014/05/24','2014/05/25','2014/05/31',
'2014/06/01','2014/06/02','2014/06/07','2014/06/08','2014/06/14','2014/06/15','2014/06/21',
'2014/06/22','2014/06/28','2014/06/29',
'2014/07/05','2014/07/06','2014/07/12','2014/07/13','2014/07/19','2014/07/20','2014/07/26',
'2014/07/27',
'2014/08/02','2014/08/03','2014/08/09','2014/08/10','2014/08/16','2014/08/17','2014/08/23',
'2014/08/24','2014/08/30','2014/08/31')
group by substr(tc_zpb04,1,16) order by 1
--by 日、最大
select substr(a,1,10),max(b) from (
select substr(tc_zpb04,1,16) a,sum(tc_zpb03) b From ds.tc_zpb_file
where substr(tc_zpb04,1,10)>='2013/10/02'
and substr(tc_zpb04,12,5) between '09:00' and '18:00' and not(substr(tc_zpb04,12,5) between '12:00' and '13:00')
and substr(tc_zpb04,1,10) not in ('2013/10/05','2013/10/06','2013/10/10','2013/10/12','2013/10/13','2013/10/19','2013/10/20',
'2013/10/26','2013/10/27',
'2013/11/02','2013/11/03','2013/11/09','2013/11/10','2013/11/16','2013/11/17','2013/11/23',
'2013/11/24','2013/11/30',
'2013/12/01','2013/12/07','2013/12/08','2013/12/14','2013/12/15','2013/12/21','2013/12/22',
'2013/12/28','2013/12/29',
'2014/01/01','2014/01/04','2014/01/05','2014/01/11','2014/01/12','2014/01/18','2014/01/19',
'2014/01/25','2014/01/26','2014/01/29','2014/01/30','2014/01/31',
'2014/02/01','2014/02/02','2014/02/03','2014/02/04','2014/02/08','2014/02/09','2014/02/15',
'2014/02/16','2014/02/22','2014/02/23','2014/02/28',
'2014/03/01','2014/03/02','2014/03/08','2014/03/09','2014/03/15','2014/03/16','2014/03/22',
'2014/03/23','2014/03/29','2014/03/30',
'2014/04/04','2014/04/05','2014/04/06','2014/04/12','2014/04/13','2014/04/19','2014/04/20',
'2014/04/26','2014/04/27',
'2014/05/01','2014/05/03','2014/05/04','2014/05/10','2014/05/11','2014/05/17','2014/05/18',
'2014/05/24','2014/05/25','2014/05/31',
'2014/06/01','2014/06/02','2014/06/07','2014/06/08','2014/06/14','2014/06/15','2014/06/21',
'2014/06/22','2014/06/28','2014/06/29',
'2014/07/05','2014/07/06','2014/07/12','2014/07/13','2014/07/19','2014/07/20','2014/07/26',
'2014/07/27',
'2014/08/02','2014/08/03','2014/08/09','2014/08/10','2014/08/16','2014/08/17','2014/08/23',
'2014/08/24','2014/08/30','2014/08/31')
group by substr(tc_zpb04,1,16)) group by substr(a,1,10) order by 1
--by 日、最小
select substr(a,1,10),min(b) from (
select substr(tc_zpb04,1,16) a,sum(tc_zpb03) b From ds.tc_zpb_file
where substr(tc_zpb04,1,10)>='2013/10/02'
and substr(tc_zpb04,12,5) between '09:00' and '18:00' and not(substr(tc_zpb04,12,5) between '12:00' and '13:00')
and substr(tc_zpb04,1,10) not in ('2013/10/05','2013/10/06','2013/10/10','2013/10/12','2013/10/13','2013/10/19','2013/10/20',
'2013/10/26','2013/10/27',
'2013/11/02','2013/11/03','2013/11/09','2013/11/10','2013/11/16','2013/11/17','2013/11/23',
'2013/11/24','2013/11/30',
'2013/12/01','2013/12/07','2013/12/08','2013/12/14','2013/12/15','2013/12/21','2013/12/22',
'2013/12/28','2013/12/29',
'2014/01/01','2014/01/04','2014/01/05','2014/01/11','2014/01/12','2014/01/18','2014/01/19',
'2014/01/25','2014/01/26','2014/01/29','2014/01/30','2014/01/31',
'2014/02/01','2014/02/02','2014/02/03','2014/02/04','2014/02/08','2014/02/09','2014/02/15',
'2014/02/16','2014/02/22','2014/02/23','2014/02/28',
'2014/03/01','2014/03/02','2014/03/08','2014/03/09','2014/03/15','2014/03/16','2014/03/22',
'2014/03/23','2014/03/29','2014/03/30',
'2014/04/04','2014/04/05','2014/04/06','2014/04/12','2014/04/13','2014/04/19','2014/04/20',
'2014/04/26','2014/04/27',
'2014/05/01','2014/05/03','2014/05/04','2014/05/10','2014/05/11','2014/05/17','2014/05/18',
'2014/05/24','2014/05/25','2014/05/31',
'2014/06/01','2014/06/02','2014/06/07','2014/06/08','2014/06/14','2014/06/15','2014/06/21',
'2014/06/22','2014/06/28','2014/06/29',
'2014/07/05','2014/07/06','2014/07/12','2014/07/13','2014/07/19','2014/07/20','2014/07/26',
'2014/07/27',
'2014/08/02','2014/08/03','2014/08/09','2014/08/10','2014/08/16','2014/08/17','2014/08/23',
'2014/08/24','2014/08/30','2014/08/31')
group by substr(tc_zpb04,1,16)) group by substr(a,1,10) order by 1
--by 日、平圴
select substr(a,1,10),avg(b) from (
select substr(tc_zpb04,1,16) a,sum(tc_zpb03) b From ds.tc_zpb_file
where substr(tc_zpb04,1,10)>='2013/10/02'
and substr(tc_zpb04,12,5) between '09:00' and '18:00' and not(substr(tc_zpb04,12,5) between '12:00' and '13:00')
and substr(tc_zpb04,1,10) not in ('2013/10/05','2013/10/06','2013/10/10','2013/10/12','2013/10/13','2013/10/19','2013/10/20',
'2013/10/26','2013/10/27',
'2013/11/02','2013/11/03','2013/11/09','2013/11/10','2013/11/16','2013/11/17','2013/11/23',
'2013/11/24','2013/11/30',
'2013/12/01','2013/12/07','2013/12/08','2013/12/14','2013/12/15','2013/12/21','2013/12/22',
'2013/12/28','2013/12/29',
'2014/01/01','2014/01/04','2014/01/05','2014/01/11','2014/01/12','2014/01/18','2014/01/19',
'2014/01/25','2014/01/26','2014/01/29','2014/01/30','2014/01/31',
'2014/02/01','2014/02/02','2014/02/03','2014/02/04','2014/02/08','2014/02/09','2014/02/15',
'2014/02/16','2014/02/22','2014/02/23','2014/02/28',
'2014/03/01','2014/03/02','2014/03/08','2014/03/09','2014/03/15','2014/03/16','2014/03/22',
'2014/03/23','2014/03/29','2014/03/30',
'2014/04/04','2014/04/05','2014/04/06','2014/04/12','2014/04/13','2014/04/19','2014/04/20',
'2014/04/26','2014/04/27',
'2014/05/01','2014/05/03','2014/05/04','2014/05/10','2014/05/11','2014/05/17','2014/05/18',
'2014/05/24','2014/05/25','2014/05/31',
'2014/06/01','2014/06/02','2014/06/07','2014/06/08','2014/06/14','2014/06/15','2014/06/21',
'2014/06/22','2014/06/28','2014/06/29',
'2014/07/05','2014/07/06','2014/07/12','2014/07/13','2014/07/19','2014/07/20','2014/07/26',
'2014/07/27',
'2014/08/02','2014/08/03','2014/08/09','2014/08/10','2014/08/16','2014/08/17','2014/08/23',
'2014/08/24','2014/08/30','2014/08/31')
group by substr(tc_zpb04,1,16)) group by substr(a,1,10) order by 1
CREATE OR REPLACE PROCEDURE DS.TT_ProcessRecord IS
sDatetime VARCHAR(21);
/******************************************************************************
NAME: TT_ProcessRecord
PURPOSE:
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2013/10/1 dennis 1. Created this procedure.
NOTES:
Automatically available Auto Replace Keywords:
Object Name: TT_ProcessRecord
Sysdate: 2013/10/1
Date and Time: 2013/10/1, 下午 01:30:47, and 2013/10/1 下午 01:30:47
Username: dennis (set in TOAD Options, Procedure Editor)
Table Name: tc_zpa_file, tc_zpb_file
配合job 設定時間擷取 TIPTOP p_process 記錄, 存放於 tc_zpa_file , tc_zpb_file
******************************************************************************/
BEGIN
sDatetime := to_char(sysdate ,'YYYY/MM/DD HH24:MI:SS');
FOR sRead IN
(
select * from ds.gbq_file
)
LOOP
insert into tc_zpa_file values (sRead.gbq01,sRead.gbq02,sRead.gbq03,sRead.gbq04,sRead.gbq05,
sRead.gbq06,sRead.gbq07,sRead.gbq08,sRead.gbq10,sRead.gbq11,sDatetime);
End Loop;
COMMIT;
FOR sGroup IN
(
select gbq05,count(*) as tc_zpb03
from (select distinct gbq05,gbq02 from gbq_file)
group by gbq05
)
LOOP
-- select gem02 as str_gem02 from SOC.GEM_FILE where gem01 = sGroup.gbq05;
insert into tc_zpb_file values (sGroup.gbq05,'',sGroup.tc_zpb03,sDatetime);
End Loop;
COMMIT;
END TT_ProcessRecord;
