SQL : 營收成長

select a.stkid,b.stknm,b.industry,a.rmnth,a.last_year,a.this_year,
       case
       when rmnth = to_char(add_months(sysdate,-1),'mm') then
         round((sum(a.this_year) over(partition by a.stkid order by a.rmnth) - sum(a.last_year) over(partition by a.stkid order by a.rmnth) ) /
               sum(a.last_year) over(partition by a.stkid order by a.rmnth)
               * 100 ,2
              )
       else null
        end "營收累積成長%"
  from (
        select *
          from (
                select stkid,substr(rmnth,1,4) ryear,substr(rmnth,5,2) rmnth,
                       earning
                  from stk_earnings
               ) a
         pivot (
                sum(earning) for ryear in ('2011' as last_year,'2012' as this_year)
               )
       ) a,stk_names b
 where a.stkid = b.stkid
 order by a.stkid,a.rmnth;

select industry,excorp,count(*)
  from (
select a.stkid,b.stknm,b.industry,b.excorp,a.rmnth,a.last_year,a.this_year,
       case
       when rmnth = to_char(add_months(sysdate,-1),'mm') then
         round((sum(a.this_year) over(partition by a.stkid order by a.rmnth) - sum(a.last_year) over(partition by a.stkid order by a.rmnth) ) /
               sum(a.last_year) over(partition by a.stkid order by a.rmnth)
               * 100 ,2
              )
       else null
        end "營收累積成長%"
  from (
        select *
          from (
                select stkid,substr(rmnth,1,4) ryear,substr(rmnth,5,2) rmnth,
                       earning
                  from stk_earnings
               ) a
         pivot (
                sum(earning) for ryear in ('2011' as last_year,'2012' as this_year)
               )
       ) a,stk_names b
 where a.stkid = b.stkid
       )
 where "營收累積成長%" > 0
 group by industry,excorp
 order by count(*) desc;

select a.stkid,b.stknm,b.industry,a.rmnth,a.last_year,a.this_year,
       case
       when rmnth = to_char(add_months(sysdate,-1),'mm') then
         round((sum(a.this_year) over(partition by a.stkid order by a.rmnth) - sum(a.last_year) over(partition by a.stkid order by a.rmnth) ) /
               sum(a.last_year) over(partition by a.stkid order by a.rmnth)
               * 100 ,2
              )
       else null
        end "營收累積成長%"
  from (
        select *
          from (
                select stkid,substr(rmnth,1,4) ryear,substr(rmnth,5,2) rmnth,
                       earning
                  from stk_earnings
               ) a
         pivot (
                sum(earning) for ryear in ('2011' as last_year,'2012' as this_year)
               )
       ) a,stk_names b
 where a.stkid = b.stkid
   and b.industry = '金融保險'
   and b.excorp = '上市'
 order by a.stkid,a.rmnth;