00:00
嗯,好了各位,那现在呢,我们来再来看一下咱们最后一张表,呃,最后这张表呢,我们管它叫做什么呢?我们管它叫做用户域用户力度啊,然后呢,是登录历史至金汇动员,诶那大家琢磨琢磨,你说这个东西它将来会存哪些信息呢?你想啊,登录登录是那个登录实时表对吧?哎,你把每个用户的历史至今的这个登录记录全汇总到一起了,OK,那而且汇总到用户里头的,你说咱们能得到一个什么样的结果呀。啊,你想啊,所有人的所有的登录记录汇总到一起对不对,然后得到一个,哎,这个用户力度的结果,那应该是什么结果,这应该是每个用户他的。首次的登录日期,末次的登录日期咱是不是也能拿出来对吧?那完了之后呢,还能拿到每个人历史至今总的登录次数吧,对不是不是,无非就是这些东西啊对吧?OK,那所以这样呢,咱们应该也是能够想到的啊好,那咱接下来呢,就把这张表的这个表结构咱一起来看一下,来CTRLV放在这来咱一起看一下啊,首先先看一下它的表表名啊,表名这个表名呢叫做DWS,然后呢是user字,User字两个user字一个是数据域,一个是这个力度,对吧,有区别啊,这个理解一下,后边呢是login,也就是登录业务过程,然后TD历史至今,好,那就来继续往下看,我们来看一看它最终的表结构,行还有列啊,先看行一行仍然是一个用户,因为力度是U字,好,那接下来往下走,下面的就是它具体的字段了,字段这边咱们来看一下啊,一个就是UID,这个很简单,然后再往下这儿有一个啥,有一个login it last对吧,是不是就是末次登录日期啊,诶这块怎么没有首次登录日期呢?
01:45
你像我们刚才下单还有支付那个历史资金的汇总表,对吧,咱是不是都有一个首次加末次的那个什么下单或者是支付时间呀,对吧?那为什么这个登录我们只有一个末次登录日期,咱没有去给他维护一个首次登录日期,这是为啥呀?为啥呀,因为没必要,为啥没必要呢?
02:07
咱们想一想啊,什么叫首次终止期?每个用户的注册日期是不是就可以作为首次登日期对不对,那咱们是不是前边有一张实时表,我们叫做注册实时表啊,这个注册实时表当中是不是就已经存储了每个用户的首次注这个活跃日期啊,对吧?或首次登录日期对吧,已经有了,在这儿呢,就没有必要再重复的去计算,重复的维护了,是这样的,所以在这呢,咱们大家理解一下好了,那这个完成之后,看下一个字段,这个叫做什么?叫做log in count TD,这个指的是每个用户的历史至今的总的登登录次数,也就或叫做累积登录次数也是可以的,行了,那这就是咱这张表的所有的字段,哎,字段比较少啊,好,那字段完了之后,我们再来明确一下它的这个最终的分区规划,这个分区规划其实也简单,它的分区划是不是还是那种,呃,就是呃,所有的这种T里表的一个通用的分区规划呀,对吧,每天一个,然后每天的分区存放的是历时截至当天的登录记录的汇总结果,好了表结构咱就说完了,说完之后,接下来咱们来看一看它的数据装载怎么做,这个数据装载我们不能直接看文档了,咱。
03:12
得分析分析,咱一起来写一下这个数据状态啊,这个视频我也不单独录了,咱们就一块录上就行了啊来在这呢,我写一个数据状态,来咱们分析分析他的数据状态怎么做,呃,它的数据状态是不是理论上也得分一个首日和每日啊,对吧?那得先看首日,首日我们应该怎么做,首先得select from对吧,那from谁?呃,是不是还是老规矩,你看一看,就是在我们当前的DWS层有没有跟它相对应的ED表,对吧?有咱就从ED表里取,对不对,那有没有呢?CTRLCCTRLF来搜一下啊,我把这个TD给它去掉,我换成ED有吗?没有对不对,没有没有的话,那是不是只能是来自于DWD的明细表了呀,对吧?那来自于DW的哪张表?肯定是来自于log in那张实时表对不对?所以我们应该找到DD层的,然后呢,找到谁TRA,呃,应该是不是TRA应该是用户预,对吧?用户下面的是不是应该找到log in INC这张表对不对?那首先我们需要先拿到这张表,是不是全表数据啊,对吧?拿到之后我们需要干啥?是不是直接进行一个分组聚合就行了,对吧?那按谁分组呢?按照user ID进行分组呗,没问题吧?好,分完组之后我们是不是就可以求上面那些具体的质量,对吧?User ID先拿出来之后呢,有一个log in last这个怎么求?这个是不是也取一个求一个最大值就完事了,对吧?那完了之后我们把DT放进来,好,接下来继续往下走,那下边该取谁呢?是不是该取那个log in count来,对吧?这个直接怎么算就行,在这张表里一行,是不是就表明这个人登陆了一次啊,对不对?那你要求次数,直接说看星那就完事了,求行数就是相当于在求次数对吧?好,那好像这个。
04:50
首日装载三课是不是就写完了呀,对吧?OK,好,写完之后这个会不会有什么问题呢?这么简单吗?这是咱们的首日啊,这个大家说会不会有什么问题。
05:02
其实这里边儿可能会存在问题,可能会存在问题啊,可能会存在什么样的问题呢?哎,首先第一个问题就是咱们得到的这个结果,首先可能就不全。对不对,而且第二一个呢,就是我们这个结果它也可能不准确,为什么这么说啊,道理很简单,大家想想这张表咱们里边的数据是来自于哪的,大家还记得不?还记得不点一下啊,我们点过去啊,点过去完了之后,这是咱们之前那个简面语句对吧?下边是它的数据状载对吧?来咱们看一看啊,咱不用看逻辑了,我们就看他从哪取数啊,看子查询是不是select,然后呢,From,然后里边又嵌了一个查询,对吧,又from又一个子查询,哎,咱们找到最内层就在这呢,对吧,是不是select from ods log跟AC啊对吧?那这张表它的数据主要是来自于ods log是来自于日志的,而日志是没有历史数据的,对不对?那所以说实际上呢,我们这儿呃,拿到的这个表,虽然咱们看起来是全表数据,但其实大家都知道这张表目前它其实只有一天的登录记录吧,只有14号的登录记录是这样的啊OK,那所以既然只有14号的登录记录,我们这儿最终得到的结果就只包含什么,只包含14号这一天的登录用户啊,对吧,那我其余的用户是不是根本就没有,对不对,而且我们这儿得到的这个所谓的康乃星,这是真正意义。
06:30
上的这个人历史至今的登录次数并不是对吧,这个显然是不对的,对不对,那所以说诶,咱们这存在这样的两个问题啊,第一个问题那就是啥呢?啊,就是首先你这个用户他不全对不对,第二个问题呢,就是得到的结果是不准确的,好那这两问题咱们来分析分析,能不能解决一下啊,首先咱们先一个个来先说用户不全的问题,这个咱能解决。能解决,其实你从这两本里取出来,你肯定是解决不了的,因为它本身就是没例数句对吧?哎,你怎么查他也查不出来,哎,这个是肯定的,那但是我们可以想想别的表啊,对不对,我要想拿到全部用户,你说咱们是不是可以有别的办法呀,对不对,比如说我从哪能拿到全部用户,我从注册实时表里能不能拿到全部用户,能,那我从那个用户维度表能不能拿到全部用户也能,对不对,诶,都能拿到全部用户,所以在这儿呢,我们要想解决用户不全的问题,其实是其实是能解决的,我们可以比如举个例子啊,我们写一个子查询,写一个子查询对吧,比如说另一个子查询呢,我们从哪取份呢?我们从是呃那个注册的实值表,或者从用户的维度表里边取份,对吧?诶咱们把所有的user先都给它查出来,那查出来之后呢,我们怎么办呢,是不是在用这个跟他做一个。
07:45
Left draw,那是不是就能保留下来所有的用户了,那能理解吧,OK,好,那这是用户不全的问题,我们能解决,但是这个结果他不准确的问题,咱们怎么解决,这个能不能解决,这个能解决吗?这个怎么你也解决不了,因为你就是没历史数据,你就是没有历史的登录记录,对吧?那我怎么能知道他到底登录了多少次,这个咱没法解决啊,是这样的啊,这一点咱们只能认了,对吧?没办法,只能是,呃,就是用一个这个呃,相当于不太准确的值了啊就是这样的,这个大家要理解一下,但是呢,呃,你要知道,就是只要我们开始搭建数仓了,对不对,那我从搭建数仓这一天开始,后续所有的新增用户,那咱们的统计值是不是都是准确的呀,对不对,这个大家应该是能够想明白的啊好,那接下来咱们再继续往下走,其实咱们这儿一会儿做这个join的时候呢,你可能还会面临一个问题啊,什么问题呢?你想啊,这是我们的全部用户对不对,那这个呢,是我们刚刚统计出来的这个,呃,14号的活跃。
08:46
用户对吧,来登录用户对吧?好,那他俩做left的时候,是不是肯定会有一部分是闹纸啊,对吧,这块对不对,这这部分是闹,那怎么处理呢。对不对,你这不是闹对吧,那是不是就拿不到我们这一部分用户的什么末次的登录日期,还有这个登录次数啊,对吧,你连个错误的值你都拿不到,对那怎么办呢?我们只能是给他一个哎默认值了,对吧,给他一个初始值对不对?比如举个例子,那我的这个这部分用户对吧,我拿不到他的莫斯登日期,那我把什么作为他的莫斯登日期,比如说我可以随便点一个日期,把举个例子啊,比如说把我们这个数仓上线的这一天,第一天这个日期作为他的末次登录日期,这个行,或者说你再怎么做呢?诶,我用这个人的注册日期作为他的末次登录日期,这个是不是也说的通啊,对吧?啊,因为你注册那天是不是至少登录过一次啊,对吧?啊是这样的,那完了之后呢,呃,我的这个康的星,也就是我的累积登录次数,我这个给他个什么值呢?比如说给他个一对吧,那因为啥呢?因为他注册的时候是不是至少登录过一次啊,这个就这意思,也就在这呢,你就按照具体的一个规则,给他个默认值,给他个初始值就行了,啊是这样的,这个呃,要是有具体的。
09:55
要求按照具体要求来,没要求呢,咱们就随便定,那就完事了,是这样的啊,这一点大家需要注意一下,好,那咱们这怎么规定呢?我们这么规定啊。
10:04
就是对于这部分人而言,对吧,咱们拿不到他真正的末次登录日期,那我们就用他的注册日期作为末次登录日期,那这个累积登录次数咱拿不到,拿不到呢,我们就给他一个一啊是这样的,好,这是咱们这儿的一个一个一个规定啊好,那接下来呢,我们就把这块给他实现一下啊这个具体怎么做呢?很简单,我们只需要再写一个子查询就行,对吧,我们s from,那找到谁呢?咱就从维度表取吧,我们找到dim,然后呢,User save之后呢,我们需要拿到全量的用户对吧?全量的用户从哪个分区里边拿这个大家还记得不?哎,是不是9999那个分区啊,对不对,9999是不是始终存放全量最新对吧?我们这儿拿它9999这个分区好拿到之后呢,我们只需要先拿一个user ID出来,对吧,它叫ID应该是啊好ID出来,那还要拿别的,是不是还得拿一个那个注册的时间呀,对吧?哎,注册时间在那儿呢,就用那个口time就行,为什么要拿它呢?是不是咱们一会儿得用它作为就是那就是有一部分用户是。
11:04
不照样照样是空啊对吧?哎,咱们需要用它作为那部分用户的一个,诶默认的这个末次登录日期啊,是这样的,这个理解一下好了,拿完之后他俩需要作一个关联啊,我们刚才说了,我们应该对它俩是不是进行一个left状的操作呀,对吧?这个咱们给它拿过来,诶往前走,这边咱继续往下走,CTRLX放在一个括号里,这个回车,哎,CTR位,那这个拿过来,那之后呢,我们给它俩各自起一个别名,呃,这个呢,我们就管它叫做U吧啊,然后下边这个我们管叫做呃,L对吧,一个user点一个login,然后后边呢,我们做做一个left red,然后呢再做一个join操作,那之后呢,我们再来一个on on什么u.ID等于呃这个L点是不U点下角线ID啊对吧?好,那这个就抓上了,装上之后呢,我们就可以从里边选择我们所需的这个数据了,首先第一步我们要选的就是IDUID对吧,那来说这个我们应该选谁,是选这个ID还是选这个user ID。
12:00
选谁呀?应该选上面那个ID吧,对不对,为啥呢?很简单,因为你上边是不是全部的用户,因这个是不是不全呀,对吧?那所以说是不是肯定以他为准,咱们选左边这个全的对吧?好,那选ID,那完了之后呢,我们继续往下走,那下边是不是得取这个末次登陆日期了呀,对吧?在这我们先给它起别名叫做log in这个下划线data,然后呢,Last,好,我得取它,那取这个能直接取吗?不能直接取,直接取这部分全是空对吧?那所以在这我们是不是得做一个判断呀?对,那咱们判断的逻辑是什么呢?哎,这有值我就直接取,对不对,这没值呢,是不是用它注册日期代替啊对吧?OK,那在这呢,咱们就这么去做一个判断就行了啊,应该是if if啥if l点是不是u ID is not now,对吧,不为空,不为空实际上指代的就是我们下面求出来那部分用户对吧?那咱们就用我们刚刚得到的这个莫斯登日期作为它的真正的莫斯登日期,好,那完了之后对于另另外那部分人呢,是不是应该是取这个东西啊对吧,那这个东西。
13:01
这我们不能直接取,我们应该对它进行一个是不是格式化呀,对吧,咱在这先给它格式化一下吧,我们来一个data form,把它放进去后边的YYYY,然后呢,杠MM,然后杠DD,然后这边呢,我们启动这影叫做create date之后咱是不是就可以把这个字段放在这个位置了,对吧?好了,那这样一来我们就拿到了末似登录日期了,这就我们最终计算出来的啊,给它起个别名叫做love it last好了,那这个搞定之后,我们看最后一个字段,最后一个字段咱们应该怎么取啊,这个其实是不是也应该用一个if判断呀,对吧?来,那么仍然是做if啊,应该是l.user呃,ID is not no对不对?如果不为空,那我们就用谁呢?就用刚刚我们计算出来的这个这个值数就行了,对吧,Log in count,然后呢,来一个TD啊,这边拿过来TC我放在这,那完事,唯空的话呢,唯空是不是咱们刚才说了给他一个一作为它的默认值啊,对吧?啊就是这样的好了,那这样一来咱这张板的首日装载就算是完成了,好了,首日完成之后,我们接下来再看每日,大家想想你说他的每日应该怎么做呀。
14:04
啊,它的每日每日其实跟其他的那种TD表的每日是不是又是一样的呀,对吧,这个其实它只有首日的时候是稍微有点区别的,对吧?哎,但是只要我们做完了这个首日装载之后,实际上呢,我们就已经在哪在这个DWS层的这个TD表里边,是不是已经维护了一份全部用户的这个登录记录啊,对吧,已经维护了一份全量的了,对吧?OK,那在后续呢,在后续我是不是能拿到每一天的这个登录记录对不对,那完了之后呢,我再跟前一天进行累加什么的就没有任何问题了,所以后续每天删是一下的,只有首日有点区别,行了,那这个完成之后,我们直接看一下文档当中的最终的circle就完事了,这个就不带一点点写了啊来,咱给他拿过来放在这个位置好了,那看一下他这是怎么做的啊,首先第一步先从T地表当中拿到前一天分区还是拿这几个字段,对吧?拿到之后呢,我们再看这个第二次查询,第二次查询呢,由于我们没有与之相对应的1D表,所以说我们应该是取自于明细表了,对吧?明细表拿到来之后,我们首先得先干什么。
15:05
先进行聚合,得聚合到用户力度才行,对吧,那聚合完之后呢,得到诶每个呃UID对吧,它在当天的一个登录次数就可以了,好,那完事之后他俩是不是需要去做一个全外联呀,对吧?好那问一下大家,你说他俩做全外联之后,对应关系应该是什么样的,包含关系什么样的。包含关系应该什么样的,是相互包含呀,还是互不包含呀,应该是互不包含对不对,为什么你想啊,就首先这个是什么,这个其实我们拿到的相当于是会包含截至前一天的全部的用户。没问题吧,哎,截至14的全部用户,那这样呢,拿到的相当于是什么呀?是15号这一天的登录的用户对吧?那他俩是不是应该是这种对应关系,那中间共共有的部分,那指的是什么呢?是原来的老用户今天又登录了,对不对?那这部分呢,是原来的老用户今天没登录,那这一部分呢,是今天的新注册的用户吧,对不应该是这种对应关系啊好,那完了之后我们需要思考一个问题,就是我们怎样从这样的一个聚合照结果当中,对吧?也就是从这样一个续表当中去选我们所需要的最终结果呢?首先先看UID,大家说UID应该怎么选。
16:18
咱们第一个作业是不是选u.ID啊,对吧,这个怎么选,应该用NL吧,对不对,你选这边的也不行,你选这边的是不是也不合适对吧?应该用NL,哎,N lo的点ID加new.ad哎,这个很简单,接下来继续往下走,那完了之后呢,我们再来分析分析这个末次登陆日期,末次登陆日期是不是跟之前的逻辑是一样的,怎么学就行了,是不是只有这部分人末测登录日期是当天的日期,这部分是不是取他原来的值就完事了,对不对,这个还是那个逻辑啊好,那最后呢,就是咱们那个呃,累积登录的次数了,那我只需要怎么做,是不是只需要用原来的加上今天的就行了,当然这个呢,我们得加一个NV才行,对吧,还是那套逻辑啊,行了,那这个咱们就不再多说了,好了,那截止到现在,那这张表的每日装载咱也就给他做完了,诶行了,完成之后视频我停一下啊。
我来说两句