標籤

4GL (1) 人才發展 (10) 人物 (3) 太陽能 (4) 心理 (3) 心靈 (10) 文學 (31) 生活常識 (14) 光學 (1) 名句 (10) 即時通訊軟體 (2) 奇狐 (2) 爬蟲 (1) 音樂 (2) 產業 (5) 郭語錄 (3) 無聊 (3) 統計 (4) 新聞 (1) 經濟學 (1) 經營管理 (42) 解析度 (1) 遊戲 (5) 電學 (1) 網管 (10) 廣告 (1) 數學 (1) 機率 (1) 雜趣 (1) 證券 (4) 證券期貨 (1) ABAP (15) AD (1) agentflow (4) AJAX (1) Android (1) AnyChart (1) Apache (14) BASIS (4) BDL (1) C# (1) Church (1) CIE (1) CO (38) Converter (1) cron (1) CSS (23) DMS (1) DVD (1) Eclipse (1) English (1) excel (5) Exchange (4) Failover (1) Fedora (1) FI (57) File Transfer (1) Firefox (3) FM (2) fourjs (1) Genero (1) gladiatus (1) google (1) Google Maps API (2) grep (1) Grub (1) HR (2) html (23) HTS (8) IE (1) IE 8 (1) IIS (1) IMAP (3) Internet Explorer (1) java (4) JavaScript (22) jQuery (6) JSON (1) K3b (1) ldd (1) LED (3) Linux (117) Linux Mint (4) Load Balance (1) Microsoft (2) MIS (2) MM (51) MSSQL (1) MySQL (27) Network (1) NFS (1) Office (1) OpenSSL (1) Oracle (126) Outlook (3) PDF (6) Perl (60) PHP (33) PL/SQL (1) PL/SQL Developer (1) PM (3) Postfix (2) postfwd (1) PostgreSQL (1) PP (50) python (5) QM (1) Red Hat (4) Reporting Service (28) ruby (11) SAP (234) scp (1) SD (16) sed (1) Selenium (3) Selenium-WebDriver (5) shell (5) SQL (4) SQL server (8) sqlplus (1) SQuirreL SQL Client (1) SSH (2) SWOT (3) Symantec (2) T-SQL (7) Tera Term (2) tip (1) tiptop (24) Tomcat (6) Trouble Shooting (1) Tuning (5) Ubuntu (37) ufw (1) utf-8 (1) VIM (11) Virtual Machine (2) VirtualBox (1) vnc (3) Web Service (2) wget (1) Windows (19) Windows (1) WM (6) Xvfb (2) youtube (1) yum (2)

2012年2月23日 星期四

殖利率_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

沒有留言:

張貼留言