00:00
好,我们看最后一个这个这个需求这很长啊,这名字是吧,比俄罗斯人名字都长,叫什么叫做各一级品类下的,然后诶月品牌复购率。月品牌复购率啊,当然这个复购率当中呢,我们会有一个这个单次还有一个多次复购率,呃,是吧,那大家来看一下我们最终想要的一个效果,其实是这样的。这样就是这边呢,有不同的品类,不同的一级品类,那一级品类下面呢,可能会有不同的品牌,对不对,我们要求出来每个一级品类下边每个品牌的这个各种复购率,各种复购率啊,那这边呢,就是我们想要去这个最终得到的一个结果,那我们来分析分析啊,咱们来分析分析,我要想得到一个这样的一个结果,那你说咱们应该怎么一步一步的去实现。怎么一步一步去实现啊,咱们其实前面分析过了,我要想求品牌复购率,嗯,那所以说我们要求求求出来什么东西了就就可以了,我们只要求出来每个品牌诶购买它一次以上的有多少个人,当然咱们求的是不是月付月品牌复购率啊,那诶是月啊在这呢啊月,那也就是说你需要把这一个月的这个数据给他拿过来啊,得得是一个月的数据才行,嗯,是这样的啊,那你说出来什么呢?在这一个月当中,来购买这个品牌一次以上的有多少个人,来购买他两次以上的有多少个人。
01:22
三次以上的有多少个人对吧?只要我们把这几个值求出来,那这个复合率那就容易了,对不对?那关键是如何去求这个人数,怎么去求。咱们继续。可以琢磨琢磨。那其实求这个所谓的人数的时候呢,大家可以这个跟我来先来想一个咱们生活中,生活当中的例子啊,生活当中例子,那比如说诶咱们班啊,我看这个同学这个迟到现象比较严重啊,咱们早上25签到对不对,那个30有时候还有同学没到呢,是吧,这个假如说我现在啊,想统计一下咱们这个班里边,就是这一周之内,这一周之内啊,来迟到一次以上的有多少个人。
02:07
嗯,对不对,哎,那吃到两次以上的有多少人,吃到三次以上的又有多少人,诶比如说我也想想统计一个类似这样的一个需求,对不对,那OK,你说这个我应该怎么去统计。咱先不说这个复合率,可能那个概念大家不好理解,咱们以迟到这个事儿为例,咱么去统计,那你是不是得这么去统计啊,是不是得把咱们班每个同学的迟到次数先给他求出来啊,对不对,先求出来,比如说这个这个,呃,张三同学,比如说迟到几次,那我给他求出来,比如说迟到一次,那我列上那个李四同学呢,迟到两次,那王五同学呢,迟到三次。对不对,咱们把这个每个同学的迟到次数都给它列出来,列出来之后呢,我再去干什么,再去统计,诶迟到一次以上的有几个人,一次以上的,咱们这是不是有三个,两次以上的几个,是不是有两个三次以上几个一个。那我是不是就可以求出来了呀,也就是说你先把每个人迟到的次数给他求出来,那就可以了。
03:02
这个大家应该,嗯知道怎么去求了吧,那OK,那接下来咱们再返回来,再看咱这边这个所谓的复购率,我们复购率这边要求的是什么?要求的是比如说诶李宁这个品牌,就是说购买它一次以上的有多少人,那购买他两次以上的有多人,购买它三次以上的有多少人,就咱们求统统计的是这个东西,那我得先统计什么呀。我是不是得先把每个人,就是每个用户购买李宁这个品牌的次数,是不是得先先得求出来。对不对,比如说张三购买李宁一次,那李四购买李宁两次,那这个王五购买李宁这个三次,对不对,咱先把每个用户购买李宁这个品牌的次数都给它求出来,求完之后呢,再去统计,诶一次以上的几个,两次以上的几个,三次以上几个。对不对,那是不是咱就得到这个最终我们想要的这个结果了,是一个思路啊,一个思路,那OK,当然了,我们这去统计的时候呢,你不能光统计咱们这个每个人购买李宁的次数。
04:00
你不是只统计了一个品牌的复购率对不对?我要统计的是所有品牌的复购率,那你得把什么求出来呢?是不是得把每个人购买每一种品牌的次数先给他求出来呀?对吧,每个人购买每一种品牌的次数都给他求出来,求完之后呢,诶再去,诶记这个人数,再去统计这个人数,是这样的一个思路啊OK。那现在我们的任务现在变成什么了呢?我们现在任务啊,其实就变成了,诶先去求每个人购买每个品牌的次数。那就变成了这个东西了。对吧,那这个东西咱应该怎么去求它呢。怎么去求每个人购买每个品牌的次数,而且咱们统计的是愉月复购率吧,对不对,那也就说我们需要诶把这一个月的购买记录是不是给他拿出来呀,这样咱们去怎么去统计啊,其实咱们上边有一张表,那张表呢,我们来看一下DWS叫做我记得叫做什么叫做cell吧,Saa这个还诶这个CTRLZCTRLZ怎么写到那去了。
05:03
在这在这查一下啊来。DS应该是有一个,呃,Sal。诶找不着了,我直接搜吧,Fall。呃,然后再搜一下啊,搜一下往上走。诶,这个还找不着了,那我们去看这个文档吧,看文档啊,文档上应该是有这个表,诶在哪呢?在这个DW。因为是我这个笔记当中没有加上这个表DWSBW在这咱们这呢,有一个这个每日购买行为这个表,大家还记得吧,那其实这个表呢,呃,主要就是服务于咱们这个复购率的,它主要就是复购服务于咱们这个复购率的啊,那我们来回顾一下这个每日行为这个表里边,它呃统计了哪些值啊,咱们来看看往下看。呃,咱们把这个表粘出来吧,放在CRC。然后F3咱们盯一下子,这个表字段很多是吧,我们逐一的看一下啊来上边首先咱们得明确啊,那这张表它叫什么名,叫做这个SE detail对抗的是不是就是诶销售这个明细啊,销售明细,那这个销售它是一个什么,它是一个行为,一个行为呢涉及到两个主体对象,一个对象就是咱们的用户,还有一个呢,就是咱们的商品,是不是就是用户买商品就所谓的销售行为啊或者购买行为。
06:23
购买行为,那这里边我们这个表它的力度应该是什么呢?力度应该力度是不是就是U的ID加SQID啊对不对,就是哪个用户买了哪个商品,然后下边呢,可能会有一些这个统计的值,下边有什么值呢?有这个,比如说这个每个用户购买每个商品多少个对不对,那这边呢,有多少次,就是下单单数有多少次呗,那后边呢,还有这个多少金额,就是这个用户买这个商品一共花了多少钱。对不对?那咱们有这些统计值,那有这些统计值,那我们现在要要得到的最终的结果是什么?我们现在要得什,我们要得到的是每个用户购买每个品牌的次数,对不对?咱们要得到是每个用户购买每个品牌的次数,那我们能从这张表里边得到什么?我从这张表里我能得到每个用户购买每个商品的这个次数吧,下的单数就是购买次数,对不对?那你得到这个值之后,你能不能得到每个用户购买每个品牌的次数?
07:22
能得到吧,怎么去得到,是不是就是诶把这个东西诶拿出来拿拿多少,拿一个月的数据啊,那注意这个是每日购买行为,我们要拿一个月的数据,把一个月月的数据拿出来之后呢,咱们怎么办啊。是不是得按照谁去分一个组,是不是得按照用户ID,再按照谁加上咱们那个品牌,这是不是有一个SKTMID,再按照品牌ID进行分组,UID加品牌ID分组,那分完组之后呢,你需要你就会把什么样的数据分到一组呢,就会把是不是同一个品牌的这种诶商品分到一个组里边啊,那完事之后你需要对这个order count进什么进行什么,是不是进行求和。
08:02
求个就完事,那你就能够得到每个用户购买每个品牌的次数了,只要得到这个次数,那你再去统计次数大于一的有多少,大于二的多少,大于三的多少。那是不是就能够得到咱这个复购率了呀,其实这个思路就是这么一个思路,好,那现在呢,我们就奔着这个思路呢,我们去把这个思大家他写一下啊,那这边呢,我们需要用到呃一个表,那这个表呢,就是这个每日呃这个购买行为扩表,那这个表我给它粘在这吧,粘在这大会用到它啊。好,那现在呢,我们开始去实现我们想要的这个这个功能啊。我们先得到每个用户购买每个品牌的次数来,那就select,先不说选啥啊,先说from from这个DWS层的这个salle就是销售明细宽苗,诶那这是一个天苗对不对?那天苗呢,我们需要呃用这个日期去给它过滤一下,过滤到一个月,咱们应为是月复复购率嘛,那怎么去把这一个余月的数据给它呃过滤出来呢?呃,那这个方法很多,那同学可能会求月月,呃首月这个月初时间加月末时间过滤也可以,或者我这呢用date format也可以date_form format把这个呃,DT给它传进来,传来之后呢,传一个YYYYMM,把日给它去掉,那如果说这个呢,等于谁date format。
09:26
把这个2020杠零三杠幺零当天日期给它扔进来,那之后呢,我们同样是给它格式化成YYYMM的格式,那这样一来我就能够把三月份的所有数据全部查到,全部查到,那查到之后啊,我们上边接下来呢,就开始去完成我们这所需要的这个工作啊,咱们工作是什么?工作是把这个同一个品牌的商品给它汇聚到一起,对不对,那得到用户购买品牌的次数。就注意啊,这个用户你还得留着它,因为你要统计用户购买品牌的次数嘛,对不对,所以你分组的时候不能光按照品牌分组,那就不对了,那就不对了,这次呢,得带着用户,那下边呢,我们by JR up by有一个user ID,还有一个品牌ids ku TM ID,还有一个品牌ID啊那上面我们选什么东西呢?诶一个就是s user的ID,这是用户,然后呢,购买了这个品牌,诶然后呢,咱们要求求的是一个次数吧,那就是直接求和就行了,Sum,诶这块呢,有一个older count,那我们把这order count拿回来,Older c。
10:31
那这样一来就得到了用户购买品牌的次数了,那得到它之后呢,接下来我们要做的事呢,就是去统计,诶这个次数大于一的有多少个用户对不对,那大于二的有多少个,大于三的有多少个,其实就是去统计这个事了,当然咱这边呢,呃,因为咱这个需求他有一个要求啊,有什么要求,他要求我们去统计诶各一级品类下边对不对,这个月品牌复购率,也就说你最终你的结果当中啊,得带着这个是不是得带这个品类啊对不对,那在品类,那所以说我们在这你select的时候,你是不是得得把这个品类得带着呀,对不对,那这边咱们怎么怎么选啊品类呢,咱们这有俩字段,一个是这个品类的ID,一个是这个品类的名称,呃,那我们这个需求就是这个需求建本语句当中呢,要求我们这个,呃,品类ID和品类名称都有,那所以咱们这呢,选的时候都得选上,那就来一个sko什么东西,CAT1先来一个ID,然后逗号s ku的这个下划线,呃,Categ gr1name,好,那这俩字段咱都得拿过来,但是。
11:31
大家这时候来想一下了,你说我这俩字段我能直接在这写吗?能直接在这写吗?是不能的,为什么不能呢?因为咱们这儿是不是下边有葛入拜啊,那我们葛入拜有要求,就是说你只要下边分组了,那你上边选的时候只能选什么,只能选三三种东西啊,只能选三种东西,第一种就是说你可以选择你葛入bed的这个字段,诶对不对,那这这个是不是就搁入外字段啊,这是能选的,那还有一种是什么?就是聚合函数,这我也是能选的,还有什么能选的,要么就是常量值,就分别这写死一个日期2020杠零三杠幺零,这你是能写的,其他的你都写不了,那这边这俩字段属于前面刚才说这三种吧,它都不属于。
12:13
都,但是我这边是不是必须得带着它呀,那这该怎么办啊,就在下边呢,你外里边给它加上就能选了SKU,诶,Category e ID,然后逗号呢,SKU category name,那这样一来我们最终的结果呢,就会带着这个一级品类的ID和名称。OK,那这边咱们已经完成了这个我们最关键的一步了,已经求出来了每个用户购买每个品牌的次数次数了,那接下来咱们接下来干什么?以它作为一个子查询,以它作为子查询啊,CTRLX,那我去统计咱们这个人数。来,CTRLV。然后呢,我我推一下啊。推一下,然后大家要注意了,这边其实你只要有了这个子查询之后啊,你去统计这个什么一次以上的人数,两次以上人数,三次以上人数,其实就容易了,对不对,你可以怎么办?嗯,按照我们正常的这个思维,你可以这么去做,以它作为子查询,然后是不是可以用那个呃,VR去进行过滤啊对不对,咱们这已经有这个了,大于等于一的,我过滤出来,然后count一下,我得到大于等于一的对不对,那大于等于二了呢,我过滤出来count一下,大于等于三过滤出来count一下是不是都就可以了,然后这个完事之后呢,再给它撞到一起,然后该除再去除是不是就可以了呀,那那但是你这么去写呢,你需要写好几个子查询,需要去join,那这边咱还可以怎么做啊,是不是又又可以怎么做,咱们一起把这个结果出出来,用什么,是不是还是用some if去做呀,对吧,咱们还是可以用这个some if。
13:38
这some还是比较常用的啊,好了,那现在呢,我们开始去,呃,完成咱们的最后一步,我从这里边去选select,先不说选啥,先说from。对吧,那下边这个表我需要给它起个别名啊,咱们就叫T1吧,叫T1,那这时候大家要注意了,大家要注意了啊,我们最终得到的这个结果是什么样,最终得到结果是这样的。
14:02
对不对,就是诶有一个一级品类,还有一个什么呢?还有一个品牌,还有一个品牌,因为我们要统计的是什么,是每个品牌的这个复购率吧,是每个品牌的复购率,所以说你最终肯定得去分组计算,对不对,按照谁分组呢?肯定是按照品牌去分组的。对不对,肯定是按照品牌去分组的,那这时候我我我说按照品牌分组,会把什么样的分到一组里边去呢?会把购买过这个品牌的所有用户是不是分到一组里边去啊,对不对?诶大家认真听啊,我这时候如果是以他作为子查询,然后呢,按照品牌去分组的话,那就会把所有购买过诶这相同品牌的用户是不是分到一组里边啊,那购买过相同品牌这个用户呢,有可能购买这个品牌有可能有一次的,呃,也有可能有两次的,也有可能有什么三次以上的,对不对,这样的都有,那这时候我们再去干什么呀?再去用SUM1去统计,如果大于等于一,那我就给你记个一,否则就记零,大于等于二记一,否则记零,是不是就可以这么去给他进行统计了,那我们就能够得到,诶,购买每一个品牌一次以上的有多少人,两次以上的多少人,三次以上的有多少人了。
15:11
咱们的思路是这样的啊,那所以这边呢,咱们得分组统计来,Guru by这ru byt m midd,那咱们那个字段名叫什么呀,叫做SKT mid对吧?那就SKT m midd按照它进行分组,呃,当然这边分完组之后呢,上边我就需要去把这个SKTMD给它选出来来,但是大家要注意还是那个原因,什么原因啊,我们最终是不是得带着这个品类啊,得带着它才行,那这边我们还是得给它选出来,那也就说你需要把这俩字段呢,也给它选着CTRLZ播放到这个位置。那当然你这边要想选的话,那你下边得干啥呀,下边是不是得对它进行格入Y啊,那就SKU我直接写在这吧,CTRLV,然后呢,咱们格式化一下啊。这个呢,你需要给他带着,那同理这边呢,咱们也需要给他带着,好,那这回葛外他们仨就可以了,那接下来呢,我们就是去统计这个,诶购买每个品牌的这个这个次数了,购买它这个次数了,怎么去做,那就some if人数人数了,Some if some if,如果说嗯,我的,呃这边咱是不是得需要去选这个字段呢?所以说需要起一个这个别名,咱们就还叫o count吧。
16:23
来看,如果说这个order or DR。如O尔count,如果说大于等于一,那我就记个一,否则记个零,你看这次不就得到购买一次以上的有多少人了呀,那同理,那下边呢,我们CTRLZ,我拿到这啊,CTRLV,然后CTRLV,那如果说这个求大于等于二的呢,那就把这改成二就行了,大于三的呢,那么把它改成三就行了,那这样一来,我们一次、两次、三次的我全部求出来了,求出来之后呢?呃,单次复荷率怎么求,用两次的除以一次的,多次负荷率怎么求,用三次的除以一次的,那我们就把这个复购率就求出来了。
17:02
其实咱这边这个需求呢,到这就完事了,就完事了啊,就是可能咱们这这个需求他可能有有点难度,难在什么地方了呢?难带啊,他这个涉及到了几次这个力度的转换,几次力度转换,那咱们原始数据是什么力度啊,原始数据是用户商品力度对不对,那我们首先需要给它转换成用户品牌力度。对不对,先到用户品牌,然后再给他转换成品牌,这个力度当然得是什么品牌得带着一级品类啊,就是一级品类下面的品牌得到这个力度,我们涉及到了这样的几个力度的转换,但其实就是葛如BY呗,就是葛如外就是这样,所以说可能有点难度,但是这个呢,其实大家要是仔细的去思考,这里边逻辑其实并不复杂,逻辑并不复杂啊,好了,那这个需求,呃,咱们到这就这个就结束了啊,那里边呃,我们把这个建面语句拿过来啊,里边可能还有一些字段呢,需要我们去补充,需要去补充,那我们来看一下。复购率,把这个键盘语句拿回来。
18:04
呃,放到这个位置来啊,咱们看这里边还需要哪些字段需要我们去补充啊,我们这边呢,品牌ID这个品类咱们都有购买人数也就是大于等于一的有两次的有这个有这个有那这俩呢,除一下就行了。下边呢,还有一个统计的月份,因为我们是月品牌复购率嘛。所以有一个月份下边还有一个统计日期,那把这些日期给他补上就行了,这个咱们就不再手动的去补了,咱们把文档上的东西拿回来吧,然后核心的东西已经说完了,那把文档上这个东西拿回来,那就结束了啊C。呃,放到这个笔记当中。好CROK,那这里边的日期呢,这个需要去改一下,这个一会再改吧,一会再改啊行,那这个需求就完事了啊,我把视频录一下。
我来说两句