00:00
好,那接下来我们看访客主题表的数据装载啊,那由于这张表它的数据来源呢,是咱的日志表对吧?那我们的日志表是没有历史数据的,对不对?那既然没有历史数据,那是不是这张表就不需要做初始化了,对吧?那也就是说我们就不需要再区分首日和每日的装载逻辑了,因为它每一天都是一样的,没错吧?那所以在这呢,我们只写一个词儿就够了啊好,那接下来我们分析一下它具体的装载思路是什么样的啊好,我们先来分析一下这张表当中的字段啊,那这张表当中的字段啊,绝大多数是不是都是维度字段呀,对吧?你看这些是不是都是维度信息啊,像这些维度信息呢,我们实际上是不需要做计算的,对吧?啊,咱是不是直接从日表当中拿过来就可以了呀,对吧?那也就是说我们这儿真正要统计的字段其实就这两个,一个呢是visit count,一个是配置啊OK,那现在我们再来分析一下这俩字段啊,首先第一个,哎,Visit count,哎,访问次数,那这个东西怎么统计啊。啊,其实咱们前面说过啊,这个字段是不是与用户主题表当中的登录次数那个字段是对应的呀,对吧?那只不过呢,登录次数统计的是注册用户啊,而什么呀,而V在的看呢,它是不是不关注是否注册对吧?只要访问应用了,我就需要统计在内,但其实它俩的取数逻辑是相同的啊OK,那接下来我看下一个,下一个字段是配置states对吧,页面访问统计啊,那这个字段呢,让我们统计的是每个访客在每一天啊访问页面的一些情况,对吧?那包括什么?包括访问的次数以及访问的时长,当然这个字段类型有点特殊对吧?它是一个结构体数组,那当然我们之前已经处理过很多次这个结构体数组了,对吧?大家应该知道怎么去处理了啊OK,好,那接下来我们就开始编写这个circle,好,那接下来我们先看第一个字段啊,就是visit count啊,那这个字段呢,实际上和用户主题表里边的login count他们的逻辑是很像的,对吧?啊那所以说我们在计算这个字段的时候呢,也是从DW。
01:58
Log表里取数,OK,我们打开它啊,那现在我们再来明确一下这个具体的思路啊,OK,那打开这张表之后呢,我们需要从里边去寻找last配置ID为闹的记录,对吧?啊,那这样的记录可以作为什么?是不是可以作为我们一次访问的一个标志啊,对吧?OK啊,那只不过我们之前在计算呃,Login count那个字段的时候呢,我们还得再额外的增加一个条件,哪个条件呀?哎,就是user ID不能为now对吧?啊,为啥呀?因为login count是登录统计,没错吧?那登录是不是只针对于注册用户啊,对吧?所以它必须得有一个UID那才可以,但是我们现在做的是访客的访问统计,那所以说我们是不是就不关注它是否登录,是否注册了呀?OK,那在这呢,我们只需要去寻找last page ID的数据就OK了,好,那现在咱们把这个circle写一下啊,来,我们select。
02:49
然后上DWD层的是不是配置lo呀,对吧?OK,咱们往下一点啊,那完之后呢,我们需要过滤一下这个数据啊,那应该是首先先用日期过滤一下,因为咱们这儿呢,不区分首日和每日装载对吧?啊每天都是计算当天的数据,好那所以威尔DT等于2020杠零六杠幺四,那接下来我们继续啊,还有一个什么,是不是还有一个last配置ID对吧?Is now啊对吧?好,那现在我们相当于就拿到了所有的访问标志对吧?那接下来呢。
03:18
接下来咱要注意啊,我们要统计的是每个访客在今天的访问次数,没错吧,那作业得干啥,是不是得分组,那我们就按照谁按照mid分组就可以了,因为咱们前面提到过啊,那我们访客呢,是用设备I去标识的,那所以葛入mid就可以了,那接下来我们继续,那上面呢,我们就可以选择咱们所需的字段了啊,比如mid啊,完之后呢,还有啥是不是再来一个抗得清,那现在我们就得到了每个访客啊,那它的访问次数是多少了。OK啊,然后我们再继续往下进行,大家要注意观察一下啊,那因为咱这个DWS层的宽表当中是不是包含这样的一系列的维度字段呀,对吧?那这些维度字段我们是不是也得从DWD层的日志表里边选出来呀,对吧?那所以在这儿呢,我们还没有结束,我们还需要再去选择一下这些维度字段啊好,我们先看第一个字段啊,就是这个brand设备品牌对吧?啊,那大家来看一下啊,在这儿我能不能直接把这个brand的字段选择出来,可不可以,那显然是不可以的,为啥?因为咱下边是不是有分组啊,分组之后我们上边能选择的字段是不是要么就是分组字段,要么就是聚合函数。
04:24
没错吧,显然这个brand咱是没有办法直接选择的啊,那怎样就能选择了呢?哎,比如说我在下边儿这个分组字段里边,我加上brand字段,那咱上边是不是就能选了呀。但是大家思考一个问题啊,我在分组字段里边加上一个brand啊,那它会不会对我们的计算结果产生影响,会不会,那会不会咱来分析一下啊,那首先大家先要搞清楚一个问题啊,就是一个设备啊,那它的设备ID是不是肯定是固定唯一的?没错吧,咱不可能出现一个设备对应俩设备ID啊,这不可能对吧,那OK,那继续那那一个设备,那它的品牌是不是也是唯一的。
05:02
没错吧,不能说一个设备有俩品牌,没错吧,那所以说我们的设备ID与设备的品牌,它俩之间是不是一对一的关系啊。没错吧,啊,那所以说咱们在这儿啊,我按照mid一个字段分组,那我得到的结果是什么样的。是不,每行数据是一个设备啊,没错吧?那我按照mid加B的这两个字段分组,那我们得到的结果还是什么呀?是不是还是每行数据是一个设备,没错吧?那所以说我们加上brand的与不加brand的,我们这个结果都是一样的啊,所以在这儿呢,我们没问题,直接这么写就OK了啊好,接下来我们看下一个字段,下一段是啥?下一段是model,对吧?设备的型号,那设备的型号与设备的品牌是一样的道理,那在这儿呢,我们可以先在下边诶加上一个model,然后在上面呢,我们把这个model给它选择出来,Mol。OK,那这个也就搞定了,那好我们继续往下看,再往下是啥?再往下呢是一字new字段,一字new是什么?是是否首次访问对吧?那这个字段呢,我们前面已经给大家解释过了,对吧?那大家现在要搞清楚的是什么啊,就是这个一字news字段咱是怎么来的,它是不是应该是诶,由我们某一个设备啊,它这一天的所有的日志当中的一字news的值聚合而来啊,对吧?
06:16
那接下来咱就来分析一下这个聚合的逻辑应该是什么样的,那首先我们先先搞清楚,那它这一天产生的日志里边一字六字的值都有可能是什么情况啊,有没有可能全是一。有可能对吧,那有没有可能全是零。那那显然也是有可能的,那有没有可能既有一又有零呢?诶,其实也是有可能的啊,那什么情况下会出现既有一又有零呢。而且很简单,卸载重装对吧?没错吧,那假如说哎,我是一个老访客对吧,那老访客那从第二天开始之后,那他的这个一字news是不是一直都是零的呀,对吧?那假如说在今天,诶我我我上我上午的时候我还在使用,没错吧,那我上午产生的日志是不是都是零。
07:00
没错吧,那到了中午的时候,我给他卸载了,然后下午我又重装了,那重装之后那是不是又变成一了。没错吧,那所以说我们,哎这个一字拗字段呀,可能会出现这一天里边既包含零又包含一的情况,那那所以这时候呢,我们就得来分析一下啊,就是咱这个一字拗的聚合逻辑到底应该是什么样的。那一共就这三种情况对吧,咱们来分别说一下啊,假如说全是一,那那这个没得说,是不是咱们聚合之后就是一呀,对吧,那如果全是零,那肯定聚合之后是不是也全是零啊。没错吧,那关键是既有一又有零,我们应该怎么处理?啊,其实既有一又有零,我们应该认为是几啊,是不是应该认为是零啊,对吧,因为只要有零出现,那我们是不是就意味着他应该是一个老访客呀,对吧。是这个道理对吧,那所以在这儿呢,我们具体的判断逻辑应该是这样的啊,全是一就是一啊,全是零啊就是零啊,那有零又有一,那我们就按零处理。也就是咱们其实能够得到一个这样的最终的结论,什么结论,只要我们这一天产生的日志里边啊,它有零,那我们这个聚合结果是不是应就应该是零啊,对吧?那你看是不是这么回事,只要有零,那我们聚合之后就应该是零,OK,那所以接下来呢,我们把这个逻辑呢,给它实现一下啊,那这个具体应该怎么写呢?
08:16
具体应该怎么写啊,其实很简单啊,我们可以怎么做,我们可以先将啊,这个设备啊,或者这个访客,它这一天产生的日志里边的一字news字段的值给它聚合到一个数组当中,那咱是不是有克对吧,而且能够去重诶,我聚合到一个数组里边啊,然后呢,我们再去判断这个数组里边是否有零元素,有零元素,哎,那我们就按零处理,否则的话呢,我们就按一处理。没错吧,OK,那接下来具体怎么写呢?大家来看一下啊,首先我们需要先给它聚合到一个数组里,对吧?怎么聚合是不是用class就行Co class完了之后呢,我们把一字news字段放进来啊,发来之后呢。我们需要判断一下啊,这个数组里边它是否包含零元素,大家都知道克莱塞的时候反回来就是一个数组啊对吧?那所以我们CTRLX,那怎么判断一个数组里边是否有一个元素呢?哎,在这呢,我们可以用这样的一个函数AA啊完之后呢,这个啊,那这个函数呢,我们需要传两个参数,第一个参数,哎,就是咱们要判断的这个数组,第二参数呢,哎,就是咱要判断那个元素啊OK,那现在呢,我们,诶把这个谁放进来,把这个数组放进来啊,完了之后呢,诶,我们再把后边我们需要判断的这个元素给它写上,咱应该是几啊,咱判断是不是应该是零啊,对吧?啊那这样一来的话,这个函数它返回的结果是什么样的呢?如果这个数组里边包含零元素,那它就会返回true,否则的话呢,就会返回false啊,那所以在这儿呢,我们还得再嵌套一个if对吧?来咱们CTRLX来if一下。
09:50
来,如果数组当中包含零元素,那我就返回几,我是不是就返回零,否则的话呢,我就返回一啊,OK,那这个一字六字段我们就搞定了,OK,后边我们给它起一个别名啊,一字拗好了,那这个字段完之后呢,我们继续往下进行啊好,往下走,那再往下呢是啥啊?再往下呢,还有几个维度字段啊,分别是什么,China OS area code,还有什么version code。
10:16
啊,那这些字段呢,它跟我们前面讲的这个brand和model那就不一样了啊,为什么不一样,咱们来分析一下啊,刚才咱们前面提到过啊,就是我们的品牌以及型号跟我们的设备是不是肯定是一对一的呀,对吧。没错吧,也就是我一个设备,我是不是只有一个品牌,只有一个型号,没错吧,但是下边这个壳就不一定了。没错吧,那我比如举个例子,比如说系统,以系统为例啊,那我这个系统是不是可能会升级,没错吧,那所以说我这一天产生的日志里边啊,我一个设备它可能有两个系统。没头吧,上午是十个系统,下午呢,我可能升到11了,是这个道理吧,那同理,那下边这个L扣的地区呢,是不是也可能在一天的产生的日志里边,一个设备对应两个地区啊,对吧,我上午在上海,那下午呢,我去我去北京了,没错吧,那所以这样呢,诶跟前面的两个字段就不太一样了。
11:11
那那不一样的话,那这边我们应该怎么去处理呢。也就是在这儿呢,我们需要考虑到一个问题啊,就是什么问题,就是一个设备对应多个维度信息。回头吧,对应两个系统,对应两个地区,那这个应该怎么处理呢?大家来看一下咱这咱是怎么处理的呀,我们是不是把这多个系统或者是多个地区,我给它放到了一个数组当中了呀,对吧?哎,那这样一来我们就都能够保存下来了。那具体后续我们需要用的哪个,咱们再去选择哪个就可以了啊,OK,当然这边,呃,咱们实际上去写filter就比较简单了啊,咱只需要怎么做就行了,是不是只需要来一个side,把它们放到数组里边就OK了呀,对吧?OK,那接下来我们把这几个字给它写一下,那分别是China OS ever code,还有version code,对吧,咱一个来啊来,我们给它选上。首先哎,克莱Co。
12:00
来,我们先把谁放进来,先把这个China放进来,那接下来我们继续往下进行,CTRLDCTRLDCTRLD,咱一个来啊下一个谁下一个呢是OS对吧,那我们就把OS放进来CR位,那再往下是谁,再往下是error code的来CTRLZ放在这,那继续,那还有一个是word来应用版本,来CTRLV,诶给它拿过来就可以了,好,那现在我们在为每一个字段呢,都给它起一个别名,诶这个是CTRLV,来来CTRLC往后走,CRLV。来,那这个也拿过来。好了,那这几个维字字段呢,我们就全部都获取到了啊,那OK,这个count芯呢,我们也给它起一个边名,这个叫做visit count来crl way好了,那现在我们就已经把呃,这些字段给它诶拿到了啊,那现在这个表呢,我们是不是就只剩下一个字段了呀,对吧?就是这个配置stas,那接下来我们再把它求一下啊啊,我们现在来明确一下啊,这个配置states它具体的取数逻辑应该是什么样的?那大家能看到啊,那这个后边的资段类型是不是一个结构体数组啊,对吧?那我们处理这个结构体数组的逻辑应该是啥样的呀?首先第一步我们需要先去计算这个结构体数组当中的字段,对吧?把这个字段都计算出来之后呢,我们需要先将这些字段是不是使用name的start函数,诶,封装到一个结构体当中啊,对吧,那之后呢,诶,咱们再使用class这个函数,将这些结构体给它聚合到一个数组当中,是不是就完事了。
13:27
啊,OK,那接下来咱们就开始啊,那首先我们先计算结构体当中的这些字段啊,那我们再来明确一下,这个结构体里的字段分别都是啥啊,分别是配置ID、配置count during time,那这些都是啥含义呢?配置ID就是浏览的页面ID,这个不多说对吧?那配置count呢,实际上指的是哎,某个设备浏览这个页面的次数,那during time呢,指的就是某个设备浏览某个页面的时长啊,那所以说其实我们这儿要求的具体内容是啥呀?具体内容实际上是每个设备浏览每个页面的次数以及时长。
14:00
没错吧,那所以说咱们这是不是有两个每个呀,一个是每个设备,一个是每个页面,那我们统计的时候是不是肯定得按照设备加页面进行分组啊,没错吧?OK,那接下来咱们就开始去做这个相应的统计啊好们往下一点,嗯,那首先我们需要先明确啊,咱们做页面访问统计,那我们应该从哪上门取出,是不是肯定是DWD配置log呀,对吧?我们首先需要先拿到今天所有的页面诶浏览记录对吧?那所以在这儿呢,我们只需要过了一下分区就可以了啊对,2020杠零六杠幺四,没错吧?那接下来呢,我们需要进行分组统计,对不对?按谁分组,是不是按照设备ID加上这个页面ID进行分组啊对吧?那后边呢,直接格by midd,那再加上一个这个配置ID,那OK,那这样一来我们会把什么样的数据分到一组啊,咱是不是会把同一个访客,那浏览的同一个页面的记录是不是分到一组里,对吧?那接下来我们要做的呢,就是分别去统计呃,它浏览的次数以及浏览的时长啊OK,那现在我们开始选择,那首先我们先把这个mid啊这个选出来,再把配置ID选出来,那接下来呢,诶,我们。
15:00
开始统计了啊,那先看次数,次数怎么统计,是不是直接看的就可以,没错吧,因为一行记录是不是就代表浏览了一次啊,对吧,那有几行就有几次呗,啊看芯就完事了,再来下一个,下一个啥是不是浏览时长啊对吧?浏览时长那应该怎么求呢。啊,其实很简单,大家来看一下啊,我们看一下这张表,这张表当中呢,实际上是有一个during time字段的,对吧?这个指的是啥?这个指是不是就是浏览该页面的时长啊,对吧?那我们把所有的页面浏览记录里边的during time给它加起来,那是不是就得到了总的浏览时长了,没错吧,所以在这儿呢,直接sum求和就可以了啊诶,萨姆求和,把这个诶during time给它放进来啊好了,那现在我们就把这两个字段都求出来了啊,求完之后呢,我们分别给它起一个别名啊,那一个呢是page支count,一个是诶during time对吧?那咱们给它起一下。哎,这是配,那这个呢是duringtime,嗯,OK,那字段我们就学好了,那接下来我们要做的应该是啥呀?接下来是不是也咱应该把这几个字段给它封装到一个结构体当中啊,没错吧,OK,那现在我们来一个子查询,来我们CTRLX。
16:03
来往下走CTRLV好,马上往前推一下啊。好,我给它取个别名,比如就叫T1吧,啊好,那现在我们一。Select,然后from,然后选择我们所需的字段,第一个mid选出来啊,那剩余的三个字段是不是应该封装到一个结构体当中啊,对吧?那这时候我们就可以使用name的script了,对吧?然后我们需要做的呢?是不是就是依次传入我们的K和V就可以了,对吧?啊,也就是字段名和字段值,好那现在我们第一个应该是配置ID对不对?来,咱拿过来,诶,CTRLV,那后边我们继续啊,CTRLV,那第二一个应该是配置count,对吧?CTRLV,那拿过来,那来CTRLV后边呢,也是CTRLV,好,下一个应该是during time对吧?那CTRLC拿过来。啊,那这个呢是它的,呃,来来个逗号啊,逗号这个呢是它的字段名,那这个呢是它的字段值,OK,那这个结构体我们是不是也已经拿到了呀,对吧?那好,那它完之后呢,我们就只剩下最后一步了,最后个干啥?最后一步我们是不是应该将这个结构体来给它聚合到一个数组当中啊对吧?那所以下边呢,我们需要用到是不是分组啊对吧?格group by谁是不是只要格入格如BY这个mid就够了对吧?啊好,那现在我们再把这个namedru返回的结构体放到collect当中啊,Collect当中啊,OK ctrl v好了,现在我们这个结构体数组就已经拿到了啊OK,那这就是我们最终得到的这个配置state啊这样的一个字段啊好了,那到现在为止啊,那这张表咱们所有的字段就都已经拿到了啊,那接下来我们要做的应该是啥呀?是不是就将这两个子查询给它照在一起就可以了呀,对吧?那这样一来就能得到咱们的最终结果了,OK,那现在我们快速的去做一下啊好,那现在我们先把第一个给它起一个别名来,咱们CTRLV拿过来。
17:48
啊,那这个我们起名为比如说T0吧,啊,咱们就T0,那下一个哎,我们继续。来,CTRLX。来CTRLV拿过来,那在这边呢,我们给它起个名字叫做叫做T2吧,刚才已经有T1了,对吧?好,那现在我们把T0和T2给它装在一起啊,那当然这个直接使用照就可以啊,那下边我们写一个连接条件啊,应该on什么on t0点应该用谁是应该用mid作为关联条件。
18:14
没错吧啊,因为我们需要把同一个访客诶,它的行为是不是汇总到一行里边啊,对吧?那T0.mmid等于T22M mid,那OK,那完事了,那接下来我们就可以从里边选择咱所需的所有字段了啊,来select from啊,然后呢,是所有字段我们需要去处理一下啊,在这我已经提前处理好了啊,咱们只把字段拿过来,来CTRLC,那完了之后呢,放在这个位置来CTRLV好了,那现在我们就已经拿到了咱所需的所有字段了,当然这个m mid字段是不是有歧义啊对吧?在这儿呢,我们随便写一就可以,因为我们用的是join文,对吧?那比如说我这儿呢,用ATT0啊好了,那现在我们就已经拿到了所需的所有字段了,那接下来我们就只剩下最后一步了啊,就是将这个结果给它插入到呃,咱们的访客主题表里去就可以了,OK,那现在我们insert,然后后边的allright table应该是呃,Dwi层对吧,应该是visitor action,对T后边呢,我们需要写一个partition part里边写一个DT等于2020杠零六杠幺四。
19:13
那是不是就完事了?OK,那这就是访客主题表的数据装载。
我来说两句