00:00
呃,来好,各位同学,那现在呢,我们先看一下,呃,刚才布置的第一个需求,第一需求呢,我们称之为用户变动统计对吧?这个变动统计里边呢,我们一共就需要算两个数,一个呢就是每一天的新增的流失用数,还有一个就是每天的是不是新增的这个回流用数啊对吧,咱这个俩一个一个来,OK,那咱们首先就得先分析分析对吧?那我这个呃就是这个这个这个呃就是需要用到的业务过程是谁对吧?咱得先把这些东西搞清楚,是不是才能去算这个值啊对吧?那咱们先一个来,我们先看第一个,大家来看咱们这个第一个所谓的流失用户数,我们应该对应的是哪个业务过程啊。流失,那你要想知道是哪个业务过程,咱是不是得搞清楚到底什么叫做流失用户,什么叫流失用户来着啊,就是之前活跃过对吧?然后呃,之前活跃,然后最近一天一段时间呢,他没有活跃,然后我们是不是就称之为是一个所谓的流失用户啊,对吧?那当然在这儿我们要求的是每天的全部的流失用户数吗?并不是,我们要求的是什么是每一天新增的流失用户数,对吧?那比如说什么叫做每一天的新增用户数呢?流失用户呢?啊,什么叫做每一天新增的流失用户呢?啊,比如说我们以今天为例,对吧?那如果有一个用户对不对?OK,那他到今天恰好是他没活跃的第七天对不对,那OK,他是不是就是一个今天的新增的流失用户啊,对吧,那我们要找的是这样的人,OK,好,那在这儿我们定义的这个所谓的活跃对不对?OK,活跃或没活跃指的什么呢?就是登录对吧?登录即为活跃,那所以说从这个角度来看,那它对应的业务过程,那肯定是谁,那肯定是登录啊,对吧。
01:40
哎,咱们得从登录这个相关的表当中去寻找我们所需要的数据,OK,那登录对,在DWD层我们有一张是什么表,DW层有一张是这个登录的明细事实表,对吧?OK,说实话,从那张表里我能不能找到我们今天的这个流失用户数呢?能不能找到,你想啊,从明细表里,明细表里理论上我们里边会存储什么,会存储是不是每一个用户的每一次登录记录啊,对吧?OK,那大家想一想,你说我能不能求出来,诶根据他能不能求出来,我们这个所谓的流失用户数能不能求出来,如果不能,为什么不能,如果能怎么求,咱们得分析分析下,对吧,要是能的话怎么求?
02:19
从明细表里怎样能找到我们今天的新增的流失用户数对不对?怎么能求出来,怎么能求出来?啊,什么叫做新,什么叫做今天新增的流行,说来着是到今天正好是他没活跃的第七天,对不对,没没没问题吧,好,换一句话说就是什么呢?就是他最后一次活跃时间是在七天前那一天吧,对不对?所以说我要根据这个明细数据求的话,咱们怎么求就行了,我先得求出来每个用户的最后一次活跃的日期吧,是这个道理吧,OK,那怎样能从明细里边找到每个用户的最后一次活跃日期呢?很简单,找到全部数据,按照用户分组求一下活跃日期的最大值,你不能不就求出来每个人的最后一次活跃的日期了吗?对吧,你只要求出来的最后一次活跃日期了,那再我我再怎么去求流失用户,只要那个活跃日期等于七天前的日期,它是不是就是我们今天要找的一个今天新增的流失用户啊,对不对,所以说你从这个明细数,这求肯定是能求出来的,但是你能够感觉出来这个效率是不是比较低啊,对吧,你需要拿全表数据对不对,这个显然是不太合适的,OK,所以说我们现在呢,是不是也得考虑考虑考虑什么,诶我在D。
03:34
S层有没有这个登录的这个业务过程的汇总表,如果有我能不能直接用对吧?那咱这有没有呢?有没有去找一下呗,找一下DWS层的表啊,应该是什么域的,应该是用户域对吧?找到DWSU字在哪呢?在这对不对?那咱这有汇总表没?诶怎么这是DWD啊,这还挺不好找,在哪呢?呃,在这儿呢?在这儿呢,找到了是不是这个就是用户域的一张这个汇总表,然后呢,对应的业务过程是不是就是log给啊对吧?好,那咱这张表它的结构大家还记得吗?这是一个什么力度的,这是一个用户力度的啊OK,好,那这张表当中每行数据存储的是不是就是一个用户啊对吧?那完了之后对应的统计周期呢?是历史资金,那完了之后,咱这里边是不是正好就有一个所谓的呃,末次登录日期啊,呃,是这个这个这个末次登录日期啊,对吧,就是最后一次活跃的日期,OK好,那也就是在这儿我本身就已经计算出来了每一个用户的末次活跃日期了,那我是不是就不用再从咱们的明细层里边去拿了呀,对吧。
04:34
诶,所以在这儿我们直接用它,那就完事儿了,OK,好,那现在呢,我们这个大体的思路基本上也就确定下来了,好,那现在呢,我们把这个词索具体的实现一下,我们直接select,然后from DS,然后呢,找到呃,这个用户域,用户力度log in TD对吧?完之后呢,注意,那大家还记不记得这种T地表的分区规划是什么?还记得吗?T地表是不是一天一个分区,每天的分区存放的是不是就是历史截止当天的全部的登录记录的汇总结果,对不对?那我们现在要拿的是什么?我们现在要拿的是每个用户的是不是截止到今天的一个末次活跃日期啊对吧?OK,好,那所以说我们应该只拿他的一个分区就行了,对吧?拿它最新的一个分区,2020杠零六杠幺四,这里边是不是就已经拿到了所有用户了,对吧?这个一定要搞清楚啊好,那完之后我们继续往下进行,哎,我们需要用的是什么,是需要的是什么样来着,是那种末次回跃日期在七天前那一天的,对吧?所以在这儿是不是直接加一个V2过滤条件就行了,对不对,这个怎么写。
05:34
它是log in date是不是last对吧?得等于七天前的那个日期,OK,那这个七天前我在我在这呢,到底是减六呢还是减七呢?这个得分析分析啊,减六还是减七分析分析啊,假如最新一天是14号对吧?OK,好,那完之后呢,前面13 12 11,十九八七,OK,那这是七号,这一共是几天,一天两天,三天,四天,五天,六天,七天,这是他没活跃的这七天对吧?14号正好是第七天,好那玩意的话,末子回日期是不是应该是七号才行对吧?七号跟14是应该是差七,所以在这儿呢,我们应该减七才行啊在这呢来一个data sub对吧?Subb啊呃哎,然后呢,把当天日期给它放进去,诶2020杠零六杠幺四,后边呢,我们给它减一个七,诶那这样一来的话呢,我们就已经找到了我们今天新增的这个流失用户了,然后我在下边转的时候呢,我看有同学他确实差不多是这么写的啊,但有一点区别,但是怎么写的呢。
06:34
他是这么写的。他这么写的,诶小于等于,那说他要是这么写,咱们求出来呢,是今天新增的流失用户数吗?不是,他求的是什么呀,相当于是全部的流失用户数吧,对不对,那这个大家一定要搞清楚啊,所以在这儿呢,我们要的是每天新增的应该是等于对吧,也是到今天正好是第七天没活跃,这个一定要理解一下啊,行了,那用户找到了之后,我们需要对他做一个什么总结,是不是看一下就完事了,对吧?那这个需要什么?Count的有点D吗?需要吗?不需要,因为咱这面是什么力度呢?用户力度一行就是一个用户,那OK,那也就是我这么算,就已经把咱们第一个值给求出来了,当然这个结果是零,因为我们数据是不是只有一天呀,对吧?这个理解一下好了,那这个所谓的流失就求出来了,你看这难吗?这个其实不难对吧?好,那接下来我们看第二一个数,第二一个是回流,说实话这个回流可能稍微有点难啊,这个回流怎么求啊。
07:31
来咱们再回忆一下这个回流,它的这个定义是什么啊,OK,什么叫回流来着,就是之前OK,那它是活跃过的,然后呢,中间有过一段流失的经历,对吧?那所谓的流失的经历是什么意思呢?啊,就是有连续大于等于七天是没活跃的,对吧?OK,好,那完之后今天他又活跃了,OK好,那这就是一个今天的回流用户对不对,这就是他的一个定义,好我怎样能找到这样的用户呢?怎样找,咱们是不是得知道这样的用户他有什么特点呀,对吧?OK,来咱们分析一下啊,首先今天的回流用户,那他一定是出自于今天的活跃用户的,这个大家应该是能够搞清楚的,对吧,对吧,你得是今天活跃了我才可能是哎回流嘛,对吧,OK好,那也就是说今天所有的互流用户,他的末次活跃日期,也就是最后一次活跃日期,你说都是哪一天,是不是都是今天的日期。
08:26
是是这个道理吧,OK,好,那完事之后呢,我们再来观察一下啊,那你想一想,你说他的上次活跃日期应该有什么特点,上次我这是末次,最后一次是今天对吧?这个已经没有争议了,对不对?好,那他的上次呢,应该跟末次中间是不是得相隔七天几以上,是这个道理吧?OK,那所有的这个回流用户是不是都满足这样的一个特点,对吧?OK,好,那我们是不是就可以想办法根据这个特点去寻找咱们所谓的回流用户啊,对吧?啊,OK,好,那现在咱们总结一下啊,OK,所有的回流用户都具备这样的一个特点,首先第一个就是它的末次活跃日期是等于今天的日期的,完之后呢,上次活跃日期诶距今天应该是相隔是不是七天几以上的呀,对吧?诶,这一点大家应该给他明确,好,那现在关键问题来了。
09:21
怎样去求每个用户的上次活跃日期呢?对不对,说实话这个可能不是那么的好学,对吧,末次活跃日期我们这儿好说,对不对,是不是我们那个TD表当中就有每个用户的末次活跃日期啊,对吧?每天我们都会算一下每个用户的莫测回日期,关键是上次回日期怎么求,上次回日期怎么求。上次或是这个怎么求。啊,咱们有统计过每个人的上次活跃日期吗?没统计过对不对?那怎样能求出来他的上次活跃日期?
10:00
怎样能求出来DW哪一层,DWD层,然后呢?啊对每个用户都去排个序对吧?啊,那这个肯定能求出来,加一个RA呗,对不对啊,肯定能求出来,但是这个效率不高对吧?咱们暂时先不考虑这种发,但是肯定能求出来啊,啊DWS咱们能不能想办法给他求一下。DWS。哎,有有有一个考虑到了,我听听大家说什么呢,说TD表就log in TD那张表啊,完了之后呢,是什么,前一天的分区里边的末次回日期。它是不是就是它的什么上次回议日期,诶是这样的吗?是不是让咱们来分析分析啊,来咱们分析分析,来各位同学我们来看一下,呃,这个呢,我们必须得分情况去讨论啊,OK,那假定呢,咱们先只考虑什么啊,先只考虑我们今天的活跃用户,呃,先只考虑今天的活跃用户啊,大家想一想今天的活跃用户,那他的今天的也就是14号的墨子活跃日期是不是肯定是14号啊对吧?好,那你想一想这部分人他的末次回日期是不是在今天已经发生变化了呀,对吧?什么意思呢?你今天活跃了,那你今天的末次回日期跟你13号的末次活跃日期。
11:20
是不是肯定是不一样的,对不对,既然是不一样,那你13号的那个莫索回日期,你说是不是就是他的上次回日期。是是不是应该是这样的吧,OK啊,所以说其实对于今天的活跃用户而言,OK好,那他前一个分区的末次活跃日期确实就是他的上次活跃日期,哎,但是仅是对于今天的活跃用户而言,那如果这个人今天他没活跃对不对,OK好,那他既然没活跃对不对,那就意味着什么,意味着他14号的末斯回日期跟他13号的末斯回约日期是什么样的,是一样的,对不对,你是这个道理吧,因为今天你没活跃嘛,没活跃你是不是取对着前一个墨子活跃日期啊,对吧?诶他俩是一样的,那所以说对于这样的人来说,你不能说他上一个分区对吧,那个墨子活日期是他的上一次活跃日期,你不能那么说,对吧?诶,这一点咱们要搞清楚,所以说你这个得分情况去说好了,那刚才我们已经说过了。
12:17
就是今天的回流用户,他一定是出自于今天的什么呢。活跃用户的今天的活跃流一定出自于今天的活跃用户,对不对,那所以说那我们一会儿就可以怎么办呢?我可以先这样做啊,我先找到14号的活跃对不对,然后呢,再根据刚才咱们的这个逻辑去找一下这部分人的是不是上次活跃日期啊,对不对,这个是不是直接从前一个分区找就完事了,对吧?OK,好,那完了之后呢,再怎么做,诶再去比较一下它的上次回日期跟他的末次回日期中间是不是相隔八,相隔八的,那就是咱们要找的对不对,是这个道理吧,诶一其实咱们这儿可以这么去做啊,好了,这个思路大概就说清楚了,接下来呢,我们给他实现一下啊,其实说实话这个circle不难写,说白了就是查询,照一下基本上就完事了,对不对,最后看了一下就完成,Circle不难写,难在这个逻辑上边,可能就是啊好,那现在咱们一起来看一下,呃,首先我们先来完成咱们的第一个任务,就是我们得先找到今天的活跃用户对不对?好,今天的活跃用户怎么找?
13:19
怎么找到今天的活跃用户啊,其实很很简单,末测活跃日期等于今天,是不是就是今天的活跃啊对吧?好,那在这呢,我们直接来一个light from dws层的用户,然后呢,用户力度,然后log TD这张表,然后呢,我们也是只需要拿到CTRLC拿过来啊,拿到它的十四二分区完之后呢,我们需要满足一个条件,就是啥呢?哎,就是末测活跃日期得等于今天对吧?Login date data,然后呢,Last,对吧,得等于2020杠零六杠幺四对不对?那这样一来我们就拿到了6月14号这一天的活跃用户,这个没有问题对吧?好,那接下来我们继续往下走,然后还得怎么办呢?是不是得去找一下这部分人的上次的活跃日期啊,对吧?好,那这问怎么去找呢?是不是理论上咱们得拿这张表的前一个分啊,对吧?好,那接下来呢,我们就基于它给它CTRLC拿过来,呃,之后呢,在这我们选一个这个,呃,这个分区咱们得选前一点的分区啊,CTRLX,然后呢来一个。
14:20
啊,是不是sub就行了,对吧?萨完了之后呢,我们来一个啥,来一个UID,那呃什么u ID data sub呃,完了之后呢,放一个当天日期啊2020杠零六杠,诶幺四完里边我们放一个啥呢?诶放一个一是不是就行,对吧?这样一来我们就能拿到前一天的分区了,好,拿到前一天分区之后呢,我们需要拿到啥?是不是拿到它前一个这个所谓的login data last呀,对吧?好,那完之后我们接下来需要干啥。接下来咱是不是需要把这个跟这个去做一个关联呀,对吧,你是不是只有关联上之后我才能用啥,我是不是才能用这个人的末次跟他的上次怀孕日期进行一个对比啊,对吧?OK,那为了对,为了方便对比,咱这样啊,在这儿呢,我把他的这个log in data来last也给他拿出来,那这个没问题吧,那OK,对于今天的活跃用户而言,这个login last是他真正的末次活跃日期,对吧?OK,那这个login that data last呢,是不是他的上一个活跃日期,也就是上次活跃日期啊对不对,这个大家应该能想明白的啊好,那你要想对它来进行比较,那它俩是不是得进行一个关联呀,对吧?那关联咱这用哪种关联方式呢?啊对,用哪种方关联方式其实还是取决于什么啊,还是取决首先第一个他俩的包含关系对吧,还有一个就是什么呢?就是咱们最终想要谁对吧?好,那到底是怎么回事,咱们来分析分析啊,首先那咱们来看这儿,这儿咱拿到的是什么。
15:40
这儿咱拿到的是14号的活跃用户,这个没问题对吧?那这个拿到的是什么?这个相当于是13号那个分区的,这个是不是全部用户啊,对吧?也就是截至13号的全部用户没问题吧?OK,那他俩的对应关系应该是哪种呢?应该是互不包含有交集,也有各自独有的部分啊,为什么这样的呢?来分析大家首先先看交集,交集这部分指的是什么呢?指的是原来的用户,今天它活跃了,对吧?这是14号的活跃啊,这是13号的全部没问题吧?诶,OK,那这一部分用户呢?
16:16
13号没有它,那然后14号有的,这时是新增啊对吧?好,那这一部分呢,这是13号的活,呃那个呃这这这也不叫13,这应该是呃原来的用户,然后呢,今天他没活跃对不对,应该是这样一个逻辑吧,好这三个数据就摆在那儿了,那你说我们这应该要谁呢。很简单,你你要你要知道就是今天的活回流用户一定是出自于什么呢?一定是出于今天的活跃用户,对不对,没问题吧,OK,好,那完了之后呢,呃,今天的活跃用户,首先下边这是不是就不满足这部分用户,是不是今天没活跃啊,对吧?所以这个我们的回跃流肯定不出于这不出自于这部分,所以这块呢,咱们不能要,对不对,那今天的活跃是不是这块啊,对吧?然后你要注意上边这部分人,虽然他也是今增的活跃,但是他是今天的什么是新增,新增能称为回流吗?那肯定是不能啊,对吧,回流有条件呀,前边活跃过一段时间没活跃,今天又回了,那你既然活跃过是不是老用户才行啊,对吧?所以这部分肯定也不能要,所以我们能要的其实只有这部分对吧,那怎样能返回这部分呢?In到对不对,所以说在这儿呢,我们采用in到就可以了啊来CTRLX,我们这来一个括号啊来这个回车,然后CTRLV这它拿过来,呃,这边呢,咱们CTRLX,诶我们也给它拿过来,CTRLV这边呢,咱也给它往前推一下,呃,然后呢,我们给这俩各自起。
17:34
一个别名,这个呢,咱随便起吧,它叫T1,然后这边我们叫做T2啊好,那T1跟T2刚才我们分析过了,应该采用一个in the draw,那下边的连接条件应该是什么呢?应该是T一点,是不是user,呃,下划线ID等于T2点user_ID呀,对吧?好,那这个关联上之后,我们是不是就可以从里边去选择我们所需的数据了呀,对吧?好,那咱们现在需要的应该是什么样的,需要的是不是应该是这个和这个日期相差八天几以上,诶七天几以上啊对吧,中间相隔七天几以上,是不是才是我们想要的那个所谓的回流用户啊,对吧?诶是这样的啊,这个是这个人的末次回日期,这个是这个人的,诶这个上次回日期,对吧?他俩相差八七点几以上,我们才能要啊,OK,好,那接下来我们是不是就可以做这个过滤了呀,对吧?好,那在这儿呢,我们做一个过滤,呃,这儿为了方便呢,我这给它起一个这个别名吧,这个我们应该叫做什么呢?叫做log in,然后下线date previous啊这个VI。
18:34
好,那这个是上次,这个呢是墨斯,好,那我们是不是得保证它跟它相差得七天几以上对吧?那这个怎么去比啊,它俩减一下是不是就行对吧?俩日期相减用谁来着?Data dif是不是就行dif,那完了后呢,我们把第一个日期先放进去login data诶,Last完之后呢,再把第二个log data previous给它放下去,好那么它俩应该是大于等于几的,咱们都知道,应该保证它俩相隔七天几以上对吧?那应该是大于等于几呢?应该是大于等于八才行吧,对不对,这个咱们大概比一下啊,比如说这个呢,是我们的14号对不对?那完了之后呢,呃,假如说这个呢,是他上次回的日期,中间是不是得有七天几以上对吧?哎,13 12 11,十九八,呃,然后七,这是不是正好是七天呀,对吧,那这是七号,这个是不是应该是六号啊对吧,六跟14是不是差八呀,对吧?所以这样呢,得大于等于八才行,OK,好,那这样一来我们就把我们所需要的用户过滤出来了,之后我们只需要再来一个count的芯,那是不是就。
19:34
得到了所谓的今天的回流用户数了,那当然这个结果呢,应该也是零啊,是因为我们数据的问题啊,好了,那这个回流咱们也就算出来了,哎,好了,两个都算完之后,视频我给他停一下啊。
我来说两句