00:00
来看商品主题,商品主题咱们还是先看DWS层啊。来往下拉。CTRLC先把键本L语句粘出来,CTRLL啊来咱们看一下啊,那商品主题我们看一下,那这张表啊,我们首先先明确啊,它每行数据应该是什么。啊,是不是应该是一个SKU这一天当中的各种行为对不对啊,一个SKU这一天的各种行为,诶那一个SKU也就是一个商品,一个商品它能有什么行为啊,诶他肯定就是被什么东西,你看是不是全是倍呀,对吧?一个商品被下单次数,被下单件数,被下单金额,被支付,被退款,被加购,被收藏,被被评价对吧?啊全是被什么啊是这样的啊然后大家可以看到这张表其实相对来说呀,还是比较规范的啊,这张表建的还是比较规范的啊,是不是跟商品相关的所有时表的度量值它都有啊啊都有这是比较规范的啊好,那这张这张表我们已经呃相当于分析完了一行数据是什么,然后表当中字段大致有什么,咱们也看了一下子,这是这张表,那这张表呢,它也是分区表啊,每天的分区当中就存储了当天啊,就是相当于是啊被怎么怎么样的这些商品呗,啊存在当天的分区里啊这张表好了,那这张表咱们讲完。
01:22
之后,接下来就分析分析怎么往里边导数据吧。啊。DWS很简单,其实它它的数据来源是不是就是当天的数据啊,对吧,所以说我们只需要怎么做呀啊,去分析每个字段来自于这张表,然后呢,分开去求,求完之后给它拼到一起就完事了呗,对不对啊,就是这样的啊好,那现在咱们来分析一下啊,我们每张表分每每个字段应该来自于哪张表。Count。欧德尔曼number和欧德尔曼这几个字段是不是都是跟下单相关的?它应该来自于哪张表?订肯定是订单什么表。订单什么表订单试试订单in份O以后行不行?
02:03
这个不行吧,因为order错当中是不是没有记录商品信息啊,对不对,哪张表里有order detail当中记了哪个商品被下单了吧,对不对?所以说你要想统计跟商品相关的信息,是不是肯定得是older detail也是订单明细时表啊,啊,这个搞清楚,那下边分析这个refund pay payment咱们先不看啊,看refund refund应该来自于哪啊。它在于哪发是不是退款呀,对吧,退款是不是肯定得找退款事时表没错对吧?啊来来看下边,下边是评价。啊,这评价这个去哪找啊。评价是表白对不对啊,那所以这个也简单,那我们呃,给大家把这几个圈出来,还还还剩下几个,然后我们来看看剩下几个跟他们有什么区别啊,我们先看这俩吧。Cart和fe一个是加购次数,一个是收藏次数,对吧,那同理,这个它是不是也是。什么样的呀,是不是也是统计的次数啊,对吧,也是操作的次数吧,操作的次数咱们这就不能去这两张事实表里拿,因为这两张时表咱们没有记录明细,明确的那个每次操作对吧,没有记录明细的操作,所以这个也得去那拿啊,对还是X log里边拿啊,那这个有点特殊啊,一会儿咱们再说,接下来咱们说谁说他。
03:23
Payment支付相关的统计,哎,问一下啊,咱们支付的时候啊,咱们讲支付的时候,那支付跟商品有关系吗?支付时表跟SKU有没有关联?没有关联,咱们前面说了一次支付是不是只关心的就是你的订单金额是多少啊,对不对,我不关心你这里边儿有什么商品,所以说我们,哎这几个字段呢,我们肯定是不能来自于那个payment实时表的,对不对,支付时表当中这几个字段是不是肯定拿不到啊。那拿不到的话,那你说。应该怎么办?
04:01
啊,支付时表里拿不到。那你要从哪拿?错哪了?哎,有同学想了,从订单表里拿呗。对不对,只不过你从订单表里拿的时候呢,你需要去注意一下,你得去找什么样的订单呀,得找那些被支付了的订单吧,对不对,那所以这个其实他的拿法跟上边这个下单的拿分其实很像吧,应该啊,只不过就是下边可能会多一个过滤条件,是不是这个道理。啊,是这样的啊好,那这个咱们大致就分析完了,我们需要来自于这样的几个字段。那来自于这几张表啊,几张表,那现在咱们就来写一下吧,啊,咱们写一下来先看这几个,这几个是不是应该来自于一张表啊,来自一张表,我们就一个circle,把他们几个全部求出来啊,哪张表来自于哪张表?订单明细,那所以说select from dws层的应该是older detail对不对?呃,什么DWSDWD层啊,来fact o detail啊,然后日期当天等于DWS,我们统计都是当天是吧,2020杠零六杠幺四没错吧?好,那接下来继续往下走啊,那接下来接下来怎么写,数据拿到了怎么写,哎,Select怎么写,然后下面需不需要分组什么的。
05:18
怎么做?咱们要求的是每个商品被下单次数,被下单件数和被下单金额吧,是每个,那所以说得怎么办,只要涉及到每个就得分组吧,啊按谁分组,按照SKUID分组吧,啊那上面我们就可以选了,那选一个SKUID。啊,问一下啊,咱们现在分组,把什么样的数据分到一组里边了。是不是啊,就是下单商品相同的数据是不是分到一组里边了呀,对不对,那所以说我们要统计是每一个商品被下单的次数,件数和金额,那次数怎么办。是不是一行就是一次啊对吧?那数行数count星是不是就是次数啊,那件数和金额是不是求和就可以啊,那所以这呢先求这个次数,那次数就是count行啊,那完之后呢,件数件数就是sum求和,那对哪个字段求和是不是有一个SK number啊啊,这是被下单的件数,那还有什么呀,被下单的金额,那是不是也是萨步求和啊来萨求和,那当然求和的时候呢,我们那里边也有多个金额对吧?那在这边我们还是按照最终金额算啊,那就是final amount。
06:31
诶,那这个就就完了,这相当于已经拿到了我们上边所需的几个字段呢,Count number和amount,我把这个别名起一下啊,哎,这个就是older Co啊,那这个是older。啊,这个number啊,下边的是order amount,这样没问题,好搞定,这是前几个字段,好,那咱们紧接着往下走啊,再往下。再往下看谁了?再往下该支付了吧,对不对,刚才说了支付这几个字段呢,我不可能去支付实时表里求,因为里面没有商品信息,所以我只能从这张表里,对不对,然后呢,咱们要做的只是什么呀,将。
07:10
这张表当中,今天支付的订单是不是得给它过滤出来对不对,然后你统计出来的是不是就是被支付的次数件数和金额了呀,对吧,那怎么去过滤当天支付的订单。啊,是不是加过外过滤条件呀,对吧,那也就是咱们需要在这个位置是不是得加一个外二过滤条件啊,那怎么加。And。安的什么呀?怎么怎么做。那咱们是不是得先找到我们今天被支付的订单呀,对不对,今天被支付订单怎么找,这个从哪去找。今天被支付订单去哪找?是不是写了SE,然后呢,From from今天被支付的订单,这个去哪张表里查。支付市值表里边是不是就是存储了今天被支付的所有订单呀,对不对,所以说select from DW层的fact payment info这张表当中,你要知道啊,它有一个什么字段,有一个older。
08:13
ID这个是不是就是你支付的订单ID啊,对不对,那你把这张表当中啊,今天这个分区的数据过滤出来,你得到的是不是就是今天被支付的所有的订单的ID啊,零六杠幺四好,那我们只需要怎么办,从这张表里它的这个分区里边把这些订单找到是不是就行,对不对,怎么找到。这应该是好几个吧,啊,那这个应该怎么怎么加这个过滤条件呀。In呗,是不是应该是in呀,对吧?来我们在这写一下,那就是and什么order ID是不是in呀,In啊in in在in下边这个紫查询,那CTRLV啊,我们格式化一下啊。来这个踏一下。
09:00
这个推一下。嗯。好,我们这样呢,往前推好,那这样一来的话,我们是不是就能够求出来今天被支付的商品的次数,件数和金额呀,哎,搞定了啊,就这么整一下呗,其实这个逻辑跟上面一样,就是对这个订单做了一个过滤啊,啊这个咱们得搞清楚,行,那这块结束之后呢,我们再往上看啊。再往上看。哎,咱们这需不需要考虑一个问题啊,咱们来琢磨琢磨,咱们琢磨琢磨,嗯。咱们琢磨琢磨看这会不会有什么问题啊,这么写的话,我思考一下啊,呃,那咱们在这儿写啊,你认定啊,呃,在这儿我们获取的是今天支付的所有订单,对不对,是今天支付的所有订单,对吧。啊,完事呢,你要思考一个这样的问题,你今天支付的订单。有,有可能是什么时候下的,是有可能是今天下的单,也可能是昨天下的单。
10:02
是不是是不是有一个这样的问题啊,咱们刚想到啊,诶为什么?因为咱们都知道我下单之后到支付啊,这个这俩这两个这个动作之间其实是有一个时间的,对吧,多长时间,一般情况下30分钟或15分钟对不对,超时之后这个订单是不是自动取消啊,对吧?啊,但是它有一个十几分钟或30分钟的一个时间,那所以说是不是会出现这个这样的一个现象,就是下单这个动作和支付的一个动作,他跨天了对不对啊,对不对啊,那所以说你今天支付的订单。可能。是不是昨天下的呀,可能昨天下的,可能昨天下的,那你说咱们这过滤的时候,我是不是只把这个14号的订单过滤出来了。对不对,那你支付的昨天的订单是不是在那儿就找不到了呀。对不对,那所以说这样呢,就有点不太准确了,这你得改一下吧,这怎么改一下哪。是不是得把上边这个older detail这张表的过滤条件,是不是咱们得给它宽限一天呀,对不对,是不是得把13号也给它查出来,也查来之后,那我是不是就能找到咱们今天支付的所有订单了,哎,你今天下的单,你今天支付的我能找到,你昨天下的单你今天支付的我也能找到,对不对啊,那所以说这块咱们得改一下啊,那改哪主要是改这个位置啊,那就是哎暗的,呃,诶不应是暗的啊,应该是啥呀。
11:22
应该是二吧,对不对,应该是二啊,应该是两天都行啊2DT哎等于是不是得获取昨天日期,昨天应该是date爱啊,那要把今天日期放进来,2020杠零六杠幺四,然后逗号呢,我们减一个一啊就行了啊减一因为你今天支付的订单,它可是不是只能只可能是今天或者是昨天下的单,不可能是前天吧啊因为前天你是不是已经取消了啊那个订单,那所以得注意啊,那这边因为这是二这儿是暗的,所以咱最好怎么样啊,把这个二这边呢,咱们用一个哎括号给它括起来,那是不是有这个优先级的问题啊来咱们给它括一下。
12:00
好,这个应该没问题了吧,啊这个就没问题了,这个大家一个小细节,大家注意一下就行啊行,那这个完之后呢,我们再接着往下再看几个啊呃,支付完事了,是不是该这个退款了呀,对吧?退款这个事很简单,咱们只需要去哪张表,是不是从咱们退款试值表里去拿就行啊退款试表里边一行数据就是一次退款记录啊,那所以说我们直接select select,然后呢,让我们DWD层的,诶effect啊,Refund in inform啊,然后把当天的日期,诶这个过滤一下,零六杠幺四啊,然后后边呢,我们需要格入外线SK1ID啊,那上面呢,选择SQID啊,那如果说我看得清,那你求的就是啥。就是退款次数啊,那如果说我求和sum求和,那呃有一个应该是有一个这个refund number吧,Refund number,那这个是不是就是退款件数啊对吧,那再三下,那还有一个refund的金额吧,Refund amount,那这是不是就是退款金额。对不对,是不是搞定跟上边咱们所对所需要的那几个值是对应的啊,对应上了啊,就这么求呗,很简单啊,来接下来剩下几个,剩下几个呢?还有还有还有这样的两个啊呃,然后他俩这个怎么求看一下。
13:13
啊,他俩刚才分析了,它得来自于咱们那个action log,对吧?得来自action log啊来看看这怎么去获取。啊,Card count count咋获取啊?Card count count,嗯,咱们先分开求吧,先求card count啊,咱们找到action log,找到action log啊,找到它。嗯,好,那这张表里一行数据是不是就是一个action,就是一个动作对吧,但咱们这要需要的应该只有什么样的呀。要加购物车这样的动作对吧,所以说首先我们需要把这样的动作是不是得先给它过滤出来呀,对吧,先过滤出来啊,OK,那假如现在过滤出来了,那接下来咱们怎么办呀。是不是得按照某个字段分组,然后进行count呀,对吧,关键是按谁分组。啊,咱们按谁分组,这是关键。
14:01
按谁分组啊?SKD,你看这里边有SKU吗?有SQ吗?啊,这是SQID对吧,但是这是ID吗。你得看这个字段是什么意思啊,这你就必须得了解这个表,你才能知道怎么做了啊,这四个字段是不是就是咱们那个action的四个字段呀,对不对,那第一个字段是XID,也是动作ID。对不对?你的动作是必须得有一个对象对不对,那这个是啥?是你的动作对象的type是类型啊,你这个SD指的是什么?指的是我这个对应对象是一个商品对不对?那真正的商品ID应该是谁?应该是item。对不对啊啊,也就是说SQID等于八啊,也就是说你真正应该分组的字段应该是谁是他吗?不是应该是谁艾特,没错吧,这才是你的SQID啊好,那这个大家搞清楚啊,完了之后我们把这个词给我写一下来,我们往下走啊诶。
15:04
Select,然后呢,From from哪张表应该是DWD层的action log,然后日期过滤一下DT等于啊,2020杠零几014啊,然后呢,我们按谁分组,按item分组啊,当然得先过滤一下,没过滤呢啊按等it ID吧,等于car I a DD car没错吧,然后再往下啊再往下怎么。啊,是不是葛Y啊,葛Y谁啊,咱们那个呃,Item item啊,那上边呢,我们去选一下啊,这边我们选一个item啊,当然这边咱们可以起个别名,就是SKD,没错吧,那下边的我们看得清啊,那这个得到的是不是就是加购物车的次数car count呀,有问题没错吧,把这个搞定了。那它搞定之后,那剩下是不是还有一个favorver,哎,那个次数啊fair count,这个favor count是不是跟它是一样的呀,对不对,咱们只需要怎么做啊,只需要找到那个action log,我找一下啊。
16:04
挨个跑哪去了?那就这啊,这回咱们是不是过滤条件变一下就行,哎,咱们不要不要这个card了,我们要谁要fair at对吧,那所以这个呢,好写啊,只需要以它为基础,CTRLC拿过来。CTRLV把这改一下就行,把这个favor。下划线ADD是不是搞定啊,来favor诶搞定了,哎确实能搞定,但是你会发现啊,这俩circleo是不是很像啊,而且呀,你会发现他俩呀,我查的表是不是一张表是不是一张表,然后呢,我分区是是一个分区啊,然后呢,我做的操作是不是也是一样的,按它分组。对不对,然后进行一个抗统计。啊,那相当于我们读同样的数据,然后呢,做同样的分析计算对不对,只区别只是哪不一样。
17:00
只是过滤条件不一样嘛,对不对,那那这样一来咱们能不能想办法啊,就是说哎,可以不让它俩分开算,因为你分开算相当于是不是在做重复计算呀,对吧,从同样的表里做同样的操作啊,那我能不能想办法用一条so狗给它求出来呢?啊,怎么做,有没有想到用谁。沙漠。啊,If服是吧,啊,可能这会儿大家一下绕不过来啊,可能一块绕不过来,然后大家看跟我看一下啊,看这会他可以怎么做,我实际上可以这样去做。来比如说我以它为例啊,CTRLCCTRLC啊来咱们给它放在这儿啊。看一下,那这次我因为我要通过一条circle狗把它俩都求出来对吧,那所以这时候我我怎么办啊啊我这儿呢,我就不能只写一个了啊,我把它俩都求出来一下啊a in对吧,咱们用in这词啊,In什么呢?一个是哎favor ID,那CTRL为一个是car ID,一个是哎FAVAD,我是不是可以一次性的把这两个操作的啊,这个两个动作都都找出来呀,把两个动作都找出来完了之后呢,我怎么办?我这个count我就不进行直接count了。
18:12
对不对?那这我怎么办?我相当于做一个有条件的count。对不对,那有时候那怎么做呀。来,上衣啊上衣。来,那如果说我的哎XID等于谁等于CD cart ID,那我就怎么做,我就记个一,否则记记谁记六。哎,你得到的是什么?是不是就是还是cart a的次数啊,对不对,那同样的道理,CTRLD啊,那把这个card a改成favor a fav,那你你看这个得的是不是就是哎非玩I的次数对吧?你看这样一来的话,数据咱们只读一遍,然后呢,计算也算一遍,就分一次组嘛,啊也是这块呢,我们能减少这个也就是重复计算嘛啊是这样的啊,而且你加入重复计算之后呢,你一会是不是还可以少join一下呀。
19:06
对不,因为最终咱们这个是不是要拼到一块了,对不对,那这块咱们还可以少转一下,是这样的啊好,那这就是我们这张表的啊,这个一个思路啊,一个思路啊,这个some if呢,我们后续可能还会经常用到,还有经常用到大家体会一下它的这个这个作用,就一下这个作用,其实我们嗯就是可以大致简单总结一下,就是什么情况下咱们可以用到这个some衣服啊。什么情况啊啊,就以它为例啊,就是假如说首先我们得满足这样的一个条件啊,第一个条件,首先我读的数据是不是得是相同的,相同的表,相同的分区啊对不对,然后呢,你所做的操作是不是也是一样的,都是按照相同的字段分组,然后区别仅体现在什么地方。区别仅体现在VR过滤条件上。对不对啊,这个时候咱们是不是就可以给它转成some if啊,为啥?因为你区别仅体现在where上面,那所以说咱们这其实相当于什么呀,是不是把那个where放到if里边了呀,对不对,是这样的啊。
20:06
啊,你可以这样去,哎,总结一下就是啊,区别仅体现在wherer。过滤条件上的时候,你就可以把这两个circle给它并到一个里边,用sum if去做啊好,那既然咱们有some if了,上面这俩是不是就可以不要了呀,直接去掉就行啊好了,这个搞定,那这边起个别名啊,它叫car啊,Car count吧啊,它这个呢,叫做fair count。Favr啊,这有问题,好,那这个也完事了啊,剩下还有最后几个字段啊,这边是啥呀,是。好评中评差评默认评价啊,那这个应该来自于哪张表,来自于评价时时表啊?呃,评价时表的字段呢,我们去回顾一下吧,可能有同学已经忘了啊,找到DW def comment in,这是评价时表,好,那往后拉啊,往后拉。那这张表里边呢,我们有一个这样的一个字段啊,叫做appraise对吧?Appraise,那araise里边呢,咱们存的并这个字段首先是什么意思来着,就是评价对吧?那这里边呢,应该存的就是好评、中评、差评,但是它存的不是这些汉字,存的是啥字典码对吧?那所以说这个咱们得知道,呃,每一个1001是什么,102是啥?得知道每一个字典码的含义啊,咱们找一下那张表是不是ods base DA啊对吧?咱们找到幺二这一部分应该是这部分呢?啊,那这里边你看一下101就是好评,102就是中评,1203就是差评,1204就是自动评价对吧?那咱们这要求的应该是什么呀?求的是好评有几个,差评几个,中评几个,末认评价几个。
21:36
那这个应该怎么怎么办呀,可以怎么求啊。是不是可以分开求,那要分开求的话,这怎么写?啊,分开求,咱们比如说先求一个好评的吧,嗯,好评怎么办啊,这就select SE from DW d层的fact comment in for吧,Co mmt,然后呢,日期过滤一下,DT等于2020杠零六杠幺四,没错吧?啊,那我要求的是好评的个数啊。
22:05
啊,那得怎么办呀?是不是得把好评的过滤出来,And a pise等于啥?等于1201对不对?是不是所有的好评记录全拿出来了,但是我们要求的是每个商品的好评次数吧,其是每个就得葛吧葛啊SKID啊,那上面咱们选啊SKUID逗号啊ID啊逗号,然后呢,Cut count清。没错吧,诶那这是好评,那中评呢,中评只需要改哪就行,这改成1202啊,差评1203,默认评价1204对不对,你看啊,那这个是不是又是可以哎使用啥了呀,Sum if,你看还是相同的数据,相同的处理,区别仅体现在哎VR上面对吧,那是这也是sum if就行,那some if怎么改啊,这块来咱们这个是不是这就不要了,这个去掉直接查出来,完了之后呢,把这个看清,咱们改成s some if啊if。
23:02
哎,那如果说啊,我的ara等于啊1201,那我就记个一啊,否则就记个零,这就是好评数呗,那CTRLDCTRLD啊CTRLD,那如果是1202,那就记11203啊120。评四,那这得到的分别就是好评,中评,差评和默认评价的次数吧,是不是就完事了啊,然后这个每个子量菌都完事了,我们这个所需的字段也就都求完了,剩下,呃,这个剩下的事儿呢,就是将它们拼接到一起,对吧?这个拼接咱这用啥呀。用啥呀啊对with声明一下这个子查询对吧?啊然后呢,剩下主要主要是是不是主要进行这个join呢?啊进行全外联呗,副al join。这个全外联没问题吧,啊没有问题,但这边呢,其实咱们可以不用全外联。啊,可以不用全外联,不用全外联我也能够把这几个字段的横向的拼接到一块儿,而且结果呢,跟你全外联的结果是一样的。
24:06
啊,再一个呢,咱们一会儿要讲这个方法呢,要比全外联的性能还要好很多。啊。哎,还有这样的方法对吧?啊,一会儿大家可以想想啊,就课下利用课下时间吧,大家自己琢磨琢磨,看能不能想到其他的方法,可以把这几个字段横向的拼接起来。啊,这个一会咱们下节课再讲吧,啊,我把视频录一下啊。
我来说两句