Oracle中的分析函数汇总(2)-木庄网络博客

-- 此处 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有更高的效率。

lag(arg1,arg2,arg3)

第一个参数是列名，

第二个参数是偏移的offset，

第三个参数是超出记录窗口时的默认值。

-- ①列出每月的订单总额以及全年的订单总额
-- ②列出每月的订单总额以及截至到当前月的订单总额
-- ③列出上个月、当月、下一月的订单总额以及全年的订单总额
-- ④列出每天的营业额及一周来的总营业额
-- ⑤列出每天的营业额及一周来每天的平均营业额

-- ①通过指定一批记录：例如从当前记录开始直至某个部分的最后一条记录结束
-- ②通过指定一个时间间隔：例如在交易日之前的前30天
-- ③通过指定一个范围值：例如所有占到当前交易量总额5%的记录

-- 列出每月的订单总额以及全年的订单总额
1.实现方法1

select month,
 sum(tot_sales) month_sales,
 sum(sum(tot_sales)) over (order by month rows between unbounded preceding and unbounded following) total_sales
from orders
group by month;

2.实现方法2

select month,
 sum(tot_sales) month_sales,
 sum(sum(tot_sales)) over(/*order by month*/) all_sales  -- 加上Order by month , 则数逐条记录递增
from orders group by month;

-- 列出每月的订单总额以及截至到当前月的订单总额
1.实现方法1

select month,
 sum(tot_sales) month_sales,
 sum(sum(tot_sales)) over(order by month rows between unbounded preceding and current row) current_total_sales
from orders 
group by month;

2.实现方法2

select month,
 sum(tot_sales) month_sales,
 sum(sum(tot_sales)) over(order by month) all_sales  -- 加上Order by month , 则是前面记录累加到当前记录
from orders 
group by month;

-- 有时可能是针对全年的数据求平均值，有时会是针对截至到当前的所有数据求平均值。很简单，只需要将：
-- sum(sum(tot_sales))换成avg(sum(tot_sales))即可。

-- 统计当天销售额和五天内的平均销售额 range between interval

select trunc(order_dt) day,
 sum(sale_price) daily_sales,
 avg(sum(sale_price)) over (order by trunc(order_dt) range between interval '2' day preceding and interval '2' day following) five_day_avg
from cust_order
where sale_price is not null and order_dt between to_date('01-jul-2001','dd-mon-yyyy') and to_date('31-jul-2001','dd-mon-yyyy')

-- 显示当前月、上一个月、后一个月的销售情况，以及每3个月的销售平均值

select month,
  first_value(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) prev_month,
  sum(tot_sales) monthly_sales,
  last_value(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) next_month,
  avg(sum(tot_sales)) over  (order by month rows between 1 preceding and 1 following) rolling_avg
from orders_tmp
where year = 2001 and region_id = 6
group by month order by month;

-- 显示当月的销售额和上个月的销售额
-- first_value(sum(tot_sales) over (order by month rows between 1 precedingand 0 following))
-- lag(sum(tot_sales),1)中的1表示以1月为间隔基准, 对应为lead

select  month,            
 sum(tot_sales) monthly_sales,
 lag(sum(tot_sales), 1) over (order by month) prev_month_sales
from orders_tmp
where year = 2001 and region_id = 6
group by month order by month;

七、rollup()、cube()和grouping()：排列组合分组

1)、group by rollup(a, b, c)：
首先会对(a、b、c)进行group by，然后再对(a、b)进行group by，其后再对(a)进行group by，最后对全表进行汇总操作。

2)、group by cube(a, b, c)：
则首先会对(a、b、c)进行group by，然后依次是(a、b)，(a、c)，(a)，(b、c)，(b)，(c)，最后对全表进行汇总操作。

八、ratio_to_report （）：计算每条记录在其对应记录集或其子集中所占的比例。

ratio_to_report(a) over(partition by b) ：求按照b分组后a的值在所属分组中总值的占比,a的值必须为数值或数值型字段。

Ratio_to_report() 括号中就是分子，over() 括号中就是分母分母缺省就是整个占比

eg:列出上一年度每个月的销售总额、年底销售额以及每个月的销售额占全年总销售额的比例：

select region_id, salesperson_id,
  sum(tot_sales) sp_sales,
  round(ratio_to_report(sum(tot_sales)) over (partition by region_id), 2) sp_ratio
from orders
where year = 2001
group by region_id, salesperson_id
order by region_id, salesperson_id;

到此这篇关于Oracle分析函数的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持。

标签：Oracle

返回前面的内容