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 ([Partition by ] [Order by ])

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

  • row_number: 返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。
  • dense_rank: 返回一个唯一的值,当碰到相同数据时,此时所有相同数据的排名都是一样的。first、last :从DENSE_RANK返回的集合中取出排在最后面的一个值的行
  • rank: 返回一个唯一的值,当碰到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

①ROW_NUMBER:12345

②DENSE_RANK:12223

③RANK:12225

-- ①对所有客户按订单总额进行排名 
-- ②按区域和客户订单总额进行排名 
-- ③找出订单总额排名前13位的客户 
-- ④找出订单总额最高、最低的客户 
-- ⑤找出订单总额排名前25%的客户

-- 筛选排名前12位的客户, table : user_order 
-- 1.对所有客户按订单总额进行排名, 使用rownum , rownum = 13,14 的数据跟 12 的数据一样, 但是被漏掉了

select rownum, tmptb.* from 
 (select * from user_order order by CUSTOMER_sales desc) tmptb
where rownum <= 12;

-- 2.按区域和客户订单总额进行排名 Rank, Dense_rank, row_number

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;

三、分析函数:Top/Bottom N、First/Last、NTile

-- ①对所有客户按订单总额进行排名 
-- ②按区域和客户订单总额进行排名 
-- ③找出订单总额排名前13位的客户 
-- ④找出订单总额最高、最低的客户 
-- ⑤找出订单总额排名前25%的客户

-- 此处 null 被排到第一位 , 可以加 nulls last 把null的数据放到最后

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 /* nulls last */) rank
from user_order
group by region_id, customer_id;

-- 找出所有订单总额排名前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;

-- 找出每个区域订单总额排名前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;

四、汇总

  • 汇总
  • 滚动汇总
  • 分区滚动汇总
  • 当前记录和后一条记录
  • 分区汇总
Sum() Over ([Partition by ] [Order by ])
Sum() Over ([Partition by ] [Order by ]     Rows Between  Preceding And  Following)   
Sum() Over ([Partition by ] [Order by ]     Rows Between  Preceding And Current Row)
Sum() Over ([Partition by ] [Order by ]     Range Between Interval '' 'Day' Preceding    And Interval '' 'Day' Following )

五、Min()/Max():最大值/最小值

形式:

Min()/Max() Keep (Dense_rank First/Last [Partition by ] [Order by ])
  • -- min keep first last 找出订单总额最高、最低的客户
  • -- Min只能用于 dense_rank
  • -- min 函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录, 去掉会出错
  • -- keep的作用。告诉Oracle只保留符合keep条件的记录。
select 
   min(customer_id) keep (dense_rank first order by sum(customer_sales) desc) first,
   min(customer_id) keep (dense_rank last order by sum(customer_sales) desc) last
from user_order
group by customer_id;

-- 出订单总额排名前1/5的客户 ntile 
-- 1.将数据分成5块

select region_id,customer_id,
 sum(customer_sales) sales,
 ntile(5) over(order by sum(customer_sales) desc nulls last) tile
from user_order
group by region_id, customer_id;

-- 2.提取 tile=1 的数据

select * from 
(select region_id,customer_id,
   sum(customer_sales) sales,
   ntile(5) over(order by sum(customer_sales) desc nulls last) tile
 from user_order
 group by region_id, customer_id)
where tile = 1;

-- cust_nbr,month 为主键, 去重,只留下month最大的记录 
-- 查找 cust_nbr 相同, month 最大的记录

select cust_nbr,
 max(month) keep(dense_rank first order by month desc) max_month
from orders_tmp 
group by cust_nbr;

-- 去重, cust_nbr,month 为主键, cust_nbr 相同,只留下month最大的记录

delete from orders_tmp2 where (cust_nbr, month) not in 
 (select cust_nbr, max(month) keep(dense_rank first order by month desc) max_month
from orders_tmp2 tb 
group by cust_nbr)

五、first_value/last_value:首记录/末记录

形式:

First_value / Last_value(Sum() Over ([Patition by ] [Order by ] Rows Between Preceding And Following ))

六、lag()与lead():相邻记录

Lag(Sum(), 1) Over([Patition by ] [Order by ])

lag和lead函数可以在一次查询中取出同一字段的前n行的数据和后n行的值。这种操作可以使用对相同表的表连接来实现,不过使用lag和lead有更高的效率。

阅读剩余部分

相关阅读 >>

orcale中的to_number方法使用

oracle数据库自动备份脚本分享(超实用)

groupby用法详解

oracle常见错误代码的分析与解决(一)

抽取oracle数据到mysql数据库的实现过程

oracle如何建表?

部署oracle 12c企业版数据库( 安装及使用)

oracle的一些tips技巧

通过jdbc连接oracle数据库的十大技巧

oracle如何删除用户下的所有表?

更多相关阅读请进入《oracle》频道 >>


数据库系统概念 第6版
书籍

数据库系统概念 第6版

机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。



打赏

取消

感谢您的支持,我会继续努力的!

扫码支持
扫码打赏,您说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

分享从这里开始,精彩与您同在

评论

管理员已关闭评论功能...