mysql实现开窗函数

mysql实现hive row_number()函数分组

按照app字段分组,show_raw排序：
select 
	app,
	show_raw,
	rank 
from (
	select
	    report.app,
	    report.show_raw,
	    if(@str1=report.app,@row_number:=@row_number+1,@row_number:=1) as rank,
	    @str1:=report.app
	from
	 (select 
	 		app,
	 		show_raw
	 from test.show_table where date='2021-09-17'
	 order by app,show_raw desc ) report,(select @str1:=null, @row_number:=0) a 
 )result

按照app,show_type分组，show_raw排序
select 
	app,
	show_type,
	show_raw,
	rank 
from (
	 select
	     report.app,
	     report.show_type,
	     report.show_raw,
	     if(@str1=report.app and @str2=report.show_type,@row_number:=@row_number+1,@row_number:=1) as rank,
	     @str1:=report.app,
	     @str2:=report.show_type
	 from
	  (select 
	  		app,
	  		show_type,
	  		show_raw 
	  from dashboard.lingxi_show_stats_new where date='2021-09-17' 
	  order by app,show_type,show_raw desc) report ,(select @str1:=null,@str2:=null, @row_number:=0) a )result