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