maxcompute使用窗口函数，对于某个字段进行排名

https://help.aliyun.com/document_detail/34994.html?spm=a2c4e.11153940.0.0.55953073yYjdBA

SELECT *

from

(

select t.userid,t.totalamount, PERCENT_RANK() OVER(PARTITION BY t.userid ORDER BY t.totalamount DESC) as amount_rank

from ticket_ods t where t.pt=20190907

) a

where amount_rank>0.1 and amount_rank<0.9

 

【注意】：窗口函数只能用在子查询中，就是where rank>0.1这个，要在外面，不能在子查询里面使用

 

 

select sum(p.kedanjia)/count(*) as industry_per_ticket, c.tag from

(select i.userid, sum(i.totalamount)/count(*) as kedanjia

from

(

SELECT *

from

(

select t.userid,t.totalamount, PERCENT_RANK() OVER(PARTITION BY t.userid ORDER BY t.totalamount DESC) as amount_rank

from ticket_ods t where t.pt=20190907

) a

where amount_rank>0.1 and amount_rank<0.9

) i

group by i.userid) p

inner join company_classification_temp c

on p.userid = c.userid

GROUP by c.tag

 

查找每个userid下面，totalamount排名在0.1-0.9之间的单子

转载于:https://www.cnblogs.com/yjybupt/p/11512215.html