00:00
好,那接下来我们把漏斗分析这个需求实现一下啊,老规矩我们还是先来分析思路啊,来大家一起看一下啊,这儿有一个recent date字段对吧?啊,那这个字段我们仍然是暂时先不考虑啊,现在我们先只统计最近一日的下列指标啊好,那现在我们把下边这几个指标看一下啊,那其实这几个指标都是人数对吧?那所以理论上呢,这个逻辑不会太复杂,唯需要大家注意的呢,哎,就是前边两个人数指的是访客数啊,后边三个人数呢,指的是用户数啊在这儿为什么要做一个这样的规定啊,其实很简单啊,那因为前面两个人数是不是都是浏览页面的人数啊,没错吧,那浏览页面的时候咱是不需要注册,不需要登录啊,那后边这三个诶,分别是加购下单支付,那在做这些操作的时候,是必须得要求它是注册并且是登录的状态才可以啊。啊,OK,那这个呢,大家需要去注意一下,好,那接下来呢,我们再来明确一下啊,每个字段的数据来源是什么?来,我们先看前两个,前两个呢,分别是home count和good detail count,对吧?那他们是不是都是与页面浏览相关的统计啊,没错吧?那所以这两个字段的数据来源应该是哪张表啊,是不是DWD层的pagelo呀,没错吧?那我们再来看下面三个啊,那下边呢,分别是加购人数、下单人数、支付人数,没错吧?而且在这儿呢,它最终让我们统计的是最近一日的,最近七日的和最近三十日的下列人数。
01:25
没错吧,诶,那这时候我们应该能想到哪张表。咱是不是得想到DWT层的user topic这张表啊,没错吧?那为什么会想到这张表呢?因为在这张表当中呢,我们分别统计了每个用户的最近一日、最近七日、最近三日的加购次数,下单次数以及支付次数,对不对?那我们是不是就能够根据这些次数去判断这个用户有没有加购物车,有没有下单,有没有支付对吧?那进而我们就能够分别去统计最近一日、最近七日和最近三日的虾类人数了,没错吧?啊,那所以说最终我们就明确下来了啊,前两个字段诶,我们出自于DWD层的配置log表啊,那后三个字段呢,出自DWT层的user topic表啊好,那接下来我们把circle写一下。
02:11
来,我们先统计前两个指标啊,按照我们刚才的分析呢,前两个指标是不是应该是来自于DWD层的配置log表啊,没错吧?啊然后呢,在这儿我们先只统计最近一日的值啊,那所以说我们只过滤最近一个分区就够了,哎,零六杠幺四没问题吧?啊然后大家思考一下啊,那这张表当中是不是存储了所有的页面浏览记录啊,对吧?但是在这儿我们只需要统计浏览首页和浏览商品详情页的人数啊,所以在这儿呢,我们是不是可以先做一个过滤啊对吧?把所有的首页以及详情页的浏览记录诶给它过滤出来,那这样一来呢,这个数据量能少一些,没错吧?啊OK,那咱们再加一个过滤条件啊,应该是按的对吧?按照什么and配置ID,然后呢,然后再写一个in啊,那后边再写一个集合,里边应该是诶home,然后呢,还有一个good detail对吧,商品详情啊,Good啊,Detail OK,那这样一来呢,我们就能够把首页和商品详情页的浏览记录拿到了啊好,那拿到之后我们接下来要干啥呢?接下来的思路是啊,我们先。
03:11
将同一个访客浏览的页面放到一个数组当中啊,然后呢,我们再根据数组当中是否有home元素以及是否有good detail元素来去判断它是否浏览了首页,或者是否浏览了商品详情页啊,进而呢,我们再去统计浏览首页的人数以及浏览商品详情页的人数啊好,那现在我们把这个思路实现一下啊,那首先我们需要先按照m mid进行分组啊,没错吧,然后呢,我们把mid选择出来啊,之后我们再通过克莱的函数,哎,把它浏览的所有页面放在一个数组当中,没错吧?哎,那这个数组就是它浏览的所有页面,没错吧,咱们现在呢,先把这个circle执行一下。来我们看一下这个结果啊,那从这个结果当中我是不能够清楚的看到哪个人他浏览什么页面呀,对吧?啊,比如他浏览了首页和商品详情页,而它呢,是不是只浏览了后半页呀,没错吧?那那接下来呢,我们再从这个结果当中去分别统计浏览首页和详情页的人数啊,那接下来我们继续往下进行啊来,我们把它放到一个子查询当中来,CTRLX放到括号当中来往前推一下,那这个我们取个别名为T1,好,那现在我们select然from t啊OK,那从这个T里边选择的时候,我们需要用到什么呀?是不是需要用到some if啊,对吧?来咱们some if if服什么啊,衣服配置当中有后页,那我们就记为一,否则我们就记为零,那这样一来是不是就能够求出来浏览过首页的人数啊,没错吧?那怎么去判断一个数字当中是否包含一个元素呢?诶,在这儿我们需要用到一个函数啊,这个函数叫什么呢?叫做a re contains啊,那这个函数呢,一共接收。
04:54
两个参数,第一个参数,诶是一个数组啊,那第二个参数呢,就是我们要判断的那个元素啊,OK,那咱们这儿呢,是不是得把配置放进去完之后呢?诶再来一个,诶,Home home me啊,OK,那如果包含诶那它就会返回处,否则就会返回false,没错吧,那包含,那咱这儿是不是就得返回一不包含就得返回零,那那这样一来我们得到的就是浏览首页的人数了,那那接下来呢,我们再用同样的套路去计算浏览过商品详情页的人数,咱是不是只需要把这个home改成good detail就可以了。
05:27
啊DTNL啊,那这样一来我们就分别求出来了浏览首页以及浏览详情页的人数,啊,那现在我们把它执行一下,看一下结果。好,大家来看一下啊,浏览首页的人数有400,浏览商品详情页的人数呢有318啊好了,那前两个字段我们就计算完了,当然大家要注意一下啊,我们现在是不是只计算出来了最近一天的值啊,没错吧,我们还有最近七天以及最近30天的值要去算,没错吧?那这儿怎么去处理呢?啊,其实跟之前的处理逻辑还是一样的啊,那同样呢,是使用explore的函数将咱的数据呢,给它扩张为三倍啊,然后呢,我们再分别去过滤最近一天,最近七天和最近30天的数据,那之后呢,我们再分组进行统计就可以了啊,那现在呢,我们就把它改一下啊,那首先我们需要改哪儿,是不是需要在这个位置加上一个letter view后边的ex explod。
06:20
没错吧?来来,括号里边呢,放一个arra a里边的三个元素分别是一,哎,然后七,然后呢,30,没错吧?那后边来个tmp is,哎,Re this das,没错吧?那接下来过滤条件是不是得改一下对吧?那这个呢,我们需要给它改成大于等于,没错吧?然后大大等于啥呀,是不是应该是data I的,然后当前日期放进去减一个recent date完了之后呢,是不是再加上一个一啊啊,OK,那这部分数据我们就已经得到了啊好,那接下来我们就可以按照recent this进行分组统计了,没错吧?OK,那最终的结果当中,诶,我们期望它有一个recent this,没错吧?来,给它放在这儿CRV啊,那这儿要想有death,那下边是不是得分组啊,没错吧,那我们就group by这个recent death啊,那当然,这儿要想对recent death进行分组,那就得保证T1里边得有re才行,没错吧,那就在这儿呢,我们需要把re放放进来,然后大家要注意啊,我们从DWD配置。
07:20
或这张表当中选择数据的时候是不是分组了,没错吧,那所以说这儿要想有recent代词,那下边咱是不是就得按照recent代进行分组才行,没错吧,这个一定要注意一下啊好了,那现在呢,我们就把诶最近七天和最近30天的数据考虑进去了,那接下来这个circle我们再执行一下,看一下效果什么样的走你。好,那现在我们是不是就同时计算出来了,最近一天最近七天,还有最近30天的值啊啊,然后大家注意一下啊,你会发现后边咱的值是不是都是一样的呀,没错啊,那为啥最近一天,最近七天,最近30天的值都是一样的呀?诶给大家解释一下啊,咱们现在统计的是什么值,是不是与页面浏览相关的值啊,啊,那与页面浏览相关的值是不是来自于用户行为日志,没错吧,那咱的用户行为日志呢,是没有历史数据的,那所以说咱现在是不是只有一天的数据。
08:12
没错吧,那所以说实际上我们现在的最近一天,最近七天,还有最近30天是不是都是最近一天的值啊,啊,所以他们是一样的啊,这个大家理解一下就可以了啊OK,那到目前为止呢,我们前两个指标就算是完成了,好,那我们再来看剩下的三个啊,剩下的三个呢,分别是card count,加购人数,Order count,下单人数,Payment count,支付人数啊那我们现在呢,仍然是先统计最近一天的诶,加购人数,下单人数以及支付人数啊好,那刚才我们已经分析过了啊,这三个字段应该来自于哪张表啊,是不是应该是DWT层的user topic呀,没错吧,这个其实比较简单,来我们一起写一下啊,来,首先我们select,然后from。DWT层的user topic,对吧?啊,完了之后呢,我们需要获取它最新的一天的分区,2020杠零六杠幺四,没错吧?那接下来呢,我们就可以分别去统计这三个人数了,那怎么去统计啊?诶,仍然是使用sum if就可以了啊来sum sum什么呢?诶来我们先统计加个人数应该是什么?Cart last e count,这是啥?是不是就是每个用户的最近一日的加购次数了,对吧?如果这个大于零,那我们就记个一,否则就记个零,那这样一来,我们得到的是不是就是最近一日的加购人数啊?
09:27
没错吧,那我们CTRLDCTRLD,那我们把card count改成older count,是不是就能得到下单人数,没错吧?来older count,那同理,诶,改成payment count,咱是不是就能得到支付人数啊,对吧?Payt好了,那现在我们就分别统计出来了最近一日的加购人数,下单人数以及支付人数了,那那当然呢,我们现在统计出来的也是只有最近一天的,没错吧?那那我们同样要考虑最近七天,最近30天,那这个应该怎么处理呢?那这个的处理方式呢,与前面两个字段相比就有点区别了啊在这儿呢,我们需要这样处理啊,首先我们也需要使用explore的函数将数据是不是扩张为三份啊,对吧?那分别是一、二、三这样的三份啊,但是呢,这三份数据啊,都是一样的啊,那都是哪个分区啊,是不是都是DWT层user topic6月14号的分区啊。
10:18
啊,那这三份数据不同的地方在哪呢?诶,不同的地方啊在这儿啊,第一份数据我们要获取的是最近一日的统计值啊,也就是最近一日的加购次数,最近一日的下单的次数,以及最近一日的支付次数,那另一份呢,哎,另一份需要获取的是最近七日的累计值啊,那还有一份呢,就是最近三十日的累计值,OK,那我们得到这样的三份数据之后呢,诶,我们再进行分组统计就可以了啊好,那接下来咱把这个思路呢去实现一下啊,首先我们先获取咱所需的这样的三份数据啊,来咱select,然后呢,From d wt user topic,分区呢,仍然是2020杠零六杠幺四,这个没有问题,对吧?然后我们需要使用let review ex prode explode,里边呢放一个arra啊,里边呢,元素分别是一,然后七以及30啊,后边TP is啊,Recent this。
11:13
没问题对吧?好,那现在咱数据呢,是不是已经扩张为三份了呀,对吧?然后我们要做的呢?诶就是分别去选择最近一日,最近七日,还有最近三日的统计值,没错吧?那这怎么选啊,是不是又得使用case啊,对吧?啊好,那现在我们先把recent days选出来啊,Re recent days之后呢,咱是不是就得用case one了对吧?来case,那我下边呢先写一个end啊来,我往上1.case什么呀,应该是问recent this re啊re recent this啊如果等于一,诶那我就返回啥,诶我是不是就返回最近一日的加呃这个加购次数啊,对吧?啊,那如果等于七呢?诶,那我就返回最近七天的,诶30就30对吧?那在这儿呢,咱直接改就行啊,这个改成七,那后边呢也改成七啊那这下边这两个位置呢,都改成30,是不是就可以来,我给他一起改一下啊,这个改成30。
12:05
好,那现在我们就分别得到了最近一期三十日的诶加购次数,那咱们来一个cut count这样的别名啊,OK,那我们需要再按照同样的办法呢,去分别获取诶下单次数以及支付次数,没错吧?啊好,那现在我们直接复制一下,然后改一下就可以啊来,CTRLC拿过来。在这里边儿呢,我们需要把所有的card是不是都改成这个order啊,对吧,来来or DR来下一个。O啊,那还有一个,哎,这个是O。好,没问题对吧,那当然这个别名呢,我们也需要改一下,这个也给它改成older count OK,那我们再按照同样的方式呢,诶把payment count也给它选择出来。CTR为好,最后一个逗号咱就不要了啊,那在这呢,我一起改一下来order order order点错地方了啊,点这来这个也改一下,好,同时给它改成p ment,那现在我们是不是就分别选择出来了最近一日,最近七日以及最近三十日的累计值啊,对吧?那也就是现在我们这三份数据就已经拿到了啊OK,那接下来咱就可以按照recent date进行分组统计了,那好,那现在我们把它操作一下啊,这个当然得起一个别名来括号,哎,CTRLV,那在这呢,我给它起名为T1吧,啊来,往前推一下,这就是T1。
13:26
好,我们继续啊,现在我们就可以select from t1了啊,那OK,那select from t的时候,咱是不是得分组统计对吧?按谁分组,是不是就按照recent this分组啊,没错吧,OK,那当然这个计算逻辑呢,跟我们计算一天数据的时候逻辑是一样的啊好,那我给它放过来,那只不过呢,我们需要把里边的字段改一下,对吧?啊,咱们需要改成哎,Card count,呃,然后呢,改成older count,这个呢改成payment count。啊好,那在上面的我们再选择一个recent this啊recent this,那我们现在呢,就能分别统计出来最近一日的,最近七日的,以及最近三十日的下列人数了,啊好,那现在这个S我们执行一下,看一下结果。
14:09
好,大家注意观察,我们现在是不是已经分别得到了最近一期三日的计算结果呀,好了,那这三个字段我们也就完成了,那最后呢,我们就只剩下两步工作了,第一步join,第二步insert,对吧?啊,OK,那接下来咱们操作一下啊来,我们首先给这两个子查询分别起个别名啊呃,这个子查询咱现在是不是就不需要了对吧?来,咱给它删除,我们先给第一个子查询起个别名来,CTRLX放到括号里来,CTRLV,那我们把它往前推一下啊来,走你那这个咱们起个名叫什么呢?就叫做呃,配置吧,那下一个呢,我们给它个名字叫做Co吧,Cart o payment对吧?来,CTRLX放到括号里来,把这个分号删除啊,放到括号里。来回车CRV来,我们把它往前推一下。来开一下好,那这个呢,就叫Co Co啊,Co card order payment好,那它俩呢,需要做一个join啊来goin啊join,那连接条件应该是啥呀?连接条件是不是应该是recent this啊,对吧?啊,那所以说我们在这应该是配置点啊,Recent this re c DS应该等于谁co.recent this。
15:21
没错吧,那现在我们就可以从中选择咱们所需的最终结果了,来我们select,然后呢,From,然后大家要注意一下啊,我们要选择的话,这些是不是都得有别名才行,那我给它加一下啊,那这个呢,我们就叫做呃,Home count home me Co,那下面呢,咱叫good detail countt good,那detail detail,没错吧,那接下来继续啊,那在这儿呢,它就叫做car count,来走。那下面呢,叫做older。来这个往前对一下,那这个呢,就叫做payment count,来走好了,现在我们就可以选择咱所学的字段了啊,那第一个呢,就是recentes,那下边有两个对吧,咱选谁都可以啊,然后下一个,下一个呢就是home count啊,再往下呢,就是哎,Good detail count,然后呢是哎,Car count啊,Older count,还有一个诶,Payment count,好,最后呢,我们再给它补上一个DT啊DT呢,就是常量值就行,2020杠零六杠幺四啊,没问题对吧?啊好了,那到目前为止,我们就拿到了咱所需的所有字段了啊,最后我们再来一个insert啊,Insert orright table ADS,这应该是呃,User action没错吧,然后下面呢,诶,Select星from as user action,最后呢,再来一个union啊好,那到现在呢,漏斗分析这个需求我们就全部结束了。
我来说两句