00:00
再把这个建表语句先拿出来,咱们根据它的要求啊,去求所需字段啊。拿过来,呃,放在这个位置LV啊,这个应该是借本语句啊。嗯,好,那现在看一下,其实在这边我们大家已经很明确了啊,在这我们要求的是当天的浏览首页的有多少人,浏览详情页的有多少人,加购物车的有多少人啊,下单有多少人,值是不是求这个人数对不对,那剩下的几个值呢?就是比值啊,那我们先来先分析分析啊,那这些字段我们应该分别出自于哪些表啊,来吧。来分析一下吧。啊,这个从哪张表来,这可就方案就太多了,对不对啊,这个方案太多了啊,那咱们来分析分析,先看第一个hongkong浏览首页的人数,这个咱说了按照设备ID算对吧,那这个最基础的你可以从哪求。
01:00
Dwd log,我能不能求出来浏览首页的有多少人?肯定可以,因为你里边是不是记录了最明细的页面浏览日志对不对,那谁浏览过什么页面我都记着呢,所以说你求浏览首页的人数肯定能求出来,只不过呢,就是你需要去进行过滤啊,去进行去重啊,是不是需要这些操作呀啊,这是得考虑的啊,那同理浏览详情页的人数是不是也能从DWD配置log里求出来啊,这个没错,或者说还可以从哪求啊?我记得咱们在DWS层,DWS层啊,DWS层的设备主题表里边。咱们有一个字段叫做配置states,记得吧,那张表里咱是不是记录了哪一个设备今天浏览了哪些页面啊,对不对啊,当然了,他浏览那些页面信息,我们给它放到了一个啊结构化数组当中,对不对?结构体数组当中没错吧?啊,应该还记得一会儿可以去看一下啊,那那张表里是不是也记录了,就是谁浏览的什么页面啊,那从那儿是不是也能统计出这俩值来,也可以啊啊,那是这俩字段,然后再往下他们仨。
02:09
啊,当天加过购物车的人数。啊,当天下单的人数,当天支付过的人数。啊,对于加购来说呢,这个咱们可以从哪学啊。最简单的动作日志里行不行?动作日志是不是记录了咱们所有动作包括谁,包括加购物车这个动作对不对?那从里边咱是不是就能够求出来加购人数啊,没错吧,那对于下单来说呢?注意啊,我们那个日动作日当中,我们没有记录下单和支付相关的操作,没记录啊,没有记录啊,这个得注意啊,为什么没有记录,因为下单支付呢,我们在呃买so当中是不是有相关的这个数据啊,所以那边咱们是没有记录的啊,那边没记录行,那所以这个呢,我们要想求的话,只能去哪求啊。订单实时表能不能求,订单时表里是不是记录了当天的所有订单,对不对,咱这咱是不是能够找到那个下单的人数啊啊怎怎么求啊,如果从订单时表里怎么求。
03:14
啊,订单师表是不是拿到当天分区单单分区里边是不是里边记录了当天谁下的单,谁下的单,那我们要求的是人数吧,你这里边只需要怎么做就行,对user ID进行去重,然后进行count,得到的是不是就是下单人数没错吧,那下边支付人数呢。是不是支付时表里边拿到当天分区,然后呢,对U的ID进行去重,然后进行统计得到的是不是就是支付人数没问题吧?哎,这个很简单的很简单啊啊你看啊,咱们这些值是不是都从DWD层求的,那我从DWS层能不能行?DWS。可不可以,其实可以,嗯,为啥可以呢?你得转换一下啊,现在我们去看一下DWS层的user action account,因为咱们说了啊,这仨字段是不是都用user算呀,那咱们得拿user action对抗。
04:07
诶,你看user I抗这张表里,我们记录了每一个user当天的登录次数,加购次数,下单次数,是不是后边还有支付次数啊,是不是记录的这么多呀?啊,我们现在要找的什么来着,是加购的有几个,下单的有几个,支付的有几个,对不对,那怎么着。咱们是不是可以根据这个logging count,呃,可以根据card count或者什么order count是否大于零,去判断这个人今天有没有下单,有没有支付,有没有加购物车,是不是啊,那所以说假如我现在呢,想去求加购物车的人数。怎么办呀,你就去找呗,只要cut count大于零的,我就给他记一个,对不对啊,只要order count大于零的,我就记一个,这是下过单的,只要payment康大于零,我就记个一,那这是不是都是也也是可以求出来下单人数,支付人数和加购物车人数的呀。
05:03
这也是可以从DWS也行,咱这从哪求啊。显然我们从DWS层求要好一些,为什么?因为DWS本身数据量要比DWD小,那再一个呢,在这块我们可以从一张表当中读一次数据,是不是求出来三个值啊,对不对,那就不用分开求了,那显然咱们从这要好一些啊,那所以最终我们这个大致的这个需求逻辑就定下来了啊,那这俩浏览页面的人数,我们可以从DWD配置log或DWS层的那个,呃,那个表里边去求,那下边这个三个人数呢,就是加购车下单和支付,咱们这个从哪呢?从DWS层求啊,是这样的啊好,那接下来我们先求上面这俩吧,啊先上面这俩啊,这俩呢,我们怎么求啊,咱们来分析分析。比如说我现在先从最原始的方案吧,先从DWD对吧,DWD是不是应该是最好理解的一个地方,那咱们从DWD求啊来,那怎么求呢?比如说先分开求,比如先分开求啊,比如我先求一个什么home count,那这个怎么求,那是不是应该是select,然后呢,From from哪DWD层的啊,Page置lo,那当然日期得过滤出来当天的数据啊,2020杠零六杠幺四啊,那我们现在要统计的是啥?是浏览首页的人数吧,那所以这咱咱得怎么样,是不是得把首页的浏览记录得过滤出来,那怎么过滤,And,咱是不是有一个配置ID字段,这个字段是不是就记录了你浏览的什么页面啊,对吧,只要配置ID等于咱们首页是home啊,Home面啊,只要等于home,那我就把你这个记录过滤出来,那这相当于过滤出来了所有的首页浏览记录对不对,那我们要求的是啥。
06:46
是。人数对吧,那咱们需要先干啥?先对人进行去重对吧,先格入y mid啊,先格入y mid,然后再求啥呀。是不是还得把M先求一下,这能直接看吗?还是不能,你要看了的话,你求的还是什么,还是每个组内的对吧,只要你分组了,你上面选的聚合函数那肯定是啥呀。
07:09
肯定是你每一个组的聚合对吧,我们要的不是每个组的聚合,那所以这儿呢,你可以你你得先选一个mid啊,然后呢,再以它作为一个子查询。是不是在进行count呀,它是X select,然后呢,诶from。啊来括号啊,CTRLV啊这个往前推一下。啊,这个往回放。好,那这个呃,完之后呢,我们再直接选一抗氢是不是就行抗乃清啊Co。好,那现在得到的就是首页的访问人数了啊,当然这块得写一个啥呀,别名啊,别名别别忘了啊,这个得有啊,写个T吧,那T1。搞定啊行,那他玩之后呢,我们还得拿谁拿那个。什么样来着,商品详情页对吧?啊浏览人数,那其实他俩的这个求法应该是一样的吧,对不对,拿下来只需要把这改一下。
08:07
把这个home改一下对吧,那商品详情页呢,它的那个页面ID是good detail啊good detail,那这个是不是完事了,这俩。啊,你会发现啊,这俩是不是很像很像,区别就在哪儿,区别就在啊这个外公理条件不一样对吧?那所以咱这呢可以怎么做呀,想办法就是一条so给它求出来对不对啊,那这块怎么一条so求出来呢?啊怎么求啊。来,那首先你要想一条circle求出来,那首先select,呃,From,哪张表是不是也是DW配置log呀,对不对?这个过滤条件怎么写啊,这过滤条件你还能直接就是配置ID等于谁吗?就呃,首先日期得先拿一下啊,这是必须得有的吧啊来CTRL。非啊,那后边and的,既然你要想一块统计,所以说你这个配置ID,你是不是得把这两个页面的浏览记录一次性的都过滤出来,对吧,所以说应该是配置ID等于就不行了,应该是用啥呀,In啊或者是等于也行,等于是不是就来一个二对吧?啊来二,那这会in in谁,In home homeme或者谁。
09:13
或者是good。Detail,那DT,好,那这样一来的话,我们相当于就已经从咱们这个页面日志表当中,把当天的首页的和详情页的浏览记录全部拿到了,对不对?啊,那现在咱们要统计的是什么?浏览首页的有几个人,浏览详情页的有几个人,那这边咱们应该怎么做啊,写错了是吗?呃,哪写错了啊,也号啊,这应该分开写啊,这个得注意啊,那这个没错啊,这个得改一下啊,得这么写啊,好,那首页记录拿出来了啊,详情页记录也拿出来了,那这会儿咱怎么做呀。那刚才咱们分开求的时候,是不是还对mid进行去重了呀,那这次咱是不是也得去重啊,对不对,那这个驱虫是不是也得是格入Y,那这个格入Y格入Y谁。
10:05
跟刚才一样的话,假如只格入y mid啊,那你说。行不行?行不行?如果只个如外midd是不是相当于对设备ID进行分组对不对?那你对设备ID分组,分完组之后会把什么样的数据分到一组?是不是同一个设备的浏览记录放到一个组里边去了?对不对,那放到一组之后,你还能体现出来谁浏览了什么页面吗?是不是体现不出来了呀?对不对。咱们这儿呢,你要想统计,想这俩东西一起统计,你必须得保留什么,必须保留谁浏览了什么页面,谁浏览了什么页面,是不是得把这个信息留着呀,这个信息留着一会咱们才能去统计,浏览首页的有几个,浏览详情页的有几个,对不对,那你这直接只按照mid分组,那是不是不管他浏览的是什么页面,是不是都给他分到一块去了啊,那这个怎么怎么办呢,这块。
11:03
怎么办?我们应该按照谁驱重啊?应该按照midd加配置ID进行驱虫,那相当于驱虫到什么效果呀?如果说我同一个设备在同一天里边浏览了同一个页面,如果浏览了多次,那我需要给它进行驱重吧。对不对啊,需要进行去重啊,那也就现在呢,我们这个去完中之后我们选啥啊,我们选一个呃midd再选一个配置ID,那现在我们所返回的结果应该是什么形式啊。就是你返回的结果现在就能表示出来,谁在当天浏览过什么页面,我不管它浏览几次啊,就是相同的页面啊,不管他浏览几次,我只有几条记录,只有一条对不对啊啊,因为我们现在只是要统计谁浏览过首页,至于他到底浏览几次,咱们关心吗?不关心啊,不关心啊,那也是现在这个结果,就是今天的这个记录当中,谁浏览了什么页面,谁浏览了什么页面。
12:02
好,那到现在为止,我们相当于已经完成了聚拢了,那现在我们是不是还得去最终去数人呀,对吧,那许人数人怎么数,那是不是得以这个再作为一个子查询CTRLV。这我们选一个T1吧,啊T1那我们select select啊,然后呢。From。From,那from这回就可以怎么做了?对,这次就可以一起求了some if了啊,你看啊,Some if some if啥。如果说配置ID等于谁等于后,那我就记个几,记个一,否则就记个零,那这样一来我相当于得到的就是浏览过首页的人,对不对?那我这时候呢,再把home这个改一下啊,改成good detail。哎,那你得到的就是浏览过详情页的有多少人?诶,这就完事了。啊,也就这个需求呢,咱们最终其实前俩字段可以这么写啊,可以这么写,然后你看这这个mid是变灰了呀,对不对,其实这mid现在还有用吗。
13:09
其实没用了,你上面根本没选的没选的,这其实就可以怎么的怎么干怎么怎么掉啊,就不要它就行。能理解吧,其实咱们这这个mid其实没啥用,我们这最终因为你统计相对是啥呀。相对统计的是行数对不对啊,其实就行数嘛啊,所以这mid有没有无所谓的啊啊,但是有的呢,咱们好理解一些啊好,那这就是我们前两个字段的写法,那当然前面这种方式呢,咱们就不用带,不用再要了,给这干掉就行啊,好给它去掉。行,那前两个字段求出来了,那接下来呢是呃,Car count order count和payment payment count对吧?呃,这个这块这个字段名也有问题吧,他叫amount,对应该是啥,应该payment看看啊支付人数吧,啊来咱们选人数,这个刚才我们已经分析过了啊,他要求的话,我们直接去哪,直接去从DWX层的user action a account这两边的需求啊,那日期我们过滤一下,DT等于2020杠零六杠幺四。
14:09
啊,没问题吧,呃,然后呢,我们怎么求,怎么求加购人数,怎么求下单人数,怎么求支付人数。这不也是三个if啊,对不对,那咱们if if什么,如果说我的加购次数。大于零,那我就记一,否则就记零,那你是不是能够把加过购物车的人找到对不对,那依次往下改一下就行了,那这个应该是order count,那下边的是payment count,诶,Older。Count,那这个呢,改成payment count pay。好,那这样一来我们就能得到呃,加购人数。支付人数和下单人数搞定啊,那五个值是不是都拿到了呀,五个值都拿到,那剩下的事就简单了,剩下咱们干啥呀,是不是将这五个值给它拼,拼到一块儿啊对不对,那这个往一块拼,这个咋拼呀,这个。
15:01
这咋拼啊这个。啊,这个怎么拼。是不是join呗,对,这是不是可以join,但是有的同学这块就就就有点纳闷了啊,那我join的话,我是不是得有个join条件呀,对不对,你看这是求出来的俩值,这是求出来的仨值,那他们五个值要拼,横着拼在一块是不是得join,那join的话,那用哪个字段进行join呢?是不是没有字段呀。没有没有的话怎么办。没有的话怎么办呀。没有的话,其实都可以不写。嗯,当时这儿比较特殊,为什么可以不写呀,因为你你可以看一下这张表,我返回的是几条数据啊。咱这是不是直接聚合函数下边也没有分组吧,那是不是就是一条,那下边这个呢。也是一条吧啊,直接聚合下边也没有分组,是不是就一条,所以这俩子条线都是一条,都是一条的话,那它俩之如果不写连接条件,那就是啥呀,抵卡耳机,但是两边都是各有一条数据,那是不是就笛卡耳机之后还是一条啊对不对,那所以这块其实不写连接条都行,或者说你要想规范一点,想写上怎么做呢?我们可以给他补一个字段啊,你看咱们这是不是需要最终有一个DT啊,对吧,我们这就可以这样做,给每个子查询,我们都给他写上一个2020杠零六杠幺四啊,给它起个别名叫DT啊,那一会儿我们是不是就可以用DT字段形状了。
16:25
对不对啊,你也可以这样去做啊,这样处理一下也可以啊,啊,这是这俩子弹菌,那剩下的事儿呢,就是转到一起,然后呢,哎,该除的除一下就行了啊,那这个咱们就不再浪费时间啊,这个一点点写了,我们把文档上这个粘过来啊来大家来看一下。CTRLC啊,看一下这个circle能不能看懂啊。嗯。好,那大家来看一下是不是就OK了啊,那就这么求啊,就是照的方式就这么照啊,那当然它这个写法跟我这不一样,一会给大家解释一下它这个怎么写的啊,你看这。
17:02
嗯,看那看到没有,他这是他的第一个次查询,是不是求出来的那两个页面的浏览人数啊,那下边这个呢,这是啥呀?这是咱们那个下单支付和加购物车的人数对不对?那下边呢,它都是按照谁呀,是不是照之后呢,用咱们那个DT字段照的啊,然后呢,该选里边自段选里边字段,该比就比就可以了,这就是最终结果啊,这最终结果然后呢,接下来我们看一下啊,就是他这个子查询哪个子查询就这个啊,页面浏览人数的子查询就这个,你看他写的跟我们写的有啥区别啊,你看他怎么写的。看他这个能不能看懂啊,那它也是从哪张表里求的,也是DW的配置log对吧,也是从这张表里求的,那也从那边这张表里求,然后呢,这个分区字段是不是,呃,按照DT这个进行过滤啊,找到这个哎,14号的分区,然后呢,页面呢,也是拿到这俩页面对不对,这个到目前为止是不是跟咱们这个写法是一样的呀,然后下面就不一样了,它是不是也去重了格数Y了啊,也分组了,那它这个分组不跟我们一样吧,我们是按照设备ID跟页眉ID进行分组,那它是怎么分呢?
18:12
他是按照。设备ID分的啊,按照设备ID分的,然后你看一下他按照设备ID分,你想一想他会把什么样的数据分到一组里。会把什么样的同一个设备的所有的浏览记录分到一组里,对不对,然后呢,你看它上面做了一个什么处理啊。Class set把谁放到class set里边了,配置ID,那你说这个结果得到的就是啥呀?这个子查询得到的结果应该是什么?俩字段,一个是设备ID,一个呢是一个数组吧,这个反应是数组对不对?那这个数组里边装的是啥?装的是啥?这个数组当中应该装的就是每一个设备,那当天所浏览的所有的设备ID吧,对不对,这个你可以让他跑一下的啊,那我们让他跑一下,可以看下这个结果是啥样的。
19:09
应该是俩字段,一个是设备ID,一个是一个数独数当中就保存了,诶你看是不是设备ID,那这是啥,是他所浏览的所有的页面ID吧,首页详情页,首页详情页这个首页啊,那还有其他的页面能看懂吧?啊那这个完之后呢,我们接着往上走啊,那其实到现在看到这个结果之后,大家应该也能猜出来了啊,就是咱们下一步应该怎么做了,下步应该怎么做呀。比如说下一步我需要去找浏览过首页的人有多少个,浏览过详情页的有多少个,你说这块应该怎么做呀?下一步是不是就得看一下,看一看谁的这个数组当中有home,有home就说明他今天浏览过home。对不对,有good detail,那就说明他今天浏览过good detail,没错吧,是不是这个道理,那所以说你看上边怎么做的,往上看啊,来找到它那个查询,哎,在这。
20:05
啊,是不是select from这个子查询,这个查询就是咱们刚才看的那个结果吧,啊然后呢,他从里边选数据选的时候怎么选呢?看一下。啊,是不是也是用的sum if啊,那sum if里边用了一个函数啊,叫做a re contains a re contains这个函数呢,它的作用就是啥呀?就是判断一个数组当中是否有某个元素,它所需要的参数呢?是两个,第一个参数是一个数组,第二个参数呢,是你要判断的那个元素啊这个函数的返回值是什么呢?布尔类型true或者false,没错吧?啊,那你看一下这是不是就是正好去判断一下。数字当中有没有home,有home我就返回截然后一,否则就返回零,那这个得到的是不是就是浏览过首页的人,那这个就是浏览过详情页的人,是不是一样的道理啊,最终其实得到的数据是一样,结果是一样的啊,这是咱们,呃,文档当中的这个做法啊,文档中一个做法行,那到这儿呢,我们相当于这个需求,咱们就介绍完毕了啊,介绍完毕了好,那我把这个视频录一下。
我来说两句