00:00
啊,那现在我们把这几个需求,嗯,统一的一起去做一下啊,一起做一下,呃,我们先看一下这个第四一个往上走啊,就第四一个这个所谓的本周回流用户,呃,首先还是先来回忆一下这个需求它的定义是什么啊呃,来我们一起来读一下它的定义,就是说上周未活跃,不是不是上周末啊,是上周未活跃,然后但是在本周他却活跃了的设备。对不对,也就是上周一整周都没有活跃,我就认为他不再用这个APP了,然后本周呢,他突然又又用又开始用了,那是不是就所谓的有了一个所谓的一个回流的概念啊,对吧,然后呢,咱们前面也分析过了,那本周的新增设备呢,其实也会被误算为本周的回流。对不对,因为什么?因为啊,你想一想,他本周新增是不是肯定在上周没活跃,但是在本周它活跃了对吧,是不是看看似也像是一个回流用活,但是它呢,实际上它不是本周回流,它不是本周回流啊,这是为什么?因为本周回流,回流一定得是老用户才行,新用户是不能作为回流的啊OK,那也就是咱们分析完这个需求之后,我们的思路应该是什么呢?就是这样啊,我们应该啊,先把本周活跃的所有的设备找出来,对不对,然后把这个本周活跃设备当中。
01:13
把什么样的去掉呢?把本周的新增给去掉,还得把什么,还得把上周也活跃的那部分也给它去掉。对不对,那这样一来我们就能够找到这个所谓的本周回流了。OK,那接下来咱们去呃思考一下,看看这块咱们应该怎么去做啊,应该怎么去做,那首先啊,咱们是不是得先把本周活跃给他找到啊对不对,找到之后再往外去刨除我那些不需要的用户对不对,其实就是这样的一个主体的思路,那OK,那现在我们去呃找一下这个所谓的本周活跃的设备,那本周活跃设备这个怎么去找,这个大家应该知道怎么去找了吧。咱们在算这个活跃设备数的时候,算过日活,周活、月活,那周活咱怎么找的,周活是不是应该是这么去找啊,这样一来是不是能够拿到这个所谓的一个,呃,本周合约就是这么去过滤一下,当然他这我们那个算周活的时候呢,我们要的只是一个数,那这回我们可不能只要一个数,我们得要什么呀,我们得要具体的那些设备才行啊。
02:13
得要具体那些设备,OK,那现在呢,咱们去,呃,把这个搜狗写一下啊。来,我们先拿到本周的活跃设备select。From d wt UV topic,那上边咱们选什么呢?选选midd就够了,选设备ID,那下边的过滤条件就是这个根据谁啊,是不是根据我这个最后一次的登录时间去过滤啊,他如果说最后一次登录时间位于本周,也就是大于本周一,小于本周日,那就是在本周活跃了,OK,那现在咱们把这把这个东西找出来啊,咱们去找本周活跃,那过滤条件是不是应该是他呀,来NC,嗯,粘出来啊。C,好,那这个呢,就是咱们这个所谓的呃,本周活跃设备咱已经全部都拿到了,看啊认真看,这是呃,大于本周一,小于本周日,OK,那本周的活跃找到了,我得需要干什么,我得在这里边啊,把本周的新增给他刨除掉,还得把上周的活跃刨除掉,咱一个一个刨啊,咱们先看新增。
03:15
那你说我怎么在这个里边把本周新增的用户给他刨除掉呢。怎么把这个本周新增的刨除掉呢?奔跑。其实在这啊,我们可以用一个非常巧妙的一个方式啊,那我们直接在后边啊,其实再加一个过滤条件就可以了。再加一个过滤条件就可以了啊,加一个什么样的过滤条件了,大家来看一下啊,我其实直接在这啊,再加一个login date first,我让他怎么样,我它小于小于小于本周一就行。CC。大家看这个东西看能不能看懂啊,你看啊这个过滤条件这俩这是把什么样的找出来,是把本周活跃找出来了,那这个呢,Login date是不是就首次登录时间,如果说它小于小于小于这个本周一,那就说明它不是本周新增的了吧。
04:10
对不对,那这样一来咱是不是就把本周新增的也给它过滤掉了呀,对不对,那其实咱们这一条circle就已经拿到了,诶本周活跃当中,诶把谁去除掉的,把本周新增去除掉的那一部分用户了,对不对,已经拿到这一部分了,那咱剩下的是什么呢?咱剩下的呀,就就是把谁把那个上周活跃的给他去掉就行了。把上周活跃的给它去掉就可以了,对不对,那OK,那现在啊,有些同学可能会这么去过滤,这么去想,诶他可能诶觉得我这个新增可以这么去过滤,那我上周活跃,我是不是也可以这么去给他进行过滤啊对不对,那他怎么去做,他可能这么去学了,And,诶login data last,就是说你你是不是得是上周那个没有活跃的呀,上周没上周没活跃的,那那什么呀,也就是说他可能会这么写login data last,他可能让他去,诶大于谁大于这个这个呃上周末大于上周末,大于上周末是不是就是说明他他他就他可能啊,他会以为,诶这样一来,那就说明是这个,呃呃上周那个,诶不对啊,不是大于上周末,应该是怎么样啊,上周未回跃,上周未活跃,他可能会这么去想,让他去小于什么呀,小于这个上周一。
05:26
小于上周一对不对,他可能会这么想,然后呢,那个log in data,呃这个呃last小于小于上周一呢,就说明上周他没有活跃,上周没有活跃,但是这样一来的话,你想一想啊,你是这么做呢,他是他是不对的,这么做他是不对的啊呃,因为什么呢?因为很有可能我有一部分用户啊,我在上周他没有活跃,对不对,但是在本周活跃了,在本周活跃了,那你满足这个条件吗?你不满足,你找不全,你找不全,也就是说你只能找到一部分上周未活跃的,但是有一部分上周未活跃的,你这么去写,你是汇滤不到的,你是拿不出来的啊,也就是说说白了啊,你在这个DWT层啊,你是找不到这个上钩诶这个未活跃的用户的。
06:12
你在DWT层啊,你是找不到这个上周活跃的这些用户的,你找不到。因为什么这个你得分一下咱们DWT层的表它是什么样的啊,我们来看看DWT层的这个表。咱们这个表里边字段呢,我们看一下,呃,DWT层啊,这是咱们DWT层的设备主题,设备主题管表,那这张表里边我们的统计字段只有什么,只有首次活跃,末次活跃时间,然后还有什么呀,什么当日活跃次数,累计活跃次数,你看啊,这都是一个什么,这其实相当于都是一个累积值,有累积值对不对,那也就是说我想获取那个历史的数据,你在这个宽表当中啊,你是拿不到的。它只是一个从头到尾的一个累积值,你只能看到这个最终的一个累积状态,那历史状态呢,你是拿不到的,你在宽表里拿不到,那你要想拿历史状态怎么怎么去拿呀,你只能去咱们的DWS层去拿,为什么DWS层能拿呢?因为我们DWS层啊,我是一个这样的一个天表,就是按天分区的这样一个表,我每天的这个记录我是不是都都保留着呢,每天记录都保留着,所以说你要想拿历史状态呢,还是得去咱们哪啊,去咱们的这个,呃,DWS层去哪。
07:26
DWS层去哪推行啊,OK,那现在呢,呃,我们继续往下想啊,这个子妍同学,你这个问题呢,一会我再看啊,咱们先把这讲完,然后再统一的回答问题啊,OK,那现在呢,我们,呃,现在怎么去拿呢?我们去DWS层拿一下我这个,呃,上周的这个活跃用户,咱把它拿一下啊,那OK,怎么去拿呢?Select先不说选什么形式from啊,DWS,然后呢,应该是呃,UV detail d count,从这张表里去拿。那我现在要拿的是呃上周的活跃的用户对不对,那咱们拿上周活跃之后,那这俩东西我应该怎么样的去进行这个呃处理呢?他俩怎么去处理呢?诶咱们先先把这个整整体的思路先给大家说一下啊,那比如说我现在啊,上边已经拿到了什么,已经拿到了这个呃本周的活跃,然后呢,这里边已经把本周新增给他去掉了,对不对?然后呢,我又拿到了这个上周的活跃,又拿到了上周的活跃,那OK,大家来思考一下啊,你说我这个本周活跃,诶除去新增,然后呢,和上周活跃的这些设备,他们俩之间的这个对应关系应该是什么样的,是不是应该就是这样的呀?
08:38
应该是这样的,这什么样的一个对应关系呢?就是说他俩这一部分,这部分用设备应该是什么样的,这一部分设备应该是在本周也活跃,在上周也活跃,也就是说本周活跃中有你,上周活跃中也有你,对那这一部分呢,这部分是只在上周活跃,在本周没有活跃。能能看懂是吧,那这一部分用户呢。这部分用什么样的这一部分用户呀,他是不是就应该是只在本周活跃了,在上周没有活跃的。
09:06
对不对,那我们现在想要的是什么样的,我们想要的是不是就是上周没有活跃,然后本周活跃的用户啊,对不对,那也就是说咱们一会儿怎么样可以把这一部分用户给他拿出来。怎么样可以给它拿出来,诶这个一会大家去思考一下啊,思考一下,反正也就说我们现在我们只要拿到本周活跃,拿到这个上周活跃,那OK,我就能够找到我想要的那部分数据了,这是咱们的整体的一个思路啊,那所以咱们接下来干什么事,接下来拿这个上周活跃,拿上周活跃刚才分析过了,我们只能去咱们DWS层去拿,你从DWT层,从这个累计表当中,你拿不了这个历史的状态拿不了,那OK,咱们去去这里边,就这里边我们,呃拿上招数据的时候呢,咱们过滤条件是不是也应该跟这个是类似的呀,跟这个类似啊,CTRLC咱们把它粘一下啊。刚才这个是本周,那现在这个是我要当然这个日期这个字段得改一下啊,咱们得是DT字段D字段啊DT好,那现在咱们这个上周上周是不是还得再减个七,那就再乘个二就行了,那这个呢,同理再减一个七就行。
10:10
好,那这样一来我就把,诶上周一整周的这个,呃,用户的活跃记录全部拿到了,对不对,那拿完之后,大家这块要注意,我们要做一件事,做什么事呢?我们要去一个重,对谁去重,对设备ID去重,因为你现在你过滤出来的是一周的数据,对不对,那一周里边我可能有一个设备啊,在每天或者在多天他登录了,那OK,我算的时候呢,我其实只要一个midd就够了,我需要给他去重的。那怎么去容呢?下边直接Y一下就行,Grow y mid,那上面呢,把这个mid选出来。选出来啊OK,那现在咱们就呃,把本周的这个活跃除去新增也拿到了,把上周活跃也拿到了,那现在咱们来分析一下啊,怎么样拿到我想要的那部分数据。他俩的一个对应关系是这样的,这是我本周对不对,这是上周对应关系这样的,那这一部分用户呢,是我这周也登录,上周也登录对吧,那这部分用户才是我想要的,那问题来了,怎么拿到这部分设备?
11:11
怎么拿到它怎么拿。这怎么弄?想想怎么了?这么难怎么拿呀,是不是应该让他俩进行一个这个join啊,对不对,因为join之后呢,我我我可以根据什么,我可以根据他是否为no啊等等这些条件去想,我去选择我需要的数据。对不对,那咱们joinone的时候应该用什么joinone呢?有的同学可能会想了,我这呢,我直接用普通的这个inner join,那inner join你想一想inner joinone之后,这俩子查询返回的结果应该是哪部分数据是不应该是这部分,反回这这部分是我本周上周都活跃的,这不是我想要的,我想要的是这部分,对不对,是这部分,那这部分应该怎么拿呢?是不是应该用life对不对,Left,那left返回的数据是哪?是不是跟这边是一致的呀?对不对,那这部分数据我是不是有一部分是我需要的,有一部分是不需要的呀,对吧,哪部分需要,这一部分需要,那这部分它有什么特点呢?这部分跟这分比起来有什么特点,这部分跟这部分比起来特点是不是就是这边,诶这儿不是闹,上边是闹吧,因为left join如果说能照上,我就是join上的值,如果join不上呢,我是不是用now不全呢,对不对,那也就是说咱们用left join,然后根据什么呀,根据右边这半部分是否为now去选择我需要的那部分,那部分数据。
12:31
咱们思路就是这样的啊,那OK,那现在咱们按照这个思路呢,去落实一下,那也就是现在这俩东西应该给它作为一个子查询吧,回车回车CTRLV,咱起个别名啊,那它管它叫做这个本周那就current current。Week,那下边这个紫查询呢,咱们是上周的,那我就叫做last week。CT,来,我往前推一下,不见,这个就是last week last。
13:00
Last week啊?一个本周的,一个是呃,上周的好,那它俩应该进行left left join,那它俩join的连接条件应该是什么呢?大给写上啊,应该是on current week.id等于last week点,呃,Mid mid,那这边呢,也是m mid啊,M mid。好,那照上了,那照上之后大家要注意啊,咱们外过滤条件应该是什么,咱的where过滤条件应该是什么。外国的条件应该是什么?这个得注意啊,那这边呢,咱们来想一想,这个V过滤条件应该是啥?这个是不是就应该是根据这个图去看呢?对不对,应该是根据这个图去看了啊,怎怎么怎么怎么去过滤,是不是应该是呃,You over,这个也就是说什么,也就是last week last week.mid是不是得是no才行。那是不是就把这部分结果给他拿出来了呀,这就是咱们这个所谓的本周回流咱们就拿到了,OK,那现在咱们来一下啊,来也就是说呃,W chere,呃,应该是。
14:15
Is no OK,那这边咱们就搞定了,搞定之后那上边咱们从这个子查询里边去,这个从两个子查询的,照样结果能去选我们想要的数据啊,Select from from这个东西,那下面咱们,呃,上面呢,我们需要选哪些值呢?选哪些值得看一下咱们ADS层的表需要哪些字段了啊,咱们来看一下ADS本周回流设备。呃,把这个建表语句拿出来,Z。来放在这个笔记当中啊,大家看一下ctrl v OK,我们来看一下啊,呃,因为咱这算的是一个本周回流用户,对不对,本周回流用用户,所以说这呢,除了有一个统计的日期之外呢,还有一个周的ID,周的ID啊,那大家要注意了,我们怎么去标识一个周,你看统计日期所在周,其实这样呢,就是说我我要标识一下咱们这个周,那周的ID,咱们一般是怎么去做呢?大家想想啊,可能有的同学会想到有什么wake of year,就是说呃,这个呃,这一周是这一年当中的第几周,可能用这个去进行这个标识,但你这么去标识,其实上是实际上是不太好的,为什么呢?可以琢磨一下啊,咱们这个数仓当中,如果说我有好几年的数据,那你说是每一年都会有一个,比如说都会有一个什么第15周,每一年都有一个第15周,对不对,那也就是说你那个week v year这个ID呢,其实不是唯一的。
15:38
不是唯一的啊,那所以说咱这个通常怎么去标识这个唯一的周呢?咱可以这么去做,诶,我用这一周的周一的日期,然后呢,拼上一个周末的日期去作为他这个周的ID,去作为这个周的ID是这么去定义的,当然你这边有同学可能会想了,我其实不拼也行,我就以周一日期作为你这个周的ID行不行,是不是也是行的呀,就是只能你只要你能唯一标识出来你这一周就可以,然后呢,你自己呃是怎么标识的,你后续的所有的逻辑,你都按你自己标识的这个定义去做就可以了。
16:12
是这样啊,那咱们这儿呢,呃,统一咱们用周一日期拼上周末日期,咱这么去标识它啊,OK,那现在咱开始选了啊,Select咱们选一个日期,呃,2020杠零三杠幺零,OK,那接下来呢,咱们拼一下这个呃,日期,那拼日期呢,我们用com cn cat这个函数大家应该还是比较熟悉的吧,那在这里边我们先把周一的日期给它拿回来,这个日期呢,就是周一日期CTRLC放在这。CTRLV,然后逗号我再给它拼上一个下划线,然后逗号再给它拼上一个周末的日期,这就是咱们那个周末日期啊,CTRLC,然后呢,放到这个位置好,那现在咱这个周的ID也给它标识完了,标识完之后呢,我们来看一下啊,最终我们需要一个什么东西,需要一个所有的回流设备数,那下边咱们from这个子查询完之后,它俩照着之后是不是返回,就是所有的回流设备啊,那我们要得到的其实只有一个数就够了,那也就是咱们统计一下有多少行所count星,那就是有多少多少个。
17:15
来看到星就行,诶放在这个括号里啊。OK,那现在我们就已经把本周的回流设备数给他统计出来了,那接下来要做的事呢,就给它insert一下啊,Insert into table,呃,把上面那个表名咱看一下,叫做ADS count。OK,那这个表,呃,Circle课呢,咱们就写完了,写完之后咱们去测试一下吧,我这个iner into就先不跑了,因为表还没见啊,这个课下午再见,我就跑一下这个语法,看能不能过啊。好,现在目前语法是没错的啊好,那现在呢,我们这个需求呢,到这儿也就算是完事了,然后我们来看一下这个同学的问题啊呃,先看一下这个,呃,朱玉祥的吧,他这个简单啊,这个等号去掉吧,呃,哪个等号我看一下啊,等号去掉是呃,也就这几个日期当中呗,呃,你说一下多少行。
18:11
那个朱宇翔,你说一下多少行,第几行的那个等号。嗯。我们来看一下啊嗯。这是我们本周对不对,那这边是这个,呃,上周。等号是多少行等号我这个没感觉啊,你说子阳的是吧?啊行,那现在看一下子阳的啊,还是子阳的那OK。呃,子阳,这个他说这个可以login小于等于。这个首次登录要在这个上周前。上周前。呃,我看一下啊。呃,这个first可以,那个login date小于等于。
19:01
First login date first小于等于date next day。也也就是首次登陆要在上周前。这个首次登录为什么一定要在上周前呢?首次登录为什么要在上周前呢?我们只要保证它是什么呀,只要保证它不是本周新增的就行。只要保证不是本周的新增力行,不是说非得是什么,非得是上周钱啊,不一定非得是上周钱啊。呃,首次登录上周一之前,呃,每次登录本周一之后,然后排除上周呃登录过的用户。呃,我想一想啊,呃,首次登录上周前,然后呃这个每次登录呃,本周一之后。那个排除,呃,上周呃。
20:00
那也不能排除啊。你也不能排除啊。对不对,那比如说我现在啊,呃,就有一个用户,我在这个上周我登录了,上周登录了,但是什么时候,他什么时候注册的,他是之前注册的,那首先是满足你第一个条件,什么条件,就是你的首次登录是在上周一之前,这是不是能满足,那OK,他上周是不是也登录了,那我本周呢,本周怎么办?本周我也登录了,本周也登录了,那OK,我是不是也满足你第二一个条件,就是说我的末次登录时间大于本周一,是不是也满足,那中间他是不是登录过呀,登录过你能看出来吗?你看不出来。你是看不出来的啊,就是这个用户,他明明我在上周登录了,对不对,他是不是登录了,所以说你这边还是不行啊,你这个不行,就是说这这种历史状态呢,你在这个最终的这个DWT这张表里边,你是体现不出来的。对,你是体现不出来的啊,就是说只能是怎么的,只能是去从DWS里去找,只能去这里边去找才行啊,OK,大家把这个记住就行,我们DWT只是一个最终的累积的一个结果,你看不了历史状态,历史状态只能去从咱们的DWS层的历史记录当中去查找。
21:12
这个大家得记住啊,包括我们后边要做的这个连续的,比如说连续三周的,比如说七天内连续三天的这些东西,你在DWT层你是肯定求不出来的,对不对,因为你需要涉及到我所有的历史登录记录,你才能求出来,所以那两个需求呢,我们也要去从DWS里边去读数。OK。那这个。呃,减七可以改成减14,减七还包含了,呃这个子阳同学吧,这样吧,这个在这说呢,这个太乱了,太乱了,一会那个呃,咱们语音连线也好,或者你那个给我打电话也好,你直接跟我说,然后呃,我看你说有没有价值,有价值的话呢,我再跟大家一起再说这个事,要不在这说太乱了啊,OK,那我先把这个视频录一下啊。
我来说两句