一、分析函数(OVER)

1.1、DM分析函数简介

在日常的生产环境中，我们接触得比较多的是OLTP系统(即Online Transaction Process)，这些系统的特点是具备实时要求，或者至少说对响应的时间多长有一定的要求；其次这些系统的业务逻辑一般比较复杂，可能需要经过多次的运算。比如我们经常接触到的电子商城。在这些系统之外，还有一种称之为OLAP的系统(即Online Aanalyse Process)，这些系统一般用于系统决策使用。通常和数据仓库、数据分析、数据挖掘等概念联系在一起。这些系统的特点是数据量大，对实时响应的要求不高或者根本不关注这方面的要求，以查询、统计操作为主。
我们来看看下面的几个典型例子：
（1）查找上一年度各个销售区域排名前10的员工。
（2）按区域查找上一年度订单总额占区域订单总额20%以上的客户。
（3）查找上一年度销售最差的部门所在的区域。
（4）查找上一年度销售最好和最差的产品。
我们看看上面的几个例子就可以感觉到这几个查询和我们日常遇到的查询有些不同，具体有：
（1）需要对同样的数据进行不同级别的聚合操作。
（2）需要在表内将多条数据和同一条数据进行多次的比较。
（3）需要在排序完的结果集上进行额外的过滤操作。

1.2、DM分析函数简单实例

下面我们通过一个实际的例子：按区域查找上一年度订单总额占区域订单总额20%以上的客户，来看看分析函数的应用。

【1】测试环境：
创建表：orders_tmp
create table orders_tmp(
cust_nbr DECIMAL(5),
region_id DECIMAL(5),
salesperson_id DECIMAL(5),
year DECIMAL(4),
month DECIMAL(2),
tot_orders DECIMAL(7),
tot_sales DECIMAL(11,2)
);

【2】测试数据：
(略）

【3】测试语句：
select
o.cust_nbr customer,
o.region_id region,
sum(o.tot_sales) cust_sales,
sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
from orders_tmp o
where o.year = 2001
group by o.region_id, o.cust_nbr;

1.3、分析函数OVER解析

group by的意图很明显：将数据按区域ID，客户进行分组，那么Over这一部分有什么用呢？假如我们只需要统计每个区域每个客户的订单总额，那么我们只需要group by o.region_id,o.cust_nbr就够了。但我们还想在每一行显示该客户所在区域的订单总额，这一点和前面的不同：需要在前面分组的基础上按区域累加。很显然group by和sum是无法做到这一点的(因为聚集操作的级别不一样，前者是对一个客户，后者是对一批客户)。

这就是over函数的作用了！它的作用是告诉SQL引擎：按区域对数据进行分区，然后累积每个区域的订单总额(sum(sum(o.tot_sales)))。

现在我们已经知道2001年度每个客户及其对应区域的订单总额，那么下面就是筛选那些个人订单总额占到区域订单总额20%以上的大客户了
select *
from (select o.cust_nbr customer,
o.region_id region,
sum(o.tot_sales) cust_sales,
sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
from orders_tmp o
where o.year = 2001
group by o.region_id, o.cust_nbr) all_sales
where all_sales.cust_sales > all_sales.region_sales * 0.2;

现在我们已经知道这些大客户是谁了，不过我们还想要知道每个大客户所占的订单金额比例，看看下面的SQL语句，只需要一个简单的Round函数就搞定了。
select
all_sales.*,
100 * round(cust_sales / region_sales, 2) || '%' as "percent"
from (select o.cust_nbr customer,
o.region_id region,
sum(o.tot_sales) cust_sales,
sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
from orders_tmp o
where o.year = 2001
group by o.region_id, o.cust_nbr) all_sales
where all_sales.cust_sales > all_sales.region_sales * 0.2;

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

DM数据库分析函数的语法：
function_name(arg1,arg2,...) over (<partition-clause> <order-by-clause ><windowing clause>)
说明：

1. partition-clause 数据记录集分组
2. order-by-clause 数据记录集排序
3. windowing clause 功能非常强大、比较复杂，定义分析函数在操作行的集合。
   有三种开窗方式: range、row、specifying。

（1）Partition by: 按相应的值(manager_id)进行分组统计
SELECT
manager_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
AVG(salary) OVER (PARTITION BY manager_id) avg_salary
FROM employees;

（2）Order by：按相应的值(hire_date)进行排序并累计统计
SELECT
manager_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
AVG(salary) OVER (ORDER BY hire_date)
FROM employees;

（3）Partition by Order by：首先按相应的值manager_id分组，再按order by的值排序，然后进行累计统计
SELECT
manager_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date)
FROM employees;

（4）Partition by Order by：首先按相应的值(manager_id,hire_date)排序，并按order by的值(hire_date)进行累计统计
--该平均值由当前员工和与之具有相同经理的前一个和后两个 四者的平均数得来
SELECT
manager_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
FROM employees;

（5）Partition by Order by：首先按相应的值(manager_id,hire_date)排序，并按order by的值(hire_date)进行累计统计
该平均值由当前员工和与之具有相同经理,并且雇用时间在该员工时间之前的50天以内和在该员工之后的150天之内员工的薪水的平均值
--range为取值范围，估计只有数字和日期能够进行取值了
SELECT
manager_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING)
FROM employees;

（6）Partition by Order by：首先按相应的值(manager_id,hire_date)排序，并按order by的值(hire_date)进行累计统计
--该平均值由当前员工和与之具有相同经理的平均值
--每行对应的数据窗口是从第一行到最后一行
SELECT
manager_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date) avg_salary_part_order,
AVG(salary) OVER (PARTITION BY manager_id ) avg_salary_order,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) avg_salary_unbound1, --等同于仅partition时候的值
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) avg_salary_unbound2--等同于上面
FROM employees;

二、DM分析函数三 SUM,AVG,MIN,MAX,COUNT

SUM
功能描述：该函数计算组中表达式的累积和。
SAMPLE：下例计算同一经理下员工的薪水累积值

MIN
功能描述：在一个组中的数据窗口中查找表达式的最小值。
SAMPLE：下面例子中dept_min返回当前行所在部门的最小薪水值

MAX
功能描述：在一个组中的数据窗口中查找表达式的最大值。
SAMPLE：下面例子中dept_max返回当前行所在部门的最大薪水值

AVG
功能描述：用于计算一个组和数据窗口内表达式的平均值。
SAMPLE：下面的例子中列c_mavg计算员工表中每个员工的平均薪水报告

SELECT
department_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
MIN(salary) OVER (PARTITION BY department_id order by hire_date) AS dept_min,
MAX(salary) OVER (PARTITION BY department_id order by hire_date) AS dept_max,
AVG(salary) OVER (PARTITION BY department_id order by hire_date) AS dept_avg,
SUM(salary) OVER (PARTITION BY department_id order by hire_date) AS dept_sum/,
COUNT() OVER (ORDER BY salary) AS count_by_salary,
COUNT() OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS count_by_salary_range/
FROM employees；

COUNT
功能描述：对一组内发生的事情进行累积计数，如果指定*或一些非空常数，count将对所有行计数，如果指定一个表达式，count返回表达式非空赋值的计数，当有相同值出现时，这些相等的值都会被纳入被计算的值；可以使用DISTINCT来记录去掉一组中完全相同的数据后出现的行数。
SAMPLE：下面例子中计算每个员工在按薪水排序中当前行附近薪水在[n-50,n+150]之间的行数，n表示当前行的薪水
例如，Philtanker的薪水2200，排在他之前的行中薪水大于等于2200-50的有1行，排在他之后的行中薪水小于等于2200＋150的行没有，所以count计数值cnt3为2（包括自己当前行）；cnt2值相当于小于等于当前行的SALARY值的所有行数

SELECT
department_id,
first_name||' '||last_name employee_name,
salary,
COUNT() OVER (ORDER BY salary) AS count_by_salary,
COUNT() OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS count_by_salary_range
FROM employees
WHERE department_id in (10,20,30);

三、FIRST_VALUE，LAST_VALUE，LAG，LEAD

FIRST_VALUE
功能描述：返回组中数据窗口的第一个值。
SAMPLE：下面例子计算按部门分区按薪水排序的数据窗口的第一个值对应的名字，如果薪水的第一个值有多个，则从多个对应的名字中取缺省排序的第一个名字

LAST_VALUE
功能描述：返回组中数据窗口的最后一个值。
SAMPLE：下面例子计算按部门分区按薪水排序的数据窗口的最后一个值对应的名字，如果薪水的最后一个值有多个，则从多个对应的名字中取缺省排序的最后一个名字
SELECT
department_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
FIRST_VALUE(first_name||' '||last_name) OVER (PARTITION BY department_id ORDER BY salary ASC ) AS lowest_sal,
LAST_VALUE(first_name||' '||last_name) OVER(PARTITION BY department_id ORDER BY salary) AS highest_sal
FROM employees；

LAG
功能描述：可以访问结果集中的其它行而不用进行自连接。
它允许去处理游标，就好像游标是一个数组一样。在给定组中可参考当前行之前的行，这样就可以从组中与当前行一起选择以前的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行），其相反的函数是LEAD
SAMPLE：下面的例子中列prev_sal返回按hire_date排序的前1行的salary值

LEAD
功能描述：LEAD与LAG相反，LEAD可以访问组中当前行之后的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行）
SAMPLE：下面的例子中列prev_sal返回按hire_date排序的后1行的salary值
SELECT
first_name||' '||last_name employee_name,
hire_date,
salary,
LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal,
LEAD(salary, 1,0) OVER (ORDER BY hire_date) AS "next_sal"
FROM employees；

四、Rank, Dense_rank, row_number

4.1、使用rownum为记录排名

在前面，我们认识了分析函数的基本应用，现在我们再来考虑下面几个问题：
（1）对所有客户按订单总额进行排名
（2）按区域和客户订单总额进行排名
（3）找出订单总额排名前13位的客户
（4）找出订单总额最高、最低的客户
（5）找出订单总额排名前25%的客户

【1】测试环境：
创建表：user_order
create table user_order(
region_id DECIMAL(2),
customer_id DECIMAL(2),
customer_sales NUMBER
);

【2】测试数据：
(略）

注意这里测试数据中有3条记录的订单总额是一样的。假如我们现在需要筛选排名前12位的客户，如果使用rownum后果是：
select rownum, t.*
from (
select *
from user_order
order by customer_sales desc) t
where rownum <= 12
order by customer_sales desc;

很明显假如只是简单地按rownum进行排序的话，我们漏掉了另外两条记录(参考上面的结果)。

4.2、使用分析函数来为记录排名

针对上面的情况，DM从开始就提供了3个分析函数：rand，dense_rank，row_number来解决诸如此类的问题，下面我们来看看这3个分析函数的作用以及彼此之间的区别：

Rank，Dense_rank，Row_number函数为每条记录产生一个从1开始至N的自然数，N的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。

（1）ROW_NUMBER：
Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
（2）DENSE_RANK：
Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。
（3）RANK：
Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

这样的介绍有点难懂，我们还是通过实例来说明吧，下面的例子演示了3个不同函数在遇到相同数据时不同排名策略：
select
region_id,
customer_id,
sum(customer_sales) total,
rank() over(order by sum(customer_sales) desc) rank,
dense_rank() over(order by sum(customer_sales) desc) dense_rank,
row_number() over(order by sum(customer_sales) desc) row_number
from user_order
group by region_id, customer_id;

这里生动的演示了3种不同的排名策略：
（1）对于第一条相同的记录，3种函数的排名都是一样的：12
（2）当出现第二条相同的记录时，Rank和Dense_rank依然给出同样的排名12；而row_number则顺延递增为13，依次类推至第三条相同的记录
（3）当排名进行到下一条不同的记录时，可以看到Rank函数在12和15之间空出了13,14的排名，因为这2个排名实际上已经被第二、三条相同的记录占了。而Dense_rank则顺序递增。row_number函数也是顺序递增

比较上面3种不同的策略，我们在选择的时候就要根据客户的需求来定夺了：
（1）假如客户就只需要指定数目的记录，那么采用row_number是最简单的，但有漏掉的记录的危险
（2）假如客户需要所有达到排名水平的记录，那么采用rank或dense_rank是不错的选择。至于选择哪一种则看客户的需要，选择dense_rank或得到最大的记录

4.3、使用分析函数为记录进行分组排名

上面的排名是按订单总额来进行排列的，现在跟进一步：假如是为各个地区的订单总额进行排名呢？这意味着又多了一次分组操作：对记录按地区分组然后进行排名。幸亏DM也提供了这样的支持，我们所要做的仅仅是在over函数中order by的前面增加一个分组子句：partition by region_id。
select
region_id,
customer_id,
sum(customer_sales) total,
rank() over(partition by region_id order by sum(customer_sales) desc) rank,
dense_rank() over(partition by region_id order by sum(customer_sales) desc) dense_rank,
row_number() over(partition by region_id order by sum(customer_sales) desc) row_number
from user_order
group by region_id, customer_id;

现在我们看到的排名将是基于各个地区的，而非所有区域的了！Partition by子句在排列函数中的作用是将一个结果集划分成几个部分，这样排列函数就能够应用于这各个子集。

五、查找前N行、NTile

5.1、带空值的排列nulls first/nulls last

我们已经知道了如何为一批记录进行全排列、分组排列。假如被排列的数据中含有空值呢？
select region_id, customer_id,
sum(customer_sales) cust_sales,
sum(sum(customer_sales)) over(partition by region_id) ran_total,
rank() over(partition by region_id
order by sum(customer_sales) desc) rank
from user_order
group by region_id, customer_id;

我们看到这里有一条记录的CUST_TOTAL字段值为NULL，但居然排在第一名了！显然这不符合情理。所以我们重新调整完善一下我们的排名策略，看看下面的语句：NULLS LAST/FIRST告诉DM让空值排名最后或最前。
select region_id, customer_id,
sum(customer_sales) cust_total,
sum(sum(customer_sales)) over(partition by region_id) reg_total,
rank() over(partition by region_id
order by sum(customer_sales) desc NULLS LAST) rank
from user_order
group by region_id, customer_id;

5.2、查找前N排名

在日常的工作生产中，我们经常碰到这样的查询：找出排名前5位的订单客户、找出排名前10位的销售人员等等。现在这个对我们来说已经是很简单的问题了。下面我们用一个实际的例子来演示：

案例1：找出所有订单总额排名前3的大客户：
select *
from (
Select
region_id,
customer_id,
sum(customer_sales) cust_total,
rank() over(order by sum(customer_sales) desc NULLS LAST) rank
from user_order
group by region_id, customer_id
)
where rank <= 3;

案例2：找出每个区域订单总额排名前3的大客户:
select *
from (select region_id,
customer_id,
sum(customer_sales) cust_total,
sum(sum(customer_sales)) over(partition by region_id) reg_total,
rank() over(partition by region_id
order by sum(customer_sales) desc NULLS LAST) rank
from user_order
group by region_id, customer_id)
where rank <= 3;

5.3、按层次查询：（ntile）

现在我们已经见识了如何通过DM的分析函数来获取前N行，第一个，最后一个记录。有时我们会收到类似下面这样的需求：找出订单总额排名前1/5的客户。
我们马上会想到第二点中提到的方法，可是rank函数只为我们做好了排名，并不知道每个排名在总排名中的相对位置，这时候就引入了另外一个分析函数NTile，下面我们就以上面的需求为例来讲解一下：
select region_id,
customer_id,ntile(5) over(order by sum(customer_sales) desc) til
from user_order
group by region_id, customer_id;

Ntile函数为各个记录在记录集中的排名计算比例，我们看到所有的记录被分成5个等级，那么假如我们只需要前1/5的记录则只需要截取TILE的值为1的记录就可以了。假如我们需要排名前25%的记录(也就是1/4)那么我们只需要设置ntile(4)就可以了。