用分析函式改寫冗長的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/，如需轉載，請註明出處，否則將追究法律責任。