ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE分析函数的常用查询总结

ORACLE分析函数的常用查询总结

原创 Linux操作系统 作者:0x0x0x 时间:2012-06-29 15:26:37 0 删除 编辑
在OLAP系统中,由于数据量比较大,该类系统对查询功能的要求比较高!
 
ORACLE的分析函数正好能解决这类问题。
 
分析函数主要用over()来标识,在11g的官方文档上,可以看到下述函数都能用在分析函数中:

AVG *
CORR *
COUNT *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
LISTAGG
MAX *
MIN *
NTH_VALUE*
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Linear Regression) Functions *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE *
 
下面通过实际例子来说明分析函数的应用!
 
1、实现累加的效果:sum(sum())
 
SQL> select * from t;
     YEARS     MONTHS PRODUCT_NA      SALES
---------- ---------- ---------- ----------
      2008          1 A                1000
      2008          1 B                1500
      2008          2 A                2000
      2008          2 B                3000
      2008          2 C                1000
      2008          3 A                3000
已选择6行。
SQL> select years,months,sum(sales) sum1,
  2  sum(sum(sales)) over(partition by years order by months) sum2 from t
  3  group by years,months
  4  /
     YEARS     MONTHS       SUM1       SUM2
---------- ---------- ---------- ----------
      2008          1       2500       2500
      2008          2       6000       8500
      2008          3       3000      11500

2、为记录排名:row_number()、rank()、dense_rank()
 
--在以下的查询结果中,如果用传统的rownum,那么显然取前五名时便会漏了第六名
SQL> select rownum,tt.* from
  2  (select * from t order by sales desc)tt
  3  /
    ROWNUM      YEARS     MONTHS PRODUCT_NA      SALES
---------- ---------- ---------- ---------- ----------
         1       2008          2 B                3000
         2       2008          3 A                3000
         3       2008          2 A                2000
         4       2008          1 B                1500
         5       2008          1 A                1000
         6       2008          2 C                1000
已选择6行。
 
--使用row_number()
SQL> select t.years,t.months,t.product_name,t.sales,
  2  row_number() over(order by t.sales desc) row_numer,
  3  rank() over(order by t.sales desc) rank,
  4  dense_rank() over(order by t.sales desc) dense_rank
  5  from t
  6  group by t.years,t.months,t.product_name,t.sales
  7  /
     YEARS     MONTHS PRODUCT_NA      SALES  ROW_NUMER       RANK DENSE_RANK
---------- ---------- ---------- ---------- ---------- ---------- ----------
      2008          2 B                3000          1          1          1
      2008          3 A                3000          2          1          1
      2008          2 A                2000          3          3          2
      2008          1 B                1500          4          4          3
      2008          1 A                1000          5          5          4
      2008          2 C                1000          6          5          4
已选择6行。
 
从上述SQL的结果来看,可以看出row_number()、rank()、dense_rank()三个排名函数的区别,
row_number()返回的结果是连续递增的,不管排序的值是否相同;
rank()函数,如果遇到相同的值,那么返回相同的排名,然后接下来将跳过相同的排位,继续排名,整个序列并不一定是连续的;
dense_rank()函数返回的排序结果是连续的序列,即使它们有相同的值。
在排序时,如果遇到空值null,那么在DESC的降序排列时,NULL将排在第一位,如果要让NULL排到最后,可以添加一个nulls last
比如:
select t.years,t.months,t.product_name,t.sales,
row_number() over(order by t.sales desc) row_numer,
rank() over(order by t.sales desc) rank,
dense_rank() over(order by t.sales desc nulls last) dense_rank
from t
group by t.years,t.months,t.product_name,t.sales
 

3、返回最多最少的信息:first、last
SQL> select min(months) keep (dense_rank first order by sum(sales) desc) most,
  2  min(months) keep (dense_rank last order by sum(sales) desc) least
  3  from t
  4  group by months
  5  /
      MOST      LEAST
---------- ----------
         2          1

4、将查询出来的数据分等级:NTILE(N)
SQL> select t.years,t.months,t.product_name,
  2  ntile(5) over(order by sum(sales) desc) tile
  3  from t
  4  group by t.years,t.months,t.product_name
  5  /
     YEARS     MONTHS PRODUCT_NA       TILE
---------- ---------- ---------- ----------
      2008          2 B                   1
      2008          3 A                   1
      2008          2 A                   2
      2008          1 B                   3
      2008          2 C                   4
      2008          1 A                   5
已选择6行。
 
一共返回6行,切成5份,那么平均每份1.2行,那么第一份2行,余下每份1行。
 
SQL> select t.years,t.months,t.product_name,
  2  cume_dist() over(order by product_name) cume_dist
  3  from t
  4  group by t.years,t.months,t.product_name
  5  /
     YEARS     MONTHS PRODUCT_NA  CUME_DIST
---------- ---------- ---------- ----------
      2008          1 A                  .5
      2008          2 A                  .5
      2008          3 A                  .5
      2008          1 B          .833333333
      2008          2 B          .833333333
      2008          2 C                   1
已选择6行。
 
cume_dist() 函数返回累计分配比例,上述的总行数N=6,A的行数为3行,那么返回3/6=0.5,B的行数张2行,返回0.5+2/6=0.83333333
 
SQL> select t.years,t.months,t.product_name,
  2  percent_rank() over(order by product_name) cume_dist
  3  from t
  4  group by t.years,t.months,t.product_name
  5  /
     YEARS     MONTHS PRODUCT_NA  CUME_DIST
---------- ---------- ---------- ----------
      2008          1 A                   0
      2008          2 A                   0
      2008          3 A                   0
      2008          1 B                  .6
      2008          2 B                  .6
      2008          2 C                   1
已选择6行。
 
percent_rank()函数返回累计百分比,上述的总行数N=6,A的第一行为1,那么返回(1-0)/(6-1)=0,有重复值同样返回0,B的行数是4,那么返回(4-1)/(6-1)=0.6
 
5、窗口计算:window
 
5.1、计算累积值
SQL> select months,product_name,sum(sales) sum1,
  2  sum(sum(sales)) over(order by months rows between unbounded preceding and unbounded following) sum2,
  3  sum(sum(sales)) over(order by months rows between unbounded preceding and current row) sum3
  4  from t group by months,product_name
  5  /
    MONTHS PRODUCT_NA       SUM1       SUM2       SUM3
---------- ---------- ---------- ---------- ----------
         1 A                1000      11500       1000
         1 B                1500      11500       2500
         2 A                2000      11500       4500
         2 B                3000      11500       7500
         2 C                1000      11500       8500
         3 A                3000      11500      11500
 
上述中巧妙地运用了rows between N preceding and N follwing 的语法!
 
5.2、计算上下月份之类的,用first_value、last_value
SQL> select months,sum(sales) curr,
  2  first_value(sum(sales)) over(order by months rows between 1 preceding and 1 following) prev,
  3  last_value(sum(sales)) over(order by months rows between 1 preceding and 1 following) last,
  4  avg(sum(sales)) over(order by months rows between 1 preceding and 1 following) avg
  5  from t
  6  group by months
  7  /
    MONTHS       CURR       PREV       LAST        AVG
---------- ---------- ---------- ---------- ----------
         1       2500       2500       6000       4250
         2       6000       2500       3000 3833.33333
         3       3000       6000       3000       4500
 
上述中的curr表示当前月份的累积值,prev表示上月的累积值,last表示下月的累积值。
 
5.3、求相邻记录,lag、lead
 
SQL> select  months,sales,lag(sales) over(order by months) lag,
  2  lead(sales) over(order by months) lead
  3  from t
  4  /
    MONTHS      SALES        LAG       LEAD
---------- ---------- ---------- ----------
         1       1000                  1500
         1       1500       1000       2000
         2       2000       1500       3000
         2       3000       2000       1000
         2       1000       3000       3000
         3       3000       1000
已选择6行。
 
点评:分析函数的功能非常强大,平时要多练习,熟练应用各种不同的场景!做到信手拈来的境界!
 
 

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

请登录后发表评论 登录
全部评论

注册时间:2012-06-26

  • 博文量
    35
  • 访问量
    95884