00:00
漏洞分析呢,我们上午分析过了啊,我们要分析的是什么,我们要分析的是呃,每天访问这个咱们首页,也就是说那个咱们在这呢,就以启动作为,呃,咱们这个访问首页啊,就他俩是相当于是等价的,就启动之后就相当于访问的首页,OK,那访问首页的这个人数,然后再再加上加,加上这个加购物车的这个人数,再加上什么呢?再加上这个呃下单的人数,再有一个什么呢?再有一个咱们那个呃。呃,支付的人数是这样的啊,那我们要统计的是什么呢?统计的大家要注意,我们统计的是当天的这个行为,注意是当天行为啊,就是咱们漏分析是一天我统计一个,一天统计一个,所以说你要想统计当天行为,咱们应该去哪拿这个数据啊,去哪拿数据,你去DWT层呢,你其实是呃拿不到的啊,拿按按理来说在DWT其实也能也能拿到你这个,呃,当天的这个行为也能拿到啊,但这里边呢,我们有些值是拿不到的啊,比如说哪一个呢?大家来看一下啊。
01:03
呃,咱们现在呢,我们要计的是什么呢?我们要统计的是这个会员啊,会员首先有什么,加购物车的这个人数,还有什么呀,还有加这个,还有这个下单的人数,还有这个,呃,支付的这个人数,对不对,咱们要统计这些东西,那这些东西啊,呃,我们因为在这个DWT层的这个用户表呢,其实这个表建的其实有稍微有点问题啊,就刚才也分析过了,咱们我搜一下吧,CTRLF啊DWT。User,那这张表啊,大家来看一下。可F3F3啊,那比如说我们现在要统计的是什么,我们现在要统计的有来我这个拉着对一下啊。我们现在这个漏斗分析里边呢,我们要统计的有,呃,这个访问首页的,也是这个启动的,还有什么呀,还有这个加购物车的,还有下单的,还有支付的,我要统计这些这么多的人数,对不对?那咱们从DWT这张这张表里边,大家来想想我能够获取哪些值啊,能获有哪些支,首先呃,加购物车的你在这里是获取不到的。
02:09
你在这里获取不到,因为什么,因为咱这张表呢,刚才分析过了,这里边那个加购物车的那个行为啊,是不是那个事实没有没有在这张表里体现出来啊,其实应该是有的啊,应该有,如果都有的话,那我其实这这些值啊,我都可以从这张表里去出,其实是能出的啊根据什么,根据是不是根据最后一次下单时间,或者是去根据什么呀,根据什么,这个这个最最后一次支付时间,等于今天我我通我通过这些东西去过滤是不是就可以啊,就能过滤最后一次加购时间是不是也能过滤对吧?那其实是可以的,但是这边他们因为这张表里边没有加购物车这个事实,就是加购物车的人数在这你统计不了,这统计不了呢,那咱们怎么办啊,诶往回走呗,往DWS层呗,DWS层我们是不是有一个用户,也也有一个跟他对应的一个用户行为啊,那个表里是有这个加购车的行为的。那里边有,那里边有的,那咱们就去,诶那里边去找去那边去找啊是这样的,这是因为咱这个表的问题,导致咱这个需求呢,要去DWS层去拿数据,那OK,那后边有加购物车的,还有谁还有这个支付的,还有下单的,那下单跟支付我其实都能从这张表里取出。
03:12
都能从这张表里去出啊,那这边咱们这个文档上呢,呃,即便是下单和支付呢,但是他也没从这张表里出,他也也也是从DWS里去出的,也是从DWS去出的,为什么呢?因为你要是呃这个再给他分开的话,那这边呢,我可能需要写两个叉,两个circle口,还得干什么,还得进行join对吧?那呃,单独的拿一个这个谁啊单独的拿一个,呃购物车从DWS拿,那这个下单支付呢,从DWT拿,拿完之后这俩还得再join唤起来,那这个其实就更不好了,更不好了,所以说这边呢,咱们就统一把什么呀,把购物车下单支付呢,全部都放到了DWS,都去DWS去拿。都从那去拿啊,这是咱们这边这个需求大家要注意的地方,咱们都去DMS层拿的,OK,那说完这个,呃,加购物车,说完这个支付,说完这个下单之后呢,我们再来看这个所谓的这个首页的访问人数,也就咱们那个启动次数,启动的人数啊,就是当日启动过的人数,那这个东西咱们是按照谁去统计的呢?呃,这块呢,可能同学会想啊,诶,那我支付我这个下单,我加购物车,我都是按照用户去统计的,对不对,都是按照user ID去统计的,是不是啊,那我那个呃访问首页我是不是也应该按照这个诶user ID去统计呢?是不是也要按照它去统计呢?嗯,这边咱们不是这样的,为什么啊?呃,咱是这样,这个得跟咱们的业务系统有关系,业务系统就是咱们那个。
04:39
加VE那个那边是有关系的,呃,咱们大家都知道啊,我们这个一个电商系统,我们用户。要是想下单或者想去这个支付的时候呢,你你是必须得是登录状态呀。得是登录状态,但是加购物车这个事呢,就是有些这个电商平台呢,我加购物车的时候,我可以在也可以是这个未登录状态,我也能加购物车。
05:02
这是有些啊,但是还有一些这个电商平台呢,我们要求什么要求诶,你加购物车的时候也得是登录状态才行。是这样的,所以说咱们这个电商平台不同,我这个其实业务是有些许的差别的,有些许差别的啊,那这个电商系统就是咱们自己做的这个电商储仓啊,我们自己的业务系统,我们怎么定义的呢?我们就是这么定义的,就是说你想加购物车,那你也得是登录状态才行,也得是登录状态,那所以说加购物车以及这个下单以及支付,我们都按照这个user ID去统计,因为你必须得登录嘛,对吧,你不登录你是做不了这些这些业务的,OK,这是这个,那对于这个浏览首页来说呢,浏览首页也是打开APP来说,你是不是可以不用登录啊。对不对,所以说那它呢,我们就是按照这个设备ID去统计的,按照设备ID去统计的啊OK,那这里边呢,呃,你按照设备ID去统计,可能会有一些些许的这个小的误差,小的误差,这些小小误差呢,我们是能够容忍的啊,就比如说有同学说可能诶我这一个设备啊,我可能今天有俩这个什么用户去登录过,那这个东西咱们就不考虑了,就不考虑了,这些东西咱们诶就给它忽略掉,忽略掉啊OK,这是咱们这边给大家做的一个分析啊,那分析完之后呢,接下来我们就开始去,呃,做咱这个所谓的漏斗分析了,去拿好几个人数,拿完之后拼在一起,然后再进行各种处罚比较就行了。
06:21
那刚才已经分析过了,呃,那下单支付还有加购物车统一去DWS层去拿数,那我们去拿一下啊,把这个表拿过来。DWS。然后应该是有一个user action action de来。这个表呢,其实还是呃,这个其实这个也不太全啊,也不太全,呃,咱们这个跟用户相关的这个事实呢,其实很多对吧?呃,加购物车下单,这个支付收藏对吧,那个评价,其实这些东西都是跟用户相关的事实,其实都应该在这个诶用户的主题表里边体现出来,在这边咱建表的时候,因为需求没用到,虽然这个表没加上,但实际在大家在公司开发的时候呢,应该是加上才对啊,应该加上才对,那把这块知道就行了啊好了,那我把这个表肯定是CF3拿过来。
07:07
拿来之后,咱们从这这张表里边去拿我们想要的这些行为啊,好回到最底下,那我们现在呢,逐一的去拿一下来,Select select from from,咱们这个DWS层的要是有一个user count,因为我们要统计的是当天的这个漏斗分析,对不对,那所以说把当天的数据先过滤出来,DT,因为它是一个分区表,2020杠零,三杠幺零。好,当天数据过来出来,过滤出来之后呢,我们去统计一下,统计下什么呢?统计一下呃,咱们这个当天这个加入过购物车的人和这个下过单的人,以及支付过的人,注意啊,这边你得注意你你这块得怎么去做得怎么去做啊,大家还能想起来咱这张表里面的数据大致是什么样的吗?大家还能想起来吗?可能有同学想不起来了,那我现在我查一下吧,咱们来一个select星,咱们把这一天的数据过滤出来给大家看一下啊CC,然后放到这个,呃,还有当中执行一下回车。
08:09
大家来看一下啊,那这张表呢,它的数据是这样的啊,是这样的,你看一下咱们呢,就比如说这是什么呀,这是我们的登录,这是前面是用户ID,前面用户ID啊,那这边是这个呃,登录的次数,这什么加购物车次数,这是加购物车及额对吧?那后边可能会有一些其他的一些这个统计值,一些其他统计值啊,然后这里边儿我想跟大家说的是什么呢?你看这里边有一大堆的零。对不好多零对吧,那所以说我们什么样的才叫这个加加入过购物车,是不是你得加购物车次数得大于零才叫加入购物车,那什么才叫下过单,下单次数大于零是不是才叫下过单呢?对吧?所以说我想跟大家说的是这个啊,也就说我们再去统计你这个加购物车人数,以及这个呃下单人数的时候呢,你得怎么去做呀,是不是又得用到这个sum if了呀,对吧?来或者说你也可以怎么办,也可以写VR过滤条件,然后再去进行看,然后再照,也可以那么做,但这边咱们同意还是用sum if做。
09:06
来sum if,我现在首先啊,我先拿谁啊,我比如说我现在我想先拿一下这个加入购物车的人数,那怎么办?Sum if应该是啥?应该是加购物车次数吧,加次数大于零,那这样我就给你记为一,否则我就记为零。这样我统计出来的就是加入过购物车的这个人数,对吧,这块大家要能够反应过来啊,好,那现在给它写上some if应该是有一个CT car,就是加购物车的这个次数,诶,如果大于零我就记一,否则就记零,然后这边来个逗号,好,那这个已经搞定一个了,搞定一个之后呢,我们再去搞下一个啊。再就搞下一个,那下一个该谁了?该sum还是sum if下边呢,该这个下单下单的人数了吧,那下单人数同理,是不是应该是older count大于零我才叫下过单啊,大于零那我就记一,否则就记零,这个下单拿到接下来拿那个支付some if。
10:05
然后这个支付也就是payment count,诶大于零我就记一,否则我就记零。呃,G0好,那现在我们已经把加购下单和支付的人数全部拿到了,呃,拿到之后我们这还差一个值,差一个什么值啊,差一个那个,呃,访问首页的人数也就所谓的这个,呃,活跃的这个人数对不对?那那个人数咱们去哪拿呢?那个人数其实咱可以可以怎么办,咱们可以直接拿结果了,我们那个值可以不用统计了,因为什么?因为我们那个值是不是现在在这啊,就跟我们刚才求的那个谁一样啊,跟我们求的刚才那个呃,日活是不是应该是一样的值啊,对吧?那咱们那个日活是一张纸,就是咱们那个设备主题里边第一个,第一个需求和日活是一个值,所以咱们现在呢,直接跟它,诶join一下,拿到我想要的数据就行了啊,那既然要join,所以这个东西你得给它做一个子查询才行,CTRLX来来括号。回车,那CTRLV给扔进来,那它我们管它起个别名啊,这个别名比如说叫做这个user,这个act吧,这用户的一些行为,那下边呢,让他去join一下go draw哪张表呢,Join d打ADS吧,ADS然后呢,应该是UV count,这就是咱们那个ad SUV count这张表啊,那现在我们去看一下这张表里字段啊,咱们搜一下CTRLV。
11:24
呃,这个退一下,然后回车。好,那这个就是咱们这张表的字段CTRLCF3拿拿回来,拿来之后呢,我们这边应该需要的是哪一个值呢?我们来看一下咱们需要哪个值T,咱们所需要的值其实就是它就是这个所谓的day count当日的诶活跃用户数量,那咱们给他拿过来就行啊,那拿过来这边join的时候,呃,拿知道拿哪个了,那那join的时候我应该用用哪个字段进行join呢?这时候有点有点麻烦了,对吧,照样不上,那用谁比较照样比较合适呢?其实啊,咱们这用一个日期照样是比较合适的,对不对,都用当天的日期,那上面这个子查询是不是没有日期,那没有日期咱可以给他造一个日期啊对不对,直接写死就行,2020杠零三杠幺零,那后边空格来一个D期,诶咱们需要去起一个别名啊,因为一会要用到它。
12:14
好,那完事之后呢,下边他俩就能召唤上了来啊,就如那个叫做user action,这个有点长吧,咱们叫UA得了啊,就UV这个短一点,那这个叫什么呢?这个叫UVOK,那它俩照一下,一个是ua.dt等于uv.dt好,那它俩现在就能照上了,对吧?招上之后我们接下来去呃给他呃进行这个呃选我们选所需要的字段啊,选我们所需要字段,那OK,那这个选的时候咱们就得按照谁去选了呀,咱就得按照呃ADS层咱们这个漏斗分析这个建表语句去选了啊。来看看这个字段的顺序都是什么样的,咱照着这个选一下,F3钉在这个位置,咱照着选。呃,我CTRLV吧,过来那开始选了啊,咱们照这个选,这是我们ADS层这个漏分析的表,好,那首先select先不说选啥,先说from下边已经照往上了,那我们就放心的选就行了,咱第一个统计日期,统计日期呢,那这边可能有同学想了,诶我这是不是就恰好有一个统计日期啊对不对,那这个表里边是不是也有一个DT,都有DT,那这边你选的时候能直接写DT吗?那肯定不行,这边你选的时候他会他会懵。
13:27
就是这个circleq他会懵啊,这个DT我在这个子查询里边有这个字段,那下边这张表里呢,他也有这个字段,那我到底应该选哪个呢?对不,他就懵了,所以这边你必须你要想这么去选的话,必须得指定一个,比如选UV也好,或者选UV也好,因为他俩一样的嘛,啊,必须指定啊,好,那完了之后呢,下边第二次算给谁了,干这个总访人数了,总访人数其实咱们指的就是呃,这张表里边的那个day countt吧,对吧,所以咱们选一个uv.dayy day count把它拿过来好,那下一个是加入购物车的人数加购物车的人数是不是应该是他。
14:00
对不对?那它你现在怎么选呢?没法选,咱们得给它起一个别名,那总理下边的两个值都得起别名,那咱们就叫做car count吧,CT count,那下边这个字段呢,咱们叫做older count。再往下呢,叫做呃,Payment count。KY。CT,好,那现在开始选,那上面选的时候呢,需要带着这个别名啊,就带别名,其实这边你不带别名也行了,这边不带别名也行,为什么?因为这几个字段它是不是只在这个子查询里边去有啊,那这张表里边有这个字段吗?没有没有,其实你就可以不用写了,那他能够找到这个相应的字段啊,其实不写也行,那比如说咱们这样的我就不写了,CRT car count,然后逗号再往下给谁了,呃,该这个这个谁了,该这个访问到加购车的这个转化率了,其实就是一个比值呗,谁比谁应该是诶cart count去比上这个UAUV点这个对count。给他比上那就完事了,那这边啊,咱们呃,我们统一要求一下啊,就是我们后续我们可能会有好多这种就是百分比,好多百分比啊,因为我们后边要展示都是一个百分比就百分比,所以这样呢,咱们最好给它乘一个100,再乘个100啊,然后来个逗号,然后再往下,下边该谁了,该这个下单人数了,那就直接拿就行,Older count逗号,那呃,Order count完之后呢,是不是会有一个诶car count去除以一个order count呀,有一这样一个转化,然后逗号下边该谁了,该这个payment count了,Pay这个count,那同理,下边呢,应该会有一个呃,Older count。
15:33
除以payment count。文好,那这边咱们该比的东西就全部都这个比出来了,全部比出来了啊,那这个就是呃,咱们这个所谓的一个转化率,所谓个转化率啊,那上边咱们还是写一个insert吧,Insert into table as user action convert,诶转化好这个需求呢,咱们就诶完成了就完成了啊呃,然后大家可以看一下咱们文档上这个S啊。
16:01
C。这是咱们文档上的色核,文档上色呢,其实跟咱们写的,呃,基本上是一样的,只不过这个位置啊,你看他做这个比值的时候啊,呃,他在这块呢,做了一个强转对不对,那这个语法先介绍一下cast,诶,然后一个字段as,一个这个have当中的数据类型,那就可以把这个值给它转化成这种类型,这是这个cast as这个语法,这个大家应该知道,应该知道才对啊好,那这边我们有没有必要去做这个强转呢?有没有必要去做这个强转呢?反了哪反了呀,哪个反了,我们看一下。哪个反啊,最后一个反了是吧?啊对对对,最后一个反了,最后一反了,我们应该是谁比谁,应该是支支付去比那个呃下单才对啊,咱们这边确实反了啊,那这边要全反了,全反了那不好意思啊,我们再改一下啊,再改回来,那确实写反了,这个转化的应该是谁,应该是首先这边呢,应该是CTRLX啊先把下改过来啊,应该是呃支付去比这个下单,诶这个是对的,那下面呢,应该是谁,应该是下单去比掐购物车才对啊,那边全写反了,那我们给他改一下啊。
17:08
这个CTRLX好给它改过来啊,呃,来CTRLV,那上面这个呢,上面加购物车比这个,呃,访问首页的人数,这个没错啊,OK,那这边咱们改过来了啊,确实是啊,这个到时候大家自己做的时候也要注意一下,留意一下啊,好好了,那这个完了之后呢,我们再回过头来说文档上这个啊,看下这个,呃,刚才说的是这个语法,就是这个cost,有没有必要去写这个东西,呃,这个其实你大家看一下啊,咱们上面这张表,我们建表的时候,这几个比例都是什么类型,都是decimal类型。都是DEC类型啊,这个类型大家应该之前给给大家讲了吧,咱们这里边两个参数对吧,一个是十,一个是二,那这俩参数的意义是什么呢?首先先说十,呃,首先类型它是什么?它是那个小数,是小数,那十指的是什么?小数部分加整数部分一共十位,那二指的是什么呢?二指的是小数部分有两位,也就是说这么细血的话是保留两位小数。
18:03
保两个小数啊,那OK,那这边呢,他想做的是什么呢?把这个东西呢,给他强转成这种格式,其实没有必要强转,我待会跟大家说一下,没有必要强转,就是说如果说咱们建表的时候,我这个呃,表里边的字段是这样的,表里边字段类型是这样的,那OK,你下边这个select完之后啊,你往这个表里insert的时候,它最终我们存储到HDFM的数据呢,它会自动转化的。会自动转化的啊,就是说这边呢,是没有必要去做那个强转的,没有必要去做强转的啊呃,除非你想怎么办,除非你不往这个表里插,你要干什么,你要直接select,你直接select,那也就说直接看结果,这时候那你需要给他强转一下,那你看到的效果就是两位小数了,否则的话呢,诶它不是两位小数。是这样的,所以咱们这inser的时候呢,是没有必要强转的,大家把这个记住就行了,OK,那这个咱们就说完了啊,视频我录一下。
我来说两句