SQL : 融資融券 vs. 三大法人

select stkdt,stkid,stknm,margin,short,to_char(券資比,'990.99')||' %' 券資比
  from (
        select a.stkdt,a.stkid,b.stknm,a.margin,a.short,
               round(a.short/nullif(a.margin,0)*100,2) 券資比,
               lag(a.short/nullif(a.margin,0),1) over(partition by a.stkid order by a.stkdt) 券資比_1,
               lag(a.short/nullif(a.margin,0),2) over(partition by a.stkid order by a.stkdt) 券資比_2,
               lag(a.short/nullif(a.margin,0),3) over(partition by a.stkid order by a.stkdt) 券資比_3
          from stk_margin a,stk_names b
         where a.stkid = b.stkid
       )
 where 券資比   > 券資比_1
   and 券資比_1 > 券資比_2
   and 券資比_2 > 券資比_3
   and stkdt = '20120215'
 order by stkid,stkdt;

select a.stkdt,a.stkid,a.stknm,a.margin,a.short,to_char(a.券資比,'990.99')||' %' 券資比,
       b.三大法人
  from (
        select a.stkdt,a.stkid,b.stknm,a.margin,a.short,
               round(a.short/nullif(a.margin,0)*100,2) 券資比,
               lag(a.short/nullif(a.margin,0),1) over(partition by a.stkid order by a.stkdt) 券資比_1,
               lag(a.short/nullif(a.margin,0),2) over(partition by a.stkid order by a.stkdt) 券資比_2,
               lag(a.short/nullif(a.margin,0),3) over(partition by a.stkid order by a.stkdt) 券資比_3
          from stk_margin a,stk_names b
         where a.stkid = b.stkid
       ) a,
       (
        select stkid,stknm,sum(nvl(外資,0)+nvl(投信,0)+nvl(自營商,0)) 三大法人
          from (
                select insti,stkdt,stkid,stknm,round(sum(diff)/1000) diff
                  from stk_institutions
                 where stkdt >= '20120213'
                 group by insti,stkdt,stkid,stknm
               )
         pivot (
                sum(diff) for insti in ('F' 外資,'S' 投信,'D' 自營商)
               )
         group by stkid,stknm
        having sum(nvl(外資,0)+nvl(投信,0)+nvl(自營商,0)) > 0
       ) b
 where 券資比   > 券資比_1
   and 券資比_1 > 券資比_2
   and 券資比_2 > 券資比_3
   and stkdt = '20120215'
   and a.stkid = b.stkid
 order by a.stkid,a.stkdt;