看到下面这个语句
select
iv_date statis_date,
'3' door_type,
t.scan_capacity,
t.visit_number,
t1.tiptop_online_number,
t.ip_number,
decode(t.visit_number,0,0,t2.visit_number_day/t.visit_number)dap_ratio,
decode(t.scan_capacity,0,0,t3.sumtimes/t.scan_capacity) page_average_visit_time,
decode(t.visit_number,0,0,t3.sumtimes/t.visit_number) caller_average_visit_time
from
(select/*+parallel (a,4)*/
iv_date statis_date,
count(1) scan_capacity, --浏览量
count(distinct term_ua_info) visit_number, --访客数
count(distinct user_ip) ip_number --ip数
from musicdw.t_dw_yth_client_action_d a
where statis_date=iv_date
) t,
(select
iv_date statis_date,
max(cou) tiptop_online_number
from(
select /*+parallel (b,4)*/
count(distinct term_ua_info) as cou
from musicdw.t_dw_yth_client_action_d b
where statis_date=iv_date
group by to_char(to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss'),'hh24')
)
) t1,--最高在线人数
(select
iv_date statis_date,
count(term_ua_info) visit_number_day
from(
select /*+parallel (c,4)*/
term_ua_info
from musicdw.t_dw_yth_client_action_d c
where statis_date=iv_date group by to_char(to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss'),'hh24'),term_ua_info having count(1)=1
)
) t2,--日跳出访客数
(select
iv_date statis_date,
trunc(sum(to_date(t2.time_stamp,'yyyy-mm-dd hh24:mi:ss')-to_date(t1.time_stamp,'yyyy-mm-dd hh24:mi:ss'))*1440,0) sumtimes
from
(select/*+parallel (d,4)*/
term_ua_info,
to_char(to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss'),'hh24') nowhour,
time_stamp,
row_number() over( partition by to_char(to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss'),'hh24'),term_ua_info order by to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss')) r1
from musicdw.t_dw_yth_client_action_d d
where statis_date=iv_date
) t1,
(select/*+parallel (e,4)*/
term_ua_info,
to_char(to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss'),'hh24') nowhour,
time_stamp,
row_number() over( partition by to_char(to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss'),'hh24'),term_ua_info order by to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss') desc) r3
from musicdw.t_dw_yth_client_action_d e
where statis_date=iv_date
) t2
where t1.term_ua_info=t2.term_ua_info
and t1.nowhour=t2.nowhour
and t1.r1=1
and t2.r3=1
) t3 --总时长
where t.statis_date=t1.statis_date
and t.statis_date=t2.statis_date
and t.statis_date=t3.statis_date;
commit;
from musicdw.t_dw_yth_client_action_d a where statis_date=iv_date 这一部分在这个语句中出现了五次之多,我决定尝试下优化
开始挑刺
1.from musicdw.t_dw_yth_client_action_d a where statis_date=iv_date一般这种相同的语句是应该避免出现的,对同样的表同样的条件在一个查询语句里面多次查询毫无疑问是要挨批的,
第一个建议:如果无法避免,用with as或者创建临时表处理下
2. to_char(to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss'),'hh24'),一个表示时间的varchar2类型取小时这么取毫无疑问又会被挨批,直接截取固定位即可
3.看起来条件很多很复杂,细想下我尝试用分析函数写了下,是可以处理的
原语句如下:
改写为:
select iv_date statis_date,
'3' door_type,
t.scan_capacity,
t.visit_number,
t1.tiptop_online_number,
t.ip_number,
decode(t.visit_number,0,0,t.visit_number_day/t.visit_number)dap_ratio,
decode(t.scan_capacity,0,0,t.sumtimes/t.scan_capacity) page_average_visit_time,
decode(t.visit_number,0,0,t.sumtimes/t.visit_number) caller_average_visit_time
from (select term_ua_info,
scan_capacity,
visit_number,
ip_number,
max(cou) over(partition by 1) tiptop_online_number,
sum(c1) over(partition by 1) sumtimes,
row_number() over(order by 1) rn2
from (select substr(time_stamp, 9, 2) hour1,
term_ua_info,
count(1) over(partition by 1) scan_capacity, --浏览量
count(distinct term_ua_info) over(partition by 1) visit_number, --访客数
count(distinct user_ip) over(partition by 1) ip_number, --ip数
count(distinct term_ua_info) over(partition by substr(time_stamp, 9, 2)) cou,
(max(time_stamp) over(partition by substr(time_stamp, 9, 2), term_ua_info)) - (min(time_stamp) over(partition by substr(time_stamp, 9, 2), term_ua_info)) c1, --时间差
row_number() over(partition by substr(time_stamp, 9, 2) order by 1) rn1
from musicdw.t_dw_yth_client_action_d a
where statis_date = '20131130')
where rn1 = 1) t
where t.rn2 = 1
select
iv_date statis_date,
'3' door_type,
t.scan_capacity,
t.visit_number,
t1.tiptop_online_number,
t.ip_number,
decode(t.visit_number,0,0,t2.visit_number_day/t.visit_number)dap_ratio,
decode(t.scan_capacity,0,0,t3.sumtimes/t.scan_capacity) page_average_visit_time,
decode(t.visit_number,0,0,t3.sumtimes/t.visit_number) caller_average_visit_time
from
(select/*+parallel (a,4)*/
iv_date statis_date,
count(1) scan_capacity, --浏览量
count(distinct term_ua_info) visit_number, --访客数
count(distinct user_ip) ip_number --ip数
from musicdw.t_dw_yth_client_action_d a
where statis_date=iv_date
) t,
(select
iv_date statis_date,
max(cou) tiptop_online_number
from(
select /*+parallel (b,4)*/
count(distinct term_ua_info) as cou
from musicdw.t_dw_yth_client_action_d b
where statis_date=iv_date
group by to_char(to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss'),'hh24')
)
) t1,--最高在线人数
(select
iv_date statis_date,
count(term_ua_info) visit_number_day
from(
select /*+parallel (c,4)*/
term_ua_info
from musicdw.t_dw_yth_client_action_d c
where statis_date=iv_date group by to_char(to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss'),'hh24'),term_ua_info having count(1)=1
)
) t2,--日跳出访客数
(select
iv_date statis_date,
trunc(sum(to_date(t2.time_stamp,'yyyy-mm-dd hh24:mi:ss')-to_date(t1.time_stamp,'yyyy-mm-dd hh24:mi:ss'))*1440,0) sumtimes
from
(select/*+parallel (d,4)*/
term_ua_info,
to_char(to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss'),'hh24') nowhour,
time_stamp,
row_number() over( partition by to_char(to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss'),'hh24'),term_ua_info order by to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss')) r1
from musicdw.t_dw_yth_client_action_d d
where statis_date=iv_date
) t1,
(select/*+parallel (e,4)*/
term_ua_info,
to_char(to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss'),'hh24') nowhour,
time_stamp,
row_number() over( partition by to_char(to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss'),'hh24'),term_ua_info order by to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss') desc) r3
from musicdw.t_dw_yth_client_action_d e
where statis_date=iv_date
) t2
where t1.term_ua_info=t2.term_ua_info
and t1.nowhour=t2.nowhour
and t1.r1=1
and t2.r3=1
) t3 --总时长
where t.statis_date=t1.statis_date
and t.statis_date=t2.statis_date
and t.statis_date=t3.statis_date;
commit;
from musicdw.t_dw_yth_client_action_d a where statis_date=iv_date 这一部分在这个语句中出现了五次之多,我决定尝试下优化
开始挑刺
1.from musicdw.t_dw_yth_client_action_d a where statis_date=iv_date一般这种相同的语句是应该避免出现的,对同样的表同样的条件在一个查询语句里面多次查询毫无疑问是要挨批的,
第一个建议:如果无法避免,用with as或者创建临时表处理下
2. to_char(to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss'),'hh24'),一个表示时间的varchar2类型取小时这么取毫无疑问又会被挨批,直接截取固定位即可
3.看起来条件很多很复杂,细想下我尝试用分析函数写了下,是可以处理的
原语句如下:
改写为:
select iv_date statis_date,
'3' door_type,
t.scan_capacity,
t.visit_number,
t1.tiptop_online_number,
t.ip_number,
decode(t.visit_number,0,0,t.visit_number_day/t.visit_number)dap_ratio,
decode(t.scan_capacity,0,0,t.sumtimes/t.scan_capacity) page_average_visit_time,
decode(t.visit_number,0,0,t.sumtimes/t.visit_number) caller_average_visit_time
from (select term_ua_info,
scan_capacity,
visit_number,
ip_number,
max(cou) over(partition by 1) tiptop_online_number,
sum(c1) over(partition by 1) sumtimes,
row_number() over(order by 1) rn2
from (select substr(time_stamp, 9, 2) hour1,
term_ua_info,
count(1) over(partition by 1) scan_capacity, --浏览量
count(distinct term_ua_info) over(partition by 1) visit_number, --访客数
count(distinct user_ip) over(partition by 1) ip_number, --ip数
count(distinct term_ua_info) over(partition by substr(time_stamp, 9, 2)) cou,
(max(time_stamp) over(partition by substr(time_stamp, 9, 2), term_ua_info)) - (min(time_stamp) over(partition by substr(time_stamp, 9, 2), term_ua_info)) c1, --时间差
row_number() over(partition by substr(time_stamp, 9, 2) order by 1) rn1
from musicdw.t_dw_yth_client_action_d a
where statis_date = '20131130')
where rn1 = 1) t
where t.rn2 = 1
通过两个嵌套解决,但是由于没有数据,最重要的效率问题无法展示。。。灰常可惜。。。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26477854/viewspace-1072447/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26477854/viewspace-1072447/