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分析函数的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持。