Mysql实现开窗函数

最新推荐文章于 2024-09-26 23:23:12 发布

你看我会发光i

最新推荐文章于 2024-09-26 23:23:12 发布

阅读量644

点赞数

分类专栏： 03 Hive及数仓文章标签： mysql sql

本文链接：https://blog.csdn.net/liyang_007/article/details/103578507

版权

03 Hive及数仓专栏收录该内容

15 篇文章 2 订阅

订阅专栏

文章目录

- - 原始oracle代码
  - Mysql代码

原始oracle代码

select 
row_number() over(partition by  a.statis_day,a.app_version order by to_number(a.all_consume/a.cnt) desc) as rn
from
(
	select
	statis_day
	,app_version
	,cast(action_consume+gl_consume+draw_consume+engine_consume+ss_consume+sdk_consume as int) as all_consume
	,cnt
	from 表
)a

Mysql代码

select 
@rownum:=@rownum+1 
,if(@pdept=a.statis_day and @pver=a.app_version,@rank:=@rank+1,@rank:=1) as rank
,@pver:=a.app_version
,@pdept:=a.statis_day

from
(
	select
	statis_day
	,app_version
	,cast(action_consume+gl_consume+draw_consume+engine_consume+ss_consume+sdk_consume as int) as all_consume
	,cnt
	from 表
	-- 1 排序
	ORDER BY cast(action_consume+gl_consume+draw_consume+engine_consume+ss_consume+sdk_consume as int)/cnt desc
)a
-- 2 join表
,(select @rownum :=0 , @pdept := null,@pver := null ,@rank:=0) c