00:00
呃,我们现在,呃,按照这个东西咱们去算啊,咱们这个算,算这种需求的时候,大家要要跟我一起想啊,就是说你看呃,我现在已经明确了,我一天的任务有这些,那怎么去做呢?你就得先先先怎么办,一个一个先去逐一的去攻破,攻破完之后再给它放到一块儿,然后放的时候呢,你看你是横着放,横着拼,横着拼,那我就用照,那你要竖着拼,那你用union对不对,你看看你是怎么给它拼到一起。这个咱们后续再考虑,就是你现在呢,你先一个一个的先先算把你想要的数据给他拿到就可以了,那来,那接下来呢,咱们去给他拿一下这个东西啊,拿的过程当中呢,我们还得还是得先来看一下咱们这个建表是什么样的啊呃,咱得照着这个表去拿我们想要的数据CRC。然后放到这个界面语句当中,CTRLV,大家来看一下这个所谓的这个所谓的留存率,我们统计的时候呢,咱们需要哪些这个字段,首先第一个统计的日期,统计日期,呃,那统计日期这个比较简单,不用多说了,下边呢,有一个create date。
01:00
这个date指的是谁?指的就是我说留存的时候,不是一说就是哪一天的日留存吗?这个所谓的哪一天,也就是那个新增日期,诶指的是他啊,这是哪一天的,那下边呢,有一个retention day retention day呢指的什么?也就是那个几日留存里边的己。对吧,一日留存,那这个re return day就是一两日留存,那就是二对吧,那也就是说咱们应该是是不是每一个留存我都应该用这两个字段去唯一的标识它呀,对不对,就是哪一天的几日留存,用他俩去唯一的标识好,那这个完了之后往下看,下边呢,有一个retention countten count什么意思,就是那个留存的数量,就是留存数量,那后边呢,有一个这个新增的这个设备数新增是哪一天的新增,是不是这一天的新增啊。对不对,那这样一来的话,咱们拿到了留存的数,拿到了新增的数,那留存率是不是就很容易就能出来了,他俩一比就完事了,这就是咱们这个需要的拿到的这个数据啊,OK,现在呢,我们,呃,按照这个要求,我们去写一下这个circle。
02:02
OK,那这些东西呢,咱们分开算啊,咱们分开算,我们先算第一个,呃,3月10号的三日留存,那OK,我们来想一想,我们需要的是什么样的啊,需要的是不是应该是呃,在3月10号新增。并且在这个3月13号活跃的数据。对不对,那这这样的这样的这个用户叫做什么叫做3月10号的,诶活跃这个留存设备。对不对,那来想一想怎么去拿这个东西。咱们去拿这个东西。怎么呢?嗯。可以琢磨琢磨怎么拿,什么样的用户是我这个所谓的3月10号的三日留存用户。前面那是。3月10号新增,并且在3月13号活跃了,这个可以根据谁是不是可以根据咱们那个首次跟末次登录时间去获取。那你想一想,如果说我有一个设备啊,它是首次登录时间是3月10号,然后呢,它的末次登录时间是什么呀?是这个3月13号。
03:04
那OK,那你说我们算出来的是什么,我们算出来的是不是就是一个3月10号的三日留存用户啊,咱们就拿到了,那同理,那你要拿3月11号的两日留存呢,那是不是就是首次登录时间是3月11号,然后末次登录时间是3月10号。呃呃三呃,三月也是3月13号,也是3月13号对不对,那这样一来是不是就拿到了三月时一号的两日留存呀,诶也就是咱可以根据那个时间去获取,根据时间去获取,那这个其实就比较容易了啊,那OK,咱们把这个思路分析完之后,我们开始写这个circleq,因为啊,咱们需要在这个circleq当中呢,去获取好几个值,有什么呀,有这个新增,有有新增的值对不对,有留存的值对不对,那如果说这俩值你要是单独的呃,每一个值都去写一个子查询,这样能不能查出来,能查出来,那查完之后呢,这俩东西啊,你还得给他进行照,所以可能你那个呃,SQ要比较复杂,那性能要差一些,那这边我怎么做呢?我还是给他们并到一个SQ里去获取。
04:04
那既然要并到一个词会去获取了,那是不是就又得用到sum if这个函数组合了呀,对吧?满足条件的G1不满足我G0,那这样一来我就能统计出来各自的这个数量了,好,那现在咱们去写一下这个色啊,来select。From from DW TUV,呃,然后咱们下边就不用过滤了,因为我上面呢,用some这个方式去做啊,首先我们有一个统计日期,那我们就写2020杠零三杠呃幺几啊,咱们杠113吧,咱们杠幺三啊注意啊,咱们这边这个,呃,所谓的统计日期呢,所谓统计日期呢,呃呃,咱们不是说我这个S是在哪一天跑的。那个统计日期就是谁?呃,咱们按照前边那个写的SQ的那个惯例呢,我们其实这个所谓的统计日期指的是什么?指的是你统计的是哪一天的数据,哪一天的数据,对不对,其实也就是说是不是就那个Dollar do date呀,就是Dollar do date,也就是前一天就咱们14号算的时候,这呢,你是不是应该写这个13号啊,就咱跟前面保持一致,跟前面保持一致啊,OK,那逗号,然后咱们继续这个,接下来呢,我需要一个什么,需要有一个这个,呃,新增日期,新增呢,咱们新增这边算的是十号的,是十号的三日,对不对,那新增是不是就得是十号的呀。
05:20
那咱们把这个十号拿回来,OK,那十号完事,那下边该谁了?下边呢,该这个这个截止当前日期的这个留存天数,那咱们天数是不是限制三日留存,那三日留存这呢,你直接写死就行,那就三日,然后逗号,那在下边呢,该什么了?该这个留存数量了,那留存数量刚才咱们分析过了,什么样的是这个留存呢?是不是就是在十号新增,也就是首次登录时间是十号,然后呢,末子登录时间是13号,那这样的我就算算的它是什么呀,3月10号的三日留存。那也就是咱这么做就行,Some来括号if。If,如果说你的首次登录时间,也就是说log in first。
06:01
等于十号2020杠零三杠幺零,然后还得还得按还得并且一下吧,来and and谁and log。等于13号2020杠零三杠幺三,那这时候我就给你记一个一,否则我就记一个零对吧,那现在咱是不是就已经把这个所谓的留存这个用户找到了,而且是12的三日留存就找到了,那接下来往下走,下边该谁了?下边是不是该那该那个新增设备了,那新增是不是就容易了,新增大家都知道怎么去求了,那同理也是三位啊。Some,也就说如果我的log date log date first,诶等于等于等于天等于哪天是不是等于13号,呃,十号就行啊对吧,2020杠零三杠幺零,如果说等于十号,那OK,我就给你记一个一,否则我就记一个零。所有几个零,那这样一来咱们就已经拿到了这个新增设备,新增设备了,那接下来就容易了,接下来是不是就诶用这个东西诶去除一下下面这个东西,就得到这个所谓的十号的30留存率了呀,来,那现在呢,咱们把这个上面东西粘一下吧。
07:16
粘一下啊,CTRLC。然后呢,放在这个位置,CTRLV,然后呢再往下走。把这个东西也给它粘一下,嗯。CTRLCCTRLV,好,那现在我们是不是就已经拿到了咱们14号计算任务当中的这个1/3了呀,咱们已经算完一个了,算下还有俩,剩下俩呢,是不是就比较容易了呀,咱们就以它为模板,以它为模板,然后去改一下这个日期是不是就完事了呀,对吧?那现在咱们去改一下CTRLC,那我要算的是11号的两日留存,那CTRLV拿回来,那11号的两日留存,你的新增日期应该是哪一天呢?这个是统计日期不用改是吧,那新增日期是不是就得是11号了呀,对吧?那同理,这边这个所有的十是不是都得给它改成这个11啊,那我统一的改一下啊,这有一个快捷键,按住这个ctrl alt啊,然后按那个向下的那个方向键,你可以这个呃,同时选中这个多多行啊,然后呢,直接改成11,这改成11,那后边呢,呃,这个位置呢,咱们也需要给它改成11啊,给它干掉改成一。
08:20
诶行,那这边咱们就完事了啊,这个13咱们是不用动啊呃,因为他们这三个需求,这三个留存我们等的是不是都是13号的那个活跃啊,所以说这块你是不用动它的,那OK,这个咱们就完事了,下一个呢,还有一个最后一个12号的这个一日留存CTRLV,那现在咱们去改一下,这边要改的有谁改了,首先这个位置咱们三个的都得给它改成这个12才对,那这边这个新增日期是不是也得改一下,再告诉大家一快捷键啊,就是你可以按住CTRL,按住CTRL,然后呢去点击你你想要修改的地方,这样一来呢,你可以去这个,你可以去这个多点修改啊,可以多点修改,那这边我统一的把这个11都改成12,都12啊对啊,这个有同学这个提醒了一下,这个留存天数咱们得改一下吧,留天数得改一下啊来,这是三日留存,那这个是几日啊,这个是两日留存,改成二,那同理,下面这个是几日啊,这是一日,诶,那改成一就行了。
09:14
好,那现在我们三个留存全部搞定,那一天的计算任务咱是不是就完事了呀,那OK,呃,咱们现在呢,再去跟大家说一下啊,说一下什么东西呢。呃,我们去算这个留存的时候呢,前面不是说了吗?我可能会算好几天的,还有什么四天五天,六天七天,甭管多少天,你算的时候是不是都是这么去算啊,都是一样的思路,都是一样的思路啊好,那这边改完之后,那接下来咱们应该干什么呀,是不是就想办法把我们这几个结果得给拼到一起呀,对吧?那这回咱们拼,你想一想应该是怎么拼。就是说我这举一个就是说不太不太这个,呃,不太严格的这样一个一个一个比喻啊,就是我们拼拼结果的时候呢,我们可能会横着去拼。对吧,那也有可能怎么办?也有可能是竖竖着去拼,对吧,那什么情况下是横着拼呢?诶比如说我这个表里边呢,我的字段可能有十个字段,那这个子查询,我可能我获取到了五个字段,这个呢,我可能获到了另外五个字段,那这时候拼的时候你就得横着拼,那横着拼怎么拼,横着拼就就用join呗,对不对,横着拼用join,那我还有可能有什么需求呢?我这需要十个字段,然后我第一个字查询已经有十个字段了,第二查询也有十个字段。
10:21
对不对?那这两个子查询它们之间的关系应该是怎么样的?应该是不同的行,应该是不同的行,那不同的行之间拼怎么办?是不是用竖着拼,竖着拼用什么?是不是用union啊,UN IO union是这样的,那这边很显然,但是每个子查询都拿到了我所有的字段啊,那他们肯定是不同的行的关系,那所以他们仨应该是,诶,竖着拼起来,那就用union呗,那现在咱们给它union起来,那这个呃,我就干掉了啊,那union的时候大家就得想,我有union,有union all对不对?那应该用什么呢?Union会去虫,Union不去除不去虫,那这边咱们没有去虫的需求,那直接就union all就完事了呗。CTRLC,那这边下边也是CTRLCCTRLV,好那三个东西呢,已经UN union到一起了,然后union到一起之后啊,大家要要注意啊,我们这个union它这个语法呢,是什么样的,大家得记住啊,首先大家明确什么东西可以进行union。
11:15
两张表之间能进行妖念吗?两张表之间是不能进行妖念的,只能是什么进行妖念只能是两个子查询,几个select,一个select,一个select,这个东西是能进行妖念的,两个表是不能进行union的啊,不能说直接一个表明,然后union另一个表,这样不行,这样不行啊,大家把这个记住好,那这个只能是多个select进行union,那union完之后,那我又应该怎么去处理他这个union之后的结果呢?怎么去处理呢?OK,大家记住一个原则就够了,记住一个原则啊,多个select进行union。然后你去处理的时候呢,你就把它当做一个select去处理就可以了,一个select的语法是什么样的,那多个select union到一起呢,它的语法就还是什么样的,把这个原则记住就行了,好了,那现在他们union完之后,我们要做的是什么,我们要做的是不是要把结果插到这个表里边来呀,那CTRLC咱们把这个表名粘起来,那就直接怎么办啊,直接是不是就是insert就行,Insert into。
12:19
这个table,把这个表格拿过来,下边是不是就接上这个多个select进行union,因为前面咱们说了多个select union就跟一个select一样,那我们的语法是不是就是insert加表明后边加select呀。对不对,那也就是说这个词会这么写就完事了,那这个呢,就是咱们,呃,一天的计算任务,一天的计算任务啊,那一天计算任务完之后呢,接下来你要干什么事,接下来要做的事啊,就是。呃,去干啥呀,接下来你要做的事就是呃去写脚本了,呃因为这个呃这个留存这个需求呢,里边涉及到的这个呃日期太多了啊,所以说这个呢,我带着大家去改一下这个日期。
13:00
大家去改一下A日期啊,那写脚本应该是把前边那个脚本头拿过来对不对,呃,我就不拿了,也就是这边你会有一个什么呀,是不是会在脚本里边声明一个Dollar do date呀,对不对?来我统一的给大家把这个日期怎么去改一下啊,来,我把它粘下来啊,CTR谁我们来看看这个日期怎么去改,当然你改脚本的时候呢,你还得注意一个点什么点,就是说你那个表名前面得加库名,那里边我就不考虑那事,咱们只考虑日期啊,那边怎么去改日期呢?首先这里边我是不是所有的3月13号都得改成Dollar do date呀,对吧,那这些东西呢,都改成Dollar do date,那这个得改。那这个呢也得改,这个呢也得改。呃,同理呢,呃,下边呢。呃,这块也得改。这会儿呢,也得改,呃,还有这个位置,还有这个位置。好,那所有的Dollar do date,咱们所有的那个3月13号的全部改成Dollar do date,那然后呢,我们走。来来一个Dollar,然后do_datat Dollar do delete全部改成它CTRLDCTRLD什么快捷键啊,一会我试一下啊呃,那这个Dollar do改完之后呢,那接下来还有还有什么呀,接下来我们要做的是不是就是把这个日期给它进行一个这个这个这个加减就可以了呀,对吧?那对于十号来说,我应该它的标杆是谁,标杆是不是应该是Dollar do date也是不是应该是加一个这个date I呀,对不对,那我我就再示了一个啊。
14:27
直接选相同的日期啊,还可以这样啊,那一会我试一下啊来那现在这边呢,你就应该是data_I,然后把谁拿进来是不是就把那个Dollar do date拿进来就行了呀,来Dollar do_data然后后边呢,这边应该是它,它是本身应该是十号吧,对不对,应该给它给它改成几啊减三是不是就行。对不对,那也就是这个减几其实跟谁是一致的,是不是跟你这个留存天数是一致的呀,那你这如果是三,那就减三,那你这呢是减是几啊是二,那就应该用到招do减二,把这个所有的这个十号,诶11号都都用什么都用data I的do do减几去给它进行替换就行了,是几你就替换成几。
15:09
是这样的啊,这个我就不一个一个去改了,因为太浪费时间了,咱们这个课下自己去把这个东西去落实一下就行了,那这个呢,就是咱们这个留存的这个求法,留存的求法啊。是这样,那我会把这个视频我先录一下啊。
我来说两句