Oracle 分析函数介绍

Oracle 分析函数

Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。

语法：
<analytic-function>(<argument>,<argument>,...)
over(
  <query-partition-clause>
  <order-by-clause>
  <windowing-clause>
)
其中：
1 over是关键字，用于标识分析函数。

2 <analytic-function>是指定的分析函数的名字。Oracle分析函数很多。
   
3 <argument>为参数，分析函数可以选取0-3个参数。

4 分区子句<query-partition-clause>的格式为：
  partition by<value_exp>[,value_expr]...
  关键字partition by子句根据由分区表达式的条件逻辑地将单个结果集分成N组。这里的"分区partition"和"组group"
都是同义词。

5 排序子句order-by-clause指定数据是如何存在分区内的。其格式为：
 order[siblings]by{expr|position|c_alias}[asc|desc][nulls first|nulls last]
其中：
(1)asc|desc:指定了排列顺序。
(2)nulls first|nulls last:指定了包含空值的返回行应出现在有序序列中的第一个或最后一个位置。

6窗口子句windowing-clause
 给出一个固定的或变化的数据窗口方法，分析函数将对这些数据进行操作。在一组基于任意变化或固定的窗口中，
可用该子句让分析函数计算出它的值。
格式：
 {rows|range}
 {between
  {unbounded preceding|current row |<value_expr>{preceding|following}
 }and
 {unbounded preceding|current row |<value_expr>{preceding|following}
 }|{unbounded preceding|current row |<value_expr>{preceding|following
 }}
(1) rows|range:此关键字定义了一个window。
(2) between...and...:为窗品指一个起点和终点。
(3) unbounded preceding：指明窗口是从分区(partition)的第一行开始。
(4) current row:指明窗口是从当前行开始。

<analytic-function> OVER (PARTITION BY COL1 ORDER BY COL2)

表示根据COL1分组，在分组内部根据 COL2排序

分析函数介绍:

    a.  自动汇总函数rollup,cube,
    b.  rank 函数, rank,dense_rank,row_number
        ① ROW_NUMBER：
             Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
        ② DENSE_RANK：
             Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。
        ③ RANK：
             Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录  的排名之间空出排名。
    c.  lag,lead函数
    d.  sum,avg,的移动增加,移动平均数
    e.  ratio_to_report报表处理函数
    f.  first,last取基数的分析函数
        NULLS LAST/FIRST 例如:   order by sum(customer_sales) desc NULLS LAST
    g. Ntil函数为各个记录在记录集中的排名计算比例，我们看到所有的记录被分成5个等级，那么假如我们只需要前1/5的记录则只需要截取TILE的值为1的记录就可以了。假如我们 需要排名前25%的记录(也就是1/4)那么我们只需要设置ntile(4)就可以了。