有三个表,一个是时间代码表,一个是地区代码表,另一个是定制关系表,需要从定制关系表中查出每一天24小时的记录数,按小时和地区分组,但是ORACLE只能外连接一张表,我要连两张表,每条记录都有时间和地区,下面是按时间和地区查询,因为数据量很大,所以要考虑性能,按时间查询
procedure p_ltms_register_daily_report as
/****************************************************************
功能: 按每小时为一个时间段统计定制量,每日2点执行
****************************************************************/
v_stat_date date;
v_now_date date;
v_errmsg varchar2(100);
begin
v_stat_date := sysdate - 1;
v_now_date := sysdate;
insert into DM_LTMS_REGISTER_DAILY_REPORT nologging
(stat_day, stat_hour, rate_month, rate_year, rate_once, create_date)
select trunc(v_stat_date) stat_day,
d.hour stat_hour,
nvl(count1, '0') rate_month,
nvl(count2, '0') rate_year,
nvl(count3, '0') rate_once,
sysdate
from (select to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24') hour,
count(t.device_number) count1
from dm_user_issue_info t
where trunc(t.subs_date) = trunc(v_stat_date)
and rate_kind = '3'--包月
group by to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24')) a,
(select to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24') hour,
count(t.device_number) count2
from dm_user_issue_info t
where trunc(t.subs_date) = trunc(v_stat_date)
and rate_kind = '5'--包年
group by to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24')) b,
(select to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24') hour,
count(t.device_number) count3
from dm_user_issue_info t
where trunc(t.subs_date) = trunc(v_stat_date)
and rate_kind = '2'--按次
group by to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24')) c,
dim.dim_hour d
where d.hour = a.hour(+)
and d.hour = b.hour(+)
and d.hour = c.hour(+)
order by stat_day, stat_hour;
commit;
--捕捉异常
Exception
when others then
v_errmsg := substr(SQLERRM, 1, 100);
Bonc_Lib.Write_Etl_Log(v_now_date,
sysdate,
'PKG_REPORT_FORMS_TOTAL',
'proc_ltms_register_daily_report',
v_errmsg);
commit;
end p_ltms_register_daily_report;
如果修改成按时间和地区查询
select '2009-2-7' stat_day,
d.hour stat_hour,
nvl(count2, '0') rate_year,
nvl(count1, '0') rate_month,
sysdate,
area_id(这个要怎么查询出来)
from (
select to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24') hour,
count(t.device_number) count1,
t.area_code area_id
from dm_user_issue_info t,dim.dim_area b
where trunc(t.subs_date) = to_date('2009-2-7','yyyy-mm-dd')
and rate_kind = '5'
and t.area_code=b.area_id --包年
group by to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24'),
t.area_code) a ,
(
select to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24') hour,
count(t.device_number) count2,
b.area_id area_id
from dm_user_issue_info t,dim.dim_area b
where trunc(t.subs_date) = to_date('2009-2-7','yyyy-mm-dd')
and rate_kind = '3'
and t.area_code=b.area_id --包月
group by to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24'),
b.area_id) b ,
dim.dim_hour d,
dim.dim_area e
where d.hour = a.hour(+)
and d.hour = b.hour(+)
and e.area_id=a.area_id(+)
and e.area_id=b.area_id(+) 这里关联两张表会出错
order by stat_day, a.hour
procedure p_ltms_register_daily_report as
/****************************************************************
功能: 按每小时为一个时间段统计定制量,每日2点执行
****************************************************************/
v_stat_date date;
v_now_date date;
v_errmsg varchar2(100);
begin
v_stat_date := sysdate - 1;
v_now_date := sysdate;
insert into DM_LTMS_REGISTER_DAILY_REPORT nologging
(stat_day, stat_hour, rate_month, rate_year, rate_once, create_date)
select trunc(v_stat_date) stat_day,
d.hour stat_hour,
nvl(count1, '0') rate_month,
nvl(count2, '0') rate_year,
nvl(count3, '0') rate_once,
sysdate
from (select to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24') hour,
count(t.device_number) count1
from dm_user_issue_info t
where trunc(t.subs_date) = trunc(v_stat_date)
and rate_kind = '3'--包月
group by to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24')) a,
(select to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24') hour,
count(t.device_number) count2
from dm_user_issue_info t
where trunc(t.subs_date) = trunc(v_stat_date)
and rate_kind = '5'--包年
group by to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24')) b,
(select to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24') hour,
count(t.device_number) count3
from dm_user_issue_info t
where trunc(t.subs_date) = trunc(v_stat_date)
and rate_kind = '2'--按次
group by to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24')) c,
dim.dim_hour d
where d.hour = a.hour(+)
and d.hour = b.hour(+)
and d.hour = c.hour(+)
order by stat_day, stat_hour;
commit;
--捕捉异常
Exception
when others then
v_errmsg := substr(SQLERRM, 1, 100);
Bonc_Lib.Write_Etl_Log(v_now_date,
sysdate,
'PKG_REPORT_FORMS_TOTAL',
'proc_ltms_register_daily_report',
v_errmsg);
commit;
end p_ltms_register_daily_report;
如果修改成按时间和地区查询
select '2009-2-7' stat_day,
d.hour stat_hour,
nvl(count2, '0') rate_year,
nvl(count1, '0') rate_month,
sysdate,
area_id(这个要怎么查询出来)
from (
select to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24') hour,
count(t.device_number) count1,
t.area_code area_id
from dm_user_issue_info t,dim.dim_area b
where trunc(t.subs_date) = to_date('2009-2-7','yyyy-mm-dd')
and rate_kind = '5'
and t.area_code=b.area_id --包年
group by to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24'),
t.area_code) a ,
(
select to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24') hour,
count(t.device_number) count2,
b.area_id area_id
from dm_user_issue_info t,dim.dim_area b
where trunc(t.subs_date) = to_date('2009-2-7','yyyy-mm-dd')
and rate_kind = '3'
and t.area_code=b.area_id --包月
group by to_char(subs_date, 'hh24') || '-' ||
to_char(subs_date + 1 / 24, 'hh24'),
b.area_id) b ,
dim.dim_hour d,
dim.dim_area e
where d.hour = a.hour(+)
and d.hour = b.hour(+)
and e.area_id=a.area_id(+)
and e.area_id=b.area_id(+) 这里关联两张表会出错
order by stat_day, a.hour
你把两张表的关联条件去掉看看,如果还是有错误,说明是sql语句有问题,也就是你select 的字段或有关字符转换的有错误还有start_day是个常量,为何把它放在group by 后面
and d.hour = b.hour(+)
and e.area_id=a.area_id(+)
and e.area_id=b.area_id(+) 这里关联两张表会出错
-------------------------------------------------
左联接的驱动表只能有一个。
你现在对a驱动的有d、e两个,对b也是。