00:00
那现在呢,我们来看一下啊呃,在这块呢,我们要求的是连续三周活跃的设备数对吧?设备数或叫做用户数啊,或者叫访客数,其实是啊访客数,那这边就是说呃,我们需要按照设备ID去统计嘛啊啊那这个怎么算这个这其实之前就给给过大家提示了,对不对,在这边我们其实就可以直接上来怎么做连续三招,虽然它也叫连续啊,但是已经其实那我少写俩字,它叫什么呀?叫做最近对吧?最近啊,但有这个最近跟没这个最近还是有区别的,对不对,它有最近,那就说明它告诉你了,你这三周是哪三周,对不对啊熬周,上周上上周已经告诉你了,那所以我们这儿要做的就是怎么样啊,去把这个呃三周的啊活跃设备先做怎么着先做怎么先都全部找出来,对不对,先找出来,找出来之后呢啊,去把他们公有的,那那那些设备ID是不是找出来就OK了啊是这样,那怎么找他们公有的呀,找公有最简单那种。
01:00
方式就是写,嗯,写字查询,然后让他们仨进行inner joinone对吧,那返回的结果就是全有的啊,那这个怎么实现,这个思路很简单,大家,但是大家呢,再去写这个so的时候呢,哎,他就有点不会有点不会写了。我亚会写呢,还是语法啊,不太熟悉啊,就是我这多表join这块呢,有些东西可能还是不太熟,用的少,那所以这个S呢,虽然简单,但我们也演示一下吧,啊,那也就是这呢,我们需要先求这个三周的活跃设备,咱们先求本周的啊,那求本周的活跃设备谁来的from,咱们可以从哪求啊。行。啊,本周的求,本周的从DWS求,从DWT求是不是都可以啊,对不对,那如果求上周或者上上周的,我从DWT能求吗?不能啊,为什么呀,因为我们如果要从要从DWT求的话,那大家肯定用的是那个login date last字段去求,对吧,就是莫斯登录时间啊,也就是说我要想根据这个字段去求上周活跃的啊,那你就应该怎么写那个过滤条件,应该就是末次登陆时间大于等于上周一,小于等于上周日,对吧。
02:04
但是你这个过滤条件,你能把所有的上周活跃设备过滤出来吗?不一定啊,为什么呢?因为假如说有一个人,他的末次活跃时间是咱们本周了,对不对,末次活跃时间已经位于本周了,那你假如他上车也活跃了,那你是不是就把这个人丢了呀,是不是?呃,也就是说你只能找到一部分啊,那另一部分就丢掉了,所以说我们要求要使用这个logging date last字段啊,去求什么日活、周活和月活的时候,只能用它求什么呀,求当天的,当周的,当月的。对不对啊,那上周上月或者是昨天的前前几天的都不行啊,这个得搞清楚,那也就是我们求上周,上周我们必须得去DWS层求,对吧?啊,那我们这统一下吧,都去DWS层求。好,那现在呢,我先求咱们这个本周的啊,那这也去DWS,那DWS应该是UVD对抗的,那过滤条件呢,我们需要把这个呃,本周七天的数据过滤出来,那应该是VRDT是不是也大于等于本周一,那本周一应该是啥呀?我们前面是不是有本周一啊,那我们再再写一下吧,那data爱data艾然后呢,我们需要把next day啊next day放进来,然后呢,把当天日期先写上2020杠零六杠幺四对吧,那OK,咱们这儿呢,需要呃next day什么呀,Next Monday对吧,N day啊那后边呢,我们需要给它减一个减减几啊减七吧啊减七,那这个得到的是不是就是本轴一,这没错吧啊然后呢,还得按一下啊,按DT这个快速写一下小于等于,那把这个拿下来改成减一是不是就行啊CTRLV啊减一好,那这相当于把哎本周哎这个数据。
03:50
处理出来了,然后我们要找的是什么呀?是本周活跃的这个设备对不对啊,那你要记住一个这样的一个问题啊,那这张表当中,这张表里边啊,那我们是按天分区的对不对,那每天的分区当中是不是存储了一些设备啊,一些设备的当天的行为对不对啊,那注意在每天的分区当中,我们是不是一个设备占一行。
04:14
啊,对不对,也就是说我们实际上相当于在每天的分区当中,已经对设备是不是进行过驱重了呀,相当于一个设备一行嘛,啊是不会有重复的设备的,但是呢,我这是一周一周是不是有可能有重复的呀,所以咱们这里需考虑到一个驱重的问题啊,驱虫的问题啊,到我们这儿呢,需要怎么是不是格Y一下啊格y mid那上面呢,我们再选择对应的这个mid,那这个是不是就是去过重之后的了呀,那这个驱虫这会得需要给大家解释一下啊。大家都知道葛如拜呢,本身它不是用来驱虫的,对吧,葛如拜是干什么用的呀,是不是用分组聚合才用葛如拜呀,对不对,你看咱们这没有聚合吧,我们只是。光分组了对不对,咱们光分组为了实现一个什么效果呢?是为了实现一个去容的效果啊,其实大家可以思考一下啊,我们搁入外之后,是不是你搁入外之后,你的结果当中是不是一组只能有一行数据。
05:10
对不对,一组一行,那所以说这是不是就相当于是驱重的呀?啊,首先大家先明确,搁入外呢,能实现驱虫的效果啊,但是我们其实有一个函数是可以专门用来驱虫的,哪个函数啊,Distinct的对不对,但是我们在呃,Have当中呢,我们这个distinct呢,用的很少啊,一般情况下我们去驱虫的时候呢,我们都用谁呀?Go by或者说开窗啊,也能实现这个驱虫的效果啊,那为什么不用distinct的呢?这其实跟这个它底层的这个计算逻辑是有关系的啊,跟执行计划有关系的,如果说我们选用distinct的呢,它底层是怎么做的啊?他会把所有的数据最终都写到一个reduce里边,因为你要做全局的去种啊,对不对,你要想做全局去虫,是不是所有数据必须都得进到同一个节点,你才能做全局的驱虫啊,啊那所以说他要这这样做的话,会导致某个reduce的压力比较大,那所以说我们通常啊,哎,不这么去做,不这么去做啊,那通常怎么做呀,哎,分组啊分组,那分组的执行计划呢,跟定的执行计划它不一样,所以说这个呢,能够避免咱们某个reduce压力过大啊,这是大家呢需要去注意的一个点啊,所以我们只要在have当中,咱们涉及到驱虫了,Distinct呢,咱们尽量不用啊,咱们用谁用go by或者开窗啊,或者开窗,哎,那顺便问一下,如果想用开窗实现去重的效果,怎么怎么写呀,那词号。
06:32
开窗怎么去除啊?看怎么去找。比如说我这呢,我想用谁,我想用啊,我想对mid进行去重,对不对,那我就怎么做呀,我就加一个rank。还有一个rank啊rank,然后rank里边咱是不是得写over啊,对吧,那over我里边怎么写呢?Over啊,这里边我会写part提BY,拜谁拜mid,然后order by呢?Order by随便写一个字段。
07:00
啊,都可以啊,然后之后我相当于怎么样先生是不是对每一个mid,它里边是不是都做了一个123这样的一个排序啊,对不对,那我现在要干啥,我要去重,所以说你需要怎么做,是不是以它作为一个子查询,然后呢,把什么样的数据找出来,把所有排序等于一的数据拿出来,是不是相当于也实现了去重的效果啊,也可以这么去做啊,也能这么做啊,那在这咱们就用格Y就行,这是驱虫啊,那问一下,那现在我们返回的这个mid,那是不是就是我们本周的活跃设备的ID呢?是所有的吗?是所有的本周活跃设备ID吗?啊,其实这块呢,大家得得得得注意一个小细节了啊,得看这怎么理节了,来咱们打开这张表啊,DWUVD对抗的点进来,点进来之后呢,我们这里这里边呢,有一个字段叫做login count,对不对啊login count,这个login count咱们是怎么统计来怎么统计来的。是不是根据他当天的,首先先明确这个字段啥意思知道吧,这个字段的意思是设备当天的登录次数,对吧?啊,当天的登录次数,那这个怎么来的,是不是根据咱们那个当天的启动日志统计而来啊,对不对,但是咱们之前给大家考虑过说一个这样的一个问题啊,什么问题,就是你那个启动日志可能是什么时候,是不是可能是上一天,呃,半夜啊,就是23点多,五十五十多分,可能是那一天发的,对吧?那他是不是会发到13号的那个分区里,对不对,然后你这儿会有其他的一些操作,比如说浏览页面,页面呢,你是今天浏览的。
08:33
对不对,那也就是说会出现一个这样的数据啊,什么数据啊。捞count有可能是,是不是有可能是等于零的呀,对不对,那等于零的这种数据啊,那咱们得看怎么理解了,那捞count等于零那说明什么呀?说明他登录的那个动作没有在今天操作,登录的动作不是在今天做的,对吧,但是呢,今天有一个什么,有一个登录的状态吧。对不对,那所以说呢,这块儿咱们得得去考虑一个问题了啊,就这边你要找的这个设备啊,啊,你是按照这个诶登录的动作去找,还是按照这个登录状态去找,对不对,如果咱们这严格一点的话,我要是按照诶登录这个动作去找的话,那这块你得加一个啥呀,是不是得加一个过滤条件啊,对吧,那我得呃按一下,呃按谁按咱们那个login count你得怎么样,你得大于零才行对不对,这样一找到的就是什么,就是在咱们当天登录的所有的设备ID。
09:29
呃,当然这块是当周对吧,这是当周啊,是这样的啊,那如果说你这要不加这个字段,那你相当于过滤出来的就是啥呀啊就是在本周啊,有过活跃状态的这样的人对吧?啊还是有点区别的啊,那这边咱们之前是不是都带着这个捞count大于零了呀,那这边咱们也是按照这个登录的动作去算吧,啊这块大家搞清楚这个点就行啊,咱们文档上是没有加这个的,没没加这个,它是不是相当于按照那个登录的状态去统计的呀,对吧?啊这个其实呃无所谓的啊,咱们明确需举就行,这个到时候肯定需求也会给大家明确的啊去做的时候啊行,那这边我们就先按照这个去写,那这个我们紫砂群写完之后,相当于拿到了哎,通周的所有的登录过的设备,对不对啊OK,那现在呢,我们再取求上周的。
10:17
哎,那上周的是不是以它为基础,咱们这随便改一下就行啊,啊上周改哪。这是不是改这对不对,那这个应该减减几减个七,我再减个七,那这个减个一呢,我是不是再减个七,没错吧?啊,这就是上周了啊,那接下来呢,还得拿上上周,上周呢,我们再把它拿下来啊CTRLV啊,那这个呢,我再减个七,当然就不减七了,咱们这儿直接减个七乘三吧啊七乘三啊,你可以写这个算式的啊,那这个呢,减个七乘七乘二没错吧啊,那这个我也改成七乘二吧,显得有点那个。哎,他啰嗦了,诶72行吧,这就是相当于拿到了,哎上周啊这个,呃,这是本周,呃上周,这是上上周对不对?哎,三德大学,那刚才说了,我们需要获取这三个子查询当中是不是共有的设备ID啊对不对,那他们仨得做join吧,那问题是这个join的时候,咱这个join怎么写呀?
11:13
咋写呀,肯定会因得到对不对,那这块怎么写。呃,有没有说咱是这么写的呀?呃,先把前两个招唤到一起,然后呢作为一个子查询,然后呢再跟第三一个进行招。应该是有的啊,每个班都有,每个班都有啊,那这么写对不对呢?对,没问题啊,没问题,但其实没必要那么写啊,怎么写就行,直接这仨表进行照应就行,那具体怎么写呢?来给大家写一下啊,那既然要join了,你得怎么做,是不是得有一个自然学啊,相当于是我们需要给它来括号啊,来括号CTRLV给它起一个别名啊,往前推一下啊,这比如说就叫做T1吧啊来这个我们也是同样的处理方式啊,先来括号。改括号啊来CTRLV。大家可能还是语法不熟啊,还是得多练呀,CTRLX。
12:03
Control。我们type一下,这来一个T3啊,相当于sa子查询写完了对吧,我们要做的就是直接就是inner join,那也就是join呗,啊T1JOIN t2啊,那T2呢,我们直接照N,哎,T3对吧?那这个连接条件怎么写啊,咱们先先先说T跟T2,那T1跟T2照完之后呢,我们应该是ont1.mmid等于T2点,呃,这个m mid这个没有悬念对吧?这肯定得这么写,那关键是跟第三张表照的时候,咱们那个连接条件应该怎么写。对不对啊,应该怎么写,咱们是不是前面是想过那种什么NV,那那种那个全外联多表进行照唤对吧?那当时你还得什么NVLt1.midd,然后T2.mmidd等于T3.mmidd是不是大家是当初是这么写的呀,那现在咱们有必要这么写吗?没了,为啥呀,因为你这不是全外联的,你是阴的照呢,对不对,这相当于什么你的第一张表跟你的第二张表。
13:02
可能他们是是这种作用方式对不对,但是他返回的结果是啥。反这个去表是不是这部分呀,因为你用的是inner照,所以说你在跟第三张表照的时候,你用T1去跟T3照,或者是你用T2跟T3照,那你说结果是一样的不一样的啊,所以这呢,你随便写,写t1.mmid等于T3.mmid也行,写T2.mmid等于T3.mmidd也行啊,那比如咱们这写T1啊点m midd啊,Midd等于T3.mmidi是不是这么写也可以啊,也可以啊来那这个玩之后,我们相当于就呃select完了呗,这时候我们就直接从这里边去select,然后呢,我们from from下边这个三个字查询嘛,哎,那完了之后,我们最终所需要的结果是啥样的,所以所需要结果是不是一个抗的呀,对吧,对不对,那说咱这儿呢,是不是可以直接。看看咱们还有必要选里边的mid吗?
14:00
有必要选吗?没必要选,我现在不想要,只不想知道就是说哪些一共有哪些设备ID,我想要的只是一个个数对吧?坐这呢,直接是不是看德星就行,哎,直接看德星就可以了啊那这个看看清完之后呢,我们看看上面还有啥字段啊,那第一还有一个当天的统计日期,那这个我们就写当天日期就行,2020杠零六杠,哎幺四,那接下来往下走,那下边还有一个week克DT这个说了啊,这个位DT呢,在这就是想表示出来啊,你算的是哪三周的对不对,那咱们这怎么做呀,我们是用一个拼接就行啊,咱们用谁用con con cat啊con啊那先把这个呃第一周的周一日期拿过来,这个是不是就是咱们这是本周,这是上周,这是上上周,那这个就是。周一的日期吧,第一周周一的日期啊,你给他拿过来啊,CTRLC我们放到这个里边来啊,然后逗号我们中间拼一个下划线就行啊,然后再逗号再拿到最后一招里,也是本周的周日的日期,那就是他呗,这是C拿过来。
15:04
诶拼上就行好,那这样一来咱们这个结果呢,哎,就能出来了啊,这时候我们可以把这个S课呢,先跑一下啊,来看这个语法有没有问题,走好这个只要转圈圈啊,就就说明语法至少是没有问题的啊,然后咱们看一下这个结果吧,这个结果咱们出来之后呢,肯定是呃,没有没有数据的啊,肯定没有数据的肯定是零啊稍微等稍微等一下吧,你看。这相当于是我们是6月14号算的对不对,你算的是哪三周的,那是诶这这三周啊,然后呢,你算出来多少呢?零,因为我们现在只有一天数据,连续三周的是不是肯定是没有的呀,对不对,是这样的啊啊,那这就是咱们这一天的结果的样子就长这样啊啊那最终呢,别忘了给他insert到我们这个表当中啊,这是大家应该容易想到一种方案对吧?这应该容易想到一种方案,但是这种方案呢,说实话呢,有点不大好,呃,为啥不大好呢?啊对,造的比较多,而且呢,你要知道这里边每个子查询它的数据量啊,都不小啊对不对,这是几天的数据啊,七天的,这也是七天,这也是七天对吧?啊三个相当于三个不小的表进行这个招啊,这个性能肯定相对来说要差一些啊,但是咱们这儿呢,肯定是感受不到的,因为咱们数据没有嘛,你要是真实的这个数据,七天的这个登录记录,然后仨表示进照这个肯定比较慢啊,那所以说我们文档上呢,并没有采用这种方案啊,文档采用哪种方案呢?我们先来看一下啊,我把视频先录。
我来说两句