00:01
第一个需求这个呃,活跃设备数,活跃设备数前面咱们已经分析过了,对吧,活跃设备数呢,其实这里边所需的三个值,我都可以从哪去获取啊。这三支都可以从哪获取,其实都可以去从呃,咱们的这个呃,DWT层的这个UV topic这张表里去获取。都可以从这里边去获取啊,那咱们怎么去做这个需求呢?我现在呢,把这个表先粘出来啊,先把这个表粘出来。呃,粘到这个位置CTRLC。我放到我这个笔记当中啊,呃,这个笔记呢,我一会呃会给大家那个发下去的啊CTRLV,呃,我们来看一下表里面的字段呢,咱们就那个已经说过了是吧,里边有一个统计日期日周月活,然后呢,为了去把咱们周活这个数据和月活的数据,这个完整的数据,咱们拿到加了点标记,是否周末,是否月末,对不对,这是咱们的字段,呃,然后我们索性就说一下吧,说一下咱这个建表诶跟之前的表有什么不同。
01:06
有什么不同,大家看一下啊,呃,我们前面DWDDWSDWT对不对?我们的表都是什么表?呃,当然DWT呢,它不是分区表,我们DWT是一个完整的表啊,就一个大表,那DWDDWS咱都是什么表,基本上都是这个分区表啊,但是你看ADS咱这是不是也是没有分区啊,没有分区,其次呢,咱们前面的表D从ods层开始到后边是不是都会进行压缩呀?呃,当然那个DWTDWSDWD这几层我们还进行了列式存储,对不对啊帕的列式存储,那在ad层呢,这个表是不是就是一张普普通通的表?既没有列存储,也没有压缩,也没有分区,对不对,那就是这这么一张,这个就是说存储咱们这个原始数据,这张表就是数据什么样我就存成什么样,那这是为什么呢?其实大家可以想一想啊,你说咱们每天往ADS层导的数据会很多吗?
02:04
每天往ADS导的数据会很多吗?其实很少很少,以这张表为例,咱们就以这张表为例,大家说我每天往这张表里导多少数据啊?一天是不是也就一条数据啊。一天就一条数据,一天就一条,那你说我还有必要进行什么列式存储啊,有有必要进行压缩吗?其实没有必要的了,是没有必要的啊,那所以说这边这是我们adi层这个表的特点,大家要注意到,那OK,那完了之后呢,我们开始去写咱们这个呃逻辑啊,写这个逻辑。前面那个当日的活日活呢,我其实这个东西已经跟大家分析过了,已经分析过了,所以这个呢,我就不在这个呃赘述了,咱直接怎么办,直接开始写搜索。Select先不说选什么啊,咱就说先说from哪,先说从哪选,应该是从DWT同样的一个这个UV里边去选,对不对,那这张表它是什么表。这张表是是不是全量表啊,全量表什么意思,也就这里边呢,存着我所有的设备的这个登录信息,所有设备,而且一个设备是不是占一行啊,一个设备一行,一个设备一行,一个设备一行,这是咱这张表,OK,那我现在想找到当日诶日当日这个活跃的这个用户数量,那我应该怎么去过滤,我们这么去找啊,来CTRLC,呃,咱们找一下,不用CTRLCCTRLF吧,DWT找一下那个UV topic的这张表。
03:26
咱看这张表去选里边的这个字段啊,咱们把它粘出来。可CF3给它盯在这个位置,那现在呢,咱们给它删掉,那往下走,呃,我们来一起看着这张表,我们去往里边,呃,选我们想要的数据啊,那首先咱们这样的有这样的呃,有这样的几位,有这样的三个值,三个值。那这三个值啊,呃,有的同学可能会这么去做,诶他写三个这个子查询,然后呢,再给它招引到一起,再招引到一起,可不可以这样,可以可以这样做啊,就是说我先把咱们今天活跃的这个用户过滤出来,过滤怎么过滤,通过这个呃,Log de count也好,或者通过这个呃,Log in data last也好,嗯,这个怎么过滤都行,诶过滤出来,然后这个呃,用VR过滤,然后count得到一个值,那这个呢,同样过滤出来,Count得到一个过滤,Count出来得到一个值,然后呢,诶给他们照到一起,你可以这样去做,或者说你还可以怎么做呢,还可以用咱们这个,呃,Sum if这个嵌导函数去完成咱们这样的这个统计,比如我这呢,我就用这个sum if去做,那OK,我现在首先我先来一个什么,先来一个咱们的统计日期,那统计日期呢,就直接写死就可以了啊,2020杠零三杠幺零咱班这个数据呢,是十号和11号这两天是吧,那我就统一都以这个呃十号去写,那咱们笔记当中呢。
04:48
可能有有一些S呢,可能是用的这个,呃,15号或者其他这因为我们咱们在做这个文档的时候呢,呃,用过这个15号的数据是这样的啊,那其实甭管他这个日期,咱们具体写的是谁,你不要管,为什么,因为大家应该都知道啊,咱们去开发这个数仓当中的需求的时候,我们有一个具体的思路,什么思路呢?就是说呃,你甭管咱们这个需求到底是怎样一天一天的去跑的,不用去管,你只需要做的时候呢,只需要先考虑一个事,就是说我先考虑一天我应该做哪些东西。
05:20
对不对,咱们就随便找一天,比如说我这呢是用于用十号的数据,对不对,我以这一天为例,我就写一天的circle,写完之后呢,那这个circleq后期要干什么呀?后期这个circle是不是要封装到一个像我们之前每一层的那个呃脚本对不对,要封装到一个那样的一个脚本当中。呃,然后在脚本当中呢,所有的这个具体的日期,你都需要给它替换成咱们那个时间变量Dollar do date,你就获取前一天的日期都需要去替换的啊,所以这个具体咱们写测的具体时间是多少,这个无所谓,你那个自己的数据是哪天,你就用哪天的做测试就行了,好,那这边简单的说明一下,好,15号写完,然后下边呢,我要去获取咱们当天的这个用户数量,对不对,这个活跃用户数量当天的啊,怎么办?是不是可以这么去做sum,然后加一个if,这函数呢,大家应该使用的还是比较频繁的,就是这个函数嵌套,就是通常我们会用sum里边呢,套一个if函数,那这个怎么去使用,应该大家心里也很清楚了,这块应该怎么写呀,咱可以这么写,如果说诶咱们今天这个用户活跃了,那OK,我就给他记一个一,如果他没有活跃呢,我就记一个零。
06:27
那这样一来,咱们就求一个sum求和,是不是能够把今天活跃的所有的这个设备给它找出来,并且是完成了这个计数了,对吧?所以咱用这个sum if就不用再去嵌那么多子查询了,不用去join了啊好,那现在sum if,那OK,我就使用这个login date last吧,如果说login data last等于今天,比如说今天是二零,呃,二零,然后杠零,三杠幺零。等于3月10号,那OK,我就记个一,否则呢,我就记一个零,OK,那这我就拿到了咱们今天的日活了。
07:00
对不对,那周火怎么去拿周火同样的道理啊,CTRLV。周所谓的周活是不是就是如果说这个这个谁这个用户呢,他在本周活跃过,那OK,我就算他是一个什么呢,就算他是一个咱们,呃,本周的活跃用户对不对,那如何去判断他是否在本周活跃了呢。怎么去判断呢?是不是也是根据最后一次登录的时间呢?对吧?如果说最后一次登录的时间是在本周,那就说明他在本周活跃了,如果最后一次活跃的时间没在本周,那是不是说明本周他没活跃啊,对不对,那就是这个道理,那现在咱们去写一下这个事啊,那咱们去写来,咱们需要这次呢,需要把这个if里边的这个布条件是不是得换一下来啊,咱得给它换一下啊,那为了这个这个清楚一点呢,我给他拿到外边去写,然后再给它放进去啊,如果说这个,呃,Login date last。在本周,在本周怎么办?是不是应该是大于等于本周周一的日期啊,本周周一日期怎么去获取,这个大家应该也知道,是不是使用一个date,呃,或者一个date函数啊,呃,然后里边呢,咱们嵌套一个next day这样的一个函数,这函数大家应该还是比较熟悉的吧,那OK next day这里边应该怎么去传呢?首先把当天的日期给它传进来。
08:19
哎,2020杠零三杠幺零,OK,那再往后呢。是不是应该是我要获取的是本周一日期吧,那我可以先求出来下周一的日期,然后再用date a再减一个七,就获得到本周一的日期了,这样大家比较熟悉了啊,OK,那写这个M,这个Monday的时候呢,就写这个周几的时候呢,可以这么去写,OK,你写的时候可以写全称,比如说Monday day就是全称,或者说写简称,那简称可以怎么写呢?简称可以写前两个字母或者是前三个字母。注意写的时候只有这三种方式啊,要么全称,要么前两个字母,要么前三个字母来把这个记住啊好,那现在咱们呃,下周一的日期获取到了,那我现在还得减一个一,减一减一个七,那就得到了本周周一的日期就是login data last大于周一日期,然后呢啊and。
09:11
And,那我写在下边吧。and,这个login date。Date last还得小于等于本周周日的日期,那本周周日是不是就用下周一减个一就行了呀,CTRLV,然后呢,这边我减一个一减一个一,那我给他并上去吧,并上去来。把这个呢,CTRLC粘下来,粘下来之后呢,呃,放到放到这个位置,放到咱们的这个if里边CTRL。V,好,那这边如果说我本周活跃了,那OK,我就记一个一,否则我就记一个零,那是不是就把本周周活给他找到了呀,那鱼活呢,是不是也是这么个写法啊,也是一样的写法啊,那同样sum if。嗯。Some if,如果怎么怎么样,我就记一个一,否则我就记一个零,好,那这回怎么就记一个一了呢?是不是就是如果说我最后一次登录日期是在这个,诶本月,那OK,我就给你记一个一,否则我就记一个零啊,那这个本月咱应该怎么去获取呢?
10:16
本月应该怎么去获取呢?等下怎么回去啊?本月啊,你可以这么获取啊,呃,我看咱们班同学有用这个串个函数的,对吧?T ru nc truc有用这个函数去获取的,那这个函数可以怎么去做呢?这个函数啊,可以通过这个函数呢获得,诶每个月这个呃,第一天的日期,每个月第一天的日期可以用它去获取,那我们前面还学一个函数,学一个什么函数啊对,还有一个last day函数,那这个last day函数呢,你可以用它去获取咱们每个月月末的日期。对不对,那你有了这俩,这个能获取月,呃,这个月初这个能获取月末,那是不是就能够根据他去把咱们一一个月的数据过滤出来了呀,对不对,这是可以的,或者说呢,在这你还可以这么去做来,我们可以这么用啊,怎么去获取这一个月的数据呢?可以这么用,我们在have当中呢,有一个data format函数,那data form format函数咱可以怎么去使用它可以这么用,大家来看一下啊,如果说我login date login date last,这就是咱们最后一次登录时间嘛,如果说我给它格式化成什么样呢?格式化成YYYY,然后呢。
11:27
杠大M大M,那这样一来,我相当于把什么呀,我是不是相当于把这个日期当中那个日给它去掉了呀,是不是只留一个月份就是年月,只留一个月份,只留只留年和月,那OK,如果说它等于什么呢?等于data format。把咱具体的日期放进来,2020杠零三杠幺零,那同理给他也是格式化成这个YYYYMM的这种格式,那这样一来,你看看是不是也能把这一个月的数据给他找到啊。
12:02
对不对,你可想一想,这是咱们那个这个最后一次登录日期,OK,把那个日给他去掉,那这边呢,咱们这个具体的结果是不是这能能出来,这不是就2020年杠零三吗。对不对,是不是所有三月份的数据都能够匹配的到啊,诶是这样的啊,这样他也可以通过这种方式去过滤咱们这一个月的数据,OK ctrl X,咱们拿下来是不是给它放在这里就可以了,CTRLV。呃,那这样一来,咱们这个,呃,日活,这个周活和月活咱就全部拿到了,拿到之后呢,大家还不要忘了一件事啊,我们还得加一个标记,就是是否周末是否月末。哎,咱给它写上这个很明显,呃,这边呢,咱们需要用到一个什么,用到一个if函数呗,对吧?呃,如果是周末,那我就返回yes,如果不是周末,我就返回no,这时候很和和谁很像,和我们Java当中的那个三元运算符是不是很像啊,就一个布尔类型,然后两个值对吧,那谁谁跟那个一样,就我们if是不是跟他正好一样啊对吧?If先拉一个波尔类型的值,那也就是说判断我当天是否是那个周末,2020杠零三杠幺零,呃,OK,那它等于周末,那周末应该怎么去获取啊,周末是不是应该是用这个周末我们去找一下啊,这个是,这个是咱们的周末的日期吧。
13:19
对不对,这就是周末来CTRLC我给你拿过来放在这儿。CTRLV,好,那如果说今天是周末,那OK,我就返回一个这个yes,返回大Y,否则的话呢,我就返回一个大N。这就加上这个周末的标记了,那同理咱是不是还得再给他加一个这个,呃,月末的标记啊,那月末怎么去获取呢。是不是用last day就可以了呀?零三杠幺零来,呃,如果说今天是月末,那就是last_day对吧?那如果说呃,今天是月末,那我就返回yes,否则呢,我就返回no。
14:00
然后那个呃,写在这个位置逗号。然后大Y,然后逗号来一个大N加引号啊,别忘加引号。OK,那这样一来,呃,咱们就,呃,诶哪个是周一啊。这个是周一吗?这不是周一啊。呃,Data,哦,咱们忘了减那什么了吧,咱是丢了一个减一啊,丢了一个减一啊,来data I next day,呃,完事,咱这没有真权啊,咱们这丢了一个那个,呃,逗号还得减一个一才对啊,咱们这呢,呃,忘了减一了,对对对,忘了减一了,咱们这只粘到一半,行,那这回应该补全了。OK,呃,那现在呢,我们诶。这块呢,我还有一个问题,需要还有一个东西给大家说一下啊,你看谢波同学他说了一个,说了一个这个,呃,这样的一个函数,呃,Day of week day of week day week呢是这个,呃周一的意思,就是说它是这一周当中的这个,呃,周几。
15:05
一周当中周几,其实呢,这个呃,倒是也能根据这个函数呢,去获取我们想要的数据,也能也可以啊,那咱们去限定一个周的这个,呃,这个时间范围的时候呢,这个其实方法有很多种,方法很多种啊,那大家只要保证我能准确无误的把这个我想要的数据拿到就行,不能多也不能少,只要保证这个就可以,方法还是很多的啊,那OK,咱们现在呢,去测试一把这个数据,看能不能跑通啊,这个看看语法有没有问题,CTRLC。呃,然后我拿到这个我的这个还当中去跑一下啊,来回车走。OK,目前来看这个语法是不是没有问题的呀,语法是没问题的。好,那语法没问题,呃,咱们就让他跑着,然后咱们去把这个需求的这个思,咱们给它补全完整,补充完整啊,那补充完整其实上边是不是就接一个这个insert就可以了呀,就接一个insert就行,那大家这块要注意了,我们前面大家是不是都习惯性的上来就是insert over right呀,对不对,那这次我们还真不能用这个insert over了。
16:06
我们不能用了,为什么这次为什么不能用了,是因为你想一想啊,咱们这张表你是不是既没有分区,是不是没有分区,不是既没有就是没有分区,没有分区,那前边咱们那个之所以可以insertright呢?呃,咱们那个insertright咱的目的是什么呢?咱们的目的啊,是为了使前面那个任务呢,他实现那个就是诶密等性,什么叫幂等,就说的高大上了,什么叫密等性,就是那个任务可以重复执行。任务可以重复执行,那OK,也就是说我诶不管重试几次,那我最终的结果都是一样的,对不对,那你加上那个insert o right是不是就能够实现那个所谓的一个密等性啊就可以诶重复执行最终结果是一样的,最终结果是一样的,但是咱们前面都用的insert or right,呃,那这边咱们这次为什么不能用这个insert or write了呢?大这块也看到了,我们这个表是不是也没有分区,也没有什么的呀,对不对,就是一张普通的表,那如果说你这边还用insert or write的话,那你相当于是不是每天都会把前边所有的数据给给他,诶干掉啊,然后只在这个表里边是不是保留一天的数据啊。
17:13
对不对,那这个历史数据它就没了,所以说这边呢,我们大家得注意了,不能再用insert right了,应该用insert into insert into啊int into,然后后边加一个什么呢?加一个table。加上ADS_UV count加上这个表就行了,那这样咱们把这个SQL执行完,那数据呢,就会呃写到这张表里,而且写的时候咱是一天往这里边写一条,一天写一条。OK,那这个再往下呢,这个circle需要怎么做,大家应该都知道了吧,我是不是需要给它封装到像咱们之前写的那些脚本一样的一个shell脚本当中啊,对吧?然后这里边大家写的时候要注意,呃,表明前面呢需要加上库名,表明前面需要加库名,然后这个具体的日期呢,需要用这个Dollar do date替换一下。
18:00
这是这个第一个需求就完事了啊OK,那现在把这个视频咱录一下啊。
我来说两句