分析函数

--取得所占的百分比
select banji,
       fenzu,
       sum(chengji) chengji,
       ratio_to_report(sum(chengji)) over(partition by banji) area_pct
  from stu t
 group by banji, fenzu;
 --取得最高和最低
select banji,
       fenzu,
       sum(chengji) chengji,
       first_value(fenzu) over(order by sum(chengji) desc rows unbounded preceding) firstval,
       first_value(fenzu) over(order by sum(chengji) asc rows unbounded preceding) lastval
  from stu t
 group by banji, fenzu
 order by banji;
 --取得本月上下三个月的和
 select area_code,
        bill_month,
        local_fare,
        sum(local_fare) over(partition by area_code order by to_number(bill_month) range between 1 preceding and 1 following) 3month_sum
   from (select area_code, bill_month, sum(local_fare) local_fare
           from stu t
          group by area_code, bill_month);
 --取得下一个    
select fenzu,
       banji,
       local_fare cur_local_fare,
       lead(local_fare, 1, 0) over(partition by fenzu order by banji) pre_local_fare
  from (select fenzu, banji, sum(chengji) local_fare
          from stu t
         group by fenzu, banji)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26829342/viewspace-744848/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26829342/viewspace-744848/