殖利率_ROA_ROE

select stkid,stknm,excorp,roa,roe,殖利率,dcash 平均現金股利,
       --sum(殖利率_rank+roa_rank) ranks,
       --sum(殖利率_rank+roe_rank) ranks,
       sum(殖利率_rank+roa_rank+roe_rank) ranks,
       closep,volume,industry
  from (
select /*+ index(b) index(c)*/
       a.stkid,b.stknm,b.industry,b.excorp,
       rank() over(order by avg(dcash)/c.closep desc) 殖利率_rank,
       roa_rank,roe_rank,roa,roe,
       round(avg(dcash),3) dcash,
       round(stddev(dcash),3) stdev,
       round(stddev(dcash)/avg(dcash),3) variation,
       round(avg(dcash)/c.closep*100,2) 殖利率,
       b.bps,c.closep,c.volume      
  from (
        select stkid,yyyy,dcash
          from stk_dividends a
         where yyyy between '2007' and '2010'
           and exists (
                       select /*+ index(b)*/null
                         from stk_dividends b
                        where a.stkid = b.stkid
                       having count(b.yyyy) >= 4
                      )
         union all
        select stkid,substr(yyyyq,1,4) yyyy,greatest(sum(eps)*70/75,0) eps --保守原則
          from stk_eps
         where yyyyq between '20111' and '20113'
         group by stkid,substr(yyyyq,1,4)
       ) a,stk_names b,stk_trans c,
       (
        select a.stkid,round(avg(roa),2) roa, round(avg(roe),2) roe,
               rank() over(order by avg(roa) desc) roa_rank,
               rank() over(order by avg(roe) desc) roe_rank
          from (
                select stkid,roa,roe
                  from stk_eps
                 where yyyy between '2007' and '2010'
                 union all
                select stkid,sum(roa)*1.06 roa,sum(roe)*1.06 roe --用前三季保守推算全年
                  from stk_eps
                 where yyyyq between '20111' and '20113'
                 group by stkid
               ) a,stk_names b
         where a.stkid = b.stkid
           and b.excorp = '上市'
         group by a.stkid
       ) d
 where a.stkid = b.stkid
   and a.stkid = c.stkid
   and c.stkdt = '20120223'
   and a.stkid = d.stkid
   and c.closep > 0
 group by a.stkid,b.stknm,c.closep,c.volume,b.industry,b.excorp,b.bps,roa_rank,roe_rank,roa,roe
having avg(dcash) > 0 and stddev(dcash) > 0 and avg(dcash)/c.closep >= 0.0625 and stddev(dcash)/avg(dcash) <= 0.4
       )
 group by stkid,stknm,roa,roe,殖利率,closep,volume,industry,dcash,excorp
 order by ranks