00:00
好,那接下来我们看DWT层用户主题表的每日装载啊,我们还是先来回顾一下啊,他每日装载时的大致思路啊,来把这个PPT点开。好,大家一起看一下啊,那这个是DWS层的表,也就是咱的数据来源对吧?那这个呢,是DWT层的表啊OK,我们继续往下进行,嗯,好,那这些分区呢,是我们首日装载的时候能够拿到的分区,对吧?啊OK,那首日状载咱们怎么做的?也回顾一下吧,是不是咱们需要读到DWS层全面的数据啊对吧?然后呢,进行统一的汇总,诶把聚合之后的结果呢,给它放到DWT层当日的分区就OK了,对吧?这是首日装载啊,那接下来呢,我们再来回顾每日装载啊OK,那每日装载的时候呢?诶,我们是不是会得到一个新的分区,没错吧,就是这个15号的分区啊,那我们前面分析过啊,每日装载的时候呢,其实也可以像首日装载那样,对吧?是不是读取DWS全表的数据进行统一的汇总,把汇总之后的结果呢,给它写在哪,是不是写到咱们的15号的分区里就可以了。
01:00
没错吧,哎,但是我们前面也说了,这种做法呢,它的效率比较低,为啥呢?因为里边有大量的重复计算对吧?啊,你像这部分数据啊,咱们在前一天是不是已经算过了呀,对吧?啊,那今天我们要这样做的话,还会再计算一遍啊,所以最终呢,我们每日装载的时候呢,并没有采用这种思路啊,那咱们最终的思路是什么样的呀?诶,我们是这么分析的啊,我们可以先拿到截止到前一天的累积值对吧,再拿到今天的这个聚合值,之后呢,是把这两部分相加,咱就能得到截止到最近一天的聚合值啊。没错吧,诶,这是我们每日装载的大致的思路。但是这个思路呢,我们仔细想一想啊,你会发现它其实是有一定的问题的,那有什么问题呢?啊,实际上就是这个思路啊,它只适用于从开始至今的累积值。啊,你想一下啊,那从开始截止到前一天的累计值,然后呢,加上今天的聚合值,那是不是就等于从开始截止到今天的累计值啊,对吧?这个思路是没有任何问题的啊,但是呢,我们DWT层是不是还有最近N天的聚合值啊,对吧?比如说有最近一天,最近七天,最近30天,那对于最近N天的聚合值,那这个思路他还适用吗?
02:15
那显然就不行了,为啥咱们分析一下啊,那比如说啊,我先拿到截止到前一天的最近七天的聚合值啊,我再加上今天的值啊,那你说得到的结果它是截止到今天的最近七天的聚合值吗。那显然不是对吧,那这是多少啊,这是不是截止到今天的最近八天的聚合值了呀,没错吧,那所以说呢,这个思路我们还得再去完善一下啊,那接下来我们去看一下每日装载的具体思路啊,那下边呢,还有一个PPT,我们把它找到打开一起看一下啊。啊好,那这个就是DWT层每日装载的具体思路,来我们一起看一下。OK,大家现在看到的内容啊,就是我们每日装载的数据来源啊,实际上我们真正进行数据装载的时候呢,诶,我们需要读取四部分数据啊,那接下来我们把这四部分呢,分别来看一下啊,先看第一部分,第一部分是啥呀。
03:10
第一部分呢,实际上是DWT层的前一日分区啊,那当然前一日分区里边存储的就是啥,是不是就是截止到前一日的累积行为啊。没错吧,OK,那具体看一下啊,里边有最近一日的最近七日,最近30以及从开始至今的累计值,没错吧?那我们继续看第二部分内容啊,第二部分内容来自于哪?来自于DWS层的最新一天的分区,OK,那具体内容是什么呢?那具体内容就是维度的ID啊,以及每个维度对象哎,当日的汇总行为。啊,OK,那对应到我们的用户主题呢,那这就是user ID对吧?那这就是每个用户当日的汇总行为啊OK,我们继续看第三部分啊,第三部分呢,来自于哪儿?也来自于DWS层,只不过呢,是七天前的那一天的分区啊,注意这不是七个分区啊,这是七天前的那一天的分区啊,一定要注意啊OK,那具体内容是什么呢?哎,也是维度对象的ID,以及每个维度对象在七天前那一天的汇总行为。
04:13
啊,OK,那我们再看最后一部分啊,最后一部分呢,也是来自于DW层啊,哪个分区呢?30天之前的那一天的分区啊OK,那具体内容是什么啊,也是维度的ID啊,以及每个维度对象在30天之前那一天的汇总行为。OK,那这就是我们所需的四部分数据,好,那拿到这四部分数据之后,我们需要做什么操作呢?啊,首先我们需要先把他们join起来啊,OK,那我们具体的join方式应该是什么呢?我们来看一下啊好,第一个表跟第二个表采用的是全外联啊,这儿为什么要采用全外联啊,给大家解释一下啊,大家都知道咱们现在要做的是啥,是不是每日装载对吧?那这部分数据咱们一会儿算完之后,是不是需要放到DWT层当日的分区里边啊,对吧?那我们前面说过DWT层每个分区存放都是啥?是不是都是截止到当日的全量的维度信息啊,对吧?
05:05
啊,那所以在这儿呢,为了保证全量,我们是不是既要把原来的数据保留下来,也要把今天新增的数据保留下来啊,所以说在这边呢,我们需要使用全外联,OK,那我们继续啊,那第三部分呢,与前面的表进行关联的时候呢,在这使用的是left row啊为什么在这它可以采用left呢?给大家解释一下啊。实际上大家看这啊,第三部分数据取自于哪儿,是不是取自于DWS层七天前的一个分区,没错吧,那也就是这里边实际上存放的就是是不是就是七天前的活跃的维度对象啊,对吧?那当然对应到用户,那就是七天前的活跃用户,没错吧,那七天前的活跃用户是不是肯定包含在DT层前一日的分区当中。没错吧,因为DWT前一日的分区里边存放的啥是截止到前一日的全量的维度对象啊,没错吧,肯定包含在里边,所以在这儿呢,第三部分数据只需要与第一部分数据,哎,来一个left run就可以了,OK,那第四部分数据呢,也是同样的道理啊,也用left就可以了,OK,那接下来我们看一下他们join之后得到的结果应该是什么样的,是不是应该是这种啊,对吧?那大家注意观察啊,你看这里边是不是有些数据它自动补上闹了呀,对吧?这个大家一定要注意啊好,那我们剩下的工作是不是就是从这个join之后的结果当中选择咱们最终所需的每个字段呀,对吧?啊,OK,那接下来我们看一下这个具体的逻辑什么样的啊,OK,那我们的第一个钻石啊,是不是就是ID,没错吧,那ID应该出自于哪部分数据啊?
06:35
咱们的ID是不是应该是出自于第一部分数据,再加上第二部分数据啊,没错吧?哎,那这样一来能得到我们截止到今天的全量的维度对象的IDOK,那得到之后我们继续往下走,那再往下我们是不是就应该诶分别去获取最近N天的累计值,以及从开始至今的累计值大呀,对吧?OK,我们先看最近一天,最近一天的累计值应该怎么获取啊,其实很简单,最近一天的累计值是不是我们只需要去DWS层最近一天的分区当中获取就可以了,没错吧,咱这是不是直接拿过来就OK了?
07:09
啊,这个很简单啊,然后大家要注意的是什么啊,是不是有些用户他可能在最近一天他没有活跃啊,对吧,没活跃这块是不是在装完之后会补上闹值对吧?那闹值我们应该如何处理,那是不是就按零处理就行了,没错吧,比如举个例子啊,他今天没有下单,没有下单,那他的下单次数是不是就是零。啊,所以说在这就这样处理一下就行,其实很简单,NVR是不是就可以啊,OK,那我们继续往下就行啊,那最近一日完之后呢,我们再看最近七日,最近七日到底应该怎么获取呢?来其实也不难,大家来看一下啊。好,那这个呢,就是我们获取最近七日的这个数据的思路,看一看能不能看懂,在这儿呢,我们相当于干啥,是不是先获取到了截止到前一日的最近七天的累计值,没错吧,然后呢,再加上今天的汇总值,然后呢,再减去七天前的那一天的汇总值,那这样一来,我们是不是就能得到哎,截止到今天的最近七天的累计值了呀。
08:07
没错吧,诶其实就是这样的一个思路啊,也不难啊,OK,那我们继续往下进行,那完了之后呢,该该谁呢,是不是该最近30天来呀,对吧,最近30天跟最近七天逻辑是不是应该是一样的,没错吧,具体怎么做大家来看一下。是不是首先先获取截止到前一天的最近30天的累计值,没错吧,然后呢,再加上今天的值,再减去30天之前那一天的值,那这样一来我们就能得到截止到今天的最近30天的值了。OK啊,好,那最后我们再来看一下从开始至今的累积值应该如何获取,这个就更简单了,对吧?怎么获取,是不是只需要拿到原来的累计值,再加上今天的值,那就能得到截止到今天的累计值啊,没错吧?OK,那这样一来呢,我们实际上就已经得到了我们最终所需的每个字段了啊,那这分数据就是啥呀?这一部分数据是不是就是截止到今天的全量的维度对象的累计行为啊,那当然这部分数据呢,我们最终需要给他写到哪啊,写到DWT层的当天的分区当中。
09:08
啊,OK,那这就是DWT层每日装载的具体思路。好,那接下来我们就按照这个思路呢,去写一下每日装载的circle啊好,我们打开data RI啊来,那现在我们先获取我们所需的四部分数据,对吧?来先看第一部分啊,第一部分应该是从哪儿,是不是应该是从DWT层的user topic里边获取啊对吧?注意啊,咱要获取的是前一日的分区对吧?那因为咱们今天是不是6月15号来,对吧?所以应该是data艾,是不是减个一就行了呀,对吧?把当前日期放进来,2020杠零六杠幺五,然后后边再来一个减一啊那在这儿呢,我们需要获取这张表的全部字段,对吧?啊,字段很多,就这么多啊,来点过去,哎,这么多啊,当然在这儿呢,我已经提前处理好了,咱直接拿过来就可以了啊,这就是DWT层的所有的字段,来,咱们拿过来好给它放在这儿啊,来,CTRLV,咱们往前推一下。
10:00
OK,那第一部分数据我们就已经拿到了啊,那下来我们继续往下进行啊,那接下来看第二部分,第二部分我们应该从哪获取,是不是应该是从DWS层获取啊,对吧?来OK user action内抗T,注意那这个我们要获取的是哪个分区啊,是不是当天分区对吧?所以VDT等于2020杠零六杠幺五没问题吧?那在这我们也需要获取所有字段,那在这儿我也提前处理好了,咱们ctrl a ctrl c拿过来,来我们获取里边的所有字段,CTRLV。来,那这就是我们的第二部分数据,那接下来我们是要获取第三部分呀,第三部分是不是也来自于DWS层的这张表,只不过分区不一样对吧?好,那现在呢,我们给它复制一下,改一下分区就可以了。来CTRLV,那接着往下走啊,那这个分区应该是哪个分区啊。我们要获取的是七天前的那一天那个分区,对吧?应该减几,在这儿呢,应该是减七,这个一定要注意啊,来我们data艾特一下来把当天日期放进来,后边呢,来一个减七就可以了,好,这是七天前的分区,那接下来我们再获取30天前的分区,是不是跟这个是一样的套路啊,对吧?那在这呢,我们直接减一个30就可以了。
11:11
啊,好,那现在我们就已经拿到了我们所需的这四部分数据了啊OK,那接下来呢,我们给这四部分数据每个都起一个别名,然后呢,诶,把它们照在一起啊好,我们从第一个开始吧。来,那第一个呢,我们给它起个别名,就叫做就叫O的吧,来CTRLX来括号来给它放进来。来CV,那这个咱们就管它叫O。来往下推一下啊,再往下走。这个是old,好来接接来下一个啊,下一个是什么呢?下一个呢,是一天前的分句对吧,那我们给他起个别名叫做1d ago啊,就是一天之前对吧,大家这个往下对一下。好,这个是ED,然后ago,好,那完之后继续往下进行下一个,那这个呢,就7AGO对吧,啊30年之前。
12:06
来7D来ago ago好,下一个这个呢是36 30天之前对吧,OK来。来往前推,那这个叫30D ago。好了,那现在我们再把这四个子查询都给他们join起来啊,按照我们前面的分析呢,第一个跟第二个是不是需要做全外连啊,对吧?来,咱们来一个f ul for alter John对吧?那它俩的连接条件应该是啥?应该是啥?是不是应该是o.user ID等于ED这个ago对吧?ED,然后下划线ado,然后呢点user ID。没错吧,OK,那接下来我们继续往下进行,那再往下边去join是不是可以用left join来对吧?啊,Left join,好,那接下来继续往下走啊,那这应该怎么写,是不是应该是on,呃,old.user ID等于哎,这个7d ago,然后点user ID就可以了,对吧?
13:04
没错吧,好,那我们继续往下进行,那下面呢,是不是再left转一下来,那我们把这个连接条件的CTRLC给它复制一下。来,拿到最下边来,CTRLV,那在这边呢,我们把它改一下啊,这个改成30DGOOK,那到目前为止,我们就把这四部分数据都关联到一起了啊,那接下来我们就可以从中选择我们所学的每个字段了,对吧?Select,然后呢,From,好,那我们先获取第一部分字段啊,来,咱们拿一下第一部分字段好,这些字段呢,是不是都是与登录相关的那个统计啊,对吧?啊,OK,咱们先把它求出来,CTRLCF3来我定在这儿,好,那现在我们开始写circle,来,我们先看第一个字段啊,第一个段是啥?第一个段是不是user ID,没错吧,这个user ID我们应该如何获取啊?刚才咱们分析了对吧,现在我们是从这四部分数据这样的之后的结果当中选择我们所需的字段,对吧?OK,那U的ID是不是应该出自于前两部分数据?
14:01
没错吧,哎,我们应该从这两部分数据当中啊,选一个不为no的值出来,应该怎么写,是不是就NVL就可以,没错吧?诶,咱们就NVL里边先写一个,谁先写一个,哎,比如ED_AJ,然后点UID,然后呢,再来一个O的,点UID是不是就可以了,来UID啊。好,那ID我们就拿到了,好,那我们继续往下看啊,那下边的字段呢,是首次活跃日期和末次活跃日期对吧?啊,这两个日期我们也暂时先放一放啊,咱还是先求下边的累计值啊,好,那我们现在先往下看,那再往下呢,是最近一日登陆次数对不对啊,那这个应该怎么求啊。啊,其实这个很简单啊,按照我们刚才的分析呢,最近一日的这个统计值,咱是不是可以直接从一地ago这个子查询里边去拿呀,对吧?啊,但是拿的时候呢,我们可能会拿到no值对不对,为啥啊,因为old与ED ago这个子查询他们数据的对应关系是这样的,对吧?啊,你像这种地方是不是就会自动补上no啊,对吧?啊,那如果我们拿到闹那怎么办呀?拿到闹说明什么?拿到闹说明这个人他今天没有活跃对不对?为啥?因为他活跃了,是不是肯定会出现在异地ago这个紫查询里边啊,对吧,那没活跃,那它最近一日的登录次数那是不是就是零,没错吧?所以在这儿呢,我们直接怎么写就行,是不是直接NVL,然后里边写一个ED ago,然后点点什么,是不是点login count呀,对吧,如果没有那就是零。
15:25
没错吧,OK,那这个呢,就是最近一日的登陆次数啊好,那接下来我们继续下一个是啥,下一个呢,是最近一日登陆天数啊,实际上这个最近一日的登陆天数它的值啊,啊只有两种情况,要么是零,要么是一,对吧,所以在这儿呢,我们做一个一务判断就可以啊,那这个应该怎么写呢?啊,实际上很简单啊,在这儿呢,咱们就这样去判断就可以大家来看啊,那这个是咱的old,这个是ED ago对不对,刚才说了啊,是不是只要出现在1AGO里边,那就意味着这个人他今天活跃了,对吧?也就是他在最近一日活跃了啊否则的话呢,否则的话说明他没有活跃,没错吧,那所以在这块咱怎么写。
16:04
啊,是不是用if判断就行,If if啥,If ED ago.user ID不为no,不为no说明咱活跃了,那最近一日的登录天数,那是不是就是一,否则就是零,没错吧?那OK,来写一下啊,那是if一下if ED_ago然后点user ID is not no啊,不为那就是一,否则的话那就是零。OK,那这个呢,就是最近一日的登陆天数啊,这个也比较简单啊,OK,那我们再继续往下走啊,那再往下呢,是最近七日的登陆次数啊,那这个又应该怎么求呢?实际上它我们也分析过了,对吧,应该怎么求,咱是不是得先拿到截止到前一天的最近七日的登陆次数啊,然后呢,再加上今天的登录次数对不对,然后呢,再减去七天前那一天的登陆次数,那这样一来,我们是不是就能得到截止到今天的,也就是截止到最近一天的最近七日的登陆次数了呀?
17:01
没错吧,OK,那下来我们就把这个写一下,在这呢,我们需要加一个值,再减一个值对不对啊,OK,那当然我们再去获取这个值的时候,是不是也有可能为no啊对吧?那为闹我们就按什么处理啊,为闹是不是都按零处理?所以在这儿呢,我们是不是直接呃,NVL就可以了对吧?OK,我们来写一下啊,首先NVL咱们需要先获取原来的呃最近七日的登陆次数对不对啊,完了之后呢,如果为闹为闹我们就得按零算啊,之后呢,先加上今天的,那也就是呃,NVL,呃,这里边应该是ED ago里边写谁是不是login count呀,对吧,Lo log count来,咱把这个移一下啊。好,那如果为闹的话呢,诶我们也是按零算OK,然后呢,再减去啊,谁减去七天前的对吧,应该是7D,诶ago下线啊。点I login count啊,如果为闹也是按零处理啊,那这样一来我们就得到了最近一日的登录次数了,OK,那接下来我们继续往下走啊,那再往下呢,是最近七日的登陆天数对吧?那它又应该怎么求啊?哎,实际上它的思路与最近七日的登陆次数啊,这个思数差不多的啊,在这我们也要先拿到一个原来的最近七日的登录天数啊,然后呢,诶,再根据我们的判断,那咱们要判断啥呀?是不是得去判断一下他今天有没有活跃,以及他七天前有没有活跃。
18:23
对吧?啊,根据我们的判断结果去决定咱是否要加一个一啊,或者是否要减一个一,没错吧,就是这样的一个思路,OK,那接下来咱们写一下啊,那首先我们需要先拿到原来的最近七日的登陆天数,也就是old.log in last7D对吧?哎,7D应该是day抗的,哎,天数如果为now,那我们就按零算对吧?那完之后呢,需要加上一个啊,加什么?诶,加谁呀,是不是加他就可以了?没错吧,来你看一下是不是这么回事啊,那这个是不是就在判断他今天有没有活跃对吧?诶如果活跃了我们就加一,否则就加零,没错吧,OK,那继续,那是不是还得减一个对吧?减其实跟它一样的道理,只不过这儿呢,应该改成是不是7D额钩啊。
19:05
没错吧,诶,OK,那最近七日的诶登陆天数我们也就拿到了啊,那同样道理,下边最近三十日的登陆次数和登陆天数是不是也应该这么求,那OK,那接下来咱们把这俩呢给它求一下,CTRLC来往下走放在这儿啊,CTRLV,那我们把这里边所有的七都给它改成啥就可以了,是不是都改成30就可以了,对吧?那咱们这个字段呢,都是有规律的啊,OK,咱改一下来,这个改一下,那这样呢也要改一下,OK,那现在我们把所有的七都改成30 OK,那这样一来我们就把最近三十日的登陆次数和登录天数求出来了,那接下来呢,我们再求最后一个啊,最后一个是啥?是不是就是累积登录次数和累积登陆天数啊。没错吧,这个是不是就简单了,是不是拿到原来的值直接加上今天的值就OK了,没错吧,来我们写一下啊,那我们先写这个累积登录次数啊,对吧?那是不是就是NVL,然后呢,拿到old的点login count,没错吧,如果为no,那我们就按零算,没错吧,然后呢,加上今天的NVL,然后点log,好,OK,那当然呢,维闹的话,我们就需要按零算啊好,那累积登录次数我们就拿到了,那接下来最后一个啊,是累积登录天数,那同样呢,得先拿到原来的对吧,应该是NVL,然后呢,old.login day countt,对吧?哎,Day count,啊,完了之后呢,如果为闹就按零算对吧?那加上加上谁是不是加上它就可以了。
20:31
啊,去判断一下今天有没有活跃对吧,如果活跃了就加个一,否则就加零啊好了,那这样一来的话,我们就把这几个最近N天以及从开始至今的累积值算完了啊好,跟我们刚才分析的思路基本上是一致的,对吧?啊好,那现在呢,我们再来看一下上面的两个日期啊,一个是首次活跃日期,一个是末次活跃日期,这两个日期应该怎么处理好,我们先看第一个首次活跃日期。啊,这个应该怎么求啊,那我来分析一下啊,大家想一想,经过咱们的首日装载之后啊,那是不是原来的用户都已经有首次回日期了。
21:08
没错,而且这个呢是不用改的,对不对啊,那所以说原来的用户呢,我们直接从O的这个子查询当中获取原来的首次或日期是不是就可以了,对吧?啊,那咱们要处理的是不是只有今天的新增用户啊,对吧?那新增用户的首次活跃日期,那是不是就是今天。没错吧,那也就是说这儿的一个对应关系,实际上这样的啊,这是咱那个old的紫查询,这个呢,是1d ago,对不对啊,它俩的对应关系是这种啊,那也就是说这部分用户他的首次活日期,那是不是直接从old里边拿就行,没错吧,那是不是这一部分用户是今天的新增用户啊,对不对,那新增用户他的首次活跃日期呢?那就是啥。哎,那是不是就是今天,那其实来一个if判断是不是就可以了,对吧,来我们写一下啊来往这写那就是if对吧。If,什么if old.user ID is no,那这说明什么?是不是说明这个用户他是一个今天的新增用户啊,对吧,因为他没有出现在old里边,那是不是就证明他是今天的新增用户,没错吧,那这时候呢,我们的首册还日期,那就是2020杠零六对不对,然后杠幺五啊,那否则的话呢,那否则的话是不是咱们就获取原来的login data这个first呀,对吧,来first。
22:23
OK,那这就是首次活跃日期啊,那接下来呢,我们再获取这个末次活跃日期啊,那末次活跃日期应该如何计算呢?实际上他的计算逻辑也不难啊,那我们的逻辑应该是这样的啊,如果哎,他今天活跃了,那他的末次活跃日期是不是就是今天对吧,那否则呢,否则是不是应该保留原来的末次活跃日期的值啊。对吧,所以在这其实也是一个一判断啊,那我们来写一下来if,那应该怎么去判断他今天有没有活跃呢?是不是应该是EDGO.uid是否为闹啊,对吧?哎,如果他为闹,为闹说明什么?为闹说明他今天没有活跃,没错,没活跃,那我们就得获取原来的login data last对吧?否则的话呢,否则就是今天2020杠零六杠幺五啊OK,那这样一来,这个末次活跃日期我们也就拿到了。
23:14
啊,好了,那到现在呢,我们就把与登陆相关的字段都计算完了。那其余字段的计算逻辑呢,与这一部分啊是完全相同的,所以在这呢,我也就不再给大家演示如何去写了啊呃,我们去拿一下文档当中的最终结果啊,这个图咱给它点掉啊,好,那CTRLC咱给它粘过来,来放到下边。来看这位。好,大家一起来看一下啊,那其实我们下边呢,所有的字段都是这样的一个思路啊,咱们大致看一下,随便找一个吧,那比如说这个,这是与什么相关的呀?这是与加购车相关的统计值对不对?OK,那这个是啥?这是不是就是最近一日的加购次数,没错,是不是直接从异地额购里边去获取,获取不到那就是零,那下一个是啥?下一个呢,是不是应该是最近七日的加构次数,对吧?那仍然是先获取原来的,然后呢,加上今天的,再减去七天前的那30呢,也是一样的累计值就是原来的加上今天的啊,后边所有的字段都是这个逻辑啊,所以在这儿呢,我们就不再呃花费时间去一点点看了啊好了,那我们就相当于把所有的字段都选择出来了啊,那接下来我们就只剩最后一步了,就是把这个结果呢,给它插入到DWT层的用户主题表里,没错吧?那最后再来一个insert overri table,应该是d wt user topic对吧?来我们改一下,那后边呢,别忘了分区对不对?分区应该放在哪个分区这外放到当天分区对吧?也是DT等于2020杠零六杠幺五。
24:38
好了,那DWT层用户主题板的每日装载就完事了。
我来说两句