目 录

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中数据分片的时候也是这个算法，只不过是不均匀的时候，优先分配给较大编号的分片，如下图所示）

hive 数值转为百分比 hive输出百分比_hive

  • 语法是：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()函数主要用来求某个数据的前或后百分比问题题，在统计中具有重要意义。