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)：数据库冗余字段的设计

 

评论

2 楼 gg9088 2014-10-21  

膜拜

1 楼 ouxiang08 2012-12-10