目 录

0 需求分析

1 实现

2 小 结

---

0 需求分析

现有交易数据表user_trade如下：

user_id 用户id

pay_amount 用户支付额度

现在老板想知道支付金额在前20%的用户。

输出要求如下：

user_id用户名（前20%的用户）

1 实现

（1）数据准备

1001,100.7 1001,70.8 1002,50.4 1003,70 1005,100.5 1002,80.1 1003,36.7 1004,38.5 1003,50 1004,30 1004,300.5 1005,500 1005,3 1006,260.1 1007,360.4 1008,680

（2）创建表

drop table if exists dan_test.user_trade CREATE TABLE dan_test.user_trade ( user_id string, pay_amount double ) ROW format delimited FIELDS TERMINATED BY ",";

(3) 加载数据

load data local inpath "/home/centos/dan_test/user_trade.txt" into table user_trade;

（4）需求实现

分析：

Ntile函数使用

可以看成是：它把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。（这个算法在很多当中使用，spark中数据分片的时候也是这个算法，只不过是不均匀的时候，优先分配给较大编号的分片，如下图所示）

语法是：ntile (num)  over ([partition_clause]  order_by_clause)  as your_bucket_num然后可以根据桶号，选取前或后 n分之几的数据。数据会完整展示出来，只是给相应的数据打标签；具体要取几分之几的数据，需要再嵌套一层根据标签取出。NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

 本题要求的是支付金额前20%的用户，通过上述ntile函数分析 可知需要分5个桶，是对数据用户支付的总金额进行标签，然后求出对应的用户。具体SQL如下：

select a.user_id ,a.sum_pay_amount ,a.level from( select user_id ,sum(pay_amount) as sum_pay_amount ,ntile(5) over(order by sum(pay_amount) desc) as level from user_trade group by user_id ) a where a.level = 1 -------------------------------------------------------------------------------- OK a.user_id a.sum_pay_amount a.level 1008 680.0 1 1005 603.5 1

本题错误写法：

select user_id ,sum(pay_amount) as sum_pay_amount ,ntile(5) over(partition by user_id order by sum(pay_amount) desc) as level from user_trade FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies. Underlying error: org.apache.hadoop.hive.ql.parse.SemanticException: Line 3:34 Expression not in GROUP BY key 'user_id'

根据错误提示写测试案例：

select user_id ,sum(pay_amount) as sum_pay_amount from user_trade FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key 'user_id'

依然报错，但如下的SQL是正确的

select sum(pay_amount) as sum_pay_amount from user_trade

对比说明：说明hive中是可以单独使用sum()等聚合函数，而不使用group by,但是不使用group by使用聚合函数，只能出现聚合函数的字段，而不能有其他字段的出现。

这个案例也说明窗口函数的使用也是基于group by之后的结果进行开窗，在group by 之后的结果再进行计算，可以理解为group by的结果作为子表供窗口函数使用。

2 小 结

本文主要通过案例来分析ntile()函数的使用方法，ntile()函数主要用于对数据进行分片、分桶，其算法为如果数据能均匀分配的话就按照平均分配，不能均匀分配的话，优先将数据分配到编号较小的桶中。ntile()函数主要用来求某个数据的前或后百分比问题题，在统计中具有重要意义。