Hive 窗口函数sum over求当前行和前面n条数据的和

      前几天遇到一个这样的需求:销售总占比加起来超过75%的top分类。具体需求是这样的:商品一级分类标签下面有许多商品标签,例如运动户外一级标签,下面可能存在361°,CBA,Nike,Adidas…等这些商品标签。我们需要统计在一级标签下面占总销售比超过75%的商品标签有哪些,从而让我们了解一级品牌标签下面哪些商品比较收用户喜欢。有以下样例数据amount_precnt为这类商品在一级标签当中销售占比)。

15990432811)

按照我们的业务需求我们要得到男装->优衣库,七匹狼) 箱包->coach) 运动户外->361°,Nike,CBA)这样的结果就是符合我们的要求。amount_precnt占比在75%的top分类数据。

15990480841)

拿到这样的需求以后,思路如下。按照cat_name分组求和。然后在利用lag函数取前一条求和值作为另外一个字段。最后过滤条件为求和值小于0.5或者当前求和值大于0.5并且前一个求和值小于0.5的数据。得到思路以后主要是卡在了怎么按照cat_name依次求和。就是如何将第一条数据的amount_precnt + 第二条数据的amount_precnt。然后前面两条数据的和在加第三条数据的amount_precnt值。依次这样类推….后面翻看hive 窗口函数的官网得到资料。在sum) over 里面加上 rows between unbounded preceding and current 可以求当前行和前面n条数据的和。我们先看一下sum) over)得到的效果。amount_percent_num的值都是1.就是求和的值。

select amount_percent, cat_name,brand_num,
sumamount_percent) overpartition by cat_name  ) as amount_percent_num
  from
SELECT * FROM hive_temp_bad.dlyang_1234 order by cat_name) t

15990488741)

然后我们加上rows between unbounded preceding and current

select amount_percent, cat_name,brand_num,
sumamount_percent) overpartition by cat_name  ) as amount_percent_num
  from
SELECT * FROM hive_temp_bad.dlyang_1234 order by cat_name) t

15990487841)

得到了我们想要的结果了。最后我们贴上完整的sql代码实现上面功能。

1 select  cat_name,brand_num,amount_percent from
2 select *,lagamount_percent_num,1,0) overpartition by cat_name order by amount_percent_num) as lag1 from
3 select amount_percent, cat_name,brand_num,
4 sumamount_percent) overpartition by cat_name order by amount_percent desc rows between unbounded preceding and current row) as amount_percent_num
5   from
6 SELECT * FROM hive_temp_bad.dlyang_1234 order by cat_name) t ) t2 ) t3 where lag1 < 0.75 or amount_percent_num > 0.75 and lag1 < 0.75) ORDER BY
7   cat_name,amount_percent desc;

15990480841)

不得不说sql天花板可能真的是窗口函数了。看来以后得要多多学习了。

Published by

风君子

独自遨游何稽首 揭天掀地慰生平

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注