切片排序窗口函数
ntile(n) over(……)
ntile(n) over(partition by …A… order by …B… )
n:切分的片数
A:分组的字段名称
B:排序的字段名称
NTILE(n):用于将分组数据按照顺序切分成n片,返
回当前切片值。
NTILE不支持ROWS BETWEEN,比如 NTILE(2)
OVER(PARTITION BY …… ORDER BY …… ROWS
BETWEEN 3 PRECEDING AND CURRENT ROW)。
如果切片不均匀,则前面的组分得的数据多。
–需求6:将2019年1月的支付用户,按照支付金额分成5组
select user_name,sum(pay_amount)as total_amount,
ntile(5) over(order by sum(pay_amount)desc)
from user_trade
where substr(dt,1,7)='2019-01'
group by user_name;
注意:substr()为取出相应的字符串
substr函数格式 (俗称:字符截取函数)
格式1: substr(string string, int a, int b);
格式2:substr(string string, int a) ;
解析:
格式1:
1、string 需要截取的字符串
2、a 截取字符串的开始位置(注:当a等于0或1时,都是从第一位开始截取)
3、b 要截取的字符串的长度
格式2:
1、string 需要截取的字符串
2、a 可以理解为从第a个字符开始截取后面所有的字符串。
– 需求7:选出2019年退款金额排名前10%的用户
select a.user_name,a.total_amount,a.total_range
from
(select user_name,sum(refund_amount)as total_amount,
ntile(10) over(order by sum(refund_amount) desc) as total_range
from user_refund
where year(dt)=2019
group by user_name)a
where a.total_range=1;
注:排名前10%的用户可将整个字段分为10段.按照降序排列,取出第一段就可.