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