oracle 按周统计 获取各个省份排名前十用户信息

按周统计 获取各个省份排名前十用户信息
 
  select to_char(sysdate,'yyyy') as yYear,
        to_char(sysdate-1, 'IW')  as yWeek, 
       (select prov_name from ups_province where prov_code=mm.proCode) as proName, 
       mm.serviceNo as serviceNo, 
       countNo,
       mm.sumComsumePts as sumComsumePts, 
       mm.ranknum
  from (select s.prov_code proCode,
               s.service_no serviceNO,
               count(s.service_no) countNo,
               sum(s.consume_pts) sumComsumePts,
               row_number() over(partition by s.prov_code order by count(s.service_no) desc) ranknum
          from ups_user_order s
         where to_char(s.create_tm, 'YYYY:IW') =to_char(sysdate-1,'YYYY:IW')
           and s.transact_flag = '1'
         group by s.prov_code,
                  s.service_no) mm
   where mm.rankNum <= 10