Oracle分析函数用法详解

一、概述

OLAP的系统(即Online Aanalyse Process)一般用于系统决策使用。通常和数据仓库、数据分析、数据挖掘等概念联系在一起。这些系统的特点是数据量大，对实时响应的要求不高或者根本不关注这方面的要求，以查询、统计操作为主。

我们来看看下面的几个典型例子：
①查找上一年度各个销售区域排名前10的员工
②按区域查找上一年度订单总额占区域订单总额20%以上的客户
③查找上一年度销售最差的部门所在的区域
④查找上一年度销售最好和最差的产品

我们看看上面的几个例子就可以感觉到这几个查询和我们日常遇到的查询有些不同，具体有：

需要对同样的数据进行不同级别的聚合操作
需要在表内将多条数据和同一条数据进行多次的比较
需要在排序完的结果集上进行额外的过滤操作
1、分析函数和聚合函数的不同之处是什么？
普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。

2、分析函数的形式
分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，他们的使用形式如下：

function_name(<argument>,<argument>...) over(<partition_Clause><order by_Clause><windowing_Clause>);

function_name()：函数名称
argument：参数
over( )：开窗函数
partition_Clause：分区子句，数据记录集分组，group by…
order by_Clause：排序子句，数据记录集排序，order by…
windowing_Clause：开窗子句，定义分析函数在操作行的集合，三种开窗方式：rows、range、Specifying
注：使用开窗子句时一定要有排序子句！！！

3、OVER解析
OVER解析作用是告诉SQL引擎：按区域对数据进行分区，然后累积每个区域每个客户的订单总额(sum(sum(o.tot_sales)))。

①Over函数指明在那些字段上做分析，其内跟Partition by表示对数据进行分组。注意Partition by可以有多个字段。
②Over函数可以和其它聚集函数、分析函数搭配，起到不同的作用。例如这里的SUM，还有诸如Rank，Dense_rank等。

4、Oracle分析函数简单实例：
– 按区域查找上一年度订单总额占区域订单总额20%以上的客户 table : orders_tmp

select * from orders_tmp;

select cust_nbr, region_id, cust_sales, region_sales,  -- 此处可以用tmptb.* , 但不能用 *
100 * round(cust_sales / region_sales, 2) || '%' Percent from
 (select cust_nbr, region_id,
    sum(TOT_SALES) cust_sales,
    sum(sum(tot_sales)) over(partition by REGION_ID) as region_sales
  from orders_tmp where o.year = 2001 group by CUST_NBR, REGION_ID order by REGION_ID) tmptb
 where cust_sales > region_sales * 0.2;

二、分析函数：Rank, Dense_rank, row_number，Ntile() 排列

形式：

Rank() Over ([Partition by ] [Order by ] [Nulls First/Last])
Dense_rank() Over ([Patition by ] [Order by ] [Nulls First/Last])
Row_number() Over ([Partitionby ] [Order by ] [Nulls First/Last])
Ntile() Over <