使用分析函数改写cube，使其一步满足需求

要求：分等级的用户数 / 不分等级的总用户数

step1：求 分等级的用户数 和 不分等级的总用户数 ，但他们位于同一列

   SELECT TO_DATE(v_yesterday ,'yyyymmdd') visit_time ,r.reg_channel ,r.server_id ,

           DECODE(GROUPING(r.role_level),1,9999 ,role_level) role_level ,
           COUNT(DISTINCT u.device_mark) total_role_user ,SYSDATE
    FROM   game_role_log_history r ,game_user_log u
    WHERE  TO_CHAR(r.afferent_time ,'yyyymmdd') = v_yesterday AND r.user_name = u.user_name
  GROUP BY r.reg_channel ,r.server_id ,CUBE(r.role_level) ;

  

step2：自关联后，让分等级的用户数和不分等级的总用户数位于两列，方便两列相除

select a.* ,b.role_level ,b.total_role_user from game_total_role_server a ,game_total_role_server b
  where a.reg_channel = b.reg_channel AND a.server_id = b.server_id

        and a.role_level <> 9999 and b.role_level = 9999;

将上面两步改写成一个SQL：

SELECT DISTINCT REG_CHANNEL ,server_id ,ROLE_LEVEL ,
       MAX(a.no_level_role_user) OVER (PARTITION BY REG_CHANNEL ,server_id) AS "不分等级" ,
       MAX(a.level_role_user) OVER (PARTITION BY REG_CHANNEL ,server_id ,ROLE_LEVEL) AS "某等级"
FROM
       (SELECT r.REG_CHANNEL ,r.server_id ,r.ROLE_LEVEL ,
               DENSE_RANK() OVER (PARTITION BY r.REG_CHANNEL ,r.server_id ORDER BY u.DEVICE_MARK) AS no_level_role_user ,
               DENSE_RANK() OVER (PARTITION BY r.REG_CHANNEL ,r.server_id ,r.role_level ORDER BY u.DEVICE_MARK) AS level_role_user
        FROM   game_role_log_history r ,game_user_log u
        WHERE  TO_CHAR(r.afferent_time ,'yyyymmdd') = '20140324' AND r.user_name = u.user_name AND u.DEVICE_MARK IS NOT NULL
       ) a ;

注意：u.DEVICE_MARK IS NOT NULL 如果不加这个条件，会把NULL排名，就会比COUNT(DISTINCT u.device_mark)大1