00:00
呃,来吧各位,那现在呢,我们看一下最后一张实时表,它叫做用户与用户登录事务实时表,那它所对应的业务过程,那就是所谓的登录,对吧?好OK,好,那接下来咱们就来分析分析这张表我们到底应该怎么去做,首先先来明确一下,就是这张表的一个设计灵感吧,它应该是来自于哪儿啊,咱们的设计灵感就是所谓设计灵感,就是它的字段或者将来它的数据应该是来自于哪,是来自于表还是来自于日志,咱先把这个明确。你觉得这个登录对吧,他应该来自日志,他在这表。到底来自于谁?那其实还是那句话,你你首先得知道,就是你登录对吧,到底会对谁产生影响对不对?你比如说我现在有一个用户登录了,那登录了之后,你说我们这个表,它里边的数据会不会变呀,哪张表的数据会变呀,我们好像没有任何一张表会去记录用户的登录信息,是这个道理吧,咱根本就没有,那既然没有的话,那这边是不是从里边咱就找不到登录相关的这个操作呀,对吧?那所以说我们的设计灵感,这张其实并不是来自于表了,来自于哪儿呢?是来自于日志的,诶咱先把这点明确啊,那所以说一会儿你就会发现,咱这张表里边的绝大多数的字段都是来自于用户行为日志,诶是这样的啊好,那接下来呢,我们就来把这张表最终的表结构给他明确一下来吧,这个拿过来我们一起看一眼啊,CTRLC啊,然后呢,往下走放在这儿。
01:27
来最后我们看一下它的这个电表语句啊,先在表名,表名叫做dwd user log in in user log in INC,那这个log指的是不是就是那个登录的意思啊,对吧?好,那这张表当中我们的具体的表结构行列分区是什么样的呢?先看行,每行大数指代的应该是一个什么?是不是就是一个用户的一次登录记录啊,对吧?那就是说谁在什么时候是不是登录了一次啊,就这就是我们这张表当中每行数据所指代的含义,好那接下来呢,看一看它具体的字段都有啥,那首先呢,第一个就是user ID,那第二个是什么呢?是data ID,那这个是不是就是代表用户维度,这个代表的代表的是不是应该是那个时间维度啊对吧?然后接下来我们再继续往下走,那这儿还有一个什么呢?China,这个是不是就是渠道,那这个呢就是pro是地区,这个呢是version code的应用版本,下面的是一些设备信息,对不对,OK,好,那这样一来的话,这些字段咱们就都看完了,你会发现这些字段绝大多数都是来自于日志的,而且它只有什么呀,它只有维路版。
02:27
他有度量吗?没有,你包括刚才我们上面那张表,就是那个注册的那张表,它也没有度量吧,对不对,实际上它只有一个就是这种,呃,这个隐含的度量值次数一,这个大家理解一下就行了啊好了,那字段咱们也就说完了,那刚才已经明确了,它的字段主要是来自于日志的啊好,那最后呢,我们再来明确它的分区规划,那分区规划应该是什么样的呢?是INC啊对吧,也是增量分区,我一天一个诶增量分区,然后每天的分区当中存放的是什么呢?是当天的,这个就是相当是一个登录的记录,是这样的啊,然后这张表它里边的数据跟上边那张表就不太一样了,上边我们一个人是不是只对应一个注册成功的记录啊,对吧?但是这张表呢,我一个人是不是可能会有多个登录记录,对不对,这个还是有点区别的,这个大家稍微都注意一下啊,行了,表的结构就OK了,视频我给他停。
03:15
啊来各位同学,那现在呢,我们简单的先分析一下它的数据装载啊,好,大家来分析分析,你说这张表,咱们将来做数据装载,我们应该怎么做啊,还是先分析数据从哪来啊,数据从哪来啊。只是从日志来对不对,只是从日志里边来啊好了,那完之后到底怎样从那一堆日志里边去筛选咱们想要的这个登录记录呢?怎么去筛选,咱们日志是不是有两类啊,对吧?一类是什么?是用户行为日志,一类一一类是那个页面日志,一类是那个启动日志,对这两类,那你说我们应该从哪类日志里边去找这个所谓的登录记录呢?从启动日志里边去找,对吧,那启动日志我一条启动日志能不能代表一个登录操作。
04:03
能不能代表其实不太准确,说实话啊,说话不太准确对吧,你比如说我我现在就是一个呃,一个这个用户对吧,我没有登录对吧,我那个比如说没登录,那完之后呢,我启动应用了,我是不是也会查生导知,对所以你用这个去判断不太准确,而且再一个呢,就算是准确也不全呀。为啥不全呢?因为启动日志我们前面讲过,它只有谁会有启动日志,移动端的应用是不是才会有启动日志啊,对不对,那我PC端我进行一个登录操作,是不是咱们这儿就拿不到了,对不对,那所以说启动日志里边拿怎么考虑它都不行的啊,那我从哪去拿呢?那只能是页面日志了,对不对?好,那怎样能从那一大堆的页面日志里边找到我们想要的一个一个的登录操作呢?怎么去找?呃,先进到登录页面,再进到再进到主页,其实说实话这么考虑也不太准确啊,因为咱们得考虑有这样一种情况,什么情况呢?比如说举例子啊,我现在打开我的浏览器对吧?我现在在这儿呢,比如说我登录了,登录之后我把这个浏览器关掉,关掉之后我过一段时间对吧,在一定的时间之内我再打开,打开之后这是不是也是自动登录的一个状态啊,对不对,也就是说我不是每次登录我都一定要去登录的,因为我这个浏览器会保存咱们这个一些这个cookie啊,对吧,是吧,然后他能够完成自动登录的一个操作,所以说这个好像也不合适,那怎样能找到咱们所谓的登录操作呢?哎,这个其实大家需要考虑考虑,那到底怎么做,大家一会儿再想啊,一会儿再想行,然后我们现在先明确一下咱这个数据的流向啊,那由于我们这张表的数据只来自于用户型日志,对吧,那是不是日志就没有历史数据了,对不对,没历史数据我们是不是就不用区分首日和每日了呀,对吧?就在这儿,我们一会儿写三后,只需要写一个就OK了,每天都一样啊,是这样的啊OK,然后呢,大。
05:53
大家现在先下午休息一会儿,然后大家可以在课下的时候呢,诶琢磨琢磨看看到底怎样从那一对一页面日志当中找到那个所谓的登录的这个操作,提醒一下大家登录肯定跟谁有关系呢?User ID肯定是有关系的,你不登录是不是没有user ID,登录了之后是不是日志里面才有user ID啊,对不对,可以根据这个小细节去区分一下啊,OK,就先提示这一点吧。
06:16
来,那现在呢,我们看一下这个最后一张实时表,用户登录事物时表的一个数据装载的大体逻辑,刚才已经分析过了,就是这个呢,肯定是来于日志的,而且是来自于那个页面日志的,对吧?啊,那再有呢,就是这个circle呢,我们只需要写一个,因为首日跟每日是一样的,对吧?好,那现在咱就来看一看这个circle到底怎么写,我先写那个标题数据状载,来,咱先明确一下这个大体的逻辑啊。我上就给了大家点提示,说我们可以根据那个UUU段ID对吧,去搞一些事情对不对啊,来分析分析这个东西怎么弄啊,到底来咱们大概琢磨琢磨,就是在这儿呢,首先我们会拿到一大堆的这个页面登录页面浏览日志对不对啊,一一条这个页面日志是不是就相当是一个页面浏览记录啊,对吧,这个大家应是能想清楚的,好,我们会拿到很多的页面浏览记录。
07:07
对吧,那这么多的页面浏览记录里边,有些页面浏览记录它是有user ID的,有些呢,是不是可能是没有user ID的呀,对吧?OK,好,那没有user ID,那就说明他在浏览这个页面的时候是未登录的状态,对吧?有UID就说明他在浏览这个页面的时候呢,是登录的状态,对不对?OK,好,那完了之后我们要找的是什么?咱们要找的是那些登录的记录,对不对啊,OK,那什么叫做一个登录的记录呢?什么叫一个登录记录?很简单,如果有一个人对不对,他在浏览这个页面的时候呢?他上一个页面对不对,还是没有U段LID的,然后这个页面,诶,他有U段ID了对不对?那这样一来是不是一定能证明他刚刚已经做了一个登录的操作量,对吧?是这样的,OK,那我们其实就可以找到这个,呃,就是第一个带有UID的这样的一个,呃,页面浏览记录作为什么呢?作为它的一个登录的一个记录,对吧?呃,应该是这样的一个道理吧?好,那接下来咱们就分析分析怎样去找到这样的,就是第一个带有user ID的页面浏览记录,怎样能找到他呀?
08:13
嗯。这个怎么怎么怎怎么能找着他呀,对,OK,那我这个所谓的第一个带有UID对不对,我这个是在呃以什么为单位的前提下去说的呢?我们应该是以绘画为单位吧,就不应该是在一个绘画里边,对不对?第一个带有user ID的页面浏览记录,是不是就可以作为一个登录的这个操作记录啊,是是是一个道理吧,大家说哎,那接下来咱们就来分析分析啊,就是在一个会话当中,我这个多个页面浏览记录,那他们的登录状态可能会有哪些这个情况呢?来分析分析,那首先告诉同学,那现在呢,我们举一个具体的例子,假如说现在有一个人,他浏览我们的页面对不对,OK,他最最常见的一种场景应该是什么样的呢?可能就是说OK,他刚开始浏览咱们这个网站的时候,最开始是未登录的状态,对不对,但浏览了一部分页面之后呢,诶发现我再往下浏览,比如说我要去下单了,对不对,这时候他要求我们必须得登录对不对,OK,那下边是不是就可能会进行这个登录的操作呀,对吧?OK,那也就说。
09:16
我们最常见的一种场景就是OK,我这个会画里边前边一半的页面浏览记录是没有user ID,是未登录状态,OK,我后边这部分页面来记录呢,是有U的ID的,是登录的状态,这是一种场景,对吧?OK,好,那接下来呢,我们再看往往下边走,我们有没有另外一种场景呢?也有,就其刚才我们就说过了,假如说我现在刚才已经登录了,然后呢,我把浏览器关了,然后过了一小会儿呢,我又打开了,这是不是一个新的会话了呀,对吧?那这个会话它里边的页面浏览记录应该都是什么样的状态,都是登录的状态,它应该是都是带有UID的,对吧?诶,这也是一种场景,那还有个第三种场景呢?有,那就是说他进到我们这个页面之后呢,他他从来都没有去做过这个登录的操作,对吧,自始至终都是未登录的状态,是不是也可能会有这样的一种场景啊,对吧?那其实我们整个的绘画基本上就是分为这样的三类,没错吧,OK,那现在问题来了,那我们怎样去找到我们所期望的那个所谓的登录记录呢?怎样找到分情况去说,那对于。
10:16
这样的绘画前面一半是未登录的,哎,中间然后后边一半呢是登录的,我们应该以谁作为登录记录呢?是不是应该以他也就是第一个就是带有user ID的页面作为登录记录啊,没问题吧,OK,这个没问题,好,那对于这样的这个绘画呢。咱们应该以谁就是第一个页面吧,他刚进来是不是就是登录的状态,对不对,那所以说那这个其实第一个页面我们就能把它作为什么呢?作为咱们这个登录的一个记录啊,一个操作好,那接着往下走,第三种情况,第三种情况是不是根本就没登录啊,对吧,没登录它何谈登录操作呢,对不对,那所以说这种是不是直接pass掉了呀,对吧?好,那现在问题来了,我们怎样能从这里边儿找到各自的那个登录记录,对吧,这个我要找到中间那个,这个呢,我要找到第一个,诶怎样能把它拿出来。
11:07
怎样拿出来,哎,这个好不好呢?来大家分析分析啊,你说这个东西咱可以怎样去找。咱有,首先咱们要是想从ods层去拿数据的话,我们首先也得再做一个操作,什么操作呢?就是对绘画是不是得先进行一个划分呀,对吧?啊,因为你是从ods log这张表里取数嘛,对吧?当然有的学朋同说了啊,诶,我刚才那张页面浏览市值表我不已经分过一次了吗?我从那张表里取数不行吗?其实原则上来说呢,其其实理论上都是也行,对吧,你从那两边取出是不是就不用再划分文化了呀,对吧,没没错吧,哎,但是这边我们通常不这么做啊,因为我们数仓当中,我们通常有这样的规范,什么规范呢?就是我们同层啊,尽量不要有依赖,对不?咱们现在是不是都是DWD层啊,对吧,DW结我尽量是不是都从ods层取分没问题吧,所以从层尽量不要有依赖,所以在这儿呢,咱们不让他去拿DWD的表啊,还是让从ods层拿好,拿到之后呢,首先是不是还得再先做一个绘画的划分一下,对吧?假如说绘画已经分好了,那接下来呢,接下来怎样从这些不同的绘画里边去找我们规定的那个登录记录。
12:18
嗯,怎样去找啊,哎,大家分析分析,其实这个问题稍微的转化一下,你就能把它转成一个分组top n的问题,稍微的转化一下就行,大家想想,你说可以怎么转化一下。啊,怎么转换一下。注意啊,那这些是没有登录的,这个页浏记录对吧,但是没有UID的,这些都是带有UID的,这个也是带有的,这个也是带有的,这些都是带有UID的,那这个呢,是没有U的ID的,对吧?OK啊好,那这些记录稍微的转化一下,你就能转成一个分组淘N。哎,分析分析,这个怎么样,怎么样能转成一个分组草本。啊,就是可能大家写的三数还是少对吧,这个做的分析还是少,可能还不是那么的灵活啊这个东西啊。
13:06
琢磨琢磨,怎样能转成分组套文?能想到不?你想想,只要我们把绘画划分好了对不对?然后呢,我们对这个数据做一个过滤,哎,咱把什么样的数据过滤掉呢?把那些就是没有右段ID的过滤掉,也就是说我们只留下登录状态的页面浏览记录,对不对?好,过滤掉之后,你像这样的绘画是不是整个被过滤掉了对不对,那这样的绘画呢,是不是前面一半给过滤掉了对不对,那我们剩下的是不是只有这个,就是有U的ID的页面浏览记录了,对不对?好,那你看啊,像这种绘画直接全部删除没有了,那这个绘画呢,只剩下了一半。那这种绘画呢,是不是整个全留下来了,对不对?你看咱们现在这个问题变成了什么?是不是变成了获取每个绘画的第一个页面了,对不对,每个绘画的第一个页面,那不就是分组top吗?对不对?我们按照绘画三个组取一下排名第一的那个页面,那不就OK了吗?对不对,这其实也是一个分组草文,只不过呢,需要咱们稍微的转化一下,就加一个过滤就完事儿了啊OK,好了,那这样一来的话呢,我们基本上就明确下来了,我们去寻找这个登录操作的这个思路了。好了,明确下来之后,咱们看一下这个circle到底咋写啊来,首先我们要做的第一个工作就是划分会画,对吧?这个我快速的操作一下啊,S select from,然后呢,找到ods应该是哪张表呢?应该是log INC之后呢,我们过滤一下DT等于2020杠零六杠幺四,那之后我们需要是不是先先干啥呀,是不是需要先对数据进行一个这个会画的划分呀,对吧?当然在会画划分之前,我需要先对数据做一个什么过滤吧,对吧?我们这儿只要那个配置等于一是不是not not那种啊,对吧。
14:45
我们只要页面启动日我们是不需要的,对吧?啊,那当然呢,我还是先从里边把那几个关键的字段先给它选出来啊,那在这儿呢,我还是先选一个common里边的midd,诶选一个呃配置里边的这个配置下下ID,再选一个配置里的LAST5换一下,换一下位置吧,这个把呃谁呢,把那个last位置放前面啊配置里边的last,呃,然后A这个PA1下下ad拿出来,最后呢,再拿一个TS。
15:16
呃,当然这里边我们有一些字段可能没拿,这边有很多维度属性是不是都得取自于这里边啊对吧?这个我为了简化啊,我暂时先不取,因为都选出来太乱了啊好,咱还是先把这几个关键的字段查出来,之后呢,我们需要怎么做来着,要想划分绘画是不是得先去构造一个字段,这样呢,我们给它加上一个呃,应该是if就行了,对吧?If什们呢?呃,配置点last下角线配置下划线ad not now,哎,然后呢,我们就返回这TS,否则呢,我们就返回哎,应该是一字now吧,哎,如果为now是返回TS啊,不为呢,我们再返回now对吧?来在这呢,我们来一个session s,然后下划线point point查一下走好,大家来看,那现在这个数据是不是已经实现我们这个效果了,对吧?那之后呢,我们再对它进行一个呃子查询的嵌套,那之后呢,再对它进行这个选择啊来这个拿过来,这边呢,我们给他往前推一下,呃,在这儿咱们起个别名就叫T1就行啊来我们select之后呢,From t1,呃这回呢,我们就可以从里边选择咱们所需的。
16:16
断了,第一个就是呃,M midd选出来,呃,第二一个呢,应该是last配置ID选出来,然后第三个是配置ID,再往下是什么?是那个TS对吧?然后再往下是不是就可以选择呃,那个last value来对吧?来一个last value里边的它放谁?应该是放session start point,然后呢,传一个处对吧?那后边来一个啥,应该是over over里边的,我们应该给他放一个part by by谁BYTSBY那个mid对吧?那后边来一个o o by by谁。应该是BY那个TS对吧?OK,好,那现在呢,我们把这个先放在这儿,放之后呢,我们就其实相当于再给他拼接一下,就能拿到那个最终的CID了啊来,在这儿我们来一个contact midd先给它放进来,那之后呢,给它放上一个连字符,那后边呢,给它放上咱们的那个时间,这就是我们得到的session下午线ID,好,那现在呢,我们把这个S执行一下啊呃,在这呢,我先把那个呃绘画切一下,我先切成那个呃,E seco t先切成Spark来往下走,把这个咱们执行一下,走你啊,然后这边咱们再回过头来,诶拉到这儿,好,那现在绘画咱们相当于就已经划分好了,对吧,这个呢,我就不再执行了啊,我就不再执行了。好,那接下来咱们应该做什么工作?
17:30
啊,绘画已经划分好了,对吧?这个已经做过一遍了,所以说可能比较快啊,那接下来咱干啥?接下来是不是得过滤一下,然后把问题转成一个分组套N的问题啊对吧?好,那现在咱就开始过滤,那OK,首先我需要先CTRLX一下,把它呢作为一个子查询,然后呢,CTRLV,那这边呢,给它往前推一下啊好,那这边我们先给它把T几放上呢,把这个T2给它拿,在这之后呢,我们来一个like的from from谁的from t2,那之后咱是不是就可以做过滤了,对吧?那过滤什么呢?是不是应该把那个UID为空的过滤掉啊,对吧,也就是在这呢,我们应该是U的ID不为空才行,那U的ID咱这有吗?
18:09
没有对不对,没取出来对吧,所以说我们得怎么样,是不是一层一层的往外取啊,对吧,先从这个位置把那个UID取出来,应该是从common里边取,对吧?点UID好取出来,取出来之后这儿是不是咱们就能选一个UID了,对吧?选出来,选出来之后这咱就能够过滤了,应该是u ID is not对吧,咱给它过滤出来,好,那现在问一下大家,你说我这个既然要过滤,我能不能先过滤再加这个绘画ID呢?能不能吸烟过滤呢?先过滤再加会有ID行不行,因为你要是先过滤,是不是我这个数据量能少一点啊对不对,那理论上我这个效率是不是能高一点,对不对,那咱这能不能先过滤再加绘画ID呢?咱这现在是先加的绘画ID,再做的过滤,对吧?能不能先过滤,不行,不能先过滤,因为对,因为你要知道咱们加绘画ID的时候,我们需要用的什么,需要用到每一个绘画的起点,对不对,但是有些起点也就是第一个页面乱记,它可能是没有登录的,对吧,你没登录你直接给它过滤掉了,那你这个起点找不着了,绘画ID没法加了,对吧?这个一定要注意,得先加绘画ID再去做过滤,这个一定要注意啊,现在接下来我们继续往上看,呃,加过滤之后呢,我们就能从里边选咱们所需的每个字段了啊UID拿出来,呃,MID拿出来,那之后呢,就是那个last加ID拿出来,然后呢,配置ID,诶咱拿出来,拿出来之后呢,这个TS咱也给它放在这儿吧,那之后再往下我们就可以干啥了,可以干啥了,是不是就可以去呃做那个分组top n的一个准备了呀,对不对,你想啊,我们现在已经完成了。
19:38
和过滤了,就是现在我们已经呃剩下的这个什么呀,已经剩下的这个所有的货啊,都是那些登录状态了了,我们接下来要做的是不是就是从这里边取美个绘画的第一个页面呀,对不对,所以说首先第一步我们要做的就是啥分组套嘛,首先就得加rank开窗吧,对不对,所以在这我们先得加上一个rank开窗函数里边呢,咱们写上part申BYBY。
20:00
By by session ID吧,是不是按照绘画去找啊,对吧,取美国绘画的第一个嘛,对吧,所以part by session ID,那后边是不是有order by order by by by谁应该是拜那个TS吧,OK,那TS我们应该是升序还是降序呢。琢磨琢磨我们要取的是什么,是它的第一个对不对,所以说应该保证是不是最早的排在最前面对不对,显然就应该是升序吧,是不是直接o by ts就完事了对不对?OK,那这个应该是比较简单的,OK,那在这呢,我们来一个2K好了,那这个呃,2K咱们就算是加好了,加好之后我们现在只剩下最后一步了,那就啥呀,是不是过滤呀,对吧,只剩下最后一步过滤了,所以在这呢,我们还得再给它CTRLX来一个这个子查询啊,就给给它放进去,就记住分组套文在have当中,咱们就是固定的套路runnk开窗啊,然后呢再加外过滤,对吧,那这样呢,我们给它起一个T3,那之后我们select from这个T3LD啊,然后呢,让这个题单,呃之后呢,我们加上一个最好的外部应该是where r k等于等于一对不对,OK,那这样一来我们就拿到了我们想要的那个所谓的登录记录了啊来,我们现在select这里边,呃,当然咱这需要什么字段呢?咱选什么就行了啊这里边咱是不是需要这么多的维度属性啊,对吧,那这个里边其实。
21:18
都是从呃,我们这个最开始的这个四查群里边一层一层的选出来的,对吧?这个呢,我就不再补充了,大家知道怎么选就完事了啊,行了,那我们这张表的这个数据状态的核心逻辑就算是完成了啊,剩下的我就不再重复了,我们把文档当中的最终的circle拿出来,咱看一眼就行了啊来这个往下走,往下走,往下走,这个其实跟刚才咱写的应该没啥太大的区别啊,来,我直接把我写的覆盖掉了啊,CTRLV放在这儿好了,那现在呢,我们看看它这块到底是怎么去做的啊,咱还是先从紫砂群开始看起,呃,首先我们先看最内层的紫砂群,先看这一层,看这。呃,Select from o DS log AC,对吧?然后呢,找到DT10字号分区,然后配置一三跟我们写的都是一样的,之后呢,这个位置是不是为我们的这个划分绘画做了一个准备工作呀,对吧?之后外边是不是嵌套了一子查询,然后往上翻,往上翻来看这个位置到目前为止,截止到这儿是不是就完成了绘画的划分了呀,对吧,顺带着把那些维度属性也都选出来了,那接下来我们再往上走,完成了绘画的划分之后,它就可以去做过滤了,对吧?你看是不是就是select from这个T2,然后呢,User ID部位空给它过滤了呀,对吧?好,过滤之后往上翻到这儿是不是就可以选择出来我们所需的这些字段,以及加上一个RN,它这用的是number啊,其实咱这呢用RK,其实也一样的,因为我们TS应该是没重复的,对吧?啊,这个理解一下好,那完成之后呢,我们相当于就已经拿到了我们所需的这些,呃,这个就是登录状态的浏览记录了,OK,那之后我们就只剩下最后一步就是过滤了,对吧?然后s that from往上走是不是就是where?
22:52
而N等于一呀,对吧,给它过滤出来了,当然拿到这一步之后,我们还有一步需要去做,哪一步需要去做呀,是不是就是join一下那个省份表啊,对吧,主要是为了拿到这个pro ID,所以他这又嵌了一个自查询,然后呢跟这张表进行关联,关联之后上边就能得到咱们最终的结果了啊,这个最终结果呢,里边稍微注意一下啊,就是这个时间咱要处理一下,我们需要用原来的那个TS是不是转成具体的年月日十分秒,还有年月日啊,对吧,这个理解一下,最终结果就放到这张表14号分区就完事了,哎,这是咱们这张表的数据状,载了完整语句来视频我给他停一。
我来说两句