付费名次

场景：假设现在有几个游戏渠道，我们要对比今天的渠道付费和昨天渠道付费，看看在这些渠道中的排名是降低了还是上升了。我们可以先以今天的时间间隔为查询条件，并以渠道进行分组查询出各个渠道总的付费金额。

select to_char(logtime, 'yyyy-mm-dd') logtime,platform,sum(cash) money from platform_cash where logtime
logtime>=to_date('20150616', 'yyyymmdd') and logtime < to_date('20150616', 'yyyymmdd')+1 group by to_char(logtime,'yyyy-mm-dd'),platform order by logtime

 

然后另一个查询是以昨天为查询条件，查询出各个渠道的总的付费金额和rownum，并将付费金额以降序排列。则，这个rownum就表示了昨天各个渠道在渠道当中的付费排名了。

select rownum as lastrank,bb.* from (select sum(cash) money,platform from platform_cash where logtime >= to_date('20150616', 'yyyymmdd')-1 and logtime < to_date('20150616', 'yyyymmdd')  group by platform  order by money desc) bb

 

因为这个明细表plat_cash里面的渠道字段只是一个缩写，为了找到它所对应的中文名称还得与一个渠道表进行关联。也就是说这两个查询都得和渠道表进行关联。整合上面两条查询：

select aa.money,s1.name,aa.operate,s.lastrank from (select to_char(logtime, 'yyyy-mm-dd') logtime,operate,sum(cash) money from platform_case where logtime logtime >= to_date('20150616', 'yyyymmdd') and logtime < to_date('20150616', 'yyyymmdd')+1 group by to_char(logtime,'yyyy-mm-dd'),operate order by logtime)aa,
(select rownum as lastrank,bb.* from (select sum(cash) money,operate from platform_cash where logtime >= to_date('20150616', 'yyyymmdd')-1 and logtime < to_date('20150616', 'yyyymmdd')  group by operate  order by money desc) bb)s,(select name,oper_short as short from tab_oper)s1 where s1.short=aa.operate and aa.operate=s.operate(+)

 

将上面两个连接查询的结果作为一个中间表，外再套一层查询，查出这个中间表的渠道名称、渠道付费金额、昨天排名、rownum，并且以渠道付费金额进行降序排列rownum就代表了今天的渠道付费排名。取出前5条记录，就是今天的前5个渠道付费排名，然后也知道了今天的前5相对于昨天的排名是降低了还是上升了。

select rownum as rank,t0.name name,t0.money data ,t0.lastrank last from (
select  t. money , sl.name, t.platform ,s.lastrank from ( 
select to_char(logtime, 'yyyy-mm-dd') logtime,platform, sum(cash_add) /100 money  from mysql.t_rt_all_game_cash_add where gameid=14 and logtime >= to_date('20150616', 'yyyymmdd') and logtime < to_date('20150616', 'yyyymmdd')+1 group by to_char(logtime, 'yyyy-mm-dd'),platform order by logtime) t,(select rownum as lastrank,t1.* from(
 select sum(cash_add) /100 money,platform from mysql.t_rt_all_game_cash_add where gameid=14 and logtime >= to_date('20150616', 'yyyymmdd')-1 and logtime < to_date('20150616', 'yyyymmdd')  group by platform  order by money desc)t1)s, (select name, oper_short as platform from t_sy_operator ) sl  where  sl.platform=t.platform and t.platform=s.platform(+) order by money desc)t0 where rownum<=5