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
--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
沒有留言:
張貼留言