用分析函数改写冗长的sql

看到下面这个语句
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/