00:00
来,咱们现在一起来看一下会员主题当中的第一个会员信信息啊。先把建表语句粘出来啊,哎,放在这个位置。啊,那这个江面鱼当中呢,这个所有的字段就是我们接下来要求的字段啊,其实呃,虽然这个字段很多,但是这里边咱们绝大多数的这个呃,类似的需求是不是都做过了呀,咱们做过活跃设备新增设备啊对不对,那活跃会员新增会员是不是一样的道理啊啊没有啥区别啊,没啥区别,我在这块呢,咱们就不再呃这个呃不再去分析我从DWD怎么求DWS层怎么求了,这个应该都知道吧,跟前面几乎是一样的,只不过就是咱们换了一个表,对吧?我们这些设备主题你就得找设备相关的表,那user的主题咱们就得找user相关的表,对吧?啊这就行了,那所以说最终咱这个求新增,求活跃,我们都从哪求就行,DWT是就可以啊,从DWT就行啊。好,那现在我们来分析分析啊。我们要想求这个,呃,活跃会员,当日的活跃会员这个应该过滤条件应该是什么呀?哎,Last login date last对吧,末斯登录时间等于今天新增呢啊,首次登陆时间等于今天啊,那新增消费会员呢。
01:15
消费是不是得支付啊,对吧?哎,咱是不是有一个首次支付时间等于今天就行了,总付费的呢,你就找一下那个一共有多少人付个费啊,那总会员呢,求一下一共有多少人是不是就可以了,那所以说其实咱们大致可以分析下来啊,这里边所有的字段我都可以从DWT层求啊,那所以说直接select啊来select,然后呢,让DWT层的user topic,那这张表当中具体有什么字段,可能同学已经忘了是吧?那现在我们去看一下啊,DWT层的user topic找到这张表。在这来咱看一下这张表当中这个字段啊,首先得明确主键是谁,主键user ID没错吧,首次登录日期,末次登录日期,然后后边呢,Log看这个是啥呀。
02:02
当日。活跃次数吧,登录登登日的登录次数对不对啊,然后往后走,这是啥啊,最近30天这个活跃天数,这个是天数啊诶诶,这个是当日的活跃不是吧?这DWT层的吧,这应该是累积登录天数吧,咱们去看一下吧,啊这个来点一下log in count对不对?Log in count应该是累计登录天数吧啊这DWT不是DS对不对,那累计登录天数,这是最最近三日登的天数啊,那咱们还再看这个接着走。那后边这儿呢,有一个首次下单时间,诶末次下单时间order看的是什么。累积下单次数,累计下单金额,最近30天下单次数,最近30天下单金额没错吧,啊,然后你接着往后走,后边儿是不是还有什么,诶这是啥。这是呃,这样相当于是这个首次支付时间,这是末子支付时间,对不对,OK,那就这么多,后边咱们就不再一个一个去看了啊好,那接下来我们回到这个我们的这个需求这个位置啊。
03:09
来来,那就这个字段大咱们大致熟悉了对吧,那接下来我们就开始求上面的字段了啊,那第一个当天的活跃会员数,那这个应该怎么求啊。是不是应该是只要末次活跃时间等于今天,那我我就我我就记为已否记为零对吧,这个咱们就不再说,先where后count,咱们就直接用谁就行,Sum if对吧,咱们直接把这几个值统一求出来,直接sum if啊sum if来,那如果说诶login data啊啥last吧啊等于啊这个2020。杠零六杠幺四,那我就记个一,否则就记个零,诶好,已经拿到了当天的,诶新那个活跃了,那接下来拿当天新增,当天新增只需要把这个last改成first是不是就行了啊,这个比较简单啊,那再往下走,下边呢是新增消费会员,那应该就是首次支付时间等于今天,那我就记一,否则就记零呗,否则这个呢,也是最上面修改一下就行了,那这个改成login date。
04:13
这人date应该是应该是谁payment date吧,Payment date first好,那新增消费会员拿到,那接下来下一个啊,下一个该谁了?下一个是总付费会员对不对,这个咱们怎么拿总付费会员。总会会员是不是就找那些付过费的会员就可以了呀,对不对,那谁是付过费了呀,这个怎么判断呀。诶对,咱们是有一个累积支付次数的吧,那在哪在后边应该有一个payment count对吧?啊应该是有一个payment count啊,那payment count,只要payment count它大于零,那就说明它是一个,诶支付过的对不对,那当然这块咱们有点问题啊,是不是看这个字段。就能看出来都有毛病啊,有啥毛病啊,看的应该是big in的对不对,那都没小点呢,这应该是咋回事大家猜。
05:05
肯定是建表的时候那个字段类型咱们写的有问题对不对,咱们可以去看一下检查一下啊,那我们找一下DW哪一层T层的啊,User直接点就行对吧?啊直接点点来之后我们看看啊,是不是这块有问题。呃,果然有问题吧,对不对,这看咱们写的是DEC对不对,这个正常应该是啥呀?应该是big in啊那这这个咱们已经这样了,咱们先不改了吧,啊,那接着咱们接着往下走啊,就往下走,那也就是说只要它那个嗯,累积的支付次数大于零,那就说明它是一个付过费的对不对,那所以这样呢,咱们怎么做呀,咱是不是得找那些大于零的有几个呀,那也就是咱们这直接sum if啊,如果说我那个payment count大于零,那我就记个一,否则就记零,那你得的是不是就是总付费会员数啊,就是这样的啊,那接下来下一个,下一个是总会员数,同数应该写啥看得清啊这个问题这个不用说什么吧,就是总会员,因为什么,因为咱们前面讲过啊,那这张表呢,我们要求它里边是有全量的U字的啊,所以说第一次咱们往里边导的时候,是不是进行过一个初始化呀,对不对,初始化之后呢,我就有全量了啊,那我这个直接看得清得的,就是总会员数好,那搞定了,把这几个值求出来,剩下的事就简单了啊,是不是各。
06:22
总比值啊,那先看会员活跃率,那当天的日活比总会员数,日活先拿到哪个是日活第一个吧,啊就是日活CTRLC那CTRLV那注意看得清。搞定那下一个。下看谁呢?该payment的优呃,应该是会呃会员付费率对吧?那应该是总付费,会员数比总会员数吧,这是总付费。会员RLCTRLV拿后来让他出一个count清啊,完事啊,那最后一个呢,是啥是会员签度,会员签度呢,我们要求大家求的是那个什么呀,是当天的新增比当天的活跃吧,新增先拿这个,这是新增啊,CTRLCCTRLV,那第一个是活跃那。
07:07
Z。这个呢,除以一下这个CTRL喂啊搞定了啊,那这个需求到目前呢,我们相当于就完成了,这时候我们可以把这个先简单的跑一下啊来走看一下这个结果。嗯。这个需求呢,这个呃,这个SQ没有报错,没有报错说明语法肯定是没问题的啊,咱们等着看一下最终结果啊。呃,这个任务还没开始跑啊,还在转圈啊。啊,已经提交上去了啊。咱们稍微等一下,呃,那当然这里边咱们得改一下啊,得得得得补一个字段,咱这儿不是还差一个统计日期啊,对吧,这统计日期怎么办?直接给他写死就行是吧?啊直接写个日期就行啊,哎,这个这么慢呢。啊,已经开始跑了啊,咱们稍微等会儿吧。
08:00
50%了啊,跑完了啊,你看这个结果是不是出来了啊,结果就出来了啊好,那这里边儿呢,我们会发现,呃,你像这种地方啊。这个地方,呃,这个因为它这个百分比是一对吧,这是因为什么呀,因为咱们可能我今天的新增都是今天的这个活跃,所以说有那什么会员,呃,那个新鲜度啥的是这个是一对吧,100%,这是正常的啊,那像这种地方啊,像这个他这块是不是有那个啥呀,有这个好几位小数吧,对不对,而而且有时候是不是也可能出现那种不能整除的现象啊,那对于那种现象,咱需不需要给他保留两位小数呢?因为我最终是一个百分比对吧,用没有保留啊。用不用啊。啊,也就这是不是咱保留两位小数,可以用round的函数round,然后呢,把这个拿进来,然后写个二,是不是就保留两位小数了呀,那咱这有必要吗。其实你得注意啊。如果我只是查询,只是查询,你这要想保留两位小数,那你必须得加一个round,能理解吧,在咱们这最终要干啥,是不是要把这个数据得。
09:03
写到这张表里对不对,你往这张表写的时候呢,人家会根据你这个建表语句当中这个字段的类型去帮你进行这个转化,咱们这字类型不是两位小数,所以说最终它给你写进来之后,会自动帮你保留两位小数,能理解吧,所以说你这要加上inser的话呢,里边就不用再处理了啊,这个我们大家注意一下就行,那比如说咱们这写into吧啊,Into哪张表下面那个ADS层的啊,咱们这的是user topic就完事了啊,就这样的啊,当然这里边它这个是不是有一个大黄块啊,对吧,黄块提示我们,你这个字段实际上是不足的,你可以指一下啊,你从别的地方你指过来。出来之后呢,你看这是不是会有提示啊,那我是不是我期望是九个字段,那你只给了我八个呀,对吧,少给了一个,那再给他补一个就行了啊来我们2020杠零六杠幺四啊,那这个逗号就行了,这个完成啊很简单这个需求啊,来视频录一下。
我来说两句