00:00
来吧,我们先看呃这个活动主题,先看活动主题啊,那活动主题呢,那肯定对应的就是我们,呃,这个a Di那个DWD层,哎,维度模型里边的活动维度表对吧?啊活动维度表好,那现在我们来看一看啊,就是这个每日活动统计表DW层嘛,啊因为按天统计嘛,按天汇总,那这张表当中,哎,它的结构是什么样的啊,这张表的字段有哪些,哎,CRC先粘出来。来,我放在这儿,来,咱们一起看一下啊。那这张表呢,我们首先先来明确一下它的力度啊,就是它的一行数据,它所指来的是啥啊,这个数数据指的是啥呀?银行数据,那肯定是一个活动当天的汇总行为呗,哎,那什么叫做一个活动的行为呢?那用户的行为咱们很好理解对吧,那活动的行为指的是啥呀。活动的行为,那应该是是不是得去找一下跟活动维度表示相关的事实啊,对吧?啊,跟活动维度表相关的事实应该就是谁呀,就是订单呗,对不对啊,那其实这个所谓的活动的行为呢,就是有哪些订单参与了这个活动,是不应该是这个意思啊,对吧?啊所以说咱们来看一下在这张表当中,我们这个具体的字段啊,上边从ID开始到c time这些字段呢,大家应该是比较熟悉的啊,这这些字段都来自于哪呢?
01:29
其实都是活动维度表当中的维度字段吧,对不对,咱们去看一下啊,CTRLF啊,DWD层啊,来找到DMA,哎,VI activity in for。CTRLCF3咱们给它盯在这儿啊,然后呢,咱们往下翻,哎,往下翻找到我们那个活动啊,这个DWS层的这个每日汇总表,来咱们对比一下啊,那这几个字段是不是就是直接来自于咱们的这个活动维度表啊,啊,也就是他把活动维度表当中维度字段是不是直接给它写在了宽表当中,那写在二的一个好处就是我们后续去进行统计分析的时候呢,这个就不用再join了,对吧?是这样的,也就是这一张表就足够我们使用了啊,是出于这层考虑啊。
02:17
来,那接下来看下边,下边才是重点啊,下边重点看这。这是我们统计的几个值啊,或者是汇总的几个值,首先呢,第一个是display count,叫做曝光次数啊曝光次数,其实这个曝光是不是也是跟我们活动相关的一个事实啊,啊,也是相关的一个事实啊啊那就往下走,下边是下单相关的统计啊,下单次数,下单金额在这指的是什么呀?只是参与该活动并且下单的这个次数是多少,下单的金额是多少,对吧?啊然后下边呢。诶,支付次数,支付金额,那这个应该指的是啥呀?这个指的是参与该活动下单。
03:00
并且支付的订单啊有多少个,然后呢,金额是多少,是不是应该是这个道理啊,啊,这一层意思咱们需要搞清楚啊好,那这就是这张表里边的这个字段,因为跟活动相关的事实相对来说比较少,那所以说在这儿呢,就这么几个度量值嘛,啊很简单啊,所以说那接下来咱们就来分析一下,那这张表当中啊,咱们应该怎么去统计啊,这些字段哎,都应该来自于哪些表,来分析一下吧,首先曝光次数它应该来自于哪?它来自于哪儿啊?这是不是肯定来自于志,来自于日志啊,哪个日志曝光日志吧,肯定是曝光日志啊,那接下来下边这个。下单次数下链接,他应该来自于哪。啊,它来自于哪儿啊?它很显然应该来自于订单事时表,订单时表当中咱们有一个这样的字段,还记得吗?来,我们找到订单时表啊,金毛dwd fact order in for,你点开这是咱们那个事实表当中的所有字段吧,啊,然后这这张表当中是不是有一个activity tad啊,那这张表我是不是根可以根据这个ID去判断哪个活动,哪个订单参与了活动啊,以及他参与的是哪个活动,对吧,这个是能看出来的啊好,那所以这块呢,可以通过这张表求出来这俩字段,哎,那它呢。
04:23
这个支付怎么求啊,就是参与活动并且支付的订单次数和金额分别是多少,这个怎么求?这个理论上咱们首先应该想到的是支付时表,对不对?那支付时表当中我们看看它有没有跟活动有关联呢?找到DW def payment info啊这张表看有没有。是不是好像是没有啊,因为咱们前面也说过,从业务上角,从业务这个角度去考虑呢,我们支付跟活动没关系的,对吧,支付直接跟订单挂钩嘛,啊那所以这块呢,诶没有加上啊没有,那没有的话怎么办。
05:01
没有的话,咱是不是可以这样去做,从订单表当中把那些支付了的订单过滤出来,然后再去统计,哎,参与每个活动的订单有多少个,是不是应该是这样去做呀?诶咱们之前有过类似的操作对吧?啊,所以这块呢,大家搞清楚就可以了啊,那这就是我们这几个字段的这个来源分析,OK啊,那这个circle咱们看一下啊,我们就不带手消了啊,把它粘出来,好大家来看看能不能看懂啊,哎,CTRLC。来,咱们放在这儿。首先先是子查询呗,对吧,先子查询,那这里边儿呢,我们写了几个查询,写了一共有这样的,呃三个,那咱们先看第一个啊,第一个来看一下。TM pop啊TM pop,那TM popp在这指的是这个order和payment,是不是就是咱们上边那个下单和支付的统计啊,下单支付统计,来看看他怎么写的啊,哎,他是不是把下单和支付的统计这四个字段一次性的是不是求出来了呀,对不对,你看他怎么求的啊,来我们看一下。
06:09
首先从哪张表里求呢?Select from。DWD以后这是不是跟刚才咱们说的是一致的呀,咱们刚才说了下单和支付是不是都从这张表里求啊,完了之后呢,往下看。哎,这个日期啊,过滤了几天两天,一个六月14,一个六月13,哎,大家说为什么要过滤两天的呀。啊对,咱是不是要去统计什么呀,要统计那个支付的订单吧,对不对,统计支付,你今天支付的订单是不是有可能是昨天下的,那所以说那这块得过滤两天啊,完了之后呢,往下走啊,然后还得过滤一下是不是ID不能为no啊对吧?因为我们现在统计的是啥,统计的是参与活动的订单,对吧?那所以说actd不能为闹,为闹的话说明他没参与活动啊,所以咱们这儿需要过滤一下,那完了之后呢,这次咱们按照谁分组。
07:01
啊,按照活动为什么呀,因为咱们这儿统计的是参与每个活动的。订单有多少个,参与每个活动的订单,并且支付的有多少个,是不是参与每个活动啊,对吧,每个什么你就得按什么分组,所以这边按照活动分组啊,这个没错吧,好,那这样一来的话,我们会把什么样的数据分到一组啊。啊,你按照活动分组会把什么样的数据分组啊,是不是参与同一个活动的订单会分到一组里边,对不对,但是你要知道啊,你要知道什么啊,咱们这个数据是几天的。两天的对不对,相当于你会把这两天的订单啊,是不是参与同一个活动的,哎,这个分到一组里边啊,注意是两天的,然后咱们要求的是啥,求的是当天。哎,这个参与该活动的订单有多少个,当天支付的订单有多少个,对不对,那所以说咱们上边是不是得做这个相应的过滤才行啊,对不对,那往上看啊。
08:00
哎,先看第一个,你看这个是什么,看能不能看懂啊,Some if啊,If,什么date forat create time create time这个字段来自于哪?是不是肯定来自于这张表,这张表当中我们有这样的几个字段啊,大家还能想起来吗?这个订单表我们是一个累积型快照时表,它里边是不是有咱们整个订单每个阶段对应的时间呀,对不对,那它是不是就是通过这几个时间去追踪他这个,哎,业务的周期呀,对不对,是这样的啊,那所以说咱们这儿呢,你看这个。条件什么样的啊,Date format,当然这个因为create time呢是年月日十分秒的格式,所以他们给它格式化成了这个,呃,就是年月日的格式,对吧?啊,相当于就是cur time等于2014,那我就记个一,否则就记零。哎,那你说你这求出来这个值应该是啥呀。是应该是6月14号的订单,一共有多少个吧。这个没错吧,啊,6月14号订单一共有多少个,就是这个道理嘛,啊这个很简单,那接下来往下走。
09:07
看这。啊,同样的道理,Cur time等于64,那我就把谁返回,是不是就把这个final total amount返回,否则我就返回零,也就是14号的订单,我就返回你的订单的金额。啊,13号的呢,我就返回零,所以说你这在最终在求和,所以说最终得到应该是啥?是应该是14号的订单,它的总金额是多少对不对,那所以说这俩值是不是就我们这所要求的当日的参与该活动的下单次数和下单金额呀,哎,这就搞定啊行拉完之后呢,看下边哎看这。啊,这回你看一下啊,这回是什么呀,如果支付时间啊等于什么呀,等于14号啊,那我就记一,否则就记零,那所以说你得到的应该是什么,是不是14号支付的这个订单的个数啊,啊,当然是参与每个活动的啊,那下边同样的道理,支付时间等于六月14啊,那我就可以怎么样,我就把这个诶你的金额拿过来,哎,否则我就按零算,所以说最终得到的就是6月14号所支付的总金额啊,是这样的啊好,那这块呢,大家得搞清楚啊,这边因为什么,因为咱们这儿呢,过滤出来的是两天的数据,因为什么?因为大家都知道我支付时间等于14号的订单,是不是可能是在13号啊,那所以说咱们这呢,需要过滤两天的数据啊,哎,那通过一条circle扣呢,把这四个字段就都求出来了啊,还是咱们熟悉那个sum if啊,好,那我这个往下走,下边呢,我们是不是还剩一个字段啊,就是那个display的对吧,曝光次数这个刚才咱们分析过了啊它。
10:46
要去哪张表里求哎,是曝光日表啊,那咱们去看一下曝光日表当中的字段啊,这个得回忆一下,找到DW哪层,DWD层的display log啊,把它点开。来,咱看一下这张表当中的这个具体字段啊,啊,我们挑重点去看啊,每行数据我们大家也知道每行数据应该是什么,是不是一次曝光记录啊,对不对,那往后看,往后找到曝光相关的字段啊。
11:14
来。主要看他俩就行啊,还有这个啊,呃,其实主要看他俩就行啊,那看一下啊,这个item type是什么。Item time是啥?是曝光类,曝光对象的类型啊,是曝光对象的类型,那这个item呢?就是曝光对象的I did啊,你看这个曝光对象的类型有啥,是不是有活动有商品,是不是咱们主要就是曝光他俩东西,这俩东西啊,一个活动一个商品,那咱们这儿统计的什么是每个活动的曝光次数吧,那所以说咱们要求的话得先怎么办,先根据item type曝光对象类型,哎把这样把这种活动曝光是不是先得给它过滤出来,然后再按照谁分组。
12:02
按照。Item分组吧,啊按item分组,然后呢,看星得到的是不是就是每个活动的曝光次数啊,就是这个道理吧,来,我们打开这个磁扣,接着往下看啊,找到我们这个console往下走,你看是不是就是这么做的。来看啊,首先从这两本里把14号的数据过滤出来,然后呢得把这个item time,就是曝光的对象类型,等于活动的这个记录过滤出来,然后呢,按照哎,Item也其实也就是活动ID吧,按它分组,然后呢进行count,那咱们得的就是每个活动的曝光次数了,就是这个道理嘛。啊,然后呢,咱们还得再往上看一下啊,因为这张表当中我们融入了什么东西呀,咱们是不是融入了这个活动的维度的这个字段呀,那所以说咱们是不是还得再来一个自查群,去把活动维度表当中的当天的分区拿到,对不对,一定得拿当天分区,不要跟整张活动维度表照,因为什么?因为这张表它是一张每日全量表,对吧?啊,它是每日全量,我们要拿的话,是不是只拿最新一天的分区就够了呀,所以说诶,从这张表当中把最新一天的分区拿到,那这三个查群搞定了,那搞定之后呢,剩下的事儿是不是就是给它拼到一块了呀?啊拼到一起,那下边咱们这是怎么拼的啊,首先我们先看第一个子查询啊,这。
13:25
T pop这个里边是不是统计出来了,我们那些就是参与活动的订单的这个相关统计啊,有下单有支付啊,那下边呢,这个是曝光,那他俩做了一个全外联啊,全外联啊,那全外联之后呢,我们相当于会得到一个这样的区表,对吧?啊会得了一个区表,那完了之后呢,我们是不是还需要拿到这些活动的什么东西。这些活动的啥呀?那个维度字段嘛,对不对,维字段得去哪哪张表呢?是不是得去咱们的活动维度表当中去拿呀,哎,活动维度表当中,哎,那活动维度表跟咱们左边这个虚表它俩的对应关系应该是啥样的,应该是哪边多哪边少啊。
14:07
啊,来重新看啊,这个东西是我们刚才求出来第一个子查询里边是不是统计了每个活动啊,这个对应的订单和支付的次数以及金额呀,那右边这个第二一个是啥是曝光次数吧,啊也是里边有曝光的活动,那第三一个呢,是咱们那个诶活动维度,你说他们对应关系应该是啥样的呀。是不应该是这种啊,为啥你想一想啊,我今天曝光的活动,以及我今天就是下单参与的活动,是不是肯定。要小于等于咱们那个全量的活动维度表的活动啊啊,为啥,因为你不不一定就是我今天我所有订单,所有的活动都曝光了。对不对,不一定所有的活动都有订单参与,是这个道理吧,那所以他肯定是这样的一个对应关系啊,那咱们这想返回的数据应该是哪部分呢?是不应该是这部分呀,这是我们想要的,所以说这咱们采用的是什么招呀?
15:03
第三一个跟第三张表是不是就left join啊对不对,这块呢,我们大家要搞清楚啊,然后还是这个道理啊,因为你在前面两张表是全外联,所以你跟第3NUMBER面join的时候呢,你的连接字段是不是得去做一个判空啊对不对,找一个不为now的这个呃,ID去跟第三个自然去进行join啊就这样的,你就是去写这种招的时候呢,这这种小细节呢,大家啊,需要多琢磨一下,多思考一下啊啊那这就是咱们这张表啊,相对来说呢,没有那么难啊,没有那么难,字段比较少啊,然后这个思路呢,跟前面思路也是一样的啊好,那这个咱们就诶直接过了啊好,那接下来咱们接着往后看,哎,看咱们活动主题的DWT层的累计表啊。来,找到活动主题宽表啊,先把字段粘出来。啊,CC。Ctrl a,好,那看一下这张表啊,那这张表我们首先先明确一点,它是不是也是来自于它也是DWT层表啊,那DWT层表我们是不是都有一个这样的要求,什么要求得是拥有全量的维度数据吧,对不对,它对应活动维度,那我这张表当中就应该保存有全量的维度数据,那问一下大家,那咱们得干啥?
16:20
得干啥,第一点得干啥,初始化这个得记得啊,一定得初始化,那咱们这个初始化能不能做呢?能做,因为这张表的数据来自于咱们的业务数据,来自于MYQL里边,对吧?那我MYSQL当中有一个活动维度表里边儿本身就保存了所有的活动记录,那这时候呢,我们就可以,诶是不是来一次全量导入啊,然后呢,呃,把这个相关的,这个是下单之相关的,这个是支付相关的等等等等,你把这些相关的东西呢,全部拿过来。啊,全部拿过来,然后呢,给他做一个什么东西,做一个初始化啊,跟之前的那个思路也是一样的啊,全量倒过来做临时表,然后呢,哎,这个做一次这个大统计,然后呢,给它进行初始化就行,这是初始化啊,这个咱们就不再多说了啊,那接下来呢,我们来接着往下走啊,那这张表我们分析一下它的结构啊,首先它的力度啊,就是一行数据所指代的是什么。
17:13
是啥呀?是不是要是一个活动的各种累计值没错吧?啊,一个活动一行,一个活动一行啊把这个搞清楚,那完了之后呢,那这张表当中它的具体字段是啥?我们来看一下啊,那上边这些字段从ID开始到cur time,这也都是活动维度表里边的字段啊,就是维度字段放在这就不用再照维度表了,这个大家搞清楚就可以,那接下来我们主要看下边啊。来,那下边这些字段我们来看一下,从这,哎,不是从这,从这开始。啊,从这开始来我们看一下吧,啊,那这里边儿呢,我们可以看一看啊,它这里边的累积值其实主要有哪几个累积值是不是主要就是这几个呀。对,这是不是就是累计值啊,这个累计值呢,呃,就是只只是包含什么,就是从开始至今的累计值,从开始至今的一个累计值,他这儿呢,并没有那个什么,并没有什么,最近七天,最近15,最近30,是不是没有那些啊,对不对,为什么他没有那些呢。
18:15
这个其实考虑了什么呀,考的这个活动的生命周期啊,可能比较短对吧,因为你一个活动你想一想啊,我一个活动一般情况下是不是就一天。对吧,今天我有一个什么什么什么品牌日啊,或者是一天或者两天或者三天,是不是不会说一个活动我需要长达呃,什么15天,30天,60天,没有这么长的活动吧,啊,一般情况下很少有,所以这考虑到这个活动的特点呢,它的生命周期没有那么长,所以咱们这儿呢,也就没有去统计它那个什么七天啊,什么15天,什么30没统计,咱们这只有一个什么呀,就是一个累积的啊,从这个活动开始到这个活动结束啊,就一个累计值。是这样的啊,所以这个因为周期太短,没必要统计什么七天15天30,就这只有一个累积啊,这个应该大家能想明白啊,那上面这儿呢,还有一个啥,有一个什么display,呃,Day countt,这指的都是啥?说都是当日的下单,当日的支付相关统计啊,那这个其实有必要放在这吗?
19:10
是不是没有必要啊,对吧,这个当日的我们是在DWS层就有啊对不对,所以这块呢,大家搞清楚就行,也就是咱这需要重新计算的也就是这个了。还有这个了啊,那他更新的思路应该是啥呀?是不是也应该是先每天更新,应该怎么办?先拿到这张表当中原来的值,然后呢,再把它当天的各种值拿过来,是不是进行累加呀,就这样一个思路嘛,啊那接下来我们看一下这个文档当中的侧考啊。这个就很简单了啊,来放在这儿。啊好,那首先我们看这个old,呃,这个其实也没必要写紫查询了啊,Select星from这张表,这直接是把这张表放在这就行了,对吧,也没必要紫查询了啊,这就是咱们那old,那new是什么呢?New就是我们从DWS层的这个活动的每日统计表当中,把当天的分区拿到啊,拿到之后呢,我们就看一下上面是怎么做的啊,对于上边的字段呢,来这部分。
20:10
啊,是不是保持那个原则啊,就新的有用新的,新的没有用旧的啊,那下面这块呢,这是不是那个当日的那些相关统计啊,那当日的你就看你今天有没有呗,今天有你那我就是今天的值,没有的话,那说明你今天没有被下单,没有支付对吧,那我就是零呗,啊所以这块很简单,那关键是看下边,下边这个是不是就是咱们那个累计值啊哎,用原来的值加上今天的这个值,就完成了这个累加的统计。哎,就这样一个思路啊,所以这个,哎,咱们跟前面的也是一样的套路啊,所以这块呢,咱们比较简单,我就直接过了啊好,那活。
我来说两句