oracle之PLSQL优化查询语句——多种函数配合over()函数运用

oracle之PLSQL优化查询语句——多种函数配合over()函数运用，总结下现在网上的一些列有关于over()函数的运用。over()也叫分析函数。
我个人在学习这个时赶脚网上的对这类函数的解释太过官方了，给一些基础不是特别好的理解带来些压力，我用一些大家比较通俗易懂的概念来讲解。
over分一下三种用法

over（order by clomun）
over（partition by clomun)
over（partition by clomun1 order by clomun）

--创建测试表
create table sale(sales_id varchar2(2),sales varchar2(10),dest varchar2(10),dept varchar2(10),revenue number);
--插入测试数据
insert into sale values('11','smith','hangzhou','市场',1000);
insert into sale values('12','smith','wenzhou','市场',2000);
insert into sale values('13','allen','wenzhou','渠道',3000);
insert into sale values('14','allen','wenzhou','渠道',4000);
insert into sale values('15','jekch','shanghai','渠道',2500);
insert into sale values('11','smith','hangzhou','市场',1000);
insert into sale values('12','smith','wenzhou','市场',2000);

1：over(order by clomun) 按照clmun 字段分类来做叠加操作，相同字段值相加求sum，不同字段从上到下递加求sum。当然clomun可以有多个ps :over(order by clomun1,clomun2)

select a.sales_id,
       a.sales,
       a.dest,
       a.dept,
       a.revenue,
       sum(a.revenue) over(order by a.dest) dest分类来递加销售总额   --当然这里也可以根据clomun 倒序（ps:over(order by a.dest desc)）
  from sale a

2：over(partition by clomun) 按照clomun分类求sum，当然clomun可以有多个ps :over(partition by clomun1,clomun2)

select a.sales_id,
       a.sales,
       a.dest,
       a.dept,
       a.revenue,
       sum(a.revenue) over(partition by a.dept ) 按照dept来sum销售额
  from sale a

3：over(partition by clomun1 order by clomun2) 先按clomun1分类求sum，然后通过clomun2分类进行叠加
–先按照sales来区分求sum，然后再通过dept叠加

select a.sales_id,
       a.sales,
       a.dest,
       a.dept,
       a.revenue,
       sum(a.revenue) over(partition by a.dept order by a.sales) sumrevence 
  from sale a

——————————————————————————————————–华丽的分割线————————————————————————————————————
扩展几种常用函数的与over函数搭配运用。
count()函数与over的搭配：如图先通过order by dest对表进行分类求和出现的次数，然后通过dept对分类求和的次数做叠加。

select a.sales_id,
       a.sales,
       a.dest,
       a.dept,
       a.revenue,
       count(a.sales) over(partition by a.dept order by a.dest) sum_count
  from sale a

ROW_NUMBER()函数与over的搭配：首先通过partition by dept 对数据进行分类，然后再通过order by dest 对数据进行排序。后面给大家介绍另外两种有意思的排名做法

select a.sales_id,
       a.sales,
       a.dest,
       a.dept,
       a.revenue,
       ROW_NUMBER() over(partition by a.dept order by a.dest) sum_count
  from sale a

rank()over()和dense_rank()over()函数搭配使用

rank()–值相同时，排名也相同，同时跳过相同的排名，也叫跳跃排名。这种排名相对公平。

select a.sales_id,
       a.sales,
       a.dest,
       a.dept,
       a.revenue,
       rank() over(partition by a.dept order by a.dest) ranking
  from sale a

dense_rank()over():这种跟上面的有点相似，不同的地方就是，不会跳跃排名。这种对选手比较好，可以出现多个第一名哈哈。

select a.sales_id,
       a.sales,
       a.dest,
       a.dept,
       a.revenue,
       dense_rank() over(partition by a.dept order by a.dest) ranking
  from sale a

大概就这么多了时间也差不多，好长时间没有写博客了，以后要坚持，也方便自己以后的查看。

oracle之PLSQL优化查询语句——多种函数配合over()函数运用

相关推荐