00:00
好,那接下来我们把用户变动统计这个需求实现一下啊,好,我们先来分析一下这两个指标的思路啊,咱先看第一个啊,就是流失用户数啊,那流失用户应该怎么找。啊,其实很简单,这儿已经说的很明白了啊,那所谓的流失用户呢,实际上指的就是什么啊,就是七日前那一天活跃,但是最近七日未活跃的用户,没错吧,那我们换句话说也就是什么呀?啊,那是不是就是末次活跃日期等于七天前那一天的日期啊,那这就是第一个指标的思路,来我们再看第二个指标回流用户数啊,那回流用户又应该怎么找呢?啊,其实后边说的也比较清楚了啊,我们再来看一下啊,那首先之前的活跃用户一段时间未活跃,今日又活跃了,就称为回流用户,那接下来呢,我们把这句话好好理解一下啊,首先今日活跃表明什么?是不是表明他的末次活跃日期是今天呀,对吧,然后呢,在今日之前还得有一段时间未活跃对吧,那这个一段时间前面咱提到了对吧,咱这指的是七日及以上,那也就是啥啊,那是不是就是他的上次活跃日期距今日得超过七天才可以。
01:15
好啊,那这就是第二一个指标的思路啊,那接下来我们把这两个思路呢去实现一下,好,我们先求第一个值流失用户数啊,那按照我们刚才的分析呢,我们是不是只需要去找末次回日期等于七天前的那一天的日期的用户就可以了,没错吧?那所以我们这儿呢,直接select,然后from,那张表应该是DWT层的user topic对吧?啊,那我们需要获取它最新一天的分区啊,DT等于2020杠零六杠幺四没错吧?然后呢,咱是不是要再写一个过滤条件啊,对吧?应该是啥?应该是login date last对吧?Log in,诶,Date last之后呢,应该等于啥?是不是也等于七天前的日期,那也就是data at对吧?啊,我们先把当前日期给它写进去,2020杠零六杠幺四没错吧,后边呢,是不是还得减一个七啊。
02:02
对吧?那这样一来咱是不是就把所有的新增的流失用户找到了呀?啊,那接下来呢,我们是不是直接给他count一下就可以对吧?Cot来一个count星啊啊,那现在我们就已经把所谓的流失用户数求完了,那我们接下来再来看第二一个值啊第二一个呢是回流用户数对吧?那按照我们刚才的分析呢,回流用户需要满足两个条件,第一个条件就是它的末次活跃日期得是今日对吧?那另一个条件呢?啊,是不是应该是上次活跃日期距今日得超过七天才行,OK,那接下来咱就开始寻找满足这两个条件的用户啊好,我们先看满足第一个条件的用户怎么找啊,这个其实很简单,那我们是不是只需要从DWT里边找到末次登录日期等于今日的用户就可以了?没错吧,所以说我们先来一个DWT,然后呢,User topic where dt应该等于2020杠零六杠幺四。没错吧,然后呢,是不是来一过滤条件啊,对吧?应该是login date last等于2020杠零六杠幺四,OK,那这样一来呢,我们得到的user ID就是啥?是不是就是满足第一个条件的用户啊,OK,那接下来我们再来寻找满足第二一个条件的用户啊,第二条件是啥?是他上次活跃日期距今日得超过七天才可以,没错吧,那关键是他的上次活跃日期咱们应该怎么获取?
03:26
大家来想一想啊,那对于今日的活跃的这部分用户来说啊,那他的上次活跃日期就是啥?是不是就是DWT层前一日分区当中的末次活跃时间呀?你想一想是不是这么回事啊,应该是没问题的对吧?如果这儿大家能想明白,那剩下的其实就容易了啊好,那现在我们把事给我写一下啊,来s select,然后from应该哪张表是不是也是DWT层的user topic呀,对吧?哪个分区,注意应该是前一日的分区对吧?也就是DT等于data at,然后呢,当前日期放进去,2020杠零六杠幺四应该减个几?是不是应该减个一呀,对吧?那好,那完之后呢,我们需要再写一个过滤条件应该是啥?应该是login date last,没错吧,距今天是不是得超过七天才可以啊对吧?那也就是说得小于等于今天的日期减去八,没错吧,那在这呢,我们应该是小于等于,代艾完了之后呢,把当天的日期放进去,2020杠零六杠幺四,后边呢,来一个减八。
04:25
OK,那这样一来,我们拿到的用户呢,就是满足第二个条件的用户了啊大家来都知道啊,我们需要的是同时满足这两个条件的用户,没错吧,那所以说这两部分用户得做一个什么,是不是得做一个join,然后呢,我们获取它的交集啊好,那接下来我们把它俩照应一下啊,那先给他俩分别起一个别名啊来CTRLX,那这个就起名为T1吧,啊来往前推一下。这是T1,好,下一个呢,就是T2来CTRLX。来,我先推一下。T2,好,那它俩呢,需要做一个inner join对吧?啊后边呢,连接条件就是t1.userid等于T2点,哎,User ID,那这样一来我们返回的诶这个user ID是不是就是它俩的交集啊,OK,那接下来呢,我们就可以select from这两个子查询了啊那在这儿呢,我们拿到的user ID实际上就是咱们所谓的回流用户,那我们需要的不是这些user ID啊,我们需要的是这些user ID有多少个对不对?所在这儿呢,我们需要来一个是不是count呀,对吧?来抗芯啊OK,那这样一来我们就得到了咱们所需的回流用户数了。
05:34
好,那现在呢,我们这两个值就都计算出来了啊好,那现在我们要做的事呢,哎,就是把这两部分数据是不是给它合并到一起,然后呢,给它ins色到我们最终的这张表里就可以了,没错吧,那接下来咱们把这步操作一下,那首先我们先给这两个count芯呢,给它起个别名啊来,首先第一个诶,它叫做流失用户数对吧,那第二个呢,叫做回流用户数,来拿过来。啊好,那接下来呢,我们需要把这两部分数据是不是给它join起来呀,对吧?啊,那关键是join的时候,我们的join连接条件应该是谁呀。
06:08
啊,那现在没有连接条件对吧?那没有连接条件呢,其实我们可以给它创造一个对吧,大家注意观察啊,最终咱结果当中是不是有一个DT,那这个DT是不是就是一个常量值,2020杠零六杠幺四啊对吧?那所以在这儿呢,我们可以给每个子查询都先加上一个这样的DT啊,2020杠零六杠幺四,那后边来一个DT,那下边的也是一样的,那这样一来我们是不是就可以以DT作为关联条件了,没错吧,来好了,那这就完事了啊,那接下来呢,我们把这两个子查询给它照到一起。那我们就先分别给大家起个别名啊来CTRLX,第一个呢,我们就起名为呃创RN啊来Chun,好,那第二一个呢,我们起名为back就可以了啊来,往下走。跟着X来括号。来把它放进来CTRLV,那最后这个分号咱就不要了啊,那这个往前推一下,那它起名为BA back啊好,那他俩呢,需要做一个draw对吧?来join,哎,John,那连接条件应该是啥呀?是不是应该是呃,Ch Chen chn点啥是不是DT等于back.dt啊,没错吧,那现在我们就可以从中选择咱们所需的最终结果了,来我们select,嗯,完之后呢,先来一个来from,那这里边首先我们先选一个DT对吧,DT其实咱们选谁的是不是都可以?没错吧,它俩都是一样的了,好,下一个啊,那下一该谁了?是不是该这个user turn count了,对吧?然后再下一个user bike count,好,那现在我们就得到咱们所需的所有字段了啊那最后呢,我们再来一个inser是不是就可以了?来inser的orright table ADS层的哪张表是不应该是user change啊,对吧,User change啊好,那下边呢,再来一个select星,诶,From user change as啊,User change。
07:53
后边呢,再来一个union,那这个需求我们就全部完成了。
我来说两句