00:01
我们看这个会员主题第一个需求这个会员信息的统计,呃,这个会员信息统计呢,里边涉及到了这个,呃,会员的这个好几个指标是吧,那咱们这边也没什么可说的,就照这个这个这个我们需要的这个字段呢,我们需要什么值我们就拿什么值,呃就OK了啊,那现在咱们把它打开放到这个简表语句当中啊,CTRLV来我们一起看一下这边,呃,我们要统计的有一个什么呢?有一个呃日期咱不说了啊,有一个这个活跃会员数。这个活跃会员数跟我们前面讲的那个所谓的活跃设备数,其实统计起来那个思路是一样的啊,就根据什么,根据根据最后一次登录时间去统计就行了,对吧,只不过这个会员主题的信息,我应该从会员主题课表里边去取。是这样的啊呃,OK,那现现在我们简单的回顾一下这个会员主题课表DWT。呃,User这张表,那把这个界面语句拿下来啊,CTRLC。F3,咱定在这个位置,呃,然后呢,大家一起来看一看这个表里边的字段,我们大致的来熟悉一下啊,这个会员主题宽表里边呢,它里边应该放着什么呀?会员主题官联里边是不是应该放着跟会员相关的的这个一些事实啊,应该放着跟会员相关的一些事实,跟会员相关的一些事实啊,那这个呃,跟会员相关的事实应该有哪些呢?会员首先登录可能跟跟会员有关系,对吧?那还有还有什么,还有这个支付呃,包括下单跟这个我们的会员是有关系的,对吧?那咱们这张款表里边呢,其实就有这些东西,但其实啊,咱们会员呢,跟还跟其他的好几个实时都有关系,比如什么呢?比如那个加购物车。
01:43
对吧,比如这个收藏对吧,那这还还有什么,还有比如说跟这个退款对吧,这些其实都跟咱们的会员有关系,都跟会员有关系,那实际上在这个会员主题款表当中呢,我们也应该包含,其余的那些事实也应该包含啊,在这边呢,咱们没有加上,咱没有加上啊,但其实这里边是应该包含那些事实的。
02:05
是这样的啊,那包含那些事实,我们应该有哪些字段呢?那基本上也是跟这个类似的啊,那咱们去做这种呃,主题宽表的时候,我们那个事实定下来之后,那事实里边通常会有哪些字段呢?一般会有两个时间,首次末次,首次末次啊,其实这两个时间看似简单,那其实我们能用这两个时间做很多事,你看我们上午去算什么心脏留存留失等等等等,是不是大多都利用的是这俩时间啊,那这俩时间这样是很有用的啊,那有这个时间,其次有什么呢?会有一个累积的一个统计,会有一个累积的统计啊,就累积从头到尾累积的,还会有一个什么呢?还有一个近期的。还有一个近期的啊,这是为什么,其实这样就是我们,呃,在公司里边去统计指标的时候呢,我们要么就是统计当日的,统计当日,要么呢,就是统计这个最近多少多少天的,可能有最近30天,可能有最近60天,可能有最近15天,对吧?也就是说其实这个宽表呢,你可能在公司里边你见到呢,这个最近可能这里边同时就会有最近一周,最近七天,然后什么最近15天,最近30天,最近60天,可能同时就会有这些,诶这个最近一段时间的这这些指标。
03:15
是这样,那我们这呢,只给他放了一个最近三十日的啊,最三十日的,那有这个当天的,有最近多少天的,还有什么呢?还有一些这个累计的,累计的基基本上我们统计的时候呢,我们这个时间范围差不多都是这样的,当天最近一段时间或者是累积的,那所以说在宽表当中呢,我们就会有这样的一些值,那方便我们后续去进行统计,其实就是这样的啊,OK,那我们简单的又介绍了一下这个主题优用户主题观面啊,就会员主题观面,那OK,现在我们回到咱这个需求啊。来会员主题信息统计,会员会员信息统计,我们看第一个第一个字段,我们要的是这个活跃会员数,当然是day user当日的活跃会员数,那咱们开始选,这时候呢,大家可以可能又会想到了,怎么去求这个事呢?哎,我看到这个值,我首先我就怎么办啊,我根据这个最近。
04:07
就是最后一次登录时间,用它把这个今天登录的用户过滤出来,用while去过滤,过滤完之后呢,怎么办?进行这个count,得到这个值,那下边这个值呢,跟是不是用首次登录时间去过滤啊,那又一个where,然后一个子查询。然后再看的对不对,有可能同学可能会这么做,那你这么做的话,你这些这么多值,你会有一大堆的这个子查询,然后进行join,那其实这样并不太好,所以这边咱还是怎么做呢?还是啊,把这些所有的值全部给它用什么呀,用sum if去求some if啊,咱们也不用去过滤了,咱们就一个S搞定。那现在我们开始去写这个东西啊,Select select一点,这有一个V。Sum一副组合太强大了啊,这个sum if呢,确实是就是在我们做这种类似的这种需求的时候,其实非常非常的常用,非常非常常用啊,来,那这个DMT完了之后呢,我们选上边需要的字段,首先第一个DTDT这里边呢,我们先把当天日期拿上啊,零三杠幺零,然后逗号下边这个活跃会员数,那现在我们给它写上同样是sum if。
05:19
如果说呃,我那个是今天回来的,就记一个一,否则就记零,那login date last,如果说等于今天2020杠零三杠幺零,那我就记一个一,否则我就记一个零,那这样我就拿到了今日的活跃会员数,那下边呃,这个新增会员数,新增会员数跟它是不是应该是类似的呀?CTRLV我摘下来啊,那这边我们应该改一下,谁应该把这个login data last改成first吧,对吧,首次登录是今天,那就是今天呢新增用户。好,那下边呢,是这个新增消费会员数,新增消费会员数啊,那现在呢,我们来一个some还是if啊。If来括号,那这里边新增消费会员数,咱们应该去找谁了?你应该去找跟消费相关的字段对不对,那消费这这所谓的消费啊,支付才叫消费啊,那也就是说首次下单时间是今天,那你就是今天的这个新付费用户数对吧?那OK,那some some if,然后呢,来一个,呃,Payment time啊,Payment payment date first。
06:23
等于今天2020杠零三杠幺零,如果是今天,那记个一,否则记个零好了,那这个完事之后呢,咱们再往下,再往下该谁了呀,再往下跟这该这个总付费会员数了,对不对?那总付费会员数什么叫总付费,就是只要你付过费我就记一个,一没付过费我就记零对不对?那怎么去找这个付过费的呢?怎么去找付过费的,其实好几种方案,好几种方案,那你是不是可以根据这个时间去找。对吧,你这个首次支付时间或者是末次时间,不为闹不为闹是不是就付货款对吧?这是可以的,或者是用什么这个累计支付次数,诶诶不好意思啊,给点掉了,呃,我们再回回去拿一下吧,呃,诶这个下边from我还写错了吧,应该是从哪张表啊,该是user topic才对啊,那我们去找再拿一下啊,CTRLC。
07:11
CTRLV这个双击了一下,把它点没了,再给它粘下来啊。然后CTRLCF3定在这个位置,那现在我们再回过去啊。删掉,好,那现在再回过来,我们刚才说到了这个,呃,总的付费用户数对吧,去找付费用户的时候呢,一是可以根据这个支付时间,如果不为now,就说你支付过,还可以根据什么,根据累计支付次数或累计支付金额去统计,是不是也是可以的呀,就是你不为零,诶,那我就说明你是支付过的,这个其实还是比较容易的啊,那同样还是some if呗,那就some。然后if。呃,我这边就用这个累计支付次数去统计了啊,那就是说payment count,如果payment count大于零,那就说明你是支付过,我就记一,否则我就记零,那总付费用户数已经拿到了,好了,那下边我们看该谁了啊,该这个总会员数了,总会员数是不就容更容易了呀,咱这张表是什么表,这张表是一张用户的全量表,相当于是啊,就是这里边每一行数据代表一个用户,所有用户在这里边呢,都有一行数据跟它对应,那我们要求总会员数,那就直接count星,是不是就是总会员数啊,那就count星呗。
08:26
好,现在已经拿到了总会员数了,拿到这几个值,剩下的事我们就就是干啥,就是做除法了,对吧?那接下来咱们把这个除法去做一下啊OK呃,那这个做除法的时候呢,我这块有个有个小点先跟大家说一下啊,就有的同学啊,他那个他他怎么样,他他那那个感觉啊,诶我这个下边是不是要用到上面的值啊对不对,那有同学可能就会在这块去写起别名,比如写一个A,那下边呢这呢写一个B,诶不对啊,这边呢写一个B,这个下边呢直接用别名了,A除以B,比如说诶他这么去做,大家说这么做行吗。
09:00
这么做是肯定不对的啊,就是有可能有个别同学会犯这种错误,肯定不对的,大家要记住一个原则,你select select字段只能出自于哪,只能出自于你下边的。对吧,就是你from的那张表里,或者是那个子查询当中有这个字段,你才能select能理解吧,那你下边有什么A有B吗?没有没有,你这就不能选这个,大家要注意啊,OK,那我把这俩去掉啊来。买so应也是不行的啊,来给它去掉啊好,那这个玩意之后呢,咱们往下走往下走上边我们看这个会员呃活跃会员呃,会员活跃率啊,咱们看这个呃活跃会员率,咱们当初定义是怎么定义的来着。怎么定义来着,就是说当日的活跃会员数去比上总会员数,那就是数所谓的活跃会员率啊,那当日哪个是当日,这个是当日的活跃会员数啊,CTRLC,呃,拿下来CTRLV放在这个位置,然后呢,去除一个总会员数,那就是除以CR形式就行了啊。好,这个咱们搞定,然后下一个下一个是会员付费率,会员付费率,那会员付费率呢,我们指的是诶这个总付费用户数除以总会员数,那就是会员付费率,好我们拿一下啊总付费呃,会员数应该是谁?这个是总付费会员数CTRLC,然后放到这个位置,然后下面呢,同样是让他去比一下这个CR星。
10:25
好逗号,那接下来剩下最后一个会员新鲜度,会员新鲜度咱们怎么定义的来着,是不是新增会员数占当日活跃会员数的这个比值啊,是不是啊,那那这边呢,咱们就给它写上就行了啊来sum来一个,呃,我这还是粘一下上面的吧,这个应该是用我们今天新增的,新增就是它。对吧,CTRLC,然后放到这个位置。CTRLV,呃,那后边呢,比上这个我们当天的活跃,那也就是这个。CTRLZ,然后放在这CTRLV,好这个逗号,最后一个逗号去掉啊行,那这一步呢,咱们就完成了,那到现在为止,咱们就已经呃,把这个整个的这个所谓的会员信息统计就统计完了。
11:11
就是这些啊,你看啊,有了这个宽表,就是咱们DWT层这个宽表啊,你看咱们去求这些值的时候,实际上是非常容易的,那如果说没有这样宽表,你求这里面的任何一个值都没有这么容易,都没有这么容易啊,就是相对来说要复杂的比较多,要复杂多了,而且你想一下咱们这个这个SQ的效率呢,要比你一个一个的去从DWS层去拿这个值,那个效率要高很多,要高很多啊好了,那现在咱们把这个视频先录一下啊。
我来说两句