函数列表、概述、基本语法、说明和例子

  环境:oracle 10g
       搞数据库较大一块部分就是为了计算和统计业务数据，在10G中出现了不少有趣的新函数，所以有必要介绍一下所有的统计函数.
       官方的文档中，有各种千奇百怪的函数，有空还是自行练习下更好一些。
       先有个印象，以后才好返回来查找需要的内容.
       今天暂时没有时间，明天再来考虑这个问题吧。
         一，函数列表和概述
             AVG
            COLLECT
            CORR
            CORR_*
            COUNT
            COVAR_POP
            COVAR_SAMP
            CUME_DIST
            DENSE_RANK
            FIRST
            GROUP_ID
            GROUPING
            GROUPING_ID
            LAST
            MAX
            MEDIAN
            MIN
            PERCENTILE_CONT
            PERCENTILE_DISC
            PERCENT_RANK
            RANK
            REGR_ (Linear Regression) Functions
            STATS_BINOMIAL_TEST
            STATS_CROSSTAB
            STATS_F_TEST
            STATS_KS_TEST
            STATS_MODE
            STATS_MW_TEST
            STATS_ONE_WAY_ANOVA
            STATS_T_TEST_*
            STATS_WSR_TEST
            STDDEV
            STDDEV_POP
            STDDEV_SAMP
            SUM
            VAR_POP
            VAR_SAMP
            VARIANCE
        二,分析函数的基本语法
       如果光光用于统计，很多函数都显得比较简单，但是一旦用于分析，则其语法就变得复杂起来。
       基本的分析语法如下：
        over (partition by order by range between unbounded preceding and current row)
       意思就是按照某些列来分区，然后还好考虑数据范围，典型的就是从第一行到当前行.
        2.1 partition by
       用于分组查询的结果集合。 如果没有这个句子，那么函数就把所有的输出结果当作一个分组处理.
       允许在一个查询语句中使用多个分析函数，并且每个的partition语句可以相同也可以不同。
       如果查询的对象支持并行，那么该分析函数也支持并行方式.
        2.2 order by /order siblings by 
       排序语句，在一个分区内部进行排序。
       对于所有的分析函数(除了percentile_cont ,percentile_disc)都可以在一个分区之上进行排序      
        2.3 rows|range  between   ()  and  () --windowing_clause窗口语句
       并非所有的分析函数都支持分窗语句。
       rows和range的区别。rows－定义了一个居于物理行的窗口，range定了一个基于逻辑位移的窗口。只有使用了order by 语句，才能用分窗语句.
       即使使用了分窗语句，也不能保证分析函数每次返回的结果都是一定的（不变化）（当然这是基于表格的数据不变化的前提做出的论断）。可以设置更好的order by 语句，以便确保输出的结果是确定的.
       范围关键字:unbounded preceding |unbounded following|current row
       unbounded preceding  --此行之前的所有行
       unbounded following    --此行之后的所有行
       current row                   --当前行
       5 preceding and current row   --当前行之前5行（包括当前行,共6行)
       1 preceding and 1 following    --前一行，当前行和后一行.
       还可以有其它的组合 。
       还有个常见的统计语法是within group (order by   )，只用于统计，比较典型的是dense_rank   

        2.4   其它事项说明

• •      首先,大部分的分析函数允许在一个SQL语句中出现多次,以下以Rank为例子
  •      其次,大部分的分析函数是可以在同一个SQL中并用的,典型的例如avg(),sum(),count()
  •      统计函数和分析函数还是很不一样的(有点废话),分析突出的是比较,统计侧重于计算.
    

        2.5  示例
       返回基本信息，以及到当前月份为止的平均成交量  
       SQL> select mons,jjr,cjl,avg(cjl) over ( partition by jjr order by mons rows between unbounded preceding and current row ) from test_value ;       
              MONS JJR               CJL AVG(CJL)OVER(PARTITIONBYJJRORD
        ---------- ---------- ---------- ------------------------------
            200801 LZF               250                            250    
            200802 LZF               200                            225
            200803 LZF               300                            250
            200804 LZF                89                         209.75
            200805 LZF               356                            239
            200806 LZF               100               215.833333333333
            200807 LZF               600               270.714285714286
            200808 LZF                23                         239.75
            200809 LZF               400               257.555555555556       
        9 rows selected
        返回结果和所有成交量的平均值
        SQL> select mons,jjr,cjl,avg(cjl) over (partition by jjr order by mons rows between unbounded preceding and unbounded following) from test_value ;       
              MONS JJR               CJL AVG(CJL)OVER(PARTITIONBYJJRORD
        ---------- ---------- ---------- ------------------------------
            200801 LZF               250               257.555555555556
            200802 LZF               200               257.555555555556
            200803 LZF               300               257.555555555556
            200804 LZF                89               257.555555555556
            200805 LZF               356               257.555555555556
            200806 LZF               100               257.555555555556
            200807 LZF               600               257.555555555556
            200808 LZF                23               257.555555555556
            200809 LZF               400               257.555555555556
        
        9 rows selected
        
        三,具体说明和例子
        2.1  avg (平均)
       略,在2.4中已经举例。

        2.2  collect(收集）
        在pl/sql中也许更有施展的余地,必须和cast函数结合起来使用 .
       看看这个例子，您就知道collect到底之干什么用的呢。
       SQL> select  * from table(select cast(collect(rownum) as type_intorder) from tab where rownum<4);      
        COLUMN_VALUE
        ------------
                   1
                   2
                   3                    
        3 rows selected
         2.3   corr(exp1,exp2)  (皮尔逊关系系数)
        用于计算两个参数威尔逊关系系数，至于皮尔逊关系是什么样的统计函数，只能看看统计类书籍了。
        实际执行的结果是：COVAR_POP(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))
        例子略. 
        其它corr_*都是和这个函数类似的皮尔逊函数.
         2.4  count(计数) 
        用法和avg类似，不过count永远不会返回null而已，只会返回>=0的整数.
        基于分析的方法，可以让count返回一些奇妙的结果。
        SQL> select mons ,cjl,count(cjl) over (partition by jjr order by mons) xuhao from test_value;       
              MONS        CJL      XUHAO
        ---------- ---------- ----------
            200801        250          1
            200802        200          2
            200803        300          3
            200804         89          4
            200805        356          5
            200806        100          6
            200807        600          7
            200808         23          8
            200809        400          9       
        9 rows selected

         2.5 COVAR_POP(exp1,exp2) 
        总体协方差＝(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n ，其中的n就是参数对的个数.
        SQL> select mons ,cjl,cjje,covar_pop(cjl,cjje) over (partition by jjr order by mons) xuhao from test_value;       
              MONS        CJL       CJJE      XUHAO
        ---------- ---------- ---------- ----------
            200801        250       1999          0
            200802        200       2000      -12.5              
        SQL> select  (250*1999+200*2000-450*3999/2)/2 from dual;       
        (250*1999+200*2000-450*3999/2)
        ------------------------------
                                 -12.5
         2.6 COVAR_SAMP(EXP1,EXP2) (样本协方差)
        计算公式=(SUM(expr1 * expr2) - SUM(expr1) * SUM(expr2) / n) / (n-1) ,exp1,exp2必须是非空的.

         2.7  CUME_DIST(累计分布) ，PERCENT_RANK
        计算一组值中某个值的累计分布，但是作为统计函数和分析函数，其用法还是不一样的。
        例子（源于原文)： 
        SELECT CUME_DIST(15500, .05) WITHIN GROUP
        (ORDER BY salary, commission_pct) "Cume-Dist of 15500"
        FROM employees;
        Cume-Dist of 15500
        ------------------
        .972222222

        SELECT job_id, last_name, salary, CUME_DIST()
        OVER (PARTITION BY job_id ORDER BY salary) AS cume_dist
        FROM employees
        WHERE job_id LIKE 'PU%';
        JOB_ID LAST_NAME SALARY CUME_DIST
        ---------- ------------------------- ---------- ----------
        PU_CLERK Colmenares 2500 .2
        PU_CLERK Himuro 2600 .4
        PU_CLERK Tobias 2800 .6        

        PERCENT_Rank和cume_dist类似，返回的值也是介于0-1之间。

         2.8 DENSE_RANK
        这个函数同样可用于统计和分析。 用法同cume_dist
        相当不好翻译这个词汇 。 
        作为一个统计函数，dense_rank是这样来计算值的(值总是大于等于1）
        从下面的例子，我们可以看出是如何计算的：
        SQL> select * from Test_Value order by cjl;  （原始数据)
        
              MONS JJR               CJL       CJJE
        ---------- ---------- ---------- ----------
            200808 LZF                23        123
            200804 LZF                89        189
            200806 LZF               100        200
            200802 LZF               200       2000
            200801 LZF               250       1999
            200803 LZF               300       1000
            200805 LZF               356        456
            200809 LZF               400        500
            200807 LZF               600        700
        
        9 rows selected
        
        SQL> select dense_Rank(100) within group (order by cjl) from test_value;
        (小于100的共有2+1）
        DENSE_RANK(100)WITHINGROUP(ORD   
        ------------------------------
                                     3
        
        SQL> select dense_Rank(23) within group (order by cjl) from test_value;
        (小于23的共有0+1）
        DENSE_RANK(23)WITHINGROUP(ORDE
        ------------------------------
                                     1   
        SQL> select dense_Rank(200) within group (order by cjl) from test_value;
        (小于200的共有3+1）
        DENSE_RANK(200)WITHINGROUP(ORD
        ------------------------------
                                     4
        
        SQL> select dense_Rank(600) within group (order by cjl) from test_value;
        (小于等于600的共有8+1）
        DENSE_RANK(600)WITHINGROUP(ORD
        ------------------------------
                                     9
       所以，作为一个统计函数的dense_Rank,其计算方式是参数和order by之后的变量进行比较，如果在其内则加1，最后的结果是所有的累加，并再加1。
      作为一个分析函数，不过是再增加一份分区和窗口，有些人就利用这个来进行排序。
       很重要一点，partition by xxx这个部分并不是必须，这样就是以所有数据作为一个默认分区，在排序中尤为有效。
      SQL> select mons,cjl,dense_rank() over (partition by jjr order by cjl) from test_Value;
        
              MONS        CJL DENSE_RANK()OVER(PARTITIONBYJJ
        ---------- ---------- ------------------------------
            200808         23                              1
            200804         89                              2
            200806        100                              3
            200802        200                              4
            200801        250                              5
            200803        300                              6
            200805        356                              7
            200809        400                              8
            200807        600                              9       
        9 rows selected
        作为分析函数的dense_rank,是不能后跟参数的，例如:
        SQL> select mons,jjr,cjl,dense_Rank() over (partition by jjr order by cjl) from test_Value;
        
              MONS JJR               CJL DENSE_RANK()OVER(PARTITIONBYJJ
        ---------- ---------- ---------- ------------------------------
            200804 LZF                23                              1
            200808 LZF                23                              1
            200806 LZF               100                              2
            200802 LZF               200                              3
            200801 LZF               250                              4
            200803 LZF               300                              5
            200805 LZF               356                              6
            200809 LZF               400                              7
            200807 LZF               600                              8       
        9 rows selected
        需要注意的是，如果order by 中对应的值相同，则其rank也相同，对于作为分析函数的dense_rank，权当作一个排序函数使用（不知道oracle搞得这么复杂是什么意思).,和一般排序不同的在于允许有相同的排名，并且不会存在断裂的排名，其次还允许在不同组内各自排名(通过partition by 来实现).
         2.9  rank 
        rank的语法和dense_Rank一致，主要是取值不一样（体现在rank()分析函数上)
        SQL> SELECT MONS,JJR,CJL ,RANK() OVER (PARTITION BY JJR ORDER BY CJL) FROM TEST_VALUE; 
              MONS JJR               CJL RANK()OVER(PARTITIONBYJJRORDER
        ---------- ---------- ---------- ------------------------------
            200808 LZF                23                              1
            200804 LZF                23                              1
            200806 LZF               100                             3
       如上例，rank()会跳过一个序号，如果是dense_rank 则为1,1,2,rank则为1,1,3.
        关于同时进行多个排名(而且可以是基于不同标准的)
       还是以test_value为表格查询:
       

SQL> Select cjl 成交量,   2         rank() over(Order By cjl Desc) 成交量_排名,   3         cjje 成交金额,   4         rank() over(Order By cjje Desc) 成交金额_排名   5         From test_value   6  /     成交量 成交量_排名   成交金额 成交金额_排名 ---------- ----------- ---------- --------------------------------------------        200           6           2000             1        250           5           1999             2        300           4           1000             3        600           1           700              4        400           2           500              5        356           3           456              6        100           7           200              7         23           8           189               8         23           8           123               9

       
        2.10  FIRST ,LAST 
       这是两个复杂的函数，必须和其它的函数配套使用方可，而且目前也只有和dense_Rank配套使用.
       语法是:
       统计函数  统计函数 ＋keep +(dense_rank first/last +order by ...)
       分析函数  统计函数 +keep  + (dense_rank first/last +order by ...) + over +( over 语句)
       例子：
       SQL> select sum(cjl) keep (dense_rank last order by cjl)  max_cjl,
          2         sum(cjl) keep (dense_rank first order by cjl)  min_cjl
          3   from test_value where jjr='LZF'
          4  /       
           MAX_CJL    MIN_CJL
        ---------- ----------
               600         46       
       作为first dense_rank的内容，存在两条成交量为23的记录，故min_cl=23*2=46
        2.11  GROUP_ID
       没有特别用处，和select 语句中的group by 在一起使用，用于标明不同的分组,而且group by 后面只有跟上rollup之类的函数才是有用的，否则总是返回0，因为总是同个group.
       SQL> select jjr,sum(cjl) ,group_id() from test_value group by jjr ,rollup(jjr);       
        JJR          SUM(CJL) GROUP_ID()
        ---------- ---------- ----------
        WTH              2252          0
        LZF              2252          0
        WTH              2252          1
        LZF              2252          1
        
        2.12   GROUPING,GROUPING_ID 
       这两个函数，也主要是用于操作方便用的，没有太多实际的用处.
        SQL> select jjr ,sum(cjl) from test_value group by rollup(jjr);       
        JJR          SUM(CJL)
        ---------- ----------
        LZF               2252
        WTH              2252
                            4504    
        上例演示标明对于超级统计而言，被分组的字段会以null表示。但是我们一般不希望这样，所以可以用
        decode结合grouping处理。
        SQL> select decode(grouping(jjr),0,jjr,'total cjl') ,sum(cjl) from test_value group by rollup(jjr);       
        DECODE(GROUPING(JJR),0,JJR,'TO   SUM(CJL)
        ------------------------------ ----------
        LZF                                    2252
        WTH                                  2252
        total cjl                               4504
       也有人直接对分组字段使用decode处理，那样会存在问题，因为可能会有些字段是Null，而导致不能正确地表达，我想这就是oracle设计这个函数的初衷之一吧。
       --
       grouping_id必须和select的group by 语句使用。
       grouping_id和grouping初看起来没有什么区别，不过单单参数形式而言就存在不同， grouping只能接受一个参数，而grouping_id可以接受多个参数.其次grouping_id可以对区别分组的层次，而grouping不能（因为grouping总是只跟踪一个参数或者字段) 。
       SQL> select grouping(jjr) ,grouping_id(jjr,mons),sum(cjl) from test_value group by rollup(jjr,mons);       
        GROUPING(JJR) GROUPING_ID(JJR,MONS)   SUM(CJL)
        ------------- --------------------- ----------
                    0                     0        250
                    0                     0        200
                    0                     0        300
                    0                     0         23
                    0                     0        356
                    0                     0        100
                    0                     0        600
                    0                     0         23
                    0                     0        400
                    0                     1       2252
                    0                     0        250
                    0                     0        200
                    0                     0        300
                    0                     0         23
                    0                     0        356
                    0                     0        100
                    0                     0        600
                    0                     0         23
                    0                     0        400
                    0                     1       2252
        
        GROUPING(JJR) GROUPING_ID(JJR,MONS)   SUM(CJL)
        ------------- --------------------- ----------
                    1                      3        4504
        
        21 rows selected

         2.13 max,min --最大，最小
        都具有统计和分析功能，用法同avg.具体略。
         2.14  median   --取中间值
        和avg类似用法，兼具统计和分析功能，参数必须是数值或者是可以隐式转换为数值的类型。用于计算一组数中的中间值，具体算法如下：
        if (CRN = FRN = RN) then
           (value of expression from row at RN)
        else
             (CRN - RN) * (value of expression for row at FRN) +
             (RN - FRN) * (value of expression for row at CRN)
        其中rn=(1 + (0.5*(N-1)),n 表示分组中行数.
        CRN=CEILING(RN),FRN=FLOOR(RN)
        举例：
        SQL> select * from test_value;       
              MONS JJR               CJL       CJJE
        ---------- ---------- ---------- ----------
            200801 LZF               250       1999
            200802 LZF               200       2000
            200803 LZF               300       1000
            200804 LZF                23        189
            200805 LZF               356        456
            200806 LZF               100        200       
        6 rows selected       
        SQL> select jjr,median(cjl),median(cjje) from test_value group by jjr;       
        JJR        MEDIAN(CJL) MEDIAN(CJJE)
        ---------- ----------- ------------
        LZF                225          728
        N=6 , RN= (1 + (0.5*(N-1))=3.5 ,FRN=3,CRN=4
        则按照算法=(CRN - RN) * (value of expression for row at FRN) +
             (RN - FRN) * (value of expression for row at CRN)=0.5*200+0.5*250=225
        
         2.15  PERCENTILE_CONT,PERCENTILE_DISC
        PERCENTILE_CONT也是按照百分比位置来计算值，其形式和dense_Rank类似，但是其值和MEDIAN类似，MEDIAN是PERCENTILE_CONT(0.5)的特例。
         if (CRN = FRN = RN) then
           (value of expression from row at RN)
        else
             (CRN - RN) * (value of expression for row at FRN) +
             (RN - FRN) * (value of expression for row at CRN)
        其中rn=(1 + (p*(N-1)),n 表示分组中行数.p是[0,1]的有理数.
        CRN=CEILING(RN),FRN=FLOOR(RN) 

        PERCENTILE_DISC(p)  ,p=[0,1]
        算法上和percentile_cont类似，语句形式也相同，所不同的是,这个函数不是简单的返回特定位置上的列的值，而是计算cume_dist的值，而这个值必须是大于等于p的最小cume_dist值。
        
         2.16 STATS_*
        属于比较复杂的统计分析函数
         2.17 STDDEV*
        略。       
         2.18 VAR*
        略。
         2.19 REGR_*
        线性回归函数系列。

       20080408 晴，开始热。
       
       ---
       2010-09-26 ，近来比较有空，无需和一些不喜欢的人对面。
        3 . ratio_to_report
       这是一个很有用的函数。
       毫无疑问，常常遇到统计占比的情况，用ratio_to_report无疑是十分有效的。
       9i及其以前是否支持，就不清楚了。

SQL> select * from score2;                                       SID SUBNAME          CJ COMMENTS --------------------------------------- -------------------- ----------                                       3 c                           99 生生世世                                       1 chinese                100 2                                       2 chinese                 80 1                                       4 chinese                 60 0                                       5 chinese                 70 2                                       6 math                     70 3                                       7 math                     80 -1                                       8 math                    100 -2                                       9 english                  67 0   9 rows selected   SQL> select sid,cj,to_char(100*(ratio_to_report(cj) over()),'900.000')||'%' bfb from score2 order by bfb;                                       SID         CJ BFB --------------------------------------- ---------- ---------                                       4         60   08.264%                                       9         67   09.229%                                       5         70   09.642%                                       6         70   09.642%                                       7         80   11.019%                                       2         80   11.019%                                       3         99   13.636%                                       8        100   13.774%                                       1        100   13.774%   9 rows selected ------------ 如果不希望用ratio_to_report也有等效的方式。 SQL> select sid,cj,100*cj/sum(cj) over( order by cj rows   2   between unbounded preceding and unbounded following ) bfb from score2  order by bfb;                                       SID         CJ        BFB --------------------------------------- ---------- ----------                                       4         60 8.26446280                                       9         67 9.22865013                                       5         70 9.64187327                                       6         70 9.64187327                                       7         80 11.0192837                                       2         80 11.0192837                                       3         99 13.6363636                                       8        100 13.7741046                                       1        100 13.7741046   9 rows selected

   毫无疑问，ratio_to_report节省了不少麻烦，除了简介，还无需处理除零之类的异常。