前几天遇到一个这样的需求:销售总占比加起来超过75%的top分类。具体需求是这样的:商品一级分类标签下面有许多商品标签,例如运动户外一级标签,下面可能存在361°,CBA,Nike,Adidas…等这些商品标签。我们需要统计在一级标签下面占总销售比超过75%的商品标签有哪些,从而让我们了解一级品牌标签下面哪些商品比较收用户喜欢。有以下样例数据amount_precnt为这类商品在一级标签当中销售占比)。
按照我们的业务需求我们要得到男装->优衣库,七匹狼) 箱包->coach) 运动户外->361°,Nike,CBA)这样的结果就是符合我们的要求。amount_precnt占比在75%的top分类数据。
拿到这样的需求以后,思路如下。按照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
然后我们加上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
得到了我们想要的结果了。最后我们贴上完整的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;
不得不说sql天花板可能真的是窗口函数了。看来以后得要多多学习了。