00:00
好,那接下来我们把用户统计这个需求实现一下啊,好,我们还是先不考虑recent date这个字段啊,我们现在也是只统计最近一天的下列指标啊好,那接下来呢,我们把这几个指标呢逐个分析一下啊,假定最近一天的日期就是2020年6月14号啊好,我们先看第一个指标啊,第一个呢是新注册用户数啊,那这个应该怎么求?啊,咱们怎么去找新注册用户啊,其实很简单啊,诶想一想我们是不是知道每一个用户的首次活跃日期对吧?也就是他的log in date first对吧?啊,那是不是只要他的首次活跃日期等于2020年6月14号,那它就是一个6月14号的新注册用户,没错吧?哎,这个比较简单啊,那接下来我们继续看下一个啊,下一个呢,是新增下单用户数啊,其实这个跟新注册用数的思路是不是应该是一样的呀?我们是不是也知道每个用户的首次下单日期啊,对吧?那只要首次下单日期等于2020年6月14号,那他就是一个这一天的新增下单用户。
01:02
没错吧,啊OK,我们继续下了啊,下一个呢是下单总金额,这个就更简单了啊,我们是不是只要把6月14号这一天的所有人的下单金额加在一起,得到的就是下单总金额呀。再往下呢,是下单用户数啊,那我们应该怎样去寻找下单用户呢?啊,这个其实也比较简单对吧,是不是只要他在最近一天的下单次数或者是下单金额是大于零的,那是不是就能表明他是一个下单用户啊。没错吧,好,我们继续往下进行啊,最后一个是诶未下单用户,那这个相对来说也比较简单,没错吧,那是不是只要他在最近一天的下单次数或者是下单金额等于零,那是不是就表明他没有下单呀?没错吧,那当然这块还有另外一个要求啊,就是说咱们得是活跃用户当中的未下单用户,那活跃用户我们应该怎样去处理呢?而且很简单,是不是只要他的末次活跃日期等于最近一天的日期,也就是2020年6月14号,那是不是就表明他在这一天活跃了呀?
02:06
没错吧,OK,那这就是我们上述这些指标的大致的一个思路啊好,那接下来呢,我们把这个circle去实现一下,首先select from English表是不是应该是DWT层的user topic呀,没错吧,因为咱现在呢是用户统计对不对啊好,那完之后呢,我们需要获取它最新一天的分区,也就是2020杠零六杠幺四这个分区啊,没错吧?啊,那接下来呢,我们就可以开始统计每个指标了啊,现在我们先看第一个就是新注册用户数,这个应该怎么求。这个按照我们刚才的分析,是不是可以根据首次活跃日期去进行判断呀,没错吧,所以在这呢,我们应该用啥,是不是用summer if就可以,没错吧,应该是summer if if里边写啥啊,Login date first,如果它等于2020年6月14号,那我就记一个一,否则我就记一个零,那这样一来,我们得到的是不是就是6月14号的新注册用户数啊?没错吧,那同样的道理啊,那下边一个是啥?是新增下单用户数啊,那我们这儿是不是只需要把首次活跃日期改成首次下单日起,对吧?那也是改成older date first啊,那这样一来我们就得到了新增下单用户数了,我们再继续往下进行啊,那下一个呢,是下单总金额,然后大家要注意一下啊,咱现在统计的是不是只有最近一天的值啊,没错吧,也就是这个下单总金额呢,值是最近一天的下单总金额啊,至于累计值,最近七天,最近30天,我们后续再补充啊,所以在这儿呢,我们是不是直接把每个用户的最近一日的下单金额给它加在一起就可以了,对吧?应该是order last ED,哎,Final amount,注意啊,咱订单金额呢,有两个,一个呢是original amount,原价金额,还有一个呢,是final amount最终金额对吧,在这呢,我们需要统计的是最终金额。
03:51
啊,那这样一来,咱是不是就得到了最近一日的下单总金额呀。啊,好,我们继续往下走啊,下一个呢是啥?是下单用户数,那这个应该怎么统计?这个其实也比较简单,咱是不是直接来一个summer if就可以,没错吧?那if的判断条件应该是啥?是不是最近一日下单金额大于零,那我就记一个一,否则我就记一个零,没错吧?啊,Order last ED final amount,如果它大于零,那我就记个一,否则我就记个零啊,当然了,在这呢,写下单次数大于零也是可以的。
04:22
没错吧,好,那我们继续往下进行啊,来看最后一个,最后一个是啥是未下单用户数,那当然它跟下单用户数是不是正好是相对的呀,对吧?啊,那所以在这呢,我们也用sum if就可以了啊,来个逗号来CTRLD啊,然后这里边咱们需要改一下啊,那我们要想统计下单用户数,那咱的过滤条件是不是应该是最近一日的下单金额大于零,对吧?那未下单用户数呢,是不是应该是等于零,或者写小于等于零也是可以的,对吧?啊OK,那我们继续往下进行啊,那这里边还有一个特殊的要求,什么要求呢?他说这个未下单用户数呢,指的是活跃用户中的未下单用户。没错吧,所以在这儿呢,咱还得再加上一个过滤条件啊,加上一个什么条件呢?是不是就是末测活跃日期等于2020年6月14号啊,对吧?那咱们写一下log in date应该是last对吧?它等于2020杠零六杠幺四啊,那这个过滤条件过滤出来的,那是不是就是6月14号的活跃用户当中的未下单用户啊。
05:22
没错吧,啊好,那这样一来的话呢,我们就把所有的指标都计算完毕了啊,当然了,诶,我们只计算了最近一日的值啊OK,那接下来呢,我们就把最近七天最近三天的值也给它算一下来,我们还是先分析一下具体的思路啊,在这我们同样是使用UDTF函数explo的啊,把原来的数据呢,给它扩张为三份啊,但是这三份数据啊,跟原来是有点区别的,那我原来的三份数据是不是分别拿到了一个分区,哎,七个分区还有30个分区啊。没错吧,但是呃,咱这儿的这三份数据啊,他们都是一样的,那都是啥呢?都是DWT层user topic这张表的6月14号的分。
06:04
那这三份数据不同的是什么呢?大家注意观察一下啊,我们在计算这些指标的时候,是不是需要用到最近N天的下单金额呀?没错吧?啊,那这三份数据不同的就是第一份儿数据,我们获取的是最近一天的下单金额啊,第二份呢,获取的是最近七天的下单金额啊,而第三份呢,获取的是最近30天的下单金额。啊,好,那我们把这三份数据获取到之后呢,就可以根据recent date进行分组统计了啊好,那接下来我把这个思路实现一下,那首先我们先获取咱所需的这三份数据啊,来我们select,然后呢,From d wt层的user topic,然后where dt等于2020杠零六杠幺四对吧?那接下来呢,我们需要使用explore的函数将数据扩张为三份对吧?那在这我们来一个let review u ex prode explorelo的,那里边呢,我们同样需要放上一个AR瑞,对吧?那里边呢,还是一,然后七啊,然后30,那后边需要写一个TP is this。
07:09
那接下来我们再选择所需要的几个字段啊,那首先把recent days先选出来,Re recent days,好,那接下来下一个啊,那我们在这儿呢,还会用到是不是log in date first家对吧?那把它也拿过来啊,没错吧,那这个login date first每一份拿的都是同样的值,所以这个不需要区分啊好,接下来下一个older data first也是一样的啊来,CTRLV拿过来,那我们再继续,那再往下呢,咱是不是还需要拿到一个诶,Login data last末次登录日期啊,对吧?嗯,把它也也提前拿过来啊好,我们再继续往下进行,再往下呢,咱需要的是不是就是这个最近N天的下单金额了呀,对吧,大家一定要注意,那这个值呢,我们是不是就得区分一下了,没错吧,那是不是一份得拿最近一天的,一份拿最近七天的,还有一份呢,拿最近30天的。那咱这儿应该怎么去写这个circle啊,啊,应该用K问对吧?啊,OK,再写一下啊来case问C啊,然后呢,先写上一个end啊,里边呢,我们再分别去写啊问问什么?问recent death等于一,哎,那咱就返回啥是不是返回order last ED final amount,没错吧,那如果等于七,哎,那咱返回最近七天等于30,就返回最近30天的对吧?那就是CTRLDCTRLD,咱改一下就行啊来把这个一啊改成七是不是就完事了啊,那同理,把下面这个一呢改成30是不是就完事了?来大家改一下来30啊好,那这就是我们拿到的最近N天的下单金额,来在这儿呢,我们给它起个别名啊来这个叫做older final amon amount。
08:42
没错吧,好,那现在呢,我们就拿到了咱所需的所有字段了,那这三份数据呢,我们也就拿到了,没错吧,这三份里边呢,有一份儿是最近一天的诶统计值,那有一份呢,是最近七天的统计值,还有一份呢,是最近30天的统计值啊那接下来咱就可以干啥了啊,那接下来咱是不是就可以按照recent this进行分组统计了。
09:03
没错吧,啊,那所以现在呢,我们给它来一个子查询,来CTRLX来个括号来走CTRLV,那我们把它往前推一下啊,这个起起一个别名叫做T,来我们select from t。然后按照recent this进行分组,对吧?来recent this啊,OK,那接下来呢,我们就可以把上面这些统计值是不是拿下来了呀,对吧?来CTRLC放在这个位置啊,当然拿下来之后呢,有些地方我们需要去改一下,对吧?那首先要改的是就这几个爆红的地方啊,对吧?那原来我们是不是只针对于最近一天的数据进行了相应的统计,那现在我们数据有三份对吧?咱分别有一七三十,那所以这里边咱们就不能再写older last e final amount应该写谁呀?是不是应该写older final?没错吧,来,咱们把这个别名拿过来,CTRLC放在这个位置啊,CTRLV这个一样,CTRLV,那这个呢也是CTRLV,然后呢,还有一个地方要改一下啊,就是我们summer if当中的这个日期判断条件啊,是不是就不再适用了呀,对吧,为啥?因为咱们之前写的是不是只针对的是最近一天的统计啊,没错吧,那现在我们是不是需要考虑到最近一天,最近七天和最近30天呀,没错吧,所以咱这个诶login data first等于2020杠零零杠四应该改成啥?这不改成大于等于当前日期减re加一啊对吧?没错吧,那在这呢,咱得改一下啊,诶我们CTRLX把它放进来,这应该改成诶这个大于等于是不是data塔I的,没错吧?诶下回见data I的,然后呢,把当前日期放进去,后边呢,减一个this,再加一个一。
10:36
啊,那同样呢,这儿是不是也得改,那这儿是不是也得改呀,对吧,那也就是说这个时间我们需要用到好多次,那所以在这儿呢,咱们为了方便可以把它提到下边来啊,我们再选择一个作用就可以了,来把它放在这儿,Control v对吧?那咱给它起一个命名叫做recent days ago。哎,就是N天前,相当于是没错吧,那接下来上边咱是不是就可以直接使用这个别名了呀,没错吧,来,CTRLC咱拿过来CTRLV,没错吧?那同样道理,这个是不是也得改成大于等于re this ago啊,啊,没错吧,那这儿呢,是不是也是一样的?诶,改成大于等于re this ago OK,那现在呢,我们就能够同时去计算最近一天,最近七天和最近30天的统计值了啊好,那现在呢,我们把recent this也给它选出来啊。
11:23
好,那接下来我们把这个circle执行一下,看一下最终的效果。来大家看一下啊,这里边儿咱是不是就已经出来了,最近一天,最近七天和最近30天的统计值啊,那突然想起来啊,那这个需求除了让我们计算最近一天,最近七天和最近30天的这些指标,是不是还有一个累计值啊,没错吧,我们回回去看一下建表语句啊,来往上翻。这个recent death里边是不是还有一个零啊,对吧?啊,咱没有考虑这个零啊,那所以接下来呢,我们还得再改一改这个circle啊好,我们看一下都需要改哪个地方啊,咱们从头开始改,那首先我们要改的就是这个数组,对吧?咱们需要在数组里边再加上一个零元素。
12:03
没错吧,啊,那这要加的话,那哪还得加,是不是这个位置也得加一下,原来是三份,咱们只需要考虑三种情况,那现在是不是还得考虑一个零啊,对吧,都在这儿呢,我们再给它CTRLC来给它在这也加一个CTRLV,那如果是零的话呢,那我们就应该返回啥,是不是应该返回从开始至今的累计值,也就是这个older final amount。没错吧,啊,那除了这哪还得改一下呀,是不是这儿啊,啊,大家还记得这是啥吧?这是不是咱们计算出来了N天之前的日期啊,对吧?啊,那我们现在要想计算累积值,那这个N天前的日期是不是应该是一个极小值才对?没错吧,啊,那所以说这个公式呢,那就不适用于这个累积值了,为啥你想啊,你把零带进来,带来之后得到的日期是啥?是2020杠零六杠幺五对吧,而不是一个我们所期望的极小值,那所以在这呢,咱们特殊处理一下就行啊,我们来一个if就可以来if if什么呢?If re death等于零啊,那我就返回一个极效值,比如说1970杠零幺杠零幺啊,那否则的话呢,哎,咱们再按照这个公式去进行计算。
13:12
啊,没错吧,哎,那这样一来的话呢,这个circle咱就改好了啊,那最后我们再把这个circle执行一下来走。大家来看,那这次我们得到的结果呢,就是诶,包含了最近一天,最近七天,最近30天以及从开始至今的累计值了啊好了,那这个需求我们到现在为止,哎,就完成了大部分了啊啊好,那我们再把剩余的字段补充一下啊,这还差一个DT对不对?来我们给他写一下,这其实就写一个常量值就可以来2020杠零六杠,诶幺四啊,没错吧,那最后呢,我们再给它补上一个ins色的语句是不是就可以了?来咱insertright table ADS应该是哪张表,User total对吧?那后边呢,同样是先select,然后呢星啊,From ADS user total,后面呢,再来一个union啊,那到目前为止,他这个需求就全部完成了。
我来说两句