00:00
那好了各位,那接下来呢,我们就正式开始熟悉咱们这个DWS层的这个汇总表,OK啊,那这些汇总表呢,其实它最终是怎么来的呢?肯定是根据我们呃,ADS的需求一个一个的反推出来的,对吧?OK,那这个反推的思路在哪呢?其实咱们文档当中是不是有一个这个完整的脑图啊,对吧?这个脑图当中其实就对应了我们后续的每一个这个指标,完之后呢?诶,我都已经做完了这个分析了,它呃,依赖的派生指标是谁?那它依赖的发生指标是谁,咱们都列在这儿了,完之后所有的派生指标呢,我是不是也已经放在这样的一个这个电子表格当中了呀,对吧,就在这儿了,然后实际上我们DWS层所设计出来的这一大堆的表,最终其实都是参考谁,是不是都是参考这个东西来的呀,对吧?啊是这样的,那一会儿呢,我我也对照着这个去看啊,就比如说诶,我们把这几个派生指标的表给他看完了,那在这儿呢,咱们给它加一个标注啊,加完标注之后,最终你会发现我们所有的派生指标呢,会呃,都会被涵盖,都会被覆盖,这一点咱们稍微的理解一下。
01:00
就行了啊好,那接下来呢,我们就一个表一个表开始看齐,咱们现在先看呃,我们文档当中的第一个表,来咱们看一下第一个表是什么啊,第一个表呢,是交易与用户商品力度订单最近一日汇总表,诶看到这个标题之后,那大家现在对它这个感觉熟悉不熟悉,应该熟悉吧,这个是不是就是我们前年咱们自己举的那个例子,对吧,所对应的这个汇总表吧,我们自己设计出来的,是不是最终就是一个用户商品力度的,没问题吧,其实就是那个啊,跟那个其实很像啊,其实大家现在自己可以琢磨琢磨啊,就是我们DWS层这个表名啊,它的这个命名规范还是呃比较比较全的,比较详细的,是这样的,其实我们现在应该能够做到这样的一个程度啊,什么程度,就是你只要看到这个表明啊,对吧,你就得能够反应过来,它里边大概存着什么样的数据,大家自己琢磨琢磨,你看你能反应过来不,现在。能不能反应过来,那可能这个咱们之前自己做过这张表,所以里边存啥大家都知道了,对吧,那比我们现在随便挑一个,比如就以它为例,对吧?你看到这个表面能不能大概猜出这里边存的是什么数据,你看啊DWS交易域对不对,然后后边呢,是UU,这是其实那个对应的力度吧,U力度对不对?那这个是什么呢?是O,是下单业务过程,这个ED是最近一天对不对?那你说这张表里它会存什么东西?
02:21
你想啊,他不就是说是把下单那张实时表最近一天的数据聚合到了用户力度嘛,对吧,你想你把明细聚合到用户力度,你能得到什么样的数据,是不是就是每个人或者每个用户对不对,在这最近一天里边,他下单的总次数,商品的总件数,下单的总金额,是不是就是这些东西啊,对不对,也就是大家现在应该是能够反应过来的,就是你只要看到表名,就得知道我这张表里大概存了什么样的数据,诶你能做到这一点,那说明这个诶是合格的,那么之后我们后来将来对吧,我们做需求的时候,我们从这些表里取数,对吧,你只要一看到表名,你就能判断出来这个我到底需不需要它,对不对,其实应该是这样的一个过程啊好了,那接下来呢,咱们还回到我们的第一张表上面来,那现在我们把这张表的建表语句给它拿出来,我们看一下这个最完整的介面语句,因为我们前天举的那个小例子,它我们最终设计出来表其实并不完整啊,咱们来看一下完整的到底是啥样的,来,CTRLC咱们拿出来。
03:19
好了,那现在呢,我们一起把这个表的建表数据给它过一下啊,首先表明这个不再多说了,DWS交易域用户商品力度,这个跟我们设计出来的是一模一样的啊,不再多说,接下来往下看,呃,那下边呢,这个呃表我们就来明确一下表的这个结构啊,行列分区,那每行数据在这张表当中我们指来的应该是什么呢?呃,这个我们要用就是呃人类的语言去描述的话呢,这个应该怎么说呢?应该是一行数据代表的是一个用户购买一个SKU的什么次数,间次数,件数和金额这些统计对不对,是一个用户对应一个商品啊,这一点要搞清楚,这是行啊。那接下来呢,我们再来看字段,字段呢咱们就分两部分去看,一部分呢,我们对应的是那个维度,还有一部分的对应的是不是那个统计值啊,对吧?来先看维度,维度里边最核心的两个就是用户加SQID,那这俩呢,是不是就用来表示我们这个表的力度啊,这一点大要搞清楚,那接着往下走,那下面的这些其实都是什么。
04:18
都是我们退化进来的一些商品维度的属性啊,对不对,那这个我不去做这个退化行不行呢?完全可以,你就是你此处要是不做退化,那将来我们从这张表里取数去做需求的时候,你大不了就是用它再去跟维度表照应一下,是不是就完事了,对吧?所以这个维维度的退化呢,咱们是可选的啊,可退可不退啊,这个理解一下,行,接下来继续往下走,下边这个就不再多说了,这是不是就是我们那几个常规的统计值啊,对吧?下来的次数,件数,还有各种金额,只不过他这儿是不是要比我们前年设计那个更全一点啊,更全一点了,对吧?把所有的度量值都给它放在这儿了,行了,那截止到现在,那这张表的这个呃列咱基本上也就明确了,最后明确一下它的分区规划,它的分区是什么样的来着,是按天分区吧,所有的一列表是不是都是按天分区,然后每天的分区里放的是什么,是就是当天的明细的汇总结果啊,就是这样的一个逻辑,这一点要搞清楚啊,好,就是这种分区规划什么的,咱们一定要清楚,因为你这个分区规要不清楚,你将来写三的时候对不对,你总不能说每次都拿全表。
05:19
你肯定是要过滤的,对吧,你要分区规划不清楚,你都不知道过滤几个分区啊,所以这块儿呢,大家一定要搞清楚啊,行了,表结构跟咱们前面设计的差不多啊,这个不再多说了,那接下来呢,我们就来看一下这张本的数据装载咱应该怎么做,呃,按照我们前天的那个做法呢,我们这个数上的之前怎么做的,是不是直接找到,找到谁找到与之对应的那个明细表,然后呢,拿比如说一天的数据,然后进行聚合,聚合到咱们这个表的目标力度,对吧?啊完了之后呢,把数据一装载,那就完事了,咱们现在是不是只是那么做的,对吧?但实际上大家得考虑一个问题,什么问题呢?就是我们再往这张表里去装载数据的时候,咱第一天是不是可能需要去处理一下历史数据啊。
06:01
大家回忆一下,那我们的就是与之对应的那个明细时表,对吧?我们在做首日装载的时候,咱们是不是做过一个历史数据的初始化的操作,没问题吧,也就是我们在做第一天的装载的时候,那与之对应的这个明细表,它里边其实已经有很多的历史分区了,对吧?那么之后咱们你说第一天往这里装载,咱们要不要去对那些历史分区的数据进行一下处理的,要不要处理啊,其实是需要处理的啊,你之前呢,咱们是没有做这个处理啊,咱们之前相当于只做了一个什么操作呢?相当于是做了一个每日的一个处理,对,咱们只是拿了当天的数据,处理完之后放在了诶当天的,可气到这其实应该是对应的一个每日的一个处理,那我们这儿呢,需要再加一个首日的这个历史数据的处理,OK啊,所以在这儿呢,其实我们得分一个首日装载的circle和每日装载circle,那咱接下来呢,就得把这个首日装载的circle给它补一下啊来接下来咱就分析分析这个首日的circle咱应该怎么写,这个视频我就不单独录了啊,咱们就一块给它来讲完就行了,来咱们分析分析。
07:01
它的首日应该怎么做,来数据装载,OK,那现在呢,我们刚才说了,需要区分一个首日和每日,首日咱假定当前的日期呢,就是6月14号啊来看一看这个咱们应该怎么做。既然是要初始化历史数据,对吧,所以我们首日的时候我必须得怎么样,我得把全部的数据都拿过来吧,对不对?所以说应该先来一个c that from dwd,应该是那个交易域对吧?下过线啊,诶下过线应该是T交易域,然后找到那个OD天有INC这张表啊,对吧?那全部的数据我需要过滤分区吗?不需要是不是就是拿全表对不对?OK,拿到之后咱接下来怎么做?现在怎么做,现在正常按照我们之前的逻辑,是不是应该是把里边我们所需要的字段查出来之后呢,再去照谁,再去照商品维度对不对?之后再按照我们这个维度进行分组就完事了,对吧?之前大是这么做的啊啊,其实在,但是在这儿呢,咱们可以先干啥呢?我可以先去做分组,再去做这也行,为什么?因为你注意观察咱们现在要聚合到什么力度,是用户加SKU的力度,对吧?那你说在这个明细表里边,咱们有没有用户ID,有没有SKU都有对不对?所以说这俩分子字段我在这张试表里本身就有,所以我就可以先干啥呢?我就可以先做聚合,再去做join。
08:21
对不对,那这样一来是不是咱们照的数据量要少一点对吧?啊,这一点稍微的理解一下啊,所以在这儿呢,咱们这回是先聚合再照就行了啊,之前我们聚合到什么力度,聚合到那个用户品牌力度的时候,我们是先照再分组,对吧?那个没办法,为啥呢?因为你要是直接先去分组的话,你的明细表里也没有那个品牌啊,对吧?啊,所以你必须得先照上维度,拿到品牌再去做分组啊,但是这块儿跟那儿是不一样的啊,这回我们可以先分组,我们直接按照谁呢?按照用户对不对,然后再加上谁呢?再加上这个SKU进行这个分组,但是光按他俩分组行不行?行不行,你光按它俩分组不行,为什么不行呢?你要知道这张表目前里边是不是有很多的历史数据啊,对吧?那我们在做聚合的时候,我们要求怎么做这种一地的表,我们要求怎么做,要求是不是我一天的明细我聚合到一起啊,对吧?没问题吧,那你这里边好多天的明细,你这么直接聚合,是不是把所有隶数据都聚合在一起了,对不?这个显然不是咱们想要的,那我应该怎么做?哎,我再加上一个日期是不是就行了,对不对,我再加上一个,比如说分区,OK,那这样一来的话,我就能实现一个这样的效果,就是说我会把同一天对不对,同一个人购买同一个商品的记录是不是聚合在一起啊,对吧,那我就可以把同一天的聚在一起了,诶,咱们把天给它分开好了,那这个聚合完之后,我们选一下我们所需的数据啊,首先第一个DT咱选出来,那再来一个呢UID选出来,呃,OK,那再往下呢,我们再选一个这个呃,SKUID啊选出来,那之后剩下的我们看看怎么做啊,那截止到现在呢,这些字段咱们都拿到了啊。
09:58
那再往下这块儿,这些咱们在明细表里是拿不到的,这个必须得去照应一下商品维度表一会儿对吧?好,那就来继续往下走,那这块咱们怎么处理一下,这块是不是就现在就可以开始算了,没问题吧?哎,那现在就可以可以可以可以开始算了啊,比如说我要想拿这个呃,Count,那直接怎么做就行了,直接来一个抗星,这就是o count下单次数,但number呢,是不是直接萨姆求和,来一个o s k number就行了,对吧?诶,来一个number OK,那继续往下走,下边是不是就对各种金额进行求和呀,对吧?那这个呢,我就不再一个写了,咱们稍微节省一段时间,好,那假定我现在已经把这里边所有东西都已经查出来了,那接下来咱们怎么做呢?接下来是不是就是join一下谁就行了,Join一下那个商品维度就行了,对吧?所以接下来呢,我们需要再去写一个子查询,就是从商品维度表里边去拿我们需要的这些维度属性,这儿你需要谁拿谁就完事了,好都拿出来之后,那这俩子大学写完,他俩是不是得做一个join呀,对吧?好上完之后,接下来是不是就得到了最终的结果了,得到最终结果之后,咱是不是需要把这些数据。
10:58
写到我们最终的这个异地的汇总表里啊,对吧,好,那现在关键问题来了,写到咱们这个汇总表哪个分区里边。
11:07
你知道吗?应该写的哪个分区,写的哪个分区,还是原来咱们那句话啊,你写的哪个分区,这个取决于两点,第一点就是你这儿拿到的数据都有啥,第二一点就是你这个目标表的分区规划是什么样的,对吧?咱这个拿到的数据当前是什么?是历史上很多天的汇总结果,对不对啊,是这样的,那我这个分区表的规划是什么样的呢?我的分区规划什么样的?一天一个分区,每天的分区是不是存放当天的汇总结果呀,对吧?你哪天的明细就要放到哪天的分区啊,应该是这个道理,对吧?所以说在这儿我们需要怎么办呢?得来一个动态分区吧,对吧?我这里边有多天的汇总结果,那我这个一个分区只能放一天的,你不就得来一个动态分区,把数据写完多个分区吗?好,动态分区,关键问题来了,你用谁作为那个分区的值呢?用谁呀?是不是就用这个DT就行了,没问题吧?OK,你这个DT是不是对应的是我们最开始对应的是哪?是我们明细表的这个DT吧,对应的是明表的分区,我们前面就分析过明细表的分区跟汇总表异地表当中,汇总表的分区是不是上下是对应的呀,对吧?哪些明细我就放到哪天的这个汇总表里啊,这个大家要理解一下啊,所以在这呢,我们最终用这个DT的值作为动态分区字段的值,那就完事了。OK,所以说最终拿那个circle的,诶,怎么去写,大家应该基本上已经知道怎么写了,我就不再重复了,那现在我们把文档当中的这个完整的词拿出来,咱们看一眼就行了,来,我给它拿过来。
12:28
呃,我们放在这CTRLCCTRL,好,各位同学,我们现在一起把这个最终的circle过一下啊,呃,首先我们看第一个子查询CTR啊,这有一个select,完了之后呢,是不是符上我们这个明细表啊,对吧,直接按照这仨字的分组,跟刚才我们所说的是一样的,然后上面把这些该选的选出来,然后接着往下走,下边是不是就是从我们这个商品维度表里边去取数啊,对不对,这个大家要稍微的理解一下啊等等啊。那这个呢,我们其实就不用去做这个驱虫了,因为我们现在统计的就是人购买SKU的这个次数了,对吧?你想一想啊,就是假如说在同一个订单里边,一个人购买了一个呃,就是一个SKU,比如说买了两呃呃就是呃两次吧,对不对?在一个订单咱们说两次不好说,咱们应该说说什么,说两件吧,对吧,你一个人购买一个S科,假如买两件,你说他在明细表里会有两行记录吗?他不会的,他应该是一行,然后后边会有一个什么呢?是不是乘二啊对吧?所以说这儿你不需要对那个OID进行去重,就是在同一个订单里边不会出现同一个人同一个SKU2行记录,对不对,这一点要不要搞清楚。
13:39
呃,你要统计品牌的话,你看你怎么定义吧啊,你要统一品牌的话,可能你看具体要求啊,他可能比如说在同一个订单里面买一个品牌的两个SKL,我可能会算一次,我也可能会按两次算,这个看具体要求就行了,嗯,看具体就行了啊,是这样的啊,好了,那这个我们进来继续往下进行啊。呃,然后这俩紫扎询其实很简单,但是这块呢,其实大家需要注意到一个问题,这个是我之前没跟大家说过的啊,哪个问题呢?就是在这儿他俩去做关联的时候,你说有没有可能会出现问题啊。
14:12
上下关联的时候,你说有没有可能会出现问题?障碍关联有没有可能出问题,其实可能会出一点问题,出一点什么问题呢?你想想啊,咱们这儿拿到的这个相爱是历史上很多天的事实,对不对?而我们这儿拿到这个维度是哪一天的维度?是14号的维度没问题吧,那所以你相当于是用一个什么,用一个14号的维度状态去关联很多的历史上发生的这个下单记录,对吧?那所以说你这个维度咱们前面讲过,它不是静态的,它是会不断的变化的,对吧?所以有可能会出现,比如说我之前十号的这个商品信息跟14号的商品信息其实根本就不是一样的,它变了对不对?所以你这个站上之后,这个结果其实可能不准确,因为它现在其实相当于已经违背了我们那个原则,什么原则来着,哪天的事实是不是知道哪天的维度啊,对吧,是这样,所以这其实你知道我会有问题,但我这儿没办法,我只能这么做,为什么只能这么做呢?因为想啊,对于维度而言,我们业务系统里边会保存维度的历史状态吗?不会对吧,我们14号是不是才开始去做这个数据的同步,对吧?所以说我们能拿到的这个维度的最早的状态,其实就是14号的状态了,他13号的,12号的,11号的,我们在业务系统里面根本就没有,对吧,咱们是拿不到的,所以说没办法,咱只能是这么去做啊,这块呢,大家稍微的意识到这个问题就行了,那当然了。
15:31
那这个我们到了第二天,比如说到了15号的时候,还会出现这个问题吗?就不会了,对吧,你15号的时候,你拿15万的事实照上15号的维度,这个没有任何问题,这一点稍微的理解一下啊,行了,那现在呢,他俩照完上之后,我们再往上走,上面呢,你就按照具体的要求选择我们所需的字段,那就完事了,最终咱们使用一个动态分区,把数据呢写往哪呢?写往这张表就可以了,这块呢我们写一个DT,这儿呢也写一个DT,但是这块大家稍微注意,这块的DT跟这块的DT是一个含义吗?
16:00
这个可能看着一样,但不是一个含义啊,这个实际上指的是什么呢?就是咱们那个表当中的是不是那个动态分区作用的那个那个那个字段名啊,对吧?那这儿是什么?OK,那我们这儿是不是就得写啥呀?对吧?哎,这个一定要理解一下,OK,那这个字段实际上对应的是什么呢?对应的是我们这选出来这个东西对吧?那比如说举个例子,我这儿给它起一个边名,我这儿写一个D呢,我这儿是不是写D就完事了对吧?哎,也就是这儿跟这儿没有必然的联系,对吧?这个大家应该是能够想明白了啊,这个我就不再多说了,好了,那截止现在我们就已经完成了这张表的数据状栏了,呃,完成之后这儿为什么会爆黄呢?呃,这个倒不是说咱们这块有问题,是因为我们前面啊,就是我们这个当前的这个文件当中,是不是前面有一个跟他同名的建表语句啊,对吧?嗯,这是咱们之前自己设计也是这个表名了,对吧?那所以他其实现在呢,这个表它读的实际上是最前面的那个,那所以说在这儿呢,是不是咱这个字段跟这是不对应的呀,对吧?所以它是会爆黄,所以在这咱们这样啊,我把前面咱们所写的东西全部都给它注掉,咱们都给它注了啊。
17:01
呃,我们找一下啊,来先找到这个刚刚咱们那个开头的位置应该是在哪,我们找一下啊,应该是在这个位置,对吧?然后从这儿咱们到上边全部给它除掉,通通除掉,这是3.0的,我们再找一下前面的2.0,然后再找一下前面的1.0,诶这个全部注掉,嗯,好了,那都做掉之后,咱们下边就不会再有问题了,好了,那这张表的首日账改咱就算是完成了,那其实首日完成之后,每日咱就不用再自己写了,对吧,每日就跟我们前面写的是一样的,来咱们直接拿出来看一眼就行了啊这个相对就比较简单了,CTRLC,来咱看一下它最终的这个每日装载的S考语句在让我写一下吧,哎,这个是每日,呃,每日,诶这个每日啊好,那现在咱继续往下看啊,看一下他每日怎么做的,首先呢,他也是先从明细表当中取了是不是一天的这个数据啊,这次只取一天就行了,然后这回分组还用按照日期分吗?不用,因为这没历史数据了,只有一天的对吧,所以直接按照用户加SK分组就行了,那这样一来。
18:01
我们就能得到在这一天里边每个人购买每个商品的次数,件数和金额,那得到之后再去照这个相应的维度属性,最终把数据写到这张表十五二分区即可,哎,这个就完事了,相对比较简单啊,行了,那这个完成之后,我把视频停一下啊。
我来说两句