前几天遇到一个这样的需求：销售总占比加起来超过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, sum(amount_percent) over(partition 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, sum(amount_percent) over(partition 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 *,lag(amount_percent_num,1,0) over(partition by cat_name order by amount_percent_num) as lag1 from 3 (select amount_percent, cat_name,brand_num, 4 sum(amount_percent) over(partition 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天花板可能真的是窗口函数了。看来以后得要多多学习了。