hive-使用开窗函数实现百分比、topN、前百分比

 有一个订单表A，分别有order_id（订单id）、user_id（用户id）、amt（金额）三个字段，
 用sql实现以下功能：
    i. 求订单总量为top3的用户及交易笔数，同时求出其交易笔数占全量订单笔数的占比。
    ii. 求每个用户top3交易金额的订单。
    iii. 求订单总量从大到小排名在50%之前的用户。

--********************************************************************************************************

--    i. 求订单总量为top3的用户及交易笔数，同时求出其交易笔数占全量订单笔数的占比。

select 
user_id
,count(*) as trade_cnt --交易笔数
,count(*)/sum(count(*))over() as trade_cnt_rate--交易笔数/总交易笔数
from 
(
select 'd001' as order_id,123 as user_id,20 as amt
union all select 'd002' as order_id,123 as user_id,25 as amt
union all select 'd005' as order_id,123 as user_id,250 as amt
union all select 'd007' as order_id,123 as user_id,100 as amt
union all select 'd003' as order_id,125 as user_id,15 as amt
union all select 'd004' as order_id,125 as user_id,39 as amt
union all select 'd006' as order_id,125 as user_id,25 as amt
union all select 'd008' as order_id,125 as user_id,59 as amt
)a
group by user_id
order by 2 desc limit 3

--    ii. 求每个用户top3交易金额的订单。

select user_id,order_id,sort_flag
from 
(
select user_id,order_id
,row_number()over(partition by user_id order by amt desc) as sort_flag
from 
(
select 'd001' as order_id,123 as user_id,20 as amt
union all select 'd002' as order_id,123 as user_id,25 as amt
union all select 'd005' as order_id,123 as user_id,250 as amt
union all select 'd007' as order_id,123 as user_id,100 as amt
union all select 'd003' as order_id,125 as user_id,15 as amt
union all select 'd004' as order_id,125 as user_id,39 as amt
union all select 'd006' as order_id,125 as user_id,25 as amt
union all select 'd008' as order_id,125 as user_id,59 as amt
)a
) t
where sort_flag<=3

--    iii. 求订单总量从大到小排名在50%之前的用户。

select a.user_id
,a.trade_cnt
,a.level
from
(
select user_id
,count(*) as trade_cnt
,ntile(2) over(order by count(*) desc) as level --50%就是1/2
from 
(
select 'd001' as order_id,123 as user_id,20 as amt
union all select 'd002' as order_id,123 as user_id,25 as amt
union all select 'd005' as order_id,123 as user_id,250 as amt
union all select 'd007' as order_id,123 as user_id,100 as amt
union all select 'd004' as order_id,125 as user_id,39 as amt
union all select 'd006' as order_id,125 as user_id,25 as amt
union all select 'd008' as order_id,125 as user_id,59 as amt
union all select 'd009' as order_id,128 as user_id,200 as amt
union all select 'd010' as order_id,128 as user_id,59 as amt
) x
group by user_id
) a
where a.level = 1

 补充：NTILE(n) 用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)。