Oracle分组函数小结
Oracle分组函数小结
1.OVER (PARTITION BY ..)
例：select a,b,c, sum(c) OVER (PARTITION BY b) sum_c
对 b列值相同的行进行c值的累计.
-- 检索指定的零售户类型个数所占的百分比
select t2.*,round(t2.cust_count/t2.cust_count_all,3)*100 as count_percent from(
select t.*,
sum(cust_count) over (partition by t.regie_org_code,t.analysis_month)
as cust_count_all
from(
select r.regie_org_code,r.analysis_month,r.cust_type,
count(*) as cust_count
from rm_monitor_results r
group by r.regie_org_code,r.analysis_month,r.cust_type) t)t2
2.按照区间进行分组
select
sum(case when abs(r.sample_z_value)>=3 then 1 else 0 end) as p3,
sum(case when abs(r.sample_z_value)>=2 then 1 else 0 end) as p2,
sum(case when abs(r.sample_z_value)<2 then 1 else 0 end) as p1,
sum(1) as p0
from Rm_Monitor_Statistics_Analysis r
where r.regie_org_code='13500401'
3.ROLLUP
ROLLUP是对group by的扩展，因此，它只能出现在group by子句中，依赖于分组的列，对每个分组会生成汇总数据, rollup和group by联合一起使用，达到了按group by列顺序分组，并且实现小计和合计的功能。rollup分组还是有序的，先全部分组，然后对每个分组小计，最后合计，使处理结果更清楚.
rollup中列的顺序不同，则统计的结果不同。因为它是按列从右递减分组的。
例：
--对不同专卖局的不同月份进行分组小计和合计
select r.regie_org_code,r.year_month,count(*)
from rm_monitor_statistics_analysis r
group by rollup (r.regie_org_code,r.year_month)
如果在实际查询中，有的小计或合计我们不需要，那么就要使用局部rollup,局部rollup就是将不需要小计(合计)的列放在group by中，而不是放在rollup中。
例：
--对不同专卖局不同月份进行分组统计并只对局进行合计
select r.regie_org_code,r.year_month,count(*)
from rm_monitor_statistics_analysis r
group by rollup (r.regie_org_code),r.year_month
4.CUBE
CUBE(交叉列表)也是对group by运算的一种扩展，它比rollup扩展更加精细，组合类型更多，rollup是按组合的列从右到左递减分组计算，而CUBE则是对所有可能的组合情况进行分组，这样分组的情况更多，覆盖所有的可能分组，并计算所有可能的分组的小计。
对于CUBE来说，列的名字只要一样，那么顺序无所谓，结果都是一样的，因为cube是各种可能情况的组合,只不过统计的结果顺序不同而已。但是对于rollup来说，列的顺序不同，则结果不同。
例：
--对不同专卖局和不同月份进行两两交叉统计
select r.regie_org_code,r.year_month,count(*)
from rm_monitor_statistics_analysis r
group by cube (r.regie_org_code,r.year_month)
部分CUBE和部分ROLLUP类似，把不想要的小计和合计的列放到group by中，不放到cube中就可以了。
如果cube中只有一个列，那么和rollup的结果一致
例：
select r.regie_org_code,r.year_month,count(*)
from rm_monitor_statistics_analysis r
group by cube (r.regie_org_code),r.year_month
5.GROUPING SETS
对group by的另一个扩展，专门对分组列分别进行小计计算，不包括合计。使用方式和rollup和cube一样，都是放在group by中。
--对不同专卖局，不同月份每个列分别进行小计计算，改变统计列的顺序，不影响统计结果。
select r.regie_org_code,r.year_month,count(*)
from rm_monitor_statistics_analysis r
group by grouping sets (r.regie_org_code,r.year_month)
6.删除重复记录
例：
delete from emp e where e.rowid > (select min(y.rowid)
from emp y
where y.empno = e.empno )
7.其它性能优化
(1)：SELECT子句中避免使用 ‘ * ‘
(2)：使用exists语句代替in语句；
(3): 使用not exists代替not in
(4)：使用truncate代替delete from 表语句
(5)：减少访问数据库的次数
(6)：使用表的别名(Alias)
(7)：尽快使用COMMIT
(8)：数据库冗余字段的设计
