00:00
好,呃,那现在啊,大家来看一下,我们这个最近七天内连续三天活跃这个用户数,这个其实呃,确实有有一定的难度啊,这个当然大家前面已经讲过了,所以我这边也不卖什么关子了,也不卖什么关子了,呃我跟大家说一下啊,就是大家有可能会有一些这个呃,就是比较笨的办法,怎么做呢?你这不是有就不不就是什么,不就是最近七天内连续三天嘛,对不对,那我会算最近连续三周的。对不对,那这个东西你是不是就应该能能算出来才行啊,就说你不考虑其他的方法,就按照这个东西,你给他延伸一下,扩展一下,不就能算怎么办啊,你这么算就行。七天嘛,1234567,那七天里边连续三天的情况呢,你是可以穷举出来的,这个有可能对不对,那你按照我们刚才求连续三周的方式去求,这三天里边有没有活跃的,是不是可以求,那这玩意儿之后呢,再考虑这三天,哎,这三天,这三天这三天。对不对,就是最笨,最笨是不是用这种方法也能求出来啊,也能求出来,找出来之后呢,哎,我把这个东西诶给它,当然你需要去去一个重,需要去一个重啊,因为有可能这三天我连续了,那这三天我也连续了,你这你是需要驱重的,需要驱重的啊,所以这边你这种方式肯定能求出来这种结果,那这么做呢?呃,一是这个这个思后写起来麻烦,二是呢,不方便扩展,为什么呢?你这儿可能让你求最近七天连续三天,我后边可能让你求最近一个月连续15天的。
01:26
对不对,那你这个是不是就太麻烦了呀,那就不行了,那这边呢,那这种方案呢,咱们就不考虑了,就不考虑了啊,那接下来呢,我先给大家讲一讲,咱们文档上也是大家之前就是在那个讲那个应该是讲学have的时候吧,呃,老师给大家讲的那个所谓的一个什么等差数列的这种这种思路,先把这给大家讲一下,这种思路呢,其实是我们求这种就是多多少天内,然后连续几天诶活跃的这样的,呃一个可以说是一个模板吧,就是大家以后呃去做类似的需求,或者去面试的时候,遇到类似的这个面试题,都可以套用这个模板。
02:01
是这样的,咱们先把这个思路再给大家讲一下,讲完之后呢,呃,再给大家讲一个其他另一种思路啊,这个思路还是比较多的,我们先来看文档这个思路啊。好,那现在呢,文档的思路其实就是这样的啊,我们要想去把这个七天内连续三天活跃用户的这个用户数给他找出来的,你得干什么呀,你得这么去做,先把最近七天的注意啊,先把最近七天的所有的这个活跃记录全部找出来。注意你得把所有最近七天所有这个设备的活跃记录全部找出来,那应该去哪找呢?你要找最近七天对不对,七天是不是有历史啊,历史你得去哪?得去咱们DWS层去找,DWT是没有历史的对不对,那OK,那现在咱们去拿一下啊,那也就是说我们需要去select,那咱先不写高,咱先分析思路吧,先分析思路啊好,那比如我现在已经拿到了,拿到之后呢,我我我需要的是什么呢?我其实只需要俩字段就行了,一个是设备ID,首先我得知道是谁登录了对不对,那我还得再有一个字段,就是日期,我得知道他是哪一天登录了,那比如说我从这里边,咱们以一个用户为例吧,咱们也一这个这个设备为例,OK,那比如说呢,他在哪一天登录呢?比如说我空格空格空格来这个2020杠零三杠幺幺吧,比如他在这一登录了,那同理CTRLC。
03:18
往后走,我直接往下粘吧,嗯。CTRL回车,CTRL回车。看传。呃,这个应该是再来三条就够了啊,CTRLC。放在这CTRL好,那这是同一个设备啊,对吧,那假如说我在这个十一十二十三诶都登录了,那后边呢,我可能就到了多少了,可能到了这个,呃,15号了,这块没有连着啊16,然后这个17OK,那这样一来把这个去掉啊,那这是不是就是某一个用户最近七天的这个,呃,所有的这个登录啊,比如说咱们今天是17号,那OK,一从11~17号是不是七天啊,七天里边呢,我567连着,123连着。
04:00
这可能是一个用户的登录状态,好,那我们现在我们来给大家讲一讲这个思路,什么样的思路是这样的,我拿到它之后呢。我在这儿,哎,我给它开一个窗,开个窗,为什么这回要开窗啊,大家要知道啊,大家想一想这边啊,我们设备其实有好多的,对不对,你这呢,你可能是一号设备的CTRLC下边呢。CRV,我这边呢,我可能是这个,呃,有二号的对不对,这边呢,我可能是二号。咱们的登录记录呢,可能是这样的啊,可能这样的,OK,那这边咱们要干什么,我们要做的是啊是诶给他在每一个设备ID,注意是每一个设备ID的每一行后边,我给它加上一个这个函数,加一个什么函数,加一个排名函数。加一加一个排名函数,也就是说我需要需要干什么,需要在原来数据的基础上,原来多少行,那我这个加完这个rank函数之后呢,就还是多少行,原来多少行,我加上它之后呢,就还是多少行。
05:01
对不对,那这时候呢,需要我们用到这个,呃,开窗需要用的开窗,也就是在原来每行的基础上,我去给它加上一个这个函数,加一函数,那OK,这时候用开窗,那什么时候这个不用这个开窗呢?比如说呃,我想得到什么呀?是一个聚合,比如说我想把这个同一个mid的数据,我给它聚合到一组里边,也就说我这一组我最终就返回一个值,那这时候就是聚合,就是普通的这个格路拜,然后加去函数。呃,大致的区别是这样的,就是说你看你最终想返回的数据是什么样的,是聚合,就是说一组聚合到一起,还是我不聚合,不聚合怎么办呢?原来多少行我就还多少行,在原来的每行上面我加一个统计的函数是这样的啊这样的,这是我们开窗和不开窗之间的一个最基础,最基础的一个区别就是看返回的这个行数,那函数啊,那这边我们肯定要保留原始的登录记录,我需要用开窗,那开窗这边呢,我们加一个什么函数呢?我们加排名,那加排名的时候,大家应该能够反映到我们加排名的函数有好几个,对不对,有三个对吧?一个是rank,一个是density rank,还有一个是这个roll number,对吧?那这三函数有什么区别呢?来回顾一下,Rank呢,就是我们普通的排名,比如说我有并列第一,那也就说排名就是112对不对,那一三啊一三,诶那并列第一,那下面呢,就直接第三了,那density rank呢,Density是密度的意思啊,它要比这个稀疏要更密度一些,要要更密一些,所以所以是什么样的,如果有并列第一,那就可能这样一一下边的排第二。
06:27
对吧,它这个更密集,这个更稀疏,那OK,我还有一种叫做number number其实就很好理解,这不就是行号啊,对吧,123呗,那就123456,哎,那就是这就是我们这个三个呃,排排序函数的一种区别啊,那在这呢,我们加谁其实都一样。咱们用谁都一样,为什么呢?因为什么时候他们仨才有区别,是不是你得有这个相同的才有区别呀,有就是排序的时候有这个并列的是不是才会才有区别,那咱们这你说我们一会排序的时候按照谁排序,是不是肯定按照日期排序,大家想一想,我们这个日期有可能重复吗?
07:01
肯定是不会的,为什么啊,因为我们在DWS层,我们在为每一天的数据是不是都进行驱重了呀,也就是说一个设备在一天里边只会出现一次我们去重了,所以这个日期呢,是不会重复的啊,那OK,那也就咱们这用谁都一样,那比如我就用rank吧,用rank之后呢,咱们得到的效果是什么样的呢?Rank后边需要加那个over over里边既要加分区也要加排序,排序我们按照日期排序已经说过了,那分区按照谁分区,大家一定得注意得按照设备ID进行分区。对不对,我们得按照不同的设备自己算自己的,不能混在一起算,那所以这呢,咱们给它加上这个排第一,那继续往下排CTRLC。CTRLC这边呢,CTRLVCTRLVCTRLVCTRLVCTRV,那这个一,那它呢,呃排第二,这个呢排第三,呃,这个第四,诶这个第五,哎这个是不是第六啊,那同理下边呢,它也是123456对不对,那下边咱就不看它了啊,那给它干掉吧,那只看这一个好,那我得到这个排序之后呢,接下来干什么啊,接下来其实是咱们这种求连续,就是这个算法它的核心。
08:08
算法核心干什么?求一个差值。咱们叫D符吧,叫差值,差值是谁减谁?四用这个日期去减一个int值,日期减一个int值,那应该用什么函数,是不是应用data I的函数啊,对吧?那OK,大家看一下我剪完之后效果是什么样的啊,你看这边呢,我们剪完之后它CTRLC我放在这个位置。CTRLV,那这个应该是三月呃十号吧,对吧,那同理,下边呢,我们继续往下走啊,CTRLC,那12减二也是3月10号,13减三也是3月10号,15减四就是3月11号了吧,对不对,那大家现在应该也已经看到咱们这个规律了,什么规律啊,CTRL为。什么规律,如果说首先咱们rank这边啊,Rank这个是不是肯定是连续的。从到肯定是连续的,那如果说我这个日期它也是连续的话,连续的减一个连续的,那我最终得到的结果是一样的。
09:05
对所谓的等差啊,所谓的等差,那下边呢,我只要这个位置不连续了,那你看这边是不连续了,从13到直接到15了,但我右边它还是连续的。对不对,那你剪完之后呢,结果它肯定是不一样的。结果肯定是不一样的啊,那所以说那咱们就可以根据这个差值,根据它是否相等来去判断我这几个登录日期呢,是否是连续的,那怎么去判断呢?诶你就可以这么去做了,很简单。这么讲啊,大家想。我可以啊,把这个东西差值算完之后呢,按照谁按照这个差值进行分组。按照差值分组,因为它一样嘛,对吧,那我把一样的分到一个组里,分到一个组里之后,我对他干什么进行count。对不对,只要count大于等于三,那就说明我这三个日期,它是这这几个日期它是连续的,只要count大于等于三,那是连续的,对不对,因为我是相同的分到一组里了,那相同的是不是就是连续的呀,这边。
10:01
咱们用的其实是这样的一个原理,但是大家要注意,我这边进行抗进进行分组的时候,就对对它进行分组的时候啊,我们不能只按照它进行分组,为什么呢?你可以想一想,我们这虽说我们这这个按照这个例子来说,我只按他的分组就够了,但是不行,为什么?因为你在你的真实的数据当中呢,你是不是可能会有多个设备啊,我可能还有234对不对,那234我检完之后差值是不是也有可能等于什么3月10号啊,对吧,那你要只按它分组,那你会把不同设备的这个登录记录混到一起去算,那就乱套了,就不对了啊,所以分组的时候得带着谁,得带着设备ID。得是什么呀,得是设备ID相同,并且差值相同的,才给它分到一组,然后这一组如果个数大于等于三,那才是连续三天,这个活跃的才是连续三天的啊,这是咱这边的这个思路。那OK,这个思路因为前前面大家接触过了,所以咱们简单的回顾了一下,那接下来咱们开始写这个circle吧,那写circle啊,其实那就比较容易了,Select首先从哪,我们首先第一步啊,得先获取到这部分数据。
11:04
对不对,那设备ID加日期这个从哪去拿呢?这个应该是去从d ws UV d d count里边去拿,那然后呢,需要把这个最近七天的数据过滤出来,最近七天的数据怎么去过滤啊,应该是呃,DT应该得大于等于是不是data ID呀,Data I的,然后呢,把当天日期拿进来,2020杠零三杠幺零,大家这块要注意,你说我要获取的是最近七天的对不对,那最近七天应该减几?最近七天减几,我这是大于等于啊,是大于等于应该减几,应该减六才对啊,这块呢,大家要注意,这也是一个小细节,虽然比较简单,可以算一下啊,比如说1234567,比如说当天是七号,那我要获取的是不是一号到七号了呀,对不对,那也说应该是七减一,七减六是不是才等于一啊,七减六才,所以这块呢,你应该是减一个六,还要注意啊,OK,那把最近七天的数据先给他拿到,拿之后我们需要的只有什么字段,有什么呢?一个是mid设备midd,还有一个是日期,一个是DT。
12:02
对吧,那下边呢,我们需要加一个这个,呃,排序一个rank对吧,需要在每一行后边都要加rank,所以这时候呢,需要加一个开窗rank。呃,后边呢,加一个over,诶ctrl z o VR,那over这里边咱们分区,按照谁分区,刚才说过了,按照这个mid分区,不同的设备呃,不同的呃按照自己的去去计算,后边呢,呃,Order by order by谁呢?Order by这个呃,咱们的这个DT,这个DT。好,那呃,Order by dt的时候,大家大家要注意了,咱们这边那个我排序的时候,我应该呃怎么去排啊,应该怎么去排,大家要注意了,咱们这种方式,你这个这个排序规则应该是一样的,咱们rank呢,肯定是升序的,对不对,那同理,这边你也得是升序的,你这边要降序,那就乱套了,就不对了啊,所以说这边他俩一定都得是升序才行,都得升序才行啊,所以说后边呢,咱们就o by dt默认是不是就是升序啊,那就OK了,那接下来呢,呃,写完之后,咱们往往后走起一个别名叫做RK,叫2K啊,那OK,那这个circle咱们写完就得到哪些数据了呀,这个circle写完就已经得到了这部分的结果了。
13:13
这部分咱就拿到了,拿到之后呢,那接下来我们还得去干什么,还得去诶得到一个这个一个差值,得一个差值,那要咱怎么办呢?可以诶这样去做啊来咱们CTRLX把它作为一个子查询回车,回车CTRLV。然后呢,我type一下啊呃,Type一下之后呢,这边我写一个这个别名叫做T1好,那现在呢,我们从上往下选啊。Select,先不是选啥形from,那这回要注意一个点,那这回再选的时候,我们还需要这俩东西吗?其实咱不需要它了,我现在需要的是谁?我需要的是一个差值,还有一个谁,还有一个mid,为什么?因为一会呢,我需要按照mid加dif,按照这个差值呢去进行排序的。就是按照它的进行排序,所以这块呢,我们其实只需要选一个mid,再选一个差值就够了,那这个东西咱就不要了啊,那也就是咱们下面现在呢,我就选m mid,然后逗号这个差值怎么得到呢?差值应该用这个date I的函数,然后这里边把谁把DT拿回来,然后这回大家要注意,有的同学可能这直接就写2K了,你写2K就不对了啊,那这个函数叫做data I是加对不对,也就说应该是日期加上这个2K了,你要想想减,那怎么办呢?要么呢,你把这块呢改成这个负2K,或者说呢,我们这个date其实有一个简的函数啊,叫做date sub函数。
14:30
对吧,这个一个是加一个减,那但其实爱的是不是就能够实现这个加和减的功能啊,只不过这个你把后边要减加符号就行了,那这边你自己注意一下啊OK,那这个讲完讲完之后咱们也是起一个别名叫做diff。好,那呃,做到这一步,我们就现在就得得到得到哪块了呢?是就得到这个字段和这个字段了呀,那接下来我们要做的事就是对mid加dif进行BY进行分组,分完之后呢,把那个count大于等于三的给它过滤出来,OK,那现在咱们把它呃再做一个自查询吧,来回车L回车LCTRLV扔进来。
15:07
Type一下,呃,选一个这个起一个别名叫做T2,那这回呢,咱们要给它进行分组,对不对?那如BY谁呢来jup。嗯。呃,不用加T点吗?你说这个位置需要加T点,不需要加T点的啊,这块不需要的啊,不需要的,因为呃,我下边我只有这一张表了,对不对,我也没有跟别的表进行join,我也没有跟别的表进行join,所以说这块你不用加T点了,不用加T点了啊OK,那现在呢,我们Y。那葛如拜呢,呃,葛如拜完之后,我们需要给他,给他干什么呢?我们需要给他葛如拜谁葛如拜这个呃,Mid mid再加上谁啊,再加上那个所谓的Di,那个差值按照它进行分组,那这个呃,分完组之后呢,我们需要过滤一下啊,需要把什么样的过滤出去呢?我们需要啊把这个诶count星Co,这是咱们的核心,把count星诶大于等于三的组给它过滤出来。
16:14
注意把抗芯大于等于三的组过滤出来,那过滤完之后我们要选选的是什么呀?我们选的是from。注意啊,那咱们从这里边选,然后按照谁按照它分组,然后把这个呃,每组就是那个组里边的行数大于等于三的过滤出来,过滤出来之后大家要想我们这选的是什么,你选的什么,你选的是不是一组一组那个数据啊对吧,一组一组数据,也就是说你现在如果是这拿诶直接写抗还是不对的,还是不对的,你你你这选的是什么,你选的是比如说诶mid。对不对,然后诶抗性是几m mid2抗性是几对不对,你选的是这个东西啊,那所以这儿呢,我们仍然是不能直接抗的形式的,不能直接看我们还得干什么,还得再做一个再查询,OK,那大家这会要注意,这回咱们再选的话,我选谁就够了,我其实只选一个m mid就够了,一会我对它进行count就完事了,但是大家这回要注意,你到如果说只在这选一个mid的话,那接下来一会就直接对它进行count,那会不会有什么问题?
17:17
会不会有什么问题,会有问题的啊,如果说你这边啊只选一个mid,那咱们就以上边这个数据为例,那你得到的数据,你得到的结果应该是什么样的。得结果什么什么样的,应该是这样的啊,首先那这个我们分组的时候是按照m mid和diff进行分组的吧,对不对,那也就是说m mid相同且diff相同的数据会被分到一组里。能理解是吧,那也就是说这个同一个设备我是不是会分成两组啊,这是一组,这是一组。对不对,那这边咱们只选了一个m mid,那也就是说这个一是不是会出现两侧啊。对不对,我们统计的是,哎,这个连续三天活跃的人数,对,而不是说这个那个连续活跃的次数,对不对,我们要人数,那所以说你需要对这个mid还得干什么,还得再去一个重,还在去一个重啊,那驱虫这边我们前面讲过,就是说尽可能的呃,不要去使用这这个count distinct的。
18:11
尽可能的不要去使用这个count啊,有的人可能觉得这块呢,我直接怎么办啊,我直接这么做不就完事了吗?直接Co count,看这个Dis count d,呃,T啊然后呢,把谁啊把这个mid拿过来,哎有时可能我觉得这么算就就就行了,确实是啊,你这这么算的话,诶结果诶其实是OK的就是OK的,但是这块呢,大家不要这么去做,不要这么去做,我们怎么办呢?我们先对它进行这个呃驱虫驱完重之后呢,再进行C,诶咱这么去做啊,OK,那这边咱们先以它作为一次查询,先去重CTRLX。然后呢,来个回车,然后呢,再来一个这个呃,这个V,然后呢,咱们tap一下,那对它进行去重的话,是不是又要对它进行go by啊by mid,然后上边呢,我们去呃select一下。
19:04
Select,然后下边这个查询,那同理这边呢,你要呃这个来查询得起名才行啊,来个T3来T3好,那这边呃,咱们格入卖完之后,那这边我再选,选出来的就是什么了呀,选出来就选m mid,那你这回得到的就是那个已经驱完虫的所有的mid了,已经驱完虫的所有的mid了,那同理这边呢,你现在你仍然不能直接抗芯,还得干什么,还得再来一个自查询,那就select。呃,把这个拿过来。CTRLX来括号回撤回撤。上面先补上一个。来from,然后呢,把这个自杀去拿回来,拿回来之后我们这个type一下啊,来往下推。呃,这边呢,来一个T4。AT4,那这样一来,我们再从这个位置,我们再去选这个count count星的时候,这回你得到的才是一个最终的这个呃,连续呃三天活跃的这个人数,你这才得到了,这才最终最终的结果,最终结果啊,那上边我们看还需要补充什么字段,你再给他补上就可以了,那这边咱们拿一下吧,看一下都需要什么字段,这个呢,我就不再写了啊,这个咱们直接呃把文档上。
20:24
呃,这个界面语句拿过来CTRL位看看这里边需要什么,一是这个,呃,统计的这个日期,还有一个是最近七天的日期,也是你最近七天是从哪一天到哪一天,诶也是用这个第一天的日期拼上,最后一天的日期给他拼上就行,然后最终一个统计值,那这个呢,咱们直接把文档上的拿回来就行了啊这边呢,就是这个位置。CTRLC,然后放到这个CR上面,上面CTRLV呃就可以了,把这个12呢改成十。这个12呢,也改成十。好嗯,那现在咱们最后一个需求也就这个完成了,完成了这个思路呢,大家前面都做过,都做过,应该是比较熟悉了,比较熟悉了啊,那我OK把这视频先录一下啊。
我来说两句