00:00
看一下这个需求怎么做,呃,这个需求我们先回忆一下它怎么定义的啊。他让我们去找最近七天内三天连续的这个设备,或者叫用户的个数啊,最近七天1234567,先把这七点找出来,那我们要找的什么样的呢?啊,最近七天内三天连续,哎,这个假如说这三天的连续了,那这个是不是我们要找的一个,诶人啊,对不对,那这三天哎连续了也行,那这三天也行,这天也行,这三天也行,都行,只要这里边任意三天你是连续的,那OK,你就是一个我。就是一个满足我要求的一个用户,哎,我就把这样的人啊,找出来有多少个数数明白就行了,这就是我们要要做的事,需求很清晰,但是这个呃,实现起来呢,可能就比较麻烦了啊,那这边关键问题是什么,就是咱们得去判断一下啊,这个所谓的连续。啊,那大家说怎么去判断一个人他有没有连续登录呢。你最最容易想到的一种思路应该是什么样的?
01:01
最容易想到一种思路应该啥样的,怎么去判断一个人,他有没有连续。啊,比如说咱们就给你一个人啊,你现在就判断一下张三啊,你就从最简单的思路开始呗,判断一下张三哎,在最近七天内是否连续三天活跃了。你这个让你判断,你怎么判断呀。这你会怎么判断?啊,怎怎么整,你会怎么整。你是不会,首先你得先拿到。这个人啊,就是张三对吧,得先拿到张三在最近七天内的所有的登录记录吧,对不对,你是不是得知道张三在最近七天里边哪一天登录了,对不对啊张三这个比如说这个一号登录了二号登录了三号,然后呢,四号,然后五号,然后六号,诶你得知道他在这个一最这最近七天内他都哪天活跃了对不对,然后呢,再去判断这几个日期当中有没有连续三天。
02:05
对不对,有这三天,那他就是我们想要的,没有那就不是我们想要的,对不对,是不上是这个思路啊,哎,那所以说我们现在首先得拿到啊,就咱们当然刚才咱们举举例是张三,那现在我们要算的是所有人,我们需要得先拿到所有人什么呀,最近七天内的登录记录,对不?咱得知道他都在哪一天登录了嘛。对不对,那拿假如说咱们现在拿到了,拿到之后你怎么去判断这几个日期它是否是连续的呢?对不对,你拿到时候肯定会拿到一个人后边对应的好多日期,对不对,那你怎么判断这个日期它是否是连续的呢?怎么判断呀?怎么判断呀?哎,有同学想到了,是不是对日期可以先进行一个排序啊,对吧,比如说这是张三的最近三天内的,最近七天内的所有的登录日期,拿到之后先排序,排序之后那怎么判断是否连续,关键这个连续怎么判断。
03:04
如果说这个日期跟下一个日期咱们排完序了,假如啊,哎,如果说他俩相差天数为一,那你说这两天就是什么样的,就是连续的没错吧,哎,那你你你说怎么去判断这个日期跟他下一行这个日期是否是相差为一啊。也就是你是不需要把这一行数据跟下一行数据做一个比较,对不对,怎么比。这怎么比啊?怎么比啊这个。傻逼。嗯,要比的话,是不是肯定这俩日期,我们比如说对对相减一下这个日期减那个日期,如果等于一,那就说明它是连续的,对不对,那他俩怎么能减呀。怎增减,大家得知道我们这个circle是怎么去做,怎么去执行的,是不是一条数据一条数据的去走啊,对不对,那你现在相当于是是不是这行需要跟下一行去比较啊,那能比得了吗?比不了,那怎么样才能让他跟下一行比较啊。
04:09
咱们如果说能把下一行的日期,我如果能给他搬到上一行来。那他俩是不是就能比了,这俩是不是走到这行的时候,那用它跟它下一行比一下是不是就可以了,对不对,那这个怎么给他搬上来呀,怎么能把下一行的某个数据给他搬到上一行来。怎么能搬呀?用谁可以实现呀。咱们have当中是不是有一个开窗函数叫做lead,或者叫like?还还,还能想起来这俩函数吗?力的函数led like LA对吧?Led的函数指的是什么呀?是不是就可以获取每一行的下边某几行的某个字段的值啊,当然咱们这儿是不是要想获取下一行,那你就用例一对不对,下两行就LEAD2呗,啊对不对,诶你用它是不是能够实现类似的效果,那所以说我们这儿呢,最简单那种,最大家最容易想到一种方案应该是怎么做呀,那就应该是这么去做。
05:09
啊,就是这么去做,那紧接着咱们就先把这个思路先去实现一下,这个思路呢,应该是大家最容易想到一个思路,文档上那个确实大家不好想啊,这很正常,那我们现在先想,先先先来整这个思路啊,看这会咱们怎么做好,那现在呢,我们举个小例子吧,来,我先造点儿数据啊。啊,这个我看有没有用,这没用啊,全部删除好,那刚才说了,我们要想去找那些连续三天活跃的,我们需要先把什么呀,先把每个用户的什么东西。每个用户的最近七天的活跃记录是不是先得拿到啊,咱们得知道他在哪天登录了啊,那所以说我们这儿呢,比如说需要拿到这样的两个字段,一个是设备ID,一个是他登录的日期啊,一个登录日期啊,OK,那大家得知道啊,咱们这必须得避免一个这样的现象啊,假如说一个人我在这一天里边登录了两次。
06:01
你这需要做什么事?你需要去虫啊,需要去荣啊,也是咱们这必须得保证啊,一个人啊,一天的登录记录只有一个啊,就是表示出来他在哪一天活跃就就行啊啊那这点咱们先明确啊,所以拿到这个,那假如说咱们这儿的举个例子是这样的啊呃,比如说我这个UU段ID是101,那日期呢,随便写一个啊2020杠零六杠比就幺四吧,啊就幺四。那CTRLC。CRLV啊,CTRLVCTRLVCRLV啊CV,假如说啊,我这一个人,他在最近这个七天的活跃记录是这样的,嗯,幺五这个呢是幺六。定的是幺几啊,幺七这个不要七了,这不让他连续了,咱们幺八吧,拿幺八这个是幺九,那这个是多少二零。嗯,大家看一下啊,这个就是我们1001这个用户在最近七天内的。登录记录,他是不是在这六天里边登录了呀,对不对,那他很显然是不是就是我们一个,呃,目标用户啊,这就是连续三天活跃的嘛,就有的啊,那看看怎么找出它来啊,那刚才说了,那咱们需要怎么做,我们大家最容易想到思路呢,是怎么做呀,是是不是使用力的函数或赖函数去跟它下一行做比较啊对不对,我们需要将这个玩意儿给它是不是搬上来呀。
07:20
啊,那当然咱们这儿要找的是连续几天的呀。咱们找他连续三天的,我不是要找连续两天的,你要找连续两天是不是就只跟下一行比就行了呀,那我要连续三天的呢。那怎么比啊?连续三天你可以这样去做,这个思路很多的啊,连续三天的,我可以怎么做,我可以把每一行都怎么做,都跟他上一行比一下,再跟谁比一下。再跟下一行比一下,如果跟上一行相差为一,跟下一行相差也为一,那就说明什么,那就说明我这个日期是不是跟上一个日期是连续的,跟下一个日期也是连续的,对不对?那这个日期所对应的这个用户,那他肯定是什么呢?肯定是一个连续三天活跃的用户,那我把这样的人找到是不是就可以了?
08:10
没错吧,所以说你可以这样去做,或者说你还可以怎么做呢,还可以这样做。啊,我去找一下什么呢?找一下每一个日期,它下边两行的日期。找每一个日期下边两行的,我直接跟他比。啊,因为咱们这个日期是不是进行排序了呀,而且日期也进行了去重,相当于对不对啊,既排序就去重,那如果说我这时候出现一个这样的现象啊,如果每个日每一行,那都跟他下边两行进行比较,然后他都差几啊都差二。那就说明什么呀,那就说明从这一行开始,把它下边两行这三天一定是连续的。为什么这么肯定啊,你想一想,咱们这儿首先对日期是不是进行排序了,那所以说肯定是有序的,比如说咱们升序,那从小到大,那再一个呢,我们驱虫了。
09:06
对不对,那去虫是每个日期跟每个日期都不一样,对不对,那这时候呢,你这一行跟它下两行相差为二,那中间这一行你说肯定是什么样的呀。肯定是它俩中间那个值,因为什么?因为中间那个不可能等于14,也不可能等于16,而且是有序的,它必须得大于14,必须小于16,所以说它只能是多少,只能是15,那质量15是不是肯定是连续的呀,也可以这样去找啊,这样去找啊,那比如说咱们这就这么去写吧,那所以这样呢,我需要加一个力的函数啊,力的啊力的函数,那力的函数呢,我需要给它传几个参,还能想起来不?别谈。全忘了啊,三个参数啊,首先第一个参数啊,你得传一个啥呀,你要获取的是下边某几行的第几个,就是哪个字段,咱们要获取就是哪个字段,是不是就是DT字段对不对,那你要获取下边几行立的是下几行啊几行啊咱们两行对吧?啊,那完之后呢。
10:01
还有一个值是啥呀。默认值啊,默认值,因为你比如说咱们按后边你这个开通函数是不是肯定写over over肯定得分组对吧?Part,那part by谁呢?By mid啊part by m mid啊,那你比如说我到这儿了,那它下边两个还有值吗?没了没了你得给它一个默认值对不对,那这边呢,我们给一个日期啊,咱比如说给2020杠零几啊零。啊,不玩0.0了,咱们给一个到不了的日期,比如说呀,比如1970吧,啊,1970杠零幺杠零幺啊这个是不是很久之前了呀,咱们肯定到不了啊,所以说那这边你可以这样去写。啊,那这样一来我就能获取到每一行的下两行的数据对不对,那咱们现在把数据可以先拿过来啊,比如说我现在这一行,这一行下两行的日期应该是多少,是不是16对不对,那你就CTRLC拿过来呗。啊,CTRLV啊16啊。好,那咱们接着走啊,CRC那十五行的下两行是几?十几十八是18吧,啊没错,那16号的下两行呢,十十九。
11:06
没错吧,那18行的下两行应该是多少?20吧。是不是20没错啊,那再往下呢,19行的下两行。没了是不是没了就是解没了就是默认值呗,啊就是它呗,CTRLC啊来CTRLV,那这个呢。那也是默认值没错吧,对不对,那我们现在已经得到这个值了,那现在我们要找什么样的东西啊。咱们要找是不是这个日期跟那下两个日期相差为二的呀,对不对,那你要要找的话,是不是得先减一下,那所以说我们这减一下用谁呀,这得用谁减是不是date Di函数两个日期相减吧,啊对Di啊,那减你甭管谁减谁吧,啊也是一个正负的问题,比如说咱们用这个减这个,那咱们得到这个项是几。这是等于二对不对,那CTRLC,那下边这个呢,是二嘛,你仔细看就是八减五等于三是吧?乘二三的部分了呢,那这是三啊,这是三,你得搞清楚啊,这是三,那CTRLC接着走,那这个是几?这也是三对吧?那这是几?这是二啊,这是二。
12:20
啊,这是二,那这是几啊,这甭管是几的,肯定是一个负数对吧?肯定一个负数啊,那咱们就随便写一个负数啊,那CTRLC。那这个CTRLV。诶出事行,那现在是不是已经得到这玩意儿了,那得到它之后,那再往下走。那你是不是就会做了呀,我们找什么样的呀,我们只需要找这个差值等于二的。这个数据,然后呢,只只找谁就够了呀,是不是找这个m mid就够了呀,找m mid咱们就相当于找到了那些能够连续三天活跃的这个设备了,那当然我们最终要的不是这些设备是谁,我们要的是啥呀,我要的是个数对吧?所以说最终呢,你需要对它进行一个哎统计,当然你统计的时候需要注意一下,你看啊,那因为咱们这儿这三天是连续的,所以这个结果是不是等于二,那这三天也是连续的,所以它也是二吧,对不对,那咱们这儿呢,相对得到,你要是根据这个等于二去过滤,你得到会得到两个1001,你需要对它先去重,再进行抗的统计,对吧。
13:21
是不是这样就能找到我们这个连续三天活跃的呀,这种思路呢,应该是大家最容易想到的一个思路。大家之所以想到这个思路,那你觉得你是为什么没想到啊?是你想不到这个力的函数和那函数。对不对,你只要想到他之后呢,那那这个其实你就能想到,这儿不一定非得跟下两行比,刚才咱是不是可以跟上一行或下一行比也行啊,对不对,这个思路很多的啊,但是你可能就是没有想到力的,后来函数为什么没想到呢?就是不熟啊,可能连学过他都忘了,学没学都忘了是吧?啊,确实这样的,你这个函数你要除这个思路应该是能想到的啊好,那能想到,接下来呢,咱们把这个去实现一下呗,啊咱们实现一下啊,来看看这个怎么写来吧,一起写啊,一写。
14:12
啊,可这位啊。来,我们一起啊。嗯。那首先我们需要先拿到这个我们所需的原始数据啊,咱们需要拿先拿到最近七天每一个设备的登录日期,对不对啊,那OK,我们来一下啊,那select from哪。从DW star log是不是肯定能拿到每一个用户的登录记录,但是那个咱们还得去重吧,我们这儿可以去哪拿,可以去DWS层呢,对不对,DWS是相当于已经对每天进行驱虫了呀,对不对啊,已经每天驱虫,那咱们这直接拿就行,那来怎么办?Select,然后呢来from DW t啊DWS啊,然后呢来一个诶UVDT有对抗的,那日期呢,DT应该是大于等于。啊,咱们最近七天是不是应该大大于等于六天前的日期啊,对吧,那应该是data塔I,那把当天日期先放进来,2020杠零六杠哎幺四,那待会呢,我们减个几减个六啊减六啊减六这个要注意啊,按照D题要该说最近七天嘛,啊减于等于当天2020杠零六杠幺四啊今天拿到了,那当然这里边还是得考虑一下啊,咱这是不是还得注意到那个字段来,我给你看看对吧?呃,你你看你这你要找的是什么,是连续三天登陆的对不对,所以咱们这肯定是按照动作去统计,所以最好咱们这加一个啥,哎加个login count大于零,因为它有可能等于零是吧?啊来于零好,这是不是搞定了,那搞定之后咱们接着走啊。
15:41
咱们接着走,那我们这只需要拿哪个字段就够了呀。是不是只需要拿mid跟DT就够了,Mid dt是不是叫他就行?那当然除了它之后呢,我们还得拿谁呀?不拿后边那个函数来呀,就得诶。来拿函数,也就是这个东西吧,哎,那它下两行,那下两行怎么拿,这不应该是力的led啊,里边呢,传三个参数啊,第一个参数传啥。
16:10
说啥DP,那第二参数是啥啊,这应该是二啊下两行吧,那默认值那就写呗,一九这个,呃七零杠零幺杠零幺没问题吧,啊下两行的日期,那这边呢,咱们给它起一个别名叫李德啊李德啊那这是完事了啊完了之后呢,我们需要怎么做呀,是不是根据这个东西去进行这个过滤啊,对不对,那所以咱这呢,写一个子查询吧,啊CTRLX写子查询,或者其实你这直接怎么写就行。直接他俩在这直接相减就行,对吧?啊直接相减就行,那当然咱们这为了这个思路更清晰一点呢,我们来一次查询啊,咱一步一步去做啊,啊CTRLV,那我们在这。那我们现在就以它作为紫察询,我们起别名叫做T1吧,啊叫T1啊,那是不是select,然后呢,我们诶from这个东西啊,那from的啊,那from这个东西的话呢,我们呃大家要注意了,我们这是不是应该是有一个呃Y2过滤,咱们先先求吧,咱们一点点求吧,这个让大家思路清晰一点啊,那比如说我选一个谁选一个mid,再选一个啥呀,再选一个这俩东西相减的一个值吧,是不是用例二减DT,那我们用例二减DT,那应用谁是不是用data diff啊啊date dif啊date dif谁减谁应该是让立的二减去。
17:27
哎,不是,应该是逗号就行了,不用再去减了,哎,直接逗号减减DT,那这个是不是会得到一个差值,这个差值呢,我们叫D。A叉值,好,那现在我们相当于已经做到哪一步了,咱们已经拿到了这个东西,拿到了这个东西了,对不对啊,没错吧,那现在我们要做的就是啥是不是咱们就再以它作为一个子杀菌吧,以它作为子杀菌,然后呢,过滤条件应该是谁?应该是D,是不是等于二啊,那我们就能把那个连续的mid找到了,但是最终我们需要去重再抗的对吧?那所以咱们现在再来一次查询吧,那这里边当然很多子查询是可以优化掉的啊,这一会咱们再说,我先把这个实现啊from来括号。
18:07
来,我们往哪走,那CTRLV啊,来吧,往前推。往下退,好,那这时候我们呃已经得到差值了,那咱们需要去过滤吧,那就是Y2Y啊呃起微名,起微名T2啊WHERE2V2,什么VR Di是不是等于二,没错吧,那这样一来,上边我们再选择我们所需的mid,但是这个mid呢,它有什么样呢?它是重复的,重复的得怎么做,重复是不是得去重,去重咱怎么做,可入BY就行,可入by mid啊,那现在是不是已经去重之后的了呀,对吧,但是我们要找的是啥?是那个个数对不对,这个个数怎么处理?在这直接看看行不行。这问题行不行,不行,为啥还是那个道理,因为你这分组了,分组之后你求的count就是分组之后的count,对吧?我们应该怎么办?先选一个m mid,再来一个是子查询,对它进行一个count ctrl X啊,那现在我们select,然后呢来from。
19:08
回车来,括号来,拿下来。CTRLV啊,那现在我们来到这个位置往前推,好,那这个完事之后,我们这儿还得起一个别名吧,啊,起一个T3。第三啊,那下面看得清。哎,所有问题。那行,这个得到的就是我们最终的结果了。啊,就是这么写,来我们看一下这个这个思路有没有问题啊,这个思路会不会报错啊,走你这个报错了是吧,这个missing our class lead。呃,这个啊,没写over是吧,啊忘了我说怎么这个这么短呢。啊,这个没人提示一下啊,来oo里边写啥呀,那分组对吧,Part by PI part by by谁BY啊,那这个order by谁呀,Order by dt啊o DR order by dt,那DT我们默认是升序,那其实咱们这算是不是就按照升序升序算的呀,对不对,因为咱们这是正二吧啊正二啊就按顺序算的行,那这时候我们再来一下啊走啊,目前这个没报错,转圈圈,当然这个结果肯定是零啊。
20:13
OK,那这个东西咱们就算出来了啊,就是这么去搞啊,好,这是思路一完事。
我来说两句