00:00
好,那接下来我们看访客主题的数据装载啊,那访客主题的数据装载呢,与用户主题比起来啊,稍微有点特殊啊,特殊在什么地方啊,我给大家解释一下啊,那首先大家得明确访客主题的数据来自于什么,是不是主要来自于用户行为日志。没错吧,那用户行为日志的特点呢,是一般情况下是没有历史数据的啊,那所以说没有历史数据,我们装载时是不是就不需要做初始化了呀,对吧?啊,那也是我们就不需要再区分首日装载和每日装载了,对吧?那也就是说首日跟每日装载的逻辑啊,都是相同的。那所以在这儿呢,我们就只需要写一个每日装载的词就可以了啊好,那现在我们来明确一下啊,它每日装载的逻辑应该是什么样的?诶,实际上跟用户主题每日装载逻辑是相同的啊,在这儿呢,我们同样需要先拿到四部分数据啊,哪四部分呢?那分别是DWT访客主题的前一日分区啊,以及DWS层最近一日的分区啊,那还有什么?还有DWS层七日前的分区,三十日前的分区啊,我们同样是需要这样的四部分数据啊OK,那这四部分数据拿到之后呢,我们需要给它关联起来对吧?啊,那同样第一个表和第二个表进行关联的时候呢,需要使用全外联啊,那第三一个和第四一个呢,需要与第一个进行left run左联,没错吧?那这样一来呢,我们就能得到一个这样的join结果。
01:26
OK,那接下来呢,我们就要从这个join之后的结果里边选择我们所需的每个字段啊,在这里边儿呢,我们同样有最近一日,最近七日,最近三十日和从开始至今的累计值,对吧?那最近一日的累计值应该怎么取啊?这个很简单,是不是直接从最近一日的分区里边取就可以了,没错吧,那最近七日呢?啊,最近七日咱是不是需要先拿到截止到前一天的最近七日的值,没错吧,然后呢,再加上今天的值啊,然后呢,再减去七天前的值啊,那这样一来我们得到的就是最近七日的累计值啊,那同理,最近三十日的值是不是也是这样来呀,对吧?先拿到截止到前一天的最近三日的值,然后呢,加上今天的值,再减去30天之前的值啊,那就完事了啊,他这个累积值呢,累计值跟之前也是一样的,对不对,先拿到原来的累计值,然后再加上今天的值,那我们得到的就是最新的累计值。
02:23
OK,那这个呢,就是我们DWT层的访客主题表的数据装载的主要逻辑,其实跟之前用户主题没啥区别,OK,那接下来咱就开始写circle了啊,那首先我们先获取咱所需的四部分数据啊,来第一部分,呃,咱select,然后符哪张表应该是DWT层的对吧?呃,Visit topic啊,然后注意哪个分区是不是应该是前一日的分区啊对吧?所以说咱这呢得DT等于啊,DT等于诶,Data,然后呢,把当前日期放进来,2020杠零六杠幺五,没错吧,然后得减一个一。啊,对吧,那当然在这我们需要获取到所有的字段啊,在这儿呢,我已经处理好了,咱们把它拿过来,CTRLCTRLC来放在这个select这。
03:06
好,那我们往前推一下啊,好,那现在我们就已经拿到咱所需的第一部分数据了啊,那我们现在呢,就给它起个别名啊,这个也叫old来来括号。CTRL位。来,这是咱所需的第一部分数据,那我们起名为old。好了,接下来继续走啊,我们获取D2部分,那D2部分应该从哪张表来呀?是不是应该是DWS层的visitor action抗呀,对吧?DWS层的访客主题啊,那在这儿呢,我们需要获取的是哪天的呀?是不是最近一天也就是今天对吧?比如2020杠零六杠幺五,没错吧,那在这儿我们也需要获取它所有的字段在这了,我也已经准备好了,我们拿一下CTRLCTRLC来拿过来ctrl v OK,那第二部分数据我们也就拿到了,那当然这儿呢,咱也给它把别名齐了啊,那这个呢,就叫做ED ago,跟之前一样啊,来,CTRLV。往前推一下,这个叫做edgo ago OK,那我们继续往下进行,那除了1d ago,是不是还得有一个7d ago,还有30D ago啊,对吧?那咱们把这个复制一下,再往下走,那首先我们需要把这个分区先改一下,这块呢,应该改成啥?DT等于data塔I的对吧?ADD啊,来括号啊,然后把当前日期放进去,后边再来一个减七,没错吧,然后把这个1d ago改成7d ago OK,那我们继续再来一个30D ago ctrl v,那这个减七是不是得改成减30啊,对吧?诶,减30,那下边的7D额ago改成30D额ago OK,这四部分数据我们就拿到了啊,那接下来呢,我们把它们关联起来啊,OK,我们先看第一个和第二个,第一个和第二个应该用全外联对吧?在这呢,For al join OK,那接下来我们写一下它俩的连接条件,应该是on什么呀?On o的点m mid等于等于谁等于ED和go.m mid对吧?点m mid啊,Midd啊,OK,那它俩就关联上了,那接下来呢,我们再用。
04:58
Left draw去跟第三和第四部分数据进行关联,对吧?那接下来继续啊,这个呢,就是left draw,那接下来往下走,那在这儿呢,我们把这个连接条件给它复制一下啊,来CTRLV啊,咱们应该把ED ago改成7d ago是不是就OK了啊,那接下来我们继续,那再往这呢,也是left draw,对吧?来,继续往下走,那在这儿呢,再来一个诶,On的连接条件,把这个ED扣改成30D扣就可以了啊行,那到目前为止啊,这四部分数据我们就关联上了啊,那接下来咱们从关联之后的结果当中去选择咱们所需的每一个字段啊,来咱们select,然后呢,From OK,那现在看一下我们这都需要哪些字段啊,就这么多,其实不是特别多对吧?啊,来,咱们CTRLC给它复制一下,然后定一下来,放在这儿来,继续往下走来我们从第一个字段开始啊,那第一个字段呢,是mid对吧,也就是设备ID啊,那这个值我们应该出自于下边的哪个字查询呀。
05:53
是不是该出自于第一个和第二一个对吧?第一个呢是old对吧?第二呢,就是那ED ago没错吧?那它俩的对应关系是不是应该是这种啊,在这儿呢,我们需要选择一个不为now的值出来啊,那应该怎么选,是不是就NVL就可以了对吧?那来写一下啊,来NVL里边呢,应该是ED_ago然后点mid对吧?完了之后呢,诶,应该是O的点哎,这个呃,Mid对吧?OK,那这样一来我们就能够拿到一个不为闹的mid了。
06:22
好,那接下来继续啊,那从第二个字段开始,到这个version code结束,这都是啥呀?是不是都是那些维度信息啊,没错吧,那这个维度信息应该出自于哪个子查询呢?咱们分析啊,其实也应该出自于第一个和第二一个子查询啊,那这两部分数据的对应关系呢?仍然是这样的,对吧?啊,那在这儿我们到底应该如何选择呢?啊,那当然我们肯定也不能选闹值对吧,咱得也得选一个不为闹的值出来啊,啊,那这个数据呢,其实可以分为三部分去看啊,咱们看诶咱们分为这样的三部分。咱来看一下啊,那对于这部分数据,那我们要选应该选谁?是不是肯定得选原来的值啊,没错吧,那这部分应为是闹嘛啊那我们再看这部分呢?这部分应该是啥?这部分相当于是今天的新增访客对吧?那我们肯定选谁?是不是肯定选ED ago里边的值,因为这儿它也是now对吧?那关键是这部分数据啊,它左右都不为no,那应该选谁啊?在这呢,我们就选择最新状态就可以了啊,所以在这其实也是NVL对吧?OK,那具体怎么写,其实就这样,NVL对吧?比如来一个edago ago,然后点啥呃,下一个字是brand对吧?那完了之后呢,诶,咱们再来一个呃,O点呃这个brand,哎,其实就这么写就行了啊,那当然后边呃所有的维度字段呢,都是这么写啊,在这我们就不再一个个写了啊,来,咱们把文档当中的内容拿过来,来给它放在这啊,CTRL为。
07:44
OK,那这些维度字呢,我们就相当于已经选择完了啊,那我们继续往下走啊,那下面呢,咱就只剩下这些统计值了,对吧?啊,当然这些都是与访问相关的统计啊,没错吧,那咱们一个一个看啊,先看第一个,第一个是什么?是首次访问时间啊,那访客主题的首次访问时间应该怎么求呢?
08:05
啊,就是咱们前面提到过啊,这个访客主题的首次访问时间呢,与用户主题里边呢,首次活跃时间是不是对应的呀,对吧?所以在这儿呢,我们去计算这个首次访问时间的时候,能不能参考一下用户主题的首次活跃时间呢?可不可以。啊,实际上是可以的啊,那我们就先来回顾一下用户主题,再进行每日装载时的首次活跃日期这个字段的获取逻辑啊,OK,那当时呢,咱是这样去分析的啊,我们说原来的用户也就是老用户对吧?在经历了首日的初始化之后啊,就全部都拿到了他们的首次活跃日期对吧?那所以说在我们后续进行每日装载的时候呢,我们就只需要关注当天新增的这部分用户是不是就可以了。没错吧,那当时我们是怎样去判断它是否是今天的新增用户的呀,我们是不是根据O的这个子查询,还有ED ago这个紫查群他们的数据对应关系去判断的呀?
09:00
没错吧,我们回顾一下啊,假如说这个子查询就是O的啊,那假如说这个呢,就是ED ago这个子查询啊,那它俩现在对应关系是不是就是这种啊,那我们当时怎么判断的啊,咱是这样去判断的啊,我们说这一部分数据是什么?是截止到前一日的全量的用户,没错吧?啊,那这一部分呢,没有包含在前一日的全量用户当中,那所以它是不是就是今天的新增用户啊,对吧?所以当时咱的判断条件其实就是这样的啊,如果old.id为no,那就说明什么?那就说明他是一个今天的新增用户,没错吧?那新增用户的首次活跃日期是不是就是今天对吧?那否则的话呢,也就是这部分用户呢,是不是就用的它原来的那个首次活跃日期啊,啊,没错吧,这个呢,就是用户主题在进行每日装载时的首次活跃日期的获取逻辑,好,那接下来我们就参考这个逻辑呢,去计算一下访客的首次访问日期,那也就是visit date first啊,对吧?啊,那其实我们去计算的时候啊,大体的逻辑就是这。
10:00
这样的,如果是老访客,那我们就去获取他原来的值,那如果是新访客呢,诶,我们就去获取当天的日期。没错吧,啊,那关键是如何去判断这个新老访客啊,那我们在这儿能不能也参考用户啊,咱们根据O子查询和ED ago这个子查询的对应关系去判断新老访客呢?可不可以啊,可不可以咱们来分析一下啊,那假定这个就是哎,Old,那这个呢,就是ED ago啊好,我们先来分下啊,我们先看这部分数据。这部分数据对应的是什么?是不是对应的是DWT层的前一天的分区里边的访客呀,那这部分访客对于今天来说是不是肯定都是老访客,没错吧?啊,那所以说这一部分访客的首次回运时间肯定都已经确定了,对吧?所以我们直接取原来的值是不是就可以没错吧?那接下来我们再来看这部分数据啊,那你说这一部分它对应的一定是今天的新增访客吗?诶,实际上是不一定的啊,为啥不一定,我们来分析一下啊,我们前面一再强调啊,我们说呃,访客的数据是来自于用户行为日志对吧?啊,那用户行为日志是不是一般情况下是没有历史数据的呀?那所以说呢,我们实际上是拿不到绝对的全量访客的,没错吧,也就是这个old呢,实际上它不一定是真正的全量,那所以在这儿呢,我们只能这么去说啊,假如一个访客它出现在old里边了,那我们就能够断定它是一个老访客啊,但是如果说一个访客他没有出现在old里边啊,那这时候我们实际上是不能断定他是一个新增访客的。
11:36
啊是这样的,那也就是说这一部分数据实际上可能会包含新访客,也可能会包含老访客。那当然这个老访客呢,肯定指的是那些诶,历史的访问记录没有被保存下来的访客,对吧。这个大家一定要注意一下,那现在问题来了啊,那你说这部分数据的新老访客应该如何判断呢?诶,是不是可以根据咱那个一字news字段去判断啊,没错吧,那如果说把这个一字拗等于零,哎,那它就是一个老访客啊,如果说一字new等于一,那它就是一个今天的新增访客。
12:10
没错吧,如果是新增访客啊,OK,那它的首次访问日期是不是就是今天,那如果是老访客呢?诶,老访客,咱这是不是也没有原来的纸啊。没错吧,因为他原来的历史记录是不是没有被保存下来,对吧,那没有被保存下来,那我们是不是就无从获取它真正的首次访问日期了呀,没错吧,那所以这种情况咱没办法,我们只能给他一个初始值了,OK,那这个呢,就是首次访问日期的计算逻辑啊。那接下来我们把这个circle写一下啊,那按照我们刚才的分析呢,在那一共需要两个判断条件对吧,一个是O的mad是否为now,还有一个呢,诶是判断一下一字news字段是等于零还是等于一对吧?所以在这呢,咱用if就不好使了,我们这用谁用kiss问啊来我们case,然后呢,先写一个N啊。好,那我们开始写问问什么问O点啊,Mid啊,Is not no啊,如果不为now说明它是一个老访客对吧?那我们就Z啥Z是不是获取它原来的值应该是visit date first没错吧,OK,那我们继续往下进行啊,那再往下呢,是啥?应该是when old.midd啊,Is no对吧?然后并且哎,ED ago点什么呀,Is new啊如果它等于E啊,OK,那这时候我们就认定它是啥,是不是认定它是一个今天的新增访客,没错吧,那咱就这这什么这当天日期,假定当天日期就是2020年6月15号,那就零六杠幺五。
13:36
没错吧,啊,然后呢,我们继续往下进行,那when old点啊,这个mid啊,Is now and and什么e ago ago。然后点is new,它等于零啊,那咱就啥,那这时候呢,咱是不是就无从获知它真正的首次访问日期了,对吧?那说没办法,我们就只能给它一个初始值了啊,比如说在这儿呢,我们就给它设为数仓上线的前一天的日期,也就是2020年诶6月13号对吧?OK,那这样一来的话呢,我们的首次访问日期就算是计算完了啊OK,那接下来我们继续往下进行啊,那再往下是啥?
14:15
再往下是不是末次访问时间呀,对吧,这个其实就比较简单了啊,这个还是之前那个逻辑,那如果他今天访问了啊,那他的末次访问日期是不是就是今天,那否则呢,否则是不是就用原来的值。所以在这呢,用个if就可以了啊,If ED ago,然后点mid啊,Is not no。啊,那这时候呢,我就给他付个什么值,是不是就把当天的日期付给他就行,2020杠零六杠幺五,那否则呢,那否则是不是就让他获取原来的值对吧?应该就是visit,然后那个date last对吧?OK,那这样一来呢,我们也就获取到了它的末次访问日期了啊OK,那我们再往下走啊,那再往下呢,我们剩下就是这些累计值了,对吧?有最近一天的,最近七天,最近30,还有从开始至今的累计值啊,那这些值的计算逻辑与用户主题里边相应的计算逻辑是完全一样的,所以在这儿呢,我们就不再演示了啊好了,现在我们拿一下文档当中的结果来,CTRLC,咱们粘过来放在这个位置。
15:12
好,那这就是我们剩余的几个字段的计算逻辑啊,你可以简单看一下啊,那这个呢,是不是在获取最近一日的对吧?这是最近七日,呃,最近七日这个呢是最近三十日,这个呢,是诶从开始至今的累计值啊,跟之前的逻辑一模一样啊,在这儿呢,我们就不再一点点看了啊好了,那现在呢,我们相当于就已经把我们所需的所有字段都选择出来了,对吧?啊,那现在我们就只剩下最后一步了啊就干啥,就是INSERT1下来,我们insert啊,Insertright,然后table d wt visit topic对吧?那后边呢,别忘了写这个分区啊,Partition啊,DTDT,我们这应该是写的哪个分区啊。啊,咱们现在假定的日期实际上是6月15号对吧?那所以说我们应该写到零六杠幺五的分区里啊,那当然呢,那如果说我们第一天装载的时候,那咱们应该把数据写到哪儿啊,是不是应该把数据写到14号的分区,没错吧?那当然了,大家不能光改这儿啊,是不是也得把下边这个四核当中啊,这个六月15的地方都改成六月14啊,对吧?啊,OK,那因为这张表它的首日装载跟每日装载的逻辑是一样的,所以S克呢,我们写一个就够了啊好,那这就是DWT层的访客主题表的数据装载。
我来说两句