00:00
好,我们看优惠券统计这个需求啊,这个需求呢,让我们统计的是最近三十日发布的所有的优惠券的领用情况和补贴率啊,这个领用情况呢,主要就包括领取次数和使用次数啊,那这个补贴率又是什么呢?来我们往后看啊,补贴率是指优惠金额与使用优惠券的订单的原价金额的比值,好,那这个需求呢,也比较简单啊,无需过多的解释啊,那现在我们再来看一下它的建表语句,重点看一下它的字段就可以了啊来我们看第一个DT统计日期啊,这个不用说了,对吧?啊OK,我们看下一个啊,下一个呢是coupon ID,也就是优惠券ID,然后再往下呢是coupon name,也就是优惠券的名称啊,然后呢,再往下啊是star date发布日期,然后我们看下一个real name啊,也就是优惠规则对吧?啊,当然这个优惠规则啊,是优惠券的类型而定,那咱的优惠券呢,有满减券,也有满件打折券对吧,那比如说现在这个优惠券呢。
01:00
是一个满减券,那它的优惠规则呢?啊,就类似这样,满100元减十元啊,那如果呢,是一个满件打折券,那它的优惠规则也就是满几件打几折,没错吧?OK,那这就是所谓的rule name,好,我们再往下看啊,那下面一个呢,是get count,这是不是就是领取次数啊,对吧?那再往下呢,Order count使用下单次数,然后再往下呢,还有一个expre time,这是啥呀?这是不是过期次数啊啊,没错吧,然后我们再往下走啊,那下边一个呢,是older original amount,这是啥?这是用优惠券订单的原始金额,然后再往下,下边呢,还有一个older final amount啊,这是啥?这是不是就是使用优惠券订单的最终金额呀?啊好,继续啊,下面呢,还有一个reduce amount,这个指的就是使用优惠券的优惠金额啊,然后我们再往下啊,最后一个呢,是reduce rate,这个其实就是所谓的补贴率啊,刚才也提到了,所谓的补贴率呢,是指优惠金额与使用优惠券的。
02:00
订单的原价金额的比值对吧?那也就谁跟谁呀,是不是也就是reduce amount与order original amount的比值啊,OK,那这就是优惠券统计这个需求的说明,那接下来我们分析一下这个需求啊,大家一起看一下啊,实际上这几个字段呢,这都是啥?是不是都是优惠券的维度信息啊,没错吧?那所以这几个字段呢,肯定是来自于优惠券维度表,然后下边这些字段呢,才是真正的统计值啊,那这些统计值呢,在这儿其实指的是从开始至今的累积值啊,那每个优惠券从开始至今的累计值咱是不是都已经算过了呀?对吧?在哪放着?是不是应该是在DWT层的优惠券主题表当中存放着,没错吧?那所以实际上下边这些字段啊,诶,我们大部分都不需要计算,唯一一个需要计算的就是这个补贴率,对吧?诶,我们需要自己去除以一下,其余的字段呢,我们都可以从DWT层的优惠券主题表当中直接获取,好,那这就是这个需求的大体思路啊,那接下来呢,我们把这个思路实现一。
03:00
那我们先去获取它的维度字段啊,来select from应该哪张表,是不是应该是优惠券维度呀,对吧?也就是蒂am酷盘in for对吧?那完了之后呢,我们需要获取它的2020杠零六杠幺四这个分区啊,那来看一下我们都需要哪些维度信息啊,首先第一个库房ID,那直接拿ID是不是就可以,没错吧,然后下一个,下一个呢是coupon name拿过来,然后呢,Start date对吧?来我们选择一下啊,然后大家要注意啊,我们选择出来的一个字段呢,是start time啊,这里边呢,实际上是有年月日,也有十分秒在这儿呢,我们只需要日期对吧?那所以说我们给它格式化一下就可以了,来CTRLX,来一个data format,来把star time放进去,格式化成年月日啊,来MM,然后杠滴滴,好,那star date,我们就拿到了啊,然后我们再继续往下进行,再往下呢是real name对吧,也就是优惠券规则啊,那优惠券规则的处理相对来说要麻烦一些啊,我们先来简单回顾一下优惠券维度表当中是如何保存每个优惠券的。
04:00
会规则的啊来我们打开这张表的数据来看一下啊,这个呢,就是优惠券维度表啊,在这儿呢,我们重点看一下这样的四个字段啊,这两个,然后后边两个,这四个字段呢,实际上可以分为两组啊,那这两个是一组,那另外两个呢,诶是一组啊这两组分别是什么?简单看一下啊,咱看它这是condition amount对吧?那这个呢是benefit amount,这俩分别是啥?这个是不是满减条件,这个是满减优惠啊,那显然这两个字段描述的就是满减券的优惠规则。没错吧,那我们再来看另外一组啊,分别是condition number呃和benefit discount啊,那condition number呢,是不是就是满件打折的条件,那benefit discount呢,是不是就是满件打折的折扣啊,对吧?那所以说这两个字段呢,描述的就是满件打折券的优惠规则啊,他俩是成对出现的啊,那这四个字段都是用来描述优惠规则的,对吧?那一个优惠券它的规则到底应该以谁为准呢?啊,这个是不是得是优惠券的类型而定啊,对吧?啊,这儿有一个库房type优惠券类型啊,那如果优惠券的类型是满减券,那是不是就以他俩为准?那如果是满件打折券呢?那是不是就以他俩为准呀?OK,那这就是优惠券维度表中与优惠规则相关的字段,那回顾完之后呢,我们再来思考一下,就是咱的优惠券规则到底应该如何获取啊,那在这儿是不是得用这几个字段去拼接一下呀,对吧?啊,那具体怎么拼,那是不是还是得由库房type去决定对吧?那现在呢,我们去看一下库房type到底。
05:33
共有几种来我们打开码表啊,就是那个字典表对不对?来看一下跟优惠券类型相关的编码来,就是这几个啊,那分别是32013202 3203啊3201代表的是满额减价啊,那3202呢,指的是满量打折啊,也就是一个是满减,一个是满件打折,没错吧,那3203呢,就是代金券,实上代金券呢,相当于没有优惠条件对吧,直接减对不对啊OK,那这就是优惠券的三种类型啊,所以说咱是不是得根据这三种不同的类型去拼接不同的优惠规则呀,那接下来咱就去拼接一下啊,那由于在这儿呢,一共有三种类型对吧?也就是我们需要考虑三种情况,所以在这儿是不是又需要用到kiss when啊对吧?来我们写一下啊,来这边先写一个kiss,然后回车这写一个end啊然后呢,哎就是问对吧啊问什么是不是应该是问coup旁type对不对,等于什么等于比如说32013201是不是满减券啊对吧?那满减券的规则应该是什么?是不是应该是满多少元减多少元对吧?那所以。
06:33
后边Z,那这儿呢,我们需要用到一个慷慨的函数去拼接,对吧?啊,那我们先写一个汉字啊,也是满,满什么,满多少元对不对?那多少元应该是哪个字段呀,是不是应该是那个condition amount呀,对吧?啊,满多少,然后边来一个元对不对啊,然后后边呢,是不是减呀,对吧?减多少元,那减段应该是哪个字段,是不是应该是那个benefit amount,没错吧,那后边再写一个单位,也就是元,OK,那这样一来,我们是不是就得到了满减券的优惠规则,对吧?满多少元,减多少元,那OK,我们继续往下进行啊,那这儿呢,是不是还得来一个问,对吧,应该是库旁诶tab一样的,它如果等于,比如说3202,那它后边的规则应该什么样的?说应该是满几件打几折呀,对吧,那所以我们来写一下,这儿呢,也得用慷慨的拼一下啊,来我们这儿还是先写一个汉字满。
07:21
然后呢,后边是几键对吧?几键应该是哪个字段,这应该是那个肯迪是number啊对吧?OK,那后边呢,诶再写一个汉字,应该是满几键键数对吧?那后边呢,应该是诶打折扣来来一个二,那应该是打几折对不对?那个几折应该是谁?是不是应该是那个benefit discount呀,对吧?OK,那后边呢,再来一个汉字,诶折扣折啊好了,那这样一来我们就得到了满件打折券的优惠规则了,没错吧,那我们再继续往下进行啊,那咱是不是还有一种类型啊啊那个类型是不是就代金券啊对吧,相当于没有优惠条件直接减对不对?那所以在这儿呢,我们也是问一下啊问诶库type等于啊3203对吧?啊然后呢,这那它的规则应该什么样的呀?是不是直接就是减几元,那这吧,那所以说我们可以把它拿过来啊,复制CTRLC给它改一下是不是就可以我们把这部分给它去掉啊,只留下一个减几元就可以了啊好,那到现在为止呢,我们就已经拿到了这三种类型的优惠规则了啊,在这儿呢,我们给它起一个别名叫做。
08:21
Name,对吧?Name,好,那到现在呢,我们也就已经拿到了所有的维度字段了,那接下来呢,我们再去获取这些累积值啊,那这些累积值呢,都比较简单,是不是可以从DWT层的优惠券主题表当中直接获取啊,对吧?那现在我们去获取一下来select,然后from,应该是DWT层的coon topic,对吧?那我们同样需要获取它十四二的分区,2020杠零六杠幺四,没错吧?那接下来看一下我们都需要哪些字段啊,咱们给它盯一下来从领取次数开始,CTRLCS3盯在这儿啊,来我们选一下,首先我们先把它的coon ID选择出来啊,因为一会需要去做关联,对吧?那下一个呢是get count,诶直接拿就行,下一个呢是older count,这个也直接拿,下一个呢是exp count,再往下啊,Older original amount对吧?那再往下呢,是older final amount,再往下呢是reduce amount对吧?这个呢,咱叫做older reduce amount啊,然后我们继续啊,那现在是不是就只剩下最后一个reduce rate,也就。
09:21
补贴率了呀,对吧,那补贴率怎么算,是不是用优惠金额比上原价金额,也就是他俩比一下就行了,对吧?那我们比一下,一个是older reduce amount,另一个呢,是older,呃,Regional amount,啊好,这个补贴率我们也就拿到了啊好,那到目前为止呢,我们就已经获取了咱们所需的全部字段了啊,那接下来我们要做的事呢,就是把这两部分数据给它转一下,对吧?来,我们先分别起个别名啊来,第一个CTRLX。CTRLV,我们给它起名为T1就可以啊,再往前推一下。好,那下一个呢,我们起名为T2啊来CTRLX方括二的。来CTRL位往前推一下。
10:00
哎,这是T2啊好,那他俩呢需要做一个join啊,那在这呢,直接join就行,来我们继续往下走,连接条件呢,应该是t1.id等于T2点库ID对吧?那来好,那现在已经关联上了啊,那现在我们就可以从中选择咱们所需的最终结果了,来select,然后from啊,那我们看一下我们都需要哪些字段啊,咱们主个选一下来,把上面的字段盯一下来,CTRLCF3钉在这。来我们照着选一下啊,首先第一个字段DT啊,这个是不是写一个常量值就行,来咱们杠零六杠幺四没错吧,那就来下一个应该是upon ID,没错吧,再往下呢,是coupon name对不对?然后再往下,呃,下边呢是start date对不对?Data,然后这一字段呢,我们没有起别名,咱现在去起一下啊,应该是它对吧,CV,好,那我们继续啊,那再往下呢,是real name啊,没问题,下一个下一是get count,哎,咱有,然后呢是order I count,然后继续下边呢是exp count,对吧?来继续啊,那下面的是order original amount啊,我们再往下走,Order final对吧啊,Final amount后再往下呢是reduce amount,对吧,应该是older reduce amount没问题,然后再往下,再往下是不是这个reduce rate啊,对吧?Read啊,Reduce read啊,那这个我们也没有起别名对吧?来CTRLC咱起一下啊,应该是这个字段对吧,CTRLV啊好了,那我现在就已经拿到咱们所需的所有字段了啊,然后最后一。
11:27
这小点大家别忘了啊,这个需求要求我们统计的是什么?是最近三十日发布的所有的优惠券的领用情况和补贴率,对吧?这个最近三十日咱们是不是还没有处理啊,对吧?在这儿呢,我们需要去过滤一下啊来,我们在最后边过滤一下就可以了,来来都底一下,那下边咱是不是需要写一个where啊对吧?Where谁是不是where start data它应该是大于等于30天之前的日期啊对吧,应该是data,艾完了之后呢,把当天日期发进去,2020杠零六杠幺四完之后呢,减一个29啊,那这样一来我们就能够拿到最近30天发布的所有优惠券的信息了啊好了,那这个需求到现在为止咱们基本上就已经完成了啊,那最后呢,再来一个insert啊insert allright table应该是ADS层的coupon states对吧?那后边select星啊,From ADS coupon states,后边的再来一个union就完事了啊好,优惠券统计这个需求就完成了。
我来说两句