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之间的单子