00:00
来吧,那留存率这个需求的定义呢,我们先简单回顾一下啊,这个已经说了不下,这个算是这遍不下三遍了,是吧?呃,再回顾一下吧,所谓的留存率是什么啊,我们需要知道啊,比如6月14号呢,我们有一个新增的用户是100个啊,然后呢,六月十四一天之后来到了六月15啊,然后14号新增的100人里边有80个人活跃了,那这时候呢,我们就说6月14号的这个鞋太难看了啊,6月14号的吉日留存呀,一日要是一天之后嘛,留留留下多少个,留下80个对吧,那相当于是有80个留存。啊,那6月14号的一日留存率是多少呢?那就是80%,这是不是就是留存率的概念啊,咱们搞清楚,然后我们现在大家得搞搞清楚一个问题啊,就是我们现在要要要求大家去算的是几日留存呢?要求大家去算的是每一天的一日留存,两日留存和三日留存。是每一天的123464,能理解吧,这个我们大家得搞清楚啊。
01:03
好,那我们就来分析分析,那这个每一天的123日留存,咱们到底怎么算啊。啊,道理怎么算?那在之前给大家部署这个留存任务的时候,我当时就给大家提了一个醒,什么型呢?要求大家在做这个需求之前先明确一个问题,什么问题啊,就是你这一天的计算任务应该是啥,对吧?啊,一旦接到任务具体就是什么,就具体就是说你应该计算的是哪一天的几日留存,是不应该是这个问题啊好,那这个问题大家自己想明白没啊,想没想明白呀?可能有的同学压根就没想对吧,就忘了这句话了,上来就开始写词课了,是吧?啊,那我们就来分析分析,这一天的计算任务到底应该是什么啊,到底应该是什么?哎,我们分析一下。来。来分析一下啊呃,咱们现在呢,也先不用去分析,就是说先不用去分析这一天到底是计算是什么,我们先这样吧,因为我们现在是不是要求我们需要大家去计算每一天的123日流水啊,对不对,我们来就来先举一个例子,比如说我们先计算一下2020年杠零六杠幺四啊,就算这一天的啊什么一日。
02:16
三。然后那个日啊留存留存率啊,我们是要算的是这样的,这个三天的这个留存率啊,对不对啊,那OK ctrl c啊,CTRLVCTRLV啊,我算这三年的,那当然这个呢,应该是一日。这个呢是两日。这个呢是三日。咱们算的是14啊,这个14号的123日留存对不对,那咱们先分析分析这仨留存,我们看看他是不是一天的计算任务啊,有好的同学可能会上来,就是不是上来之后就这么去算的呀,把这个作为一天的计算任务对不对?那他们去他们真的是一天的计算任务吗?我们来分析分析啊,你假如说我现在想算6月14号的一日留存,你说我需要什么数据啊?
03:05
是不是需要6月14号的新增对不对,那这个得先搞清楚啊,CTRLC。啊,CTRLV啊得这个新增这肯定都知道了啊,那还需要啥呀,是不是还需要6月14号的一日留存对不对,因为留存率怎么来的,留存率是不是就是留存数除以新增数啊,对吧?那6月14号的留存用户,呃呃,得是一日留存啊,6月14号的一日留存用户,你说它出自于哪儿啊,肯定是出自于14号一天之后,也就是15号的活跃用户吧,是不是肯定出自于他,那也就是说我们这儿想算这个留存率的话呢,我还得拿着谁呀?还得拿到6月15号的活跃之后,是不是才能拿到它那个留存数,拿到留存数之后是不是才能算留存率,对吧?那所以这个呢,应该是诶活跃,哎,那这个咱们分析完了对不对,那接下来我们看这个,那他所需要的数据应该是啥呀。
04:05
是不是同样的道理,应该属于14需要14号的新增,以及14号两天之后,也就是16号的活跃吧,没错吧,那同样的道理,那下面这个呢,CTRLV你所需要的应该是17号的活跃,没错吧?啊,是这样的啊好,那既然是这样的话,我们来看看啊,这仨留存率都应该是哪天能够计算啊,那这个应该哪天算呀。你什么时候才能拿15号的活跃,16才能拿到,对不对,没错吧,那下边那个呢,是不是17号才能算,这是16,下面那个呢,18号才能算,那所以说你会发现你这个同一天的1232留存,它并不是在同一天计算的,对不对。啊,那我们同一天的计算任务,它到底应该是什么呢?啊,那举个例子啊,比如说咱们现在就以18号为例吧,咱比如说我现在呢,诶问他一下2020年杠零六杠18,哎,6月18号的计算任务应该是什么。
05:05
那首先肯定有一个14号的三日留存,这肯定没问题,对吧?啊,刚才刚分析的没错吧,那还有什么呀。哎,对,应该还有一个15号的两日留存吧,对不对,15号的两日还有一个什么。还有一个16号的一日,没错吧,这是不是才是咱们这一天的计算任务。啊啊,为什么他们仨放在一起了啊,为什么14号的三日,15的两日和16号的一日留存放在一起去计算了,为什么呀,他们仨有一个共同特点。他们都需要谁?他们是不是都需要17号的活跃呀?不对,你看啊,14号的三日留存,那我是不是需要,呃,需要这个17号的活跃,那15号的两日,十二两天之后是不是也是17号啊,你是不是也是需要需要17号的活跃,那16号的一日是不是就是16号一天之后也是17号的活跃,所以说那他们仨的共同点就是什么。
06:15
啊,我都需要17号的活跃。那17号的活跃什么时候才出现,是不是18号才能拿到啊,所以说那他们仨就跑到同一天去计算了,其实是这么一个道理啊,是这么一个道理啊,那这时候你其实也能看一下咱们这个文档这个图啊,咱们文档图是不是有一个相当于有一个台阶,对吧,后边缺一块对不对,这缺一块是怎么来的,怎么为什么会缺一块啊,而而且缺一块为什么是斜着缺的,这是为啥呀?是不是就是咱们这个道理,你看啊。你这样能看出来我们这一个颜色啊,同一个颜色的数据是不是同一天算的呀。同一个颜色是你同一天算的啊,这个得搞清楚啊,你看这是不是都是17号了呀啊,那我们18号是不是也是斜着的啊,那跟我们这儿是不是应该是能够对应上啊,因为我们这算的时候是不是就是斜着算的,相当于是啊,我不是说一天就一次性的把14号一日三日都求出来,不是啊,我是求三天的三个留存啊,这个大家得搞清楚啊,这个我们搞清楚行,那既然这个东西咱们明确了之后呢,那其实咱们要往下算,那就很简单了,对不对,那就很简单了啊好吧,那接下来咱们就开始算吧,那我们把这个,呃,把这个粘出来吧,啊,CTRLC。
07:32
呃,咱们去做这个数仓开发的时候呢,我们其实都是这样的一个原则啊,什么原则呢?就是,呃,咱们就以一天的这个计算任务为例,把这一天的计算任务写出来,然后给它封装在脚本里,把日期一替换,是不是就可以每日定时调度了呀?那咱们这儿呢,哎,比如说就以18号的计算任务为例吧,啊,咱们以它为例,好,那既然18号呢,我要算的这个数据呢,是这样的三个,我们现在呢,可以先分开算啊,先分开算啊,那第一个任务呢,就是要算14号的三日留存率对吧?那OK,这个怎么算?首先我们需要先拿到14号的新增对不对,那14号新增怎么拿?
08:11
是不是可以select符让DW TUV topic,然后呢,然后呢,新增怎么拿。那是不是可以这样,Where login date,哎,First等于啥?哎,等于哎,2020杠零几零六杠114,这时候拿到了14号的新增,没错吧?啊,那当然呢,我们前面选一个mid啊,新增拿到了,那留存呢?是不是还得拿到14号的三日留存呀,对不对,那14号的三日留存应该是怎么拿的呀?假如说咱们现在是不是18号的计算任务呀,对吧,也就是我们现在最新的一天的数据是18号了啊,能理解吧,最新一天的数据现在是18号了,18号啊,那怎么拿到啊三日留存。
09:00
等么等,那这个36存它的条件应该是什么呀?是不是应该是在14号新增,并且在哪一天活跃,并且在17号活跃,这是不是就是6月14号的三日留存用户,没错吧?那所以说你的条件应该怎么写,应该是s SE select from DW层的也是UV topic吧啊UV topic,然后呢,你的过滤条件应该是where where啥where logging data l date first等于谁等于2020杠零几零六杠幺四。然后呢,还得加一个啥,哎,并且你末次登陆时间login last等于哪一天2020杠零六杠幺七,那你这个过滤出来的mid就是啥。这就是啥,就是我们14号的三日留存的用户吧,对不对啊,当然了,我们这儿最终要算的是留存率对不对,留存率肯定是一个值对吧,用谁除以谁啊,是不是应该是用留存的个数除以新增的个数啊,对吧?那所以咱们这选mid有用吗?其实没啥用,咱们选谁就行了,是不是看得清就行啊cut啊直接看得清啊,这就是新增,那这个呢,就是哎刘存顺。
10:19
Con啊,看得清啊,那是不是这样一来的话,我们一会儿将这俩子查询进行一个draw,然后呢,让下面这个值除以上面这个值就拿到留存率了呀,对不对,但是这块有必要这么写吗?没必要,咱这可以优化一下,咱们优化一下你看啊,这是不是又是从同一张表里查数据,然后呢,相同的处理方式,区别仅仅是外过滤条件不同,对吧?那所以这咱可以怎么做,是不是可以用三门if也是从同一张表里去查呀,对吧,那咱这儿呢,给他改一下吧,啊那我们select。From from dwt层的UV topic啊,那怎么选啊啊那比如说先拿新增吧,嗯,那就应该怎么是不是应该是sum if啊if if谁if,你的首次登录日期等于它CTRLC,那我放在这,那我就记记记个一,否则就记个零,没错吧,然后逗号啊,那然后下面呢,是不是也是summer if。
11:23
If,谁if是可以把这个拿过来啊,那当然了,应该是拿这个啊,CTRLC。我把这个钱给他放上去吧,咱们这么的。CTRLC,然后呢,放在这里边CTRLV啊CTRLV,那这样一来的话呢,我们相当于就是呃,可以的话就记一,否则就记就记零啊那再来这是不是就拿到了14号的新增,这就拿到了14号了三日留存,那是不是就完事了,所以上面这俩子查询咱们还有必要写吗?哎,没必要了,一条就搞定了啊好,你就诶这怎么回事。嗯,我把这个删了。
12:01
走诶那就可以了啊行,那剩下的事其实就比较简单了啊,剩下事咱们干啥,是不是对上边的这个东西做一个呃啥呀,做一个补充就行了呀,对不对,咱们首先这儿呢,有一个state啊统计日期,其实这个统计日期呢,在这儿并不是很重要啊,它不像我们那个新增和那个呃活跃里边那个统计日期啊,你比如说新增里边那统计日期,那指的就是啥。你统计日期是是是是是哪一天你计算的新增就是哪一天的吧,对不对,那你统计日期是哪一天,你计计算的日活就是哪一天的,对不对,但是在这儿呢,我们去标识一个,哎,留存的时候是用它标识的吗?不是你用谁标识一个留存啊。是用他俩呀,哪一天的几日留存,是不是用这个去标识一个留存啊,所这个统计日期在这呢,就无所谓啊,就无所谓啊,那我们就用到时候do do date就可以啊,这个后期直接用Dollar do date给替换一下就行,那我们的Dollar do date,我们脚本当中获取的应该是哪一天的日期啊,Dollar do date应该是计算日期的前一天对不对,那我们现在计算日期是哪一天18号对不对,那所以咱们跟他保持一致,这我们直接写谁啊,写个17号就行啊,2020杠零六杠,哎幺七这个其实无所谓的啊,他无所谓,那接下来看下一个下一该谁了,下一该在这个创这个新增日期了,对不对,那我们现在是哪一天新增。
13:24
14号对吧,在日期我们这儿可以直接先写死2020杠零六杠幺四啊这个没错吧,那接下来再往下走。该谁了?该这个留存天数量是几天,咱们算的是三天留存,只写个三是不是就行,OK,那再往下该这个留存的数量了,留存数量,那咱们是不是这个是留存数量啊,CTRLX给它放上面来啊,CTRLV啊来个逗号啊,那下边的是新增数量啊,那这也没事,最后是不是还剩一个留存率啊,留存率谁比谁留存比新增对吧?那先拿留存啊,CTRLC啊放在这儿来,来一个诶除号,那再来一个新增CTRLC,那把这个放在这,CTRL喂,诶那这样一来我们是不是就完成了留存率的计算呀,但是留存率呢,我们假如后续我们想展示的是百分比,所以你这你可以给他乘个100啊,这就是百分比那个那个值嘛。
14:19
你可以这样去做。啊,那这个就完事了吧,啊,已经完成了咱们这一天的计算任务里边的1/3了,对不对,那剩下的其实就简单剩下怎么做就行了,剩下是不是就改改改日期就可以了呀,那一会儿咱们再改啊,那现在大家来看一下这个circle就这个位置啊,大家说有没有地方可以简单的优化一下。有没有地方可以优化一下?有没有啊?嗯,其实是有的啊,其实有的啊,这个怎么怎么可以优化一下,我们在这儿呢,可以加一个过滤条件啊,那这边我可能可粘一下吧,那咱们对它进行一个修改。CTRLV,你可以对这个东西呢做一个简单的修改,怎么修改呢。
15:01
嗯嗯嗯,来看一下啊,你说我们去找这个新增的时候。是不是咱们计日期是啥呀?是这个就是log date first等于14号对不对,那你找留存的时候,是不是也有一个过滤条件。是不是跟它是一样的呀。对不对,你留存是不是在新增的基础之上再去找那个留存呀,对不对,那所以这咱们可以怎么做,是不是可以从这个表里边直接就把14号新增的给找出来呀,对不对,来我们就直接把这个CTRLC,诶CTRLC放在哪放在这。啊,当然这个得用where啊。Re,啊,Where,那当然你这个这有了V它之后呢,那上面我们就可以修改一下了啊,比如说我现在要找留存,找留存怎么做就行。呃,找找新增,找新增怎么做就行,是不是直接抗清,这是不是就新增了对不对,那找留存的,留存的话,咱们还有必要加前面这个吗?没必要了,直接给它干掉,那这是不是就是留存没错吧,那这样一来的话,我们在这个位置也能改一下,那把这样的给他修改一下。
16:06
啊,把这个先删掉吧,咱们先拿到这个留存,这就是留存没错吧,这C,然后再CTRLV啊让它除以一个新增。CTRLCCTRL,喂,这是不是没错?其实这俩结果应该是一样的吧,但是你觉得这俩哪个要好一些?那肯定下边也好一些,为啥呀,因为你这个相当于是全表遍历,是不是这一张整张表里不管你有多少条数据,说每条数据都要去做这个判断,对不对,但是下面这个呢,我相当于先过滤掉一大部分,而你这过滤掉的是一大部分啊,你要知道,假如说咱们数仓这张表里,是不是这张表里是会有全量的用户,对吧,全量用户,那假如说我们这个加上这个条件之后,那是不是只把这一天新增的过滤出来了呀,那剩下所有天新增的是不是全部干掉了呀,就所以你这个会过滤掉一大部分的数据,那显然下边这个呢,要啊数据量要更小一些,你的计算量要更小一些啊,可能这块呢,你可以做一个这样的一个简单的优化啊,但是这个呢,如果说上来直接看,没有咱们上边这种好理解对吧?啊,没准好理解,但其实下边也更好一些啊,大家知道怎么整就行啊,那比如说我们就以下边这个为例吧,啊,以下边为例,那下边为例之后我们CTRLC拿下来啊,那下边这些东西咱需要怎么做,是不是需要给它改一下日期啊对吧?T来CTRLC拿过来,那咱们看看怎么改啊。
17:24
首先这个改不改统计日期统计日期统计都是Dollar do date,以后这个不用改,这谁得改一下14这个字段是不是那个新增日期啊,咱们算的15号的两日留存,所以说你的新增日期应该是15号吧?啊,那这个三是不是得改成二啊,没错吧,那再往下。嗯,再往下这里边呢,咱得改一下了啊来这少个逗号啊,诶二逗号,那诶还有哪需要改一下,还有哪些这个用不用改。这不用改吧,呃,这用不改呢,也不用,呃,哪得改一下,这不得改一下。
18:00
哎,是不是搞定啊,那如果说你不用这种方式,你要改上面那个的话,你看看哪需要动一下啊,你要改上面那个,这是不是得改成15,这儿改成二,那所有的14是不是都得改成15,没错吧,那后边还有一个14呢,也得改成15,能理解吧,就是这么个意思啊,这么个意思,好了,那咱们接着往下走,那我们还得把这个下边也得改一下啊,那CTRL啊C。啊,CTRL可以啊,这接着改,那这个15改成16啊,那这个15改成呃16,这个二改成一,15搞定啊这仨就完事了,那当然这仨完之后呢,我们需要怎么去做呀,是不是需要将他们仨。是不是都得放到我们这张表里,那他们仨应该是怎么怎么往里放,是不应该是union啊,应该是union啊,应该是union,也就是说相当于我们每天会往这张表里插入几条数据啊,三条三条啊,三条啊三条,那进行念,那上面这个呢,我就给它注释掉吧,咱们注释掉啊好,那我们把刚才自己写的这仨the union,那应该是union all还是union。
19:07
Union all吧,这个不用去重吧,那所以说我们union all select直接union啊。嗯。UN in,然后哦,嗯,好,那完了之后呢,我们需要将这三个子查询unit念完之后的结果是不是插入到我们上面那张表里,哎,现在问一下这个一插插入咱们这怎么写。还用给这三个东西给它来个括号,来个别名啊,再加什么select吗?用吗。不用为啥,你就记住这仨select是不是就是一个select对不对,我们的语法是不是就是insert加select它所以这呢可以直接insert,然后哪张表,呃,当然了,我们这儿如果是你要是你看是into还是right吧,比如说咱们就into吧,就into上面哪张表就行了啊,来into哪张表into ADS ad user retation,哎,是不是就完事了啊,这就咱们这个需求啊。啊,当然呢,我们现在还不不要往里边插入啊,为啥呀,因为我们现在我们要做的是啥,我们说后续用脚本再往里往里导啊对不对,但是你要知道啊,你说我我现在假如说我想查看一下你这个搜写的对不对,我现在想把这仨哎这么运行一下,你说它能运行吗。
20:16
这就不行了,这有问题了,诶诶这还没问题啊,我是不是把上面选上了。啊没没问题,诶啊行吧,那没问题没问题吧,啊我现这是有问题的来着啊啊那也没问题啊,这也就是完全这仨是不是就当当成一个select处理就行了呀啊这个甄选那你看你选出来了啊,统计日期17号,那你算出来了,有15号的三日是多少,那16号的两日多少,14号的一日留存是多少,对不对,但是咱们这是因为数据的问题,这里边有一些问题,数据出不来啊,但是当你这个数据正常之后,这个就没问题了。好啊,那这块呢,我们就能够把这个结果哎,选出来了啊,就是这样的一个一个过程啊,好了,那这个完事啊,能够把视频录一下啊。
我来说两句