00:00
好,那接下来我们看用户主题表的每日装载啊,那实际上它的每日装载呢,要比首日装载更简单一点啊,因为我们在做每日装载的时候呢,我们只需要考虑,只需要计算一天的数据,对吧?历史数据我们就不用管了,好,那接下来我们看一下每日装载的大体思路啊,那首先我们需要先去DWD层的各个时表当中去获取每个用户当日的各项业务操作记录啊,那咱们拿到之后呢,需要分别进行汇总统计啊,那统计完之后呢,我们需要把这些结果呢给它join到一起啊,那这样一来就能得到咱们最终的结果了。好,得到最终结果之后呢,我们只需要把这个结果插入到DWS层用户主题宽表当天的分区里边,是不是就可以了呀,对吧,那这就是咱们每日装载的大体思路,OK,那接下来我们就开始编写每日装载的搜O语句啊,那实际上这个每日装载与首日装载的so语句啊,很像很像啊,那因为绝大多数字段的取数。
01:00
逻辑啊,都是相同的啊,那唯一的区别是啥呀?唯一区别就是哎,首日德彩的时候,我们需要考虑历史数据对吧?啊,那也就是我们需要统计的是什么呀?是每个用户每一天的汇总行为,没错吧,而每日装载呢,我是不是只需要考虑这一天的数据对吧?那咱统计的就是啥?是不是就是每个用户,诶他的汇总行为是什么呀。没错吧,啊,那当然这个区别体现在咱的SQL语句上,其实就是是不是就是那个分组字段,首日装载的时候呢,我们需要按照日期加上UID进行分组啊,而每日装载的时候呢,我们是不是只需要按照user进行分组就可以了。啊,这就是它俩一个主要的区别,那所以说每日装载的测后语句呢,咱就没必要从头去写了啊,我们可以基于首日装载的测绘语句进行改造啊好,那现在我们先把前面写的首日装载的每个紫查询给他拿过来啊,来,咱去找一下啊。OK,到这儿了啊,这是咱的第一个子查询对吧,然后往下翻啊,找到最后一个啊,到这儿啊,来CTRLC,我们把它们全部拿过来放在这个位置啊好,那现在我们就可以从第一个开始改了啊。
02:06
好,我们先看第一个子查询啊,好,那咱先来明确一下啊,就是第一个子查询它统计的是什么值啊,是不是log in count呀,对吧,也就是登录次数啊,那之前在这儿我们统计的具体内容是什么?具体内容是每个用户每一天的登录次数,没错吧,但是我们现在呢,是不是只需要统计每个用户在6月15号这一天的登录次数啊。没错吧,那所以说在这儿具体应该如何去修改呢?那来首先我们先来明确一点啊,因为我们现在只需要统计6月15号的登录次数了,对吧?那所以说我们是不是只需要拿到6月15号的登录记录就可以了啊,那所以说这个位置诶,咱是不是就不能再拿全面的数据了,诶我们需要去过滤一下啊,那怎样能达到6月15号诶这一天的登录记录呢?实际上在这儿呢,我们直接使用DT这个分区字段去过滤就可以了啊OK,那接下来咱改一下啊呃,在这儿呢,我们需要加上一个过滤条件,在这儿呢,先给它来一个暗,然后上面呢再加一个DT等于2020杠零六,诶杠幺五。
03:08
啊,那这个是不是改完了,那改完之后我们继续往下进行啊,那由于我们现在不需要统计每天的每个用户的登录次数了,对吧?啊,而只需要统计一天的,那所以我们还需要按照这个DT分组嘛,是不需要了,对吧?那所以说我们直接把这个DT删掉就可以了,那同时上面呢,我们也不需要再选择DT了,OK,那这样一来我们就统计出来了啊,那每个用户在2020年6月15号这一天的登陆次数了。OK,第一个咱就改完了,那接下来我们看第二一个啊,OK,那第二个统计的是什么呀?第二个统计的是呃,加购物车次数和收藏次数对吧?啊,那OK,那其实他的修改逻辑呢,也是一样的啊,首先我们需要先拿到6月15号这一天的啊,是不是加购车的操作和收藏的操作呀,对吧?然后呢,我们就不需要再按照DT分组了,所以把DT字段是不是给它去掉就可以了,没错吧,其实也是一样的啊,那在这呢,我们也是给它改造一下来,把这个回车在前面呢,咱给它加一个按子啊,那前面呢,应该是DT等于2020杠零六杠幺五,没错吧,那接下来呢,我们再把这个分组的DT字段给它去掉,上面第七字段我们也不需要选择了啊OK,那到现在为止,我们第二一个也就改完了,好接下来我们看下一个子查询啊,那下一个呢,是与订单相关的一些统计啊,好,我们来看一下具体内容啊,这儿有一个count星,它统计的时间应该是下单次数对吧,那下一个呢,统计的是参与活动下单的次。
04:36
次数啊,那这个呢是参与活动优惠的金额,这个呢是用券下单的次数,这个呢是用券优惠的金额啊,那还有一个这是原价对吧?这是最终价格,OK,那这就是与订单相关的一些统计值啊好了,那接下来我们分析一下啊,那它应该怎么改,实际上思路呢,跟之前是一样的啊,那首先第一点诶,我们需要确保啊,咱这统计的是6月15号这一天创建的订单,而不是全部订单了,对吧?那是第一点,那第二点呢,我们需要把这个葛入外字段当中的日期给它去掉,没错吧,就这两部分啊,现在我们先来看第一部分啊,比如说咱们需要拿到6月15号这一天创建的订单。
05:18
OK,那咱们怎样去获取这一天创建的订单呢?那肯定是过滤,对吧,那大家思考一下啊,在这儿我们还能不能直接使用分区字段DT进行过滤,因为前面咱是不是都用DT过滤的呀,对吧?那看看行不行?啊,在这其实不行的,为啥不行,咱来分析一下啊,那首先我们回顾一下啊,那这张表它的分区规划是什么样的啊,这张表是一个累积型快照时表对吧?那咱们的分区规划是这样的啊,首先它会有一个999分区啊,那这个分区里存放的所有未完成的订单对吧?那其次呢,还会有每天的一个分区,每天的分区里边存放的是当天完成的订单。没错吧?那所以大家想一想啊,现在咱这个分区字段DT的值与订单的创建时间create time是不是不一致啊,对吧?那所以说在这儿呢,我们就没有办法直接使用DT去进行过滤了,在这儿呢,我们应该用谁啊?是不是要用cur time去过滤?没错吧?那当然cur time我们需要做一个日期的可视化处理啊好,那现在我们在这儿加一个过滤条件,一个是where data form ma,对吧,里边写啥呀?
06:20
是不是应该是呃,对吧,然后呢,把它格式成YY,然后M,然后DD这个格啊,那应该等于多少呢?是不应该是等于2020杠零六杠幺五啊对吧?OK,那这样我们就能够拿到6月15号这一天创建的订单了啊,然后大家再来跟我思考一个问题啊,如果在这儿我们只使用可一看字段去过滤数据啊,那这条circleql在执行的时候是不是肯定会全表扫描啊,那所以这个效率呢有点低,那咱们能不能优化一下呢?诶,实际上是可以的啊,怎么优化家来跟我一起分析一下啊,首先那么咱咱们先明确啊,就是这张表它的分区规划,刚才咱们也说了一下了,对吧?啊,它是什么呀?它会有一个9915分区啊,里边存放未完成订单,还会有一个每天的分区,存放当天完成的订单,没错吧,那大家想一想啊,你说我们6月15号这一天创建的订单啊,它最终会被写到这张实施表哪个分区当中啊。
07:18
啊,是不是只可能会被写到9999分区,或者是诶六月15的分区啊,为什么?你想一想今天创建的订单,它是不是最多就俩状态,要么完成了,要么没有完成对吧?那完成的是不是肯定就到进到15号的分区了,没完成的是不是就999,那所以说我们在使用cur time去过滤这个数据之前呀,咱可以先使用DT这个分区字段,诶先去过滤一下,诶咱们先把范围缩小到两个分区啊,然后呢,再使用cur time去过滤,那这样一来效率就要更高一些了,OK,那所以说我们在这儿呢,还得再补充一下这个过滤条件啊,具体怎么写来,我们往往往后走啊,咱们改一下,在这儿呢,首先需要先用DT过滤一下数据,应该是DT什么呀。
08:00
DT它应该等于9999,然后杠九九杠九九,那是不是或者呀,对吧?啊2DT等于诶2020杠零六杠幺五没错吧,然后下边再来一个and,我们把这两个二的条件呢,给它放到括号里边来,CTRLV,那后边呢再来1AND。诶,那这样一来的话呢,诶,我们就能够更加高效的拿到15号创建的订单了啊啊,那这完事之后呢,我们再来看第二一步啊,那第二步我们要做的是不是将分组字段当中的日期去掉啊,对吧?那接下来咱给它删除就行了啊,那同理,上面我们也不需要再选择了,好了,那这个子查询我们也就改完了啊,那我们继续往下看啊,下一个子查询呢,是与支付相关的啊,那具体内容有啥?具体内容有支付次数和支付金额啊那同样我们进行改造的时候呢,也是要做两步,第一步啊,先获取6月15号完成的支付记录,对吧?然后呢,哎,再把这个分组字段里边的日期去掉就可以了,那好,现在咱们先来做第一步啊,那咱怎么去获取6月15号完成的支付记录呢?
09:03
啊,很简单啊,实际在这儿呢,我们使用谁使用call back time这个字段去过滤是不是就可以了?没错吧,然后大家要注意一下啊,那call back time与这张时时表里的分区字段DT的值是不是一致的呀,对吧?那所以在这儿呢,我们就不需要再使用Co back了,我们使用DT代替它就可以了啊OK,那所以在这儿咱们改一下啊,把这个DT不等于9999改成DT等于2020杠零六杠幺五是不是就可以了?那然后呢,我们再把DT给它去掉,哎,这个删除,那上面这个也给它删除啊,OK,那这样一来我们就完成了这个紫查询的改造了,好,我们继续看下一个啊,下一个呢,是与退单相关的统计啊,没错吧,这里边具体的内容有退单的次数,退单的件数以及退单的金额啊,那同样道理啊,在这儿我们也是要做两步,第一步啊,先过滤出来6月15号产生的退单记录啊,然后呢,再把日期给它去掉,没错吧,那OK,那咱们去获取6月15号产生的退单记录的时候呢,我们理论上应该用哪个字段去过滤啊是不是?
10:03
理论上应该用cur time去过滤没错吧,但是由于这张表,那咱们是一个事物型时表对吧?那所以说呢,它的cur time和分区字段DT的值是不是一致的,所以在这呢,我们也是可以使用这个DT去代替cur time的啊,所以在这呢,咱加一个过滤条件应该是V尔DT等于2020-0606杠幺五啊就完事了,然后呢,我们再把DT去掉删除,那这个呢,也给它删除啊好了,那退单相关的统计哎,我们就搞定了,好,我们继续往下看啊,那下一个呢,是退款统计对吧?那这里边的具体内容有退款的次数,退款的件数以及退款的金额啊OK,那这个so呢,看起来复杂一些对吧?啊,我们先来回顾一下啊这个退款统计的取数逻辑啊OK,那由于啊,退款事实表当中没有退款件数这个字段,对吧,那所以说咱们当时是怎么去取的呀。啊,我们是不是需要从退单事实表当中去获取与退款记录相对应的退单记录,对吧?因为啥,因为退单记录里边是不是有退单件数啊,对吧?那这样一来呢,我们就可以获取到我们所需的这个退款件数了,OK,这是咱的取数逻辑啊,那当然呢,在我们进行每日装载的时候呢,也要有这个逻辑啊,那我们就开始改啊,那具体怎么操作呢?其实也是两步,第一步我们需要先获取6月15号完成的退款记录,那第二步呢,我们需要把日期给它去掉,没错吧?啊,现在我们先看第一步啊,那我们获取6月15号完成的退款记录的时候呢,我们理论上应该用哪个字段过滤啊,理论上是不是应该用这张表当中的call back去过滤,没错吧,但是由于这张表当中的call backtime与分区字段DT里边的值是一致的,那所以在这呢,我们可以使用DT去代替它啊,来,咱这把这个DT不等于9999改成DT等于2020杠零六杠幺五就可以了,零六杠幺五,好,那这部分已经改完了。
11:57
接下来我们继续啊,大家大家来看啊,咱下边是不是还有一个子查询呀,对吧?这是什么?这是退单实时表对吧?那你说这个需不需要过滤一下呢?哎,这个其实也需要去过滤一下啊,因为咱这没必要去拿全表的数据了,对吧?OK,那接下来我们看一下这个应该怎么过滤,那我们能不能也像这个一样,直接来一个VRDT等于2020杠零六杠幺五呢?
12:18
能不能,那接下来咱们分析一下啊,那我们现在先看一下第一个子查询,它获取到的数据是什么啊,第一个子查询获取到的是不是应该是6月15号完成的退款记录啊,对吧?那下一个查询呢?那下边一个子查询啊,如果说我们的过滤条件就这样写,那咱拿到的数据应该是6月15号产生的退单记录对吧?啊,那在这儿他们俩做关联的时候,我们期望的效果是啥呀?我们期望的效果是啊,能够从下边的子查询里边找到上边的子查询里边所包含的退款记录啊,对应的退单记录,对吧。OK,那现在咱能不能实现这个效果呢?那咱们分析一下啊,来分析一下,那这个里边是15号完成的退款记录,对吧?分享一想15号完成的退款记录啊,那它所对应的退单记录一定是在15号产生的吗。
13:09
这个其实是不一定的,对吧,可能是15,可能14,也可能是十三十二倍吧,对吧,那所以在这儿呢,我们使用DT等于2020杠零六杠幺五这个过滤条件肯定是不行的。啊,那这块我们到底应该写哪一天呢。实际上写哪一天我们也不确定,对吧?刚才咱分析了那15号完成的退款记录,它对应的退单记录可能是15,可能14,可能13,可能12,没错吧,咱也不知道是哪一天,那这个过滤条件到底应该怎么写呀?那虽然我们不知道具体是哪一天啊,但是我们知道啊,这个退单与退款的时间间隔是有上限的,对吧?那这个怎么理解啊?那举个例子,比如现在有一个用户,他发起了一个退单申请啊,而这个商家呢,一直未对其做出回应啊,那这样一来是不是超过一定时间之后,系统是不是就自动会完成这个退款呀?没错吧,那所以说退单与退款的时间间隔呢,是有上限的啊,那假如说我们这儿的这个时间间隔呢,就是15天,那最长15天,那OK,那所以在这儿我们可以怎么做,我是不是可以把最近15天的退单记录全部过滤出来,那这样一来是不是就肯定都能够找到了呀?没错吧,那所以在这儿呢,咱这个过滤条件应该这样去写啊来,首先我们应该改一下啊,应该是date是不是大于,诶大于等于,然后后边来一个data a对吧?然后呢,把当前日期放进去,后边呢,我们再来一个减十股。
14:30
啊,那这样一来我们就能够找到那与第一个次查询里边的退款记录相对应的退单记录了,好,那第一步我们就做完了啊,那接下来我们做第二一步,第二一步就比较简单了,是不是就是把日期字段去掉就可以了呀,对吧?那在这呢,我们首先把分组字段里边的DT去掉,然后这个位置咱是不是也不需要再选择这个DT了呀,对吧,给它删除,那同样上面呢,我们也就不再需要这个D了,OK,好,那这个查询咱就完事了,好,那我们继续往下进行啊好,我们看下一个子查询啊,下一个子查询呢,是与优惠券领用相关的统计,对吧?啊,那我们之前都统计了哪些值啊啊,咱们统计了领券的次数,用券下单的次数以及用券支付的次数啊,没错吧,那说的具体一点呢,实际上是每个用户每一天的呃,领券次数以及用券次数,对吧?那那所以说我们在真正去统计这几个值的时候呢,咱是不是需要按照日期进行分组啊,对吧?那那当然呢,统计不同的值,我们需要按照不同的日期分组啊。比如说我们统计领。
15:31
券次数,那就需要按照领取日期进行分组,对吧?那你统计用券次数就得按照使用日期进行分组,那所以说呢,这三个字段呢,我们没有办法啊,一次性全部求出来啊,所以在这儿呢,我们是分开求的啊,分开求完之后呢,是不是使用全外联关联到一起的呀,对吧?啊,但是我们现在要做的是什么,是每日装载了对吧?每日装载我们要统计的是啥呀?是不是只需要统计在一天当中的每个人的领券次数,以及用券下单的次数,以及用券支付的次数啊,对吧?OK,那所以说呢,在这儿我们就不需要再按照日期进行分组了啊,那所以在这儿呢,我们实际上就可以通过一个circle考来把这三个值一次性求出来了,那具体的思路是什么样的呢?诶其实就是这样啊,首先我们需要先拿到6月15号诶这一天的领取记录啊,用券下单记录以及用券支付记录啊,拿到之后呢,我们按照用户进行分组,对吧?那分完组之后呢,使用sum if分。
16:31
别去计算领取次数,诶,用券下单次数和用券支付次数,OK,好,那接下来呢,我们就按照这个思路呢,把circle写一下啊好,那原来的这个S课呢,我们就不需要了,给它删除来,咱给它删掉,好,那现在我们开始写啊,好,那我们首先select,然后呢from dwd层的库房柚子对吧?那接下来呢,我们需要把6月15号的领取记录和使用记录过滤出来啊,没错吧?那具体的过滤条件应该怎么写呢?啊,应该是get time等于65,或者是using time等于65,或者是use time等于65,没错吧?OK,我们来写一下啊,当然这个日期呢,我们需要先格式化一下啊,首先data form ma,然后get time,后边呢是YYY。
17:13
然后呢,诶杠MM,然后杠DD啊OK,那这是get完之后呢,等于2020杠零六杠幺五啊注意幺五啊,那接下来我们继续还有啥呀,是不是二啊对吧,下边呢,我们直接复制一下啊来CTRLC拿过来。二什么呀?2USING等于65没错吧,那我们再继续再来一个二,那这应该是啥?应应该写啥?应该是use time等于65对吧?OK,那这样一来呢,我们就能够把15号的领取记录和使用记录全部拿到啊,当然我们要是这么去写过滤条件的话,那是不是同样会全面扫描啊啊那所以说我们能不能先用分区缩小一下范围呢?实际上是可以的,你想一想啊。我们6月15号的使用记录和领取记录是不是只可能出现在两个分区啊,对吧,是不是也是一个就是9999分区,那还有一个呢,可能就是哎六月15的分区,所以在这儿呢,我们可以先用分区过滤一下,OK,那这个过滤条件呢,我们需要改一下啊,那在这我们先加上DT。
18:14
嗯,它等于2020杠零六杠幺五啊,然后呢,或者是DT等于9999杠九九杠九九啊没错吧,那这两个条件呢,我们给它放在一个括号当中,CTRLX来放括号,嗯,来CTRLV,那然后呢,我们再把下边诶这三个过滤条件给它放到一个括号当中,嗯。来给它放一下啊好,那前后这一切过滤条件呢,应该是用什么连接呀,是不是应该用and呀,对吧?啊and一下,OK,那这过滤条件我们就改好了啊好了,那完之后呢,我们就可以,诶是不是分组去分别统计我们所需要的领取次数和使用次数了呀,对吧?OK,我们继续往下进行啊,在后边我们怎么做,是不是直接格如by user ID就可以了,没错吧,然后在上面呢,我们需要把user ID选择出来,然后呢,是不是直接使用sum if去分别统计这几个次数啊,对吧?那首先我们先统计领取次数,那就是sum if if什么呀,来,咱把这个拿一下。
19:08
来CTRLC啊,如果get time等于65,那我就怎么做,我是不是就记一个一,否则我就记零,那现在我们得到的就是啥?那是不是就是所谓的领取次数啊,没错吧,那接下来我们继续,后边我们只需要把时间改一下就行了,那这个get time呢,改成是不是using time嘛,对吧,Using time,那下一个呢,我们改成use time。OK,那到现在为止,那这个子查询我们就改完了,那我们继续往下进行啊,来看下一个,呃,这个子查询呢,是与评价相关的统计对吧?啊OK,那它的改造就比较简单了,诶我们加一个过律条件,然后把日期去掉是不是就可以了对吧?那首先我们在这儿呢,加上一个诶V2DT等于2020,然后杠诶零六杠幺五对吧?这样一来我们能拿到6月15号产生的评价记录对吧?拿完之后呢,再把这个地T字段给它去掉啊,那这个改造咱们就完事了啊OK,那接下来我们继续往下进行啊好,那下边是谁?下边是我们最后一个自查询了,对吧?这个是订单明细统计哈,那它的改造呢,也是一样的啊,加上一个过滤条件,完了之后呢,诶,把这个呃,DT字段去掉就可以了,好,那过滤条件呢,其实跟它也是一样的啊,在这儿我们直接使用DT进行过滤,好,那现在我们已经拿到6月15号产生的订单明细了,对不对,那接下来我们就把这个日期删除啊,那首先我们需要把这儿的一个位置的DT先删除掉,到了之后呢,这儿咱是不是就不需要了呀,对吧。
20:31
那再往下走,这个为止咱是不是也不需要啊,那最后呢,再把上边这个删除啊,OK,那这个紫查询咱们也就改完了啊,那到目前为止啊,那我们每日装载的子查询就全部都写完了,那我们剩下的工作呢,其实就比较简单了啊,主要就是啥呀,是不是就把这些子查询通过全外联照到一起,对吧,然后呢,把结果写入到d wi层的用户主题宽表呢,6月15号的分区里就完事了呀,对吧?啊OK,这部分内容就比较简单了啊,那现在我们直接拿到文档当中的最终的完整的circlel,咱们一起来看一下就可以了啊好,那现在我们去拿一下啊,找到每日装载,然后往下翻。
21:09
来往下翻啊,到这儿结束啊,CTRLC,那接下来放到data group当中来,我们一起来看一下啊,CTRLV,好,我们来到这个circlel的开头啊,咱们先看一下这个circle的整体结构啊,那首先这边呢,先使用VS是不是声明了每个子查血样对吧?那接下来往后走,看他接下来干啥了啊。下边是不是就把这些紫纱裙使用全外联照到一起了呀?没错吧?然后呢,再选择我们所需的每个字段啊,那最终呢,把这个结果是不是insert all right到6月15号这个分区当中啊,没错吧?啊,这个套路就这么一回事,那接下来呢,有几个注意事项,我们一起来看一下啊那第一个呢,就是我们的关联方式啊,刚才大家也看到了,在这儿我们需要使用全外联对吧?那目的呢,就是为了保留所有的记录啊没错吧,那接下来我们看第二一个啊,那既然要用了关联了,那所以说我们是不是得有一个关联的字段呀,对吧?那咱这儿的关联字段应该是谁呀?在这儿是不是没有那个日期了,只剩下user ID了,对吧?我们只需要把一个用户的诶各种行为撞到一起是不是就可以了呀?啊,这是咱们的关联字段啊,那还有一个呢,就是多表全外联的时候,它的关联条件怎么去写?
22:17
没错吧,那应该怎么写呀?啊,咱们往下看是不是跟我们进行呃,首日装载的时候,那个关联条件的写法是一样的呀,对吧?那这个呢,我就不再解释了,大家应该知道是怎么回事了,对吧?OK,那接下来我们再继续往下进行啊,那接下来呢,我们要做的事呢,就是把这个最终的结果是不是给它insert到我们最终的这个目标表里啊,对吧?啊,因为我们现在是每日装载了,对吧?那咱是不是就不需要动态分区了,所以说在这儿呢,我们把这个分区诶固定的写死就可以了,2020-00115啊OK,那这就是我们D层用户主题宽的每日装载的。
我来说两句