OLAP 函数 建议大家看看，还是不错的！

 原文传送门：http://topic.csdn.net/t/20040419/12/2982662.html

排列函数  
   
   
  排列（ranking）函数，排列函数提供了定义一个集合（使用   PARTITION   子句），然后根据某种排序方式对这个集合内的元素进行排列的能力。例如，假设我们有一个雇员表，现在要对每个部门内的雇员薪水进行排列。要实现这一点，我们需要一个函数调用，这个函数调用可以完成以下工作：    
   
  将分区（集合）定义为各个部门   。    
  将集合内的排序方式定义为按薪水排序。    
   
  按照惯例，我们一般会将薪水高的排在前面，所以我们将指定一个对薪水的降序排序方式。下面的例子展示了这个查询的查询和输出。  
   
    select   empnum,   dept,   salary,    
    rank()   over   (partition   by   dept    
                order   by   salary   desc   nulls   last)   as   rank,    
    dense_rank()   over   (partition   by   dept   order   by   salary   desc   nulls   last)as   denserank,    
    row_number()   over   (partition   by   dept   order   by   salary   desc   nulls   last)as   rownumber    
  from   emptab;  
   
    EMPNUM     DEPT   SALARY     RANK     DENSERANK       ROWNUMBER    
    ------     ----   ------     ----     ---------       ---------    
    6               1         78000       1           1                       1    
    2               1         75000       2           2                       2    
    7               1         75000       2           2                       3    
    11             1         53000       4           3                       4    
    5               1         52000       5           4                       5    
    1               1         50000       6           5                       6    
  --------------------------------------------------    
    9               2         51000       1           1                       1    
    4               2               -         2           2                       2    
   
   
       
  展示排序的例子      
   
  首先，让我们看一下函数调用。注意，rank   函数本身没有参数。这是因为   rank   函数不对任何参数执行任何计算。相反，rank   函数只是着眼于行集合--以及每一行在集合中的位置--正如排序方式所定义的那样。那么，我们如何为这个函数定义集合和排序方式呢？两者都是用   OVER   子句定义的。在这个例子中，因为我们要在每个部门内进行排列，因此我们通过按部门划分分区来定义集合。这样做的效果是可以确保只有那些在   dept   列具有相等值的行才参与排列。对排列函数的而言，分区（partition）   和   集合（set）   这两个术语是等价的。在   PARTITION   子句后面，我们有一个   ORDER   BY   子句，这个子句定义了分区内的排序方式。在这里，我们想将高薪排在前面，因此我们将排序方式定义为降序。除了指定降序以外，我们还指定   NULLS   LAST。在   SQL   中，空值排在前面，意即空值显得要大于所有其他非空的值。这就给排列带来了问题，因为我们可能并不想将为空的薪水排在前面。因此，我们使用   NULLS   LAST   子句来更改默认的排序方式，这样就可以将空值排在后面了.  
   
  现在，让我们看一下输出。前   6   行都是   Department   1   的雇员，每一行都被赋予一个按薪水降序排列所得的名次。注意，在   Department   1   中，有两个雇员的薪水都是   75000，这两行都被赋予第二的名次。这是因为   rank   函数提供了一种   “奥林匹克式”的排列方式，在这种方式中，两个相等的值得到相等的名次。因为有两行“结在一起，同获第二”，所以就没有排在第   3   的行。相反，接下来的一行排在第   4，因为根据排序方式，有   3   行严格地排在这一行之前。  
   
  对于   Department   2，注意其中有一个行具有为空的薪水。因为我们指定了   NULLS   LAST，所以这一行被排在非空行的后面。如果我们没有指定   NULLS   LAST   的话，Department   2   中两行的排列顺序就要倒过来了。  
   
  到现在，您可能会问自己，在上面的例子中，其他两个输出列   denserank   和   rownumber   是什么呢？有三个不同的排列函数。首先是   rank   函数，它提供了奥林匹克式的排列方式，这在前面已经描述过了。其他两个函数分别是   dense_rank   和   row_number。Dense_rank   很像   rank，在这个函数中，“结”中的行排名是相等的。这两个函数惟一的不同之处在于对跟在结后面的值的处理方式，在   Dense_rank   函数中排名是按   1   递增的（而不是像   rank   函数那样按结中行的数量来递增）。因而，这里不会出现排名有间隔的现象（因此函数名中才用了“dense”）。虽然   Employee   11   的薪水在   rank   函数中获得的名次是第   4，但是   denserank   函数返回的值是   3。  
   
  最后一列给出   row_number   函数的输出。Row_number   也执行一次排列，但是当碰到有结的情况时，结中的行要进行任意的（也就是说，不是确定的）排序。这在对有重复值的数据进行分析时很有用。row_number   函数一个有趣的方面是它是惟一不要求提供排序方式的排列函数。如果在没有指定排序方式的情况下调用   row_number   函数，则所有的行都将被当作结中的行来对待，因而这些行是任意排序的。这对于在输出中给行排序来说很有用。  
标量-聚集（scalar-aggregate）   函数。  
   
  这些函数像标量函数，因为它们也是在每一行返回单个的值，但是它们也像聚集函数，因为它们要对一个集合中多个行中的值执行计算，以计算出结果。下面的标量-聚集函数执行的是与   sum   聚集函数一样的计算，但是这个函数返回的是没有合并行的结果：  
        select   dept,   salary,      
                  sum(salary)   over   (partition   by   dept)   as   deptsum,    
                  avg(salary)   over   (partition   by   dept)   as   avgsal,    
                  count(*)   over   (partition   by   dept)   as   deptcount,    
                  max(salary)   over   (partition   by   dept)   as   maxsal      
      from   emptab;    
    DEPT     SALARY     DEPTSUM     AVGSAL     DEPTCOUNT   MAXSAL      
    -----   -------   --------   -------   ---------   --------    
            1       50000   383000       63833                   6         78000    
            1       75000   383000       63833                   6         78000    
            1       52000   383000       63833                   6         78000    
            1       78000   383000       63833                   6         78000    
            1       75000   383000       63833                   6         78000    
            1       53000   383000       63833                   6         78000    
            2               -     51000       51000                   2         51000    
            2       51000     51000       51000                   2         51000    
            3       79000   209000       69666                   3         79000    
            3       55000   209000       69666                   3         79000    
            3       75000   209000       69666                   3         79000    
            -               -     84000       84000                   2         84000    
            -       84000     84000       84000                   2         84000  
     
  展示   SUM   报告函数的例子      
   
  注意，该查询没有包含   GROUP   BY   子句。相反，该查询使用了   OVER   子句来对数据分区，以便   sum   函数对同一部门中的行执行计算，并在每一个部门内的每一行中返回该部门所有薪水的总和。按惯例，为了在每一行中包括那样的聚集结果，我们需要使用一个联合，但是现在   OLAP   函数为此提供了更简易的模式。我们推荐使用这种类型的函数作为报告   函数，因为这种函数是对集合计算总和，并在每一行中都报告一次结果的。我曾经在前面和后面的例子中使用了   SUM，但是大部分聚集函数（例如   AVG、MIN、MAX、STDEV，等等）都使用   OVER   子句。在   DEPTSUM   列右边的其他列显示了平均薪水、部门中雇员的人数以及部门中的最高薪水。惟一不支持作为标量-聚集函数的聚集函数是线性回归函数。  
   
  这些报告函数一个强大的用处就是计算比率和百分比。要计算某个雇员的薪水占整个部门薪水总和的百分比，只需简单地用报告的薪水总和去除该雇员的薪水。  
   
  select   empnum,   dept,   salary,      
                    sum(salary)   over   (partition   by   dept)   as   deptsum,    
                  salary/sum(salary)   over(partition   by   dept)as   percentage    
      from   emptab;      
  EMPNUM   DEPT     SALARY       DEPTSUM         PERCENTAGE    
  ------   -----   --------   -----------   ----------    
            1           1         50000             383000           0.1305    
            2           1         75000             383000           0.1958    
            5           1         52000             383000           0.1357    
            6           1         78000             383000           0.2036    
            7           1         75000             383000           0.1958    
          11           1         53000             383000           0.1383    
            4           2                 -               51000      
            9           2         51000               51000           1.0000    
            8           3         79000             209000           0.3779    
          10           3         55000             209000           0.2631    
          12           3         75000             209000           0.3588    
            0           -                 -               84000      
            3           -         84000               84000           1.0000  
     
  百分比的例子      
   
  如果我们在要进行聚集的集合中引入一个排序方式，会出现什么情况呢？答案是，我们不处理一个报告（reporting）   函数，而是处理一个累加（cumulative）函数。累加函数是一种标量-聚集函数，它对当前行以及集合中当前行之前（相对排序方式而言）的所有行进行操作。让我们为这个例子使用一个不同的表。假设我们有一个这样的表，它记有当前历年的每月销售业绩。那么，我们如何计算每个月的年至今日（year-to-date）   销售数字呢？这里，我们要计算每月销售的累加和。我们可以这样做：  
   
   
        select   date,   sales,      
                  sum(sales)   over   (order   by   date)   as   cume_sum,    
                  count(*)   over   (order   by   date)   as   setcount    
      from   sales    
      where   year(date)   =   2000;    
    DATE               SALES                 CUME_SUM           SETCOUNT      
    ----------   ------------   ------------   ---------    
    01/01/2000         968871.12         968871.12                   1    
    02/01/2000           80050.05       1048921.17                   2    
    03/01/2000         757866.14       1806787.31                   3    
    04/01/2000           58748.13       1865535.44                   4    
    05/01/2000           40711.69       1906247.13                   5    
    06/01/2000         241187.78       2147434.91                   6    
    07/01/2000         954924.16       3102359.07                   7    
    08/01/2000         502822.96       3605182.03                   8    
    09/01/2000           97201.45       3702383.48                   9    
    10/01/2000         853999.45       4556382.93                 10    
    11/01/2000         358775.59       4915158.52                 11    
    12/01/2000         437513.35       5352671.87                 12  
     
  计算累加和的例子      
   
  让我们看一下结果。对于第一行，累加和就等于这一行的销售量。对于第二行，累加和等于一月份和二月份销售量的和（968871.12   +   80050.05   =   1048921.17）。类似地，第三行的结果是一月份、二月份和三月份销售量的和。在   CUME_SUM   列右边的列执行一个累加计数，给出在集合中行的数量。例如，第一行只有一行被求和（也就是该行本身），第二行有两行被求和（该行本身以及前一行），依此类推。上面的图给出了销售数字以及在前面的查询中计算出的累加和的图形化表示。  
   
  如果我们有多年的数据，并且想计算每一年内   到当月的累加和，那么我们也可以像下面这样使用   PARTITION   BY   子句：    
        select   date,   sales,      
                  sum(sales)   over   (partition   by   year(date)    
                                                  order   by   month(date))   as   cume_sum    
    from   sales    
    where   year(date)   >=   2000;    
    DATE               SALES                 CUME_SUM      
    ----------   ------------   -----------    
    01/01/2000         968871.12       968871.12    
    02/01/2000           80050.05     1048921.17    
    03/01/2000         757866.14     1806787.31    
    04/01/2000           58748.13     1865535.44    
    05/01/2000           40711.69     1906247.13    
    06/01/2000         241187.78     2147434.91    
    07/01/2000         954924.16     3102359.07    
    08/01/2000         502822.96     3605182.03    
    09/01/2000           97201.45     3702383.48    
    10/01/2000         853999.45     4556382.93    
    11/01/2000         358775.59     4915158.52    
    12/01/2000         437513.35     5352671.87      
   
   
   
  --------------------------------------------------------------------------------  
    01/01/2001         476851.71       476851.71    
    02/01/2001         593768.12     1070619.83    
    03/01/2001         818597.97     1889217.80    
    ...  
     
  使用   PARTITION   BY   子句计算累加和      
   
  现在，请注意   2001年1月那一行是如何重置的。这是因为日期按年划分了分区，而在   2001年内   没有在一月份之前的行，因此   cume_sum   就等于一月份的销售量。这个例子还演示了另一件有趣的事情，那就是   OVER   子句使用的参数可以是表达式，而不仅仅是列值。在更复杂的例子中，甚至可能会将其他的聚集函数嵌入到标量-聚集函数调用中。这很有用，因为在执行分析之前先执行某种类型的聚集（例如，将销售量聚集到月的层次上）是十分常见的。这就引发了下面的问题：何时处理标量-聚集函数？答案是在处理选择清单中剩下的部分时处理这些函数。通常，一个查询的处理顺序是这样的：  
   
   
  From   子句    
  Where   子句    
  Group   By   子句    
  Having   子句    
  选择清单    
   
  您可以看到，选择清单是在查询的所有其他部分处理完之后才被处理的。这意味着如果您有谓语（在   WHERE   或   HAVING   子句中），或者您有任何作为   GROUP   BY   子句结果的聚集，那么在处理标量-聚集函数之前首先要应用这些东西。例如，让我们看下面的查询：  
        select   to_char(date_fid,'yyyy')   as   year,   sum(sales)   as   sum,      
                  sum(sum(sales))   over   (order   by     to_char(date_fid,'yyyy'))   as   cume_sum    
    from   sales    
    where     to_char(date_fid,'yyyy')>=   '1995'  
    group   by     to_char(date_fid,'yyyy');  
    YEAR                 SUM                       CUME_SUM      
    -----------   -------------   ------------    
                  1995         7731162.39       7731162.39    
                  1996         4127017.98     11858180.37    
                  1997         7211584.76     19069765.13    
                  1998         4149296.50     23219061.63    
                  1999         6278023.54     29497085.17    
                  2000         5352671.87     34849757.04    
                  2001         5736777.81     40586534.85