00:00
那上一节呢,我们已经把第一个需求的核心指标都计算出来了,没错吧,那接下来我们把这个circle执行一下,看一下最终的结果。好,结果已经出来了啊,大家注意观察啊,那这儿是不是只有一行数据啊?啊,为啥只有一行啊,那大家要知道啊,这是针对于最近一日的全部访客的一个统一的汇总结果啊,咱们是没有考虑维度字段的,所以这个结果呢,就只有一行啊那接下来呢,我们就需要把维度字段考虑进去了啊,那咱的维度字段还记得有啥吗?啊,在这儿是不是有一个easy new,有一个recent days,有一个channel啊,对吧?那我们应该怎样把这些维度字段考虑进去呢?啊,让咱之前是分析过的对不对啊,咱们怎么说呢?我们说只需要在这个统一的汇总结果的基础之上,是不是加上一个分组就可以了,那具体应该如何操作呢?啊,那接下来我给大家演示一下啊,咱先从China这个字段开始,因为它相对来说更简单一些啊好,因为我们最终要统计的是每个拆的各项指标,那所以说在最终的结果当中,是不是得包含拆字段呀,对吧?啊,那当然呢,这儿要享有拆,那下边咱是必须得分组才可以。
01:09
没错吧,那所以在这儿我们需要加上一个格Y啊,好,然后大家要注意了啊,这儿我们要想对拆进行分组啊,那T3这个子查询当中是不是就得包含拆字段才行啊,没错吧,那所以说我们往上走找到T3子查询啊,也就在这儿呢,咱就需要有拆字段才可以。没错吧,然后大家又要注意了啊,T3里边要想有China,那T2里边是不是也得有China啊,没错吧,因为T3的字段是从T2这个子查询里边选择出来的啊,那也是这个位置,咱是不是得包含拆啊?然后还有一个小细节一定要注意一下啊,我们从T2子查询里边去选择字段的时候,是不是进行了分组啊?没错吧,那所以说诶这儿咱要想选channel,那这个位置是不是得按照分组才行,那所以在这儿呢,我们也需要加上一个channel字段啊,OK,那我们继续往下进行啊,那T2里边要想包含channel啊,那咱的T1里边是不是也得有channel才行,那所以在这儿呢,我们也需要把这个channel选择出来。
02:06
啊,OK,那到现在为止,咱这个拆字段呢,才算是加上了啊,那现在我们把这个circle执行一下,看一下最终的结果是什么样的回车。好,大家注意观察一下啊,现在咱们得到的结果是不是就是诶每个channel的各项指标了,那OK channel这个维度咱就处理完了,那接下来呢,我们继续处理下一个维度啊,那下一个呢,来咱处理e new这个字段啊,OK,一字new咱们还记得是啥吧?是不是新老访客的标识啊,没错吧?那我们也按照同样的方式去处理一下啊,那首先我们期望在最终的结果当中包含一字六字段,没错吧?啊,那这儿要想包含一字六字段,那下边咱是不是同样需要对其进行分组的处理?没错吧,但是这儿咱们要想对一字new分组,那T3这个子查询当中是不是就得包含一字new字段呀,没错吧,那接下来我们找到T3的子查询啊,也就是这个位置对吧?在这儿呢,我们给它加上一个一字六。
03:01
那当然T3里边要想包含一字字段,那就得保证哪也得有啊,是不是T2里边也得有E字new字段呀,对吧?也是在这个位置呢,我们也需要把一字new给它选择出来啊,那当然还是要注意这个小细节啊,咱们从T2这个子查询里边选择字段的时候呢,是不是进行了分组啊,对吧?那在这儿呢,我们需要把这个一字六诶也进行分组处理啊,好了,那我们继续往进行啊,那T2里边要想包含E字news是,那T1里边是不是就得有E字new啊。没错吧,那所以在这儿呢,我们是不是也要选择一个一字拗出来,哎,那这样一来,咱这个一字拗字段是不是也加上来呀?啊,但是我要告诉大家的是啊,咱们这样去选择一字new字段啊,是不正确的,那为什么不正确啊,那具体又是哪不正确呢?那其实就是这不正确。啊,这儿为啥不正确啊,我给大家解释一下啊,大家来思考一下,呃,我们最终期望的这个结果当中的一字牛字段,它的含义是什么呀?它的含义是新老访客的标识,没错吧?那这个一字new字段是不是直接从d WD pagelo这张表里边选择出来的呀?对吧?那我们回忆一下日志当中的一字new字段的含义是什么呀?
04:10
那它的含义是是否是应用安装之后的首日啊,那所以这个呢,是不能作为新老访客的标识的,那所以在这儿直接选择e new字段作为新老访客的标识是不对的啊,那咱们把它删除啊,那新老访客的标识到底应该如何得到呢?哎,我们应该根据DWT层的访客主题表当中的首次访问日期去计算新老访客的标识啊,那所以在这呢,我们需要再去写一个子查询啊,那这个子查询来我们在这儿写啊,来括号。来把这个格式化一下啊。然我们在里边写select,应该是from哪张表DWT层的,诶,Visit啊,这个topic,然后呢,我们需要获取最新的一天的分区,应该是DT等于2020杠零六杠幺四,里边呢,我们只需要选择mid加上首次访日期就可以了,诶,Visit,诶,Date,诶,First啊,OK,那咱们拿它之后呢,需要起一个别名啊,那这个我们起名为呃,T0吧啊,T0,然后T1跟T0的需要做一个left join left join一下啊OK,那它的连接条件应该是啥呢?诶,应该是on t一点啊,Mid等于T0.mid好,那这两个子查群呢,就召唤到一起了啊,那接下来呢,我们就可以根据这个首次访问日期去计算新老访馈的标识了,那具体怎么算,我们往上走啊,来,来到这个位置啊,那这个一字new呢,就作为别名就可以了,咱们应该怎么算,是不是应该是if一下啊,如果啊,首次访问日期等于今天2020杠零六杠幺四。
05:46
那这时候我们是不是就认定它是一个今天的新增访客呀,对吧?那咱们就返回一个一,否则的话呢,诶咱就返回一个零,OK,那这个呢,才是我们去获取新老访客标识的正确的途径,好了,那这个新老访客的标识咱就拿到了啊,但是拿到之后呢,大家一定要注意一下啊,由于咱下边是不是有joinone了呀,对吧?那那两个子查询当中是不是都有m mid,那所以说我们在选择的时候呢,是不是得标明一下这几个mid的来源呀,对吧?那咱这应该来自于谁?是不是应该是来自于T1这个子查学,那在样呢,我们需要给他写一个T一点,那同理下边也要写啊,那这个呢也是T一点啊,后边呢也是T一点。
06:27
OK,那现在咱这个新老访客的标识就算是加上了啊,那接下来我们把这个circle呢,给他执行一下,诶走。好了,那这就是我们当前得到的这个最终结果啊,这里边包含了两个维度,一个呢是新老访客,那一个呢是诶渠道,那最后我们来看一下recent date这个字段应该如何处理啊,那recent date这个字段的含义是什么呀?啊是不是最近N天呀,对吧?啊在这儿呢,他其实想让我们求的是最近一天的下列指标,最近七天的下列指标,以及最近30天的下列指标。
07:03
咱现在咱下边这个circle是不是只计算了最近一天的指标啊,你看啊,咱现在是不是只读取了DWD配置log这张表最近一天的分区。没错吧,那所以实际上咱现在只计算了最近一天的指标啊,OK,那现在我要想去计算最近七天,最近30天的指标,那咱应该怎么算呀。啊,在这呢,其实我们有两种思路啊,那我们先看第一种思路啊,第一种思路其实比较简单,那怎么做呀?啊,我们只需要把这个circle写三份对吧,然后呢,把另外两份的这个过滤条件改一下就行了。没错吧,那这个是最近一日,那最近七天的呢,我们需要把这个条件改成DT大于等于当前日期减六,那最近30呢?啊,就得改成DT大于等于当前日期减29,没错吧?啊,那改完之后,我们再把这三个circle呢,给它是不是U念到一起就可以了。对吧?那这样一来我们就能够分别统计出来最近一日的诶上述指标,最近七日的上述指标,以及最近三十日的上述指标了,OK,那这就是我们处理recent this这个字段的第一种思路啊,当然这种思路呢,写起来比较麻烦,因为这个circle呢,我们需要写三份,对吧?那再一个呢,改起来也比较麻烦,为啥?你想啊,如果说我们的逻辑有问题,那只要一改是不是就得改三份啊,对吧?所以在这呢,我们并没有采用这种思路啊,那到底是怎么做的呢?那接下来我给大家说明一下啊,在这儿我们实际上是这样去做的啊,首先我们先使用一个UDTF函数啊,UDTF大家都知道是不是有一进多出的一个特点啊,对吧?啊,我们先使用一个UDTF将原始数据扩张为三倍。
08:40
啊,就这23倍,然后呢,我们再通过一个过滤条件啊,分别去过滤最近一天,最近七天和最近30天的数据。啊,那完了之后呢,我们现在是不是得到了三份数据啊,对吧,那这一份只有最近一天的,那这一份呢是最近七天的,这一份呢是最近30天的啊之后呢,我们再根据这个最近N天,也就是一七三十进行分组统计,那这样一来呢,我们就能够通过一个S啊一下把这个最近七三十日的统计值都算出来了。
09:12
那这个到底是如何实现的呢?那接下来呢,我先通过一个小例子给大家演示一下啊好,我们往下走,在这儿呢,我们先写一个小circle啊,先select from,哪上边DWD配置log,然后后边呢,来一个letter explorelo de explode,然后里边我给它放一个什么呢?给它放一个也就是一个数组,对吧?那我们构造一个什么样的数组呢?哎,一个只有三个元素的数组,这三个元素呢,分别是一七,诶,还有30。啊,那完之后呢,后边需要写一个TP,然后再来一个recent,诶,Death OK,那这个recent death是不是就是explode这个UDTF函数输出来的那一列呀,对吧?啊,当然这一列的值应该是啥呀?是不是就是数组当中的每个元素就是一七三十,只不过呢,是竖着来的对吧?啊,这个大家要搞清楚,那其实我们现在就已经把这张表的数据扩张为三份了,对吧?啊,而且每一份是不是里边都会有一个recent date字段呀?
10:08
而且每一份的recent days的值都是不一样的,对吧?那有一份肯定是一,那另一份呢,是不是应该是七,那再有一份呢,应该是30,没错吧,OK,那咱把这个数据查一下,看一下数据到底什么样的,咱来星就可以来我们执行一下。OK,大家注意观察啊,那在最后边是不是多了一个re this这样一列啊,没错吧?啊,而且这个值是不是就是一七三十,一七三十啊,OK,然后大家一定要注意观察一下啊,这三条数据前面的值实际上是一模一样的,对吧?那也就是说咱的数据呢,实际上已经扩张了诶,三倍了啊,这个大家要搞清楚啊,好,那接下来我们继续往进行啊,数据已经扩张为三倍了,那我们的目的呢,不是只把这个数据扩张为三倍对吧?那咱还要干啥呀?咱是不是还需要通过一个过滤条件,分别去过滤最近一天,最近七天和最近30天的值啊,对吧?啊,那这个过滤条件应该怎么写呀。
11:02
其实很简单,怎么写就行了,来大家看我操作一下啊,那在这呢,我们是不是只需要加上一个where dt大于等于大于等于什么,然后把谁放进去呢?把当天日期放进去零六杠幺四,然后后边呢,咱给它减一个recent days,当然呢,还需要再加一个一,诶那这样一来呢,我们就能够分别过滤出来最近一天,最近七天和最近30天的数据了。那我们来看一下它是如何过滤出来的啊,那首先我们这儿是不是已经有三份数据了,对吧?那这三份数据当中的recent days呢,分别是一七三十对吧?那我们先看第一份数据啊,那第一份数据的过滤条件应该是啊,是不是就应应该是DT大于等于2020杠零六杠幺四减一个一再加一个一相当于不加不减对吧?那是不是就是DT大于等于2020杠零六杠幺四,那这样一来我们是不是能够拿到最近一天的数据啊?没错吧,那接下来我们继续往前行,我们再来看re this等于七的这份数据啊,那相当于是不是就是DT大于等于data I的2020杠零零杠幺四,然后减七加一是不是就减六啊对吧?是不是也就是DT大于等于六天前的日期?
12:13
没错吧,那我们得到的是不是就是最近七天的数据?那同样的道理,Recent days等于30这份数据呢,我们就能拿到最近30天的数据了,OK,那到这一步啊,后边的操作实际上就容易了啊,那后边咱是不是只需要按照recent days这个字段进行分组统计就可以了啊,那我们就能够分别得到最近一天、最近七天和最近30天的统计值了啊,OK,那接下来我们把上面的circle完善一下。那上面呢,也要做和这儿一样的处理,所以在这儿呢,咱们把它复制一下啊,来CTRLC,那我们把上面改一下,改哪个位置,是不是改这就可以了,没错吧,来CTRLV啊好,那现在呢,我们就相当于已经把原始数据扩张为了三倍,并且呢,已经分别选择出来了最近一天的数据,最近七天的数据和最近30天的数据了啊好,那现在呢,我们就可以按照recent date这个字段进行分组统计了啊好,那首先我们需要现在诶上边选择出来咱所学的这个字段对吧?那它在哪个位置啊,咱们去看一下啊,它是不是在一字new这个字段下边啊,对吧?所以说我们给它放在这个位置来CTRV,好,那当然咱要想在这儿选择recent death,那下边是不是就得对它进行分组啊,对吧?OK,我们往下走,那在这儿呢,我们需要在GU by里边加上一个recent death啊,那同样道理啊,我们在这儿要想对其进行分组,就得保证T3这个子查询里边得有reason森的电子字段才行,对吧?那所以说我们找到T3这个子查询,那就是在这个位置呢,我们需要加。
13:40
上一个recent death啊,没错吧?啊,那当然T3里边要想有recent death,那就得保证T2里边得有recent death,没错吧?那他在这儿呢,我们也需要选择一个recent death出来啊,然后要注意的是啊,我们从T2里边选择数据的时候呢,是不是进行了分组操作,那所在这儿呢,咱是不是也得加上一个recent date啊?
14:01
没错吧,啊好,我们继续啊,那T2里边要想有recent days,那我们就得保障是不是T1或者是T0里边有recent days啊对吧?那在这儿呢,当然是T1,对不对,在这儿呢,我们需要选择一个recent days出来好了,那到目前为止呢,这个recent days我们就算加上了啊,但是这个加完之后,我们有些地方啊需要去改一下,那具体是哪儿需要改呢?啊,那第一个地方诶就是诶开窗函数当中的分区啊,由于咱的数据呢,由原来的一份扩张为了三份,对吧?那所以说我们在分区的时候呢,也得把三份数据区分开才可以。没错吧,不能混在一起啊,那所以说我们这个分区的字段里边呢,需要加上一个recent this,而然后das啊,这个呢,一定要注意一下啊,啊那除了这个地方呢,还有一个地方咱也得一下啊,哪块呢,就是这啊,就是这个新老访客的标识啊,那为什么这个要改,因为这个判断逻辑呢,针对的只有最近一日的数据,对吧?啊但是现在呢,我们既有最近一日的,也有最近七日的,也有最近三十日的数据,那所以说这块这个判断条件呢,咱就得改一下,那具体改成什么呀。
15:15
哎,其实很简单,是不是应该改成if visit date first?大于等于啊,然后呢,把它CTRLX放进来,应该是data塔I的对吧?然后把当前日期放进去,后面呢,来一个减recent this完之后呢,再加一个一啊,那这样一来呢,这个逻辑就能同时适用于最近一日,最近七日和最近三十日了,好了,那到目前为止呢,咱这个recent days字段才算是真正的处理完了啊好了,那也就是到现在呢,我们所有的维度字段哎,就全部都加上了啊,那现在我们只剩下最后一个字段了,也就这个统计日期啊,统计日期呢,在这儿呢,我们直接写一个常量值就可以,2020杠零六杠幺四啊好,那到目前为止呢,我们所需的所有字段就全部拿到了啊,那现在我们就只剩下最后一件事了,就是把这个结果insert到咱这个目标表当中,没错吧?那接下来我们写一下啊,那按照我们之前的写法呢,是不是应该是insert right加table对吧,后边呢,来一个ADS visit states,没错吧?然后大家要注意一下啊,这张表我们是不是没有分区。
16:17
对吧?那所以后边是不是就不需要写文句了呀?那所以咱这个音字的语句呢,到这儿就结束了,但是呢,大家还得再跟我们思考一个问题啊,那如果我们的音字的语句就这样写的话啊,那这张表当中的历史数据呢,每天都会被覆盖掉啊,也就是说这张表当中永远只会保留最近一天的结果啊,这是为啥呢?很简单,因为我们现在这个inser o呢,它覆盖的是全表,而不是一个分区了,没错吧?啊,那所以显然在这儿呢,我们不能用insert or right,那应该用啥呀?是不是应该用insert into啊,但是用insert into呢,又会带来一个新的问题。什么问题呢?诶,就是小文件的问题,那大家想一想啊,那我们每天去执行insert into这个语句的时候呢,是不是都是一个新的任务啊,对吧?啊,那每天的这个新任务是不是都会产生一个新的文件,没错吧,那所以说日积月累我们的小文件呢,就会越来越多啊,那所以用音色into也不行啊。
17:15
那咱这儿到底应该怎么办呢?诶,最终呢,咱这是这样去做的啊,那首先啊,每天在我们网这张表当中插入新的数据之前啊,我们先把这张表当中原来的数据给它查出来啊,之后呢,再把查出来的历史数据与今天新增的数据进行一个union的操作啊,之后呢,再把数据给它insert overri到这张表当中啊,那这样一来的话呢,我们就能够有效的避免小文件的问题了啊啊那现在我们把这个改一下,也是在这呢,我们需要用insert override对吧?然后下边呢,我们要再接上一个selectt星from as visit states,后面呢再来一个union就可以了,OK,那这样一来的话呢,我们就能够解决这两个问题了啊,那既能保存原来的历史数据,那又能诶避免小文件的产生啊好了,那到目前为止呢,咱的第一个需求就全部完成了。
我来说两句