1、--创建数据分析统计表
create table UPS_TB_DAY_REPORT (
YDATE varchar2(8) not null,
PROCODE varchar2(4) not null,
SERVICENO varchar2(16) not null,
COUNTNUM number,
RANKNUM number,
);
2、数据分析统计表 更新数据定时job(每日生成一次)
declare job number;
begin
dbms_job.submit(job,'PRC_DAY_REPORT(null);',trunc(sysdate),'trunc(sysdate)+1+2/24');
end;
3、数据分析统计表 更新数据存储过程
create or replace procedure PRC_DAY_REPORT(v_sdate in varchar2) is
in_sdate varchar2(8);
begin
if v_sdate is null or v_sdate = 'null' then
select to_char(sysdate-1, 'yyyymmdd') into in_sdate from dual;
else
in_sdate:=v_sdate;
end if;
--插入日分析统计表
delete from UPS_TB_DAY_REPORT
where ydate = in_sdate;
insert into UPS_TB_DAY_REPORT
( select mm.yDate,
mm.proCode,
mm.serviceNo,
countNo,
mm.rankNum
from (select to_char(s.create_tm, 'yyyymmdd') yDate,
s.prov_code proCode,
s.service_no serviceNO,
count(s.service_no) countNo,
row_number() over(partition by s.prov_code order by count(s.service_no) desc) rankNum
from ups_user_order s
where to_char(s.create_tm, 'yyyymmdd') = in_sdate
and s.transact_flag = '1'
group by to_char(s.create_tm, 'yyyymmdd'),
s.prov_code,
s.service_no) mm
where mm.rankNum <= 10);
commit;
exception
when others then
rollback;
end;