oralce汇总函数备忘

select nvl(schememoncode,'小计'),sum(providesum),rank() over (order by sum(providesum) desc) fare_rank
from providescheme
group by schememoncode

select decode(grouping(schememoncode),1,'标准小计',to_char(schememoncode)) schememoncode,decode(grouping(providelevel),1,'月小计',to_char(providelevel)) providelevel,
sum(providesum),rank() over (partition by schememoncode order by sum(providesum) asc) area_rank
from providescheme
group by cube(schememoncode,providelevel)

select schememoncode,providelevel,curprovidesum,
lag(curprovidesum,2,0) over (partition by schememoncode order by providelevel) pre2_curprovidesum,
lag(curprovidesum,1,0) over (partition by schememoncode order by providelevel) pre1_curprovidesum,
lead(curprovidesum,1,0) over (partition by schememoncode order by providelevel) nex1_curprovidesum,
lead(curprovidesum,2,0) over (partition by schememoncode order by providelevel) nex2_curprovidesum
from
(
select decode(grouping(schememoncode),1,'标准小计',to_char(schememoncode)) schememoncode,decode(grouping(providelevel),1,'月小计',to_char(providelevel)) providelevel,
sum(providesum) curprovidesum
from providescheme
group by schememoncode,providelevel
)

 

select schememoncode,providelevel,curprovidesum,
    sum(curprovidesum)
             over (  partition by providelevel
                     order by to_number(schememoncode)
                    range between 1 preceding and 1 following ) "3month_sum",
     avg(curprovidesum)
             over (  partition by providelevel
                     order by to_number(schememoncode)
                    range between 1 preceding and 1 following ) "3month_avg",
    max(curprovidesum)
             over (  partition by providelevel
                     order by to_number(schememoncode)
                     range between 1 preceding and 1 following ) "3month_max",
     min(curprovidesum)
             over (  partition by providelevel
                     order by to_number(schememoncode)
                     range between 1 preceding and 1 following ) "3month_min"
from
(
select decode(grouping(schememoncode),1,'标准小计',to_char(schememoncode)) schememoncode,decode(grouping(providelevel),1,'月小计',to_char(providelevel)) providelevel,
sum(providesum) curprovidesum
from providescheme
group by schememoncode,providelevel
)

select decode(grouping(schememoncode),1,'标准小计',to_char(schememoncode)) schememoncode,decode(grouping(providelevel),1,'月小计',to_char(providelevel)) providelevel,
sum(providesum) curprovidesum,
ratio_to_report(sum(providesum)) over  ( partition by schememoncode ) area_pct
from providescheme
group by schememoncode,providelevel
  compute sum of curprovidesum on schememoncode
  compute sum of area_pct on schememoncode
 

 select bill_month,area_code,sum(local_fare) local_fare,
    ratio_to_report(sum(local_fare)) over
       ( partition by bill_month ) area_pct
  from t
 group by bill_month,area_code
  break on bill_month skip 1
  compute sum of local_fare on bill_month
  compute sum of area_pct on bill_month

create table demo2 as
select object_id,owner... from
(select demo.*,row_number() over(partition by object_id order by rowid) rn from demo)
where rn = 1;
truncate table demo; insert into demo select * from demo2; drop table demo2;

 

 

 

 

 

 

 

select decode(grouping(schememoncode),1,'标准小计',to_char(schememoncode)) schememoncode,decode(grouping(providelevel),1,'月小计',to_char(providelevel)) providelevel,sum(providesum)
from providescheme
group by cube(schememoncode,providelevel)

decode(grouping(providelevel),1,'标准小计',to_char(providelevel)) providelevel,

select * from (
select memberid,providedmoney,dense_rank() over (order by providedmoney desc) add_rank
from providepool
) a where  a.add_rank<10

select idcard from member group by idcard having count(id)>1
  select rowid row_id,row_number() over (partition by obj# order by rowid ) rn

select obj#,name from sys.obj$ where name='IDCARD'

 select row_id from (
      select rowid row_id,row_number() over (partition by idcard order by rowid ) rn from member
   ) where rn <> 1