00:00
好,接下来呢,我们来看一下啊,5.4DWS层业务数仓的一个搭建,比如说业务数仓这块,我们要先建主题宽表。建筑的宽表,那我们要怎么去建这宽表呢?上午的时候我们说过啊,要按照维度去建对应的宽表,对吧?按建维度,也就说DWS层的宽表字段是站在不同维度的视觉去看待相应的事实表。举个例子。啊举个例子,比如说我们站在这个用户这个角度,跟用户相关的事时表,有订单实时表,订单详情实时表,还有呢,支付表表。然后有收藏四试表,优惠券四时表,评价四时表,退单试表就是八张四时表,跟用户都有关系。那跟它都有关系,那未来我们这个DWS层这个宽表怎么去建呢?比如说建成长啥样啊,你这里面要放哪些字段。
01:00
对吧,这是我们关心的。啊,是我们关心的,那有同学说这个,那你把所有的事值表都聚合在这里得了呗,把所有的事值表都聚合在这里面。那有点太大啊,太大了哈,那往往我们是怎么做呢?重点关注的是事实表当中的度量值。关注的是四时表当中的度值,还记得我们之前定义事实表的时候吗?定义时表里面只有两个啊,两个内容,一个呢是外键,另一个就是它的度量值。啊,度量值里面包括个数,件数,还有什么,还有金额。哎,这么几个,那行,那下面我们来看哈,首先我们来看一看每日会员行为。你要做这个宽的设计者设计。哎,那你是不是会站在这个用户这个维度的角度,站在你是用户,你会统计。这个用户这一天。他下了多少单?那单数不就是这个订单市值表的度量值吗?
02:02
他的订单金额是多少?对吧,他下单的订单金额是多少?好,那订单详情这个其实他俩类似,他支付了多少次。支付的金额是多少?他加入购物车多少件商品?对吧,商品的价格是多少,然后呢,收藏了多少次。次数,那领优惠券呢?领优惠券多少次?评价,他评价了哪些商品,评价了多少次退款,退款了多少次,退款的金额是什么样。哦,那原来我们马上要建的这个DWS层会员行为宽表,其实就是各个四实表的一个度量值的一个汇总,当然你要关心每张市值表里的,呃,其他外界那些相应的维度。那不有外键嘛,有外键你可以获取它相应的,呃,值也是可以的。那你看我们现在是怎么做的啊?
03:01
首先用户这没毛病,因为你是用户使用宽表,那这个用户登录的次数。登录时所关心的是我们那个表。你说启动设备那张表,哎,是我们之前的,呃,是用户行为的哈,用户行为的一张宽表其中的一个知道,那接下来往下加入购物车次数。以及加入购车金额,那相当于我们取的是这个用户行为宽表跟加购这个他们两个之间的一个关系,而且取的是什么都是可度量的值。次数。OK,那再往下,下面呢,这个是下单的次数和下单的金额。那怎么取?看他跟订单四时表关联上,我取订单的次数和金额,那次数和金额是属于订单四时表的度量值。哎,度量值这两个,那再往下支付次数和支付金额,那我又跟谁相关呢。
04:04
这跟他支付次数和支付金额相关的,同时也是他的度量值,次数和金额。哎,如果这张宽表哈,我们要设计完整的话,还需要再把右侧这些加上,比如说这个用户今天收藏了多少次。领了哪些优惠券?评价了哪些商品,评价的次数是什么,退款的金额以及退款的次数,这才是比较完美的啊,由于时间所限啊,当时呢,只是给大家列出了这么多。啊,以点带面对吧,啊以点带面,其实其他的都是一些体力活了。哎,好,那你说我们现在呢,也知道了啊,我们要建宽表,而且是这个用户行为宽表,而且里面呢,知道了,我也知道怎么去取了,取的都是通常情况下是事实表的度量值。整张纸OK,那这张表有了之后,下面我们要往这张表里面插入数据啊。那怎么插呢?
05:02
像这张表里面插入数据,老规矩老套,目标表往这一放。下面该干什么?哎,可以干什么?我这里写了一个规律啊,先找到目标表,之后分析一下目标表这里面的字段来源于哪些表。哎,来源于哪些表,然后把它准备好。那你看准备吧,这里面登录次数。要跟相关。再来登录次数跟这个DWD层。启动。是这张表吧,哎,这张表里面你看最后一个知道,诶这张表。嗯啊,我可以从这张表里面取出count一下,不就是这个相当的次数嘛,对吧,我可以从这张表里取哈。那。这有点太长了,前面这我删掉一些,因为这些呢,我知道他之后也没用是吧?啊我删掉一些也不会有影响啊行,这是大表,那再来那下面呢,登录次数之后呢,是加入购物车次数。
06:15
啊,加入购物车是数和金额,那你要找购物车的购物车DWD乘购物车。找一下。加构,看加构事实表,那把加构试表拿过来。诶,好。再往这一放,加入购物车完事之后呢,下面是下单次数,下单金额,你还需要下单的表。下单的表。这。订单明细表。写过来。往这一放对吧,然后再往上涨,呃,下面呢,还有支付次数和支付金额,那你需要找支付的表。
07:08
支付实时表。支付。到这。好,也就是说现在呢,我们就把这个表的目标表找到了,输入目标表,这个输入表也分析到了,下面开始写逻辑,那写逻辑的话就是inser right,插入到目标表的名称,然后观察是否有分区,对吧,那也是同样套路,大家看吧,目标板在这放着呢,右侧。没事了。Table。像这张表里面插入数据,然后接下来考虑一下分区,嗯,有分区对吧,因为是DWS层嘛,嗯,P r t partan dt等于2020杠零三杠幺零。
08:11
From from,呃,查询哪张表呢?我们要查询的这些表啊,要查询的这些表是不是一张表贡献一个字段,贡贡献几个字段呢?比如说这个登录次数由他来贡献,呃,加入购物车次数呢,由下面他来贡献,还有这个加入购物车金额由下面来贡献,对吧?正常情况下呢,我们可以要写的话,应该是这样去写对吧?一张表去招另一张表,然后再另一张表。这样去写,那今天呢,教大家一个企业里面的一个写法,如果你这么写张伟。哎,看起来这个呃,噪音来噪音去容易乱的话。
09:00
那有一个很很好的招数哈,很好的招数,什么招数呢?把它干掉,这里面还是放在这儿再到上面。加一个位置啊,然后tap起一张临时表,把这个查询放在上面,放在上面怎么用,比如说这个呢,是一个表哈。嗯,之前起的名字叫log in是吧?Log in,然后at这里面放上对应的纸查询,你把这这么去写和你写在下面,然后进行招引,其实是一样的,那这里有什么好处呢?就是看起来比较清晰,一会儿你知道为什么叫清晰了啊,这里面我们都是按照这个用户ID。啊,用户ID。From,还是查这张表?查他查他的什么呢?Count登录次数对吧?哎,登录次数呢,就count清。
10:05
把它拿过来这样写,这样写的话,条件的话是今天今天,但是别忘了要分组啊啊goodbye goodbye,谁呢用快递。这样去写,哎,这样写的话,其实更等同于什么呢?你是不是等同于把它复制下来,然后放在这里面,然后再写啊这样写,那这样写的好处呢,就是嗯,比较逻辑性,比较清晰,一会你就知道为什么叫逻辑清晰了啊,先别着急往这一放,这是其中一张表就行,完了你就说我这个dwd starlo这张表只负责这里面的其中一个字段。搞定,那UI所有的表有的吧,按照用户进行组,那一接下来再写一张临时表,那下一个段呢,叫加入购物车次数和加入购车金额。
11:10
叫time模块,然后起的是一张临时表的名字,那仍然是简单select查询,第一个字段肯定放的都是UID。哎,都是按照用户ID进行分组嘛,之后下一个字段有讲究了,下一个字段呢,我们要提供的是一个是加入购物车次数,一个呢是加入购车金额。你看我这里规律,遇到统计什么什么次数。Count遇到统计什么什么金额,Some。哎,就是这样的一个规律哈,那来看一下回来哈,加入购物车第一个是次数是吧,次数的话,从这里面去统计加入购物车的次数,看哪个项次数。哪项数count一下自己就是次数了吗?对吧,Count。
12:00
哎,次数是吧,加入购物车的啊,次数次数的话应该是抗性。加入购物车试手。拿回来,OK,下一个加入购物车金额。哎,金额,那金额啊,你从这张表已经没用了啊,把它删掉啊,已经没用了啊,干掉呃,它的金额,那你找一下吧,用户IDSQID放入购物车时的价格和数量。那价格和数量这两个一层不就是购物车的金额吗?哎,购物车的金额,那购物车的金额一说及到涉及到什么,什么金额要用到什么呢?Some啊记住一定要上,因为我们是按照用户ID后面要分组的,那这块呢,肯定是要some,哎涉及到钱,那沙的话,这里面两个字段,一个是购物车的价格。一个呢是购物车的数量,商品的数量,OK,那给他一个别名,别名的话,这。
13:11
OK,看还有吗?没有没有值了是吧,一共就两个字段好搞定,那from,那查询的就是这个购物车这两表。啊,然后条件外二条件DT等于2020杠零三杠幺零啊,然后分组group by u ID对吧?哎,你会感觉哦,又一张表又搞定了,这张表搞定,那这张方表核定这个这两张字段也拿下了,那这张表呢,我先把它删掉。太多了,好继续往下走,往下走呢,下面呢,是来到了这个下单次数和下单金额,哎,那对应的这个是订单,我拿的是订单详情吗。
14:02
嗯,这里面是下单的次数和下单的金额。总金额我是不是拿的应该是另一张表看一下啊。订单表呢?应该是这个。嗯。那是订单明细表哈,这还有一个订单订单表,我看订单表里有没有钱。优惠价格订单金额应该是这张表哈,刚才应该拿错了哈,这个输入表应该是这个订单的表。因为他说的是订单的信息拿过来啊。把这个替换掉,替换成它啊,这个是订单表,那行拿过来之后呢,那下面我们继续啊,继续再往下还。那这个呢,就变成了order,好的临时表,哎的话继续只查询,哎,只查询select,先写from吧,先我就查这张表。
15:03
啊产时表,那产数表里面取哪些字段呢?首先第一个字段呢,肯定是他的用户ID,这个要有的一定要有。用,那当然下面要添加哪些呢?下面要添加的一个是下单次数,还有一个是下单金额,下单次数和下单金额来看一下哪一个是下单次数。下单次数没找到,没找到就是你这个。它的次数啊,哎,没找到,不就是count清吗?什么什么次数count,那什么什么时候count,那把它拿过来。再往下,那下面就说下单的金额。哎,一涉及到什么什么金额,而且未来还会在分组里面,那还是上,那把谁放进去呢?下单金额,那找找这里面订单状态时间,原价金额,优惠金额,运费订单金额对吧,订单金额就是final total amount拿过来。
16:05
往这一放,那这呢就是总的一个下单金额。咔,往这一放。看就是这样的一个过程啊,也就是说这里面这张表里面负责提供两个字段,一个是下单金额一呃,一个是下单次数,一个是下单金额,OK,那别忘了加上这个限制条件,DT是今天的,哎,今天的,然后呢,别忘了还有个分组。不by u ID,哎,这个判断从DW层哪张表就是靠字段匹配嘛,我们的数据你看我们现在要往DWS层里面灌数据。那你去哪一层数据啊,咱们最底层ods,再往上DWD。对吧,再往上DWSDWS上所有的数据都是从DWCD来,哎,从这往上来,那你未来你要写DWT数据哪来啊,从DMS来是一样道理,就是你的数据源只能去DWP去找他,你不能平级去找。
17:13
啊,不能平级局长啊。啊,不是靠字段匹配啊,不是靠字段匹配这一块,还这不是靠字段匹配啊,这这还没完事儿呢,你现在呢,只是把这些字段的值我们已经给覆盖上去了,他不会去自动去找他。啊,自动去找他的需要你在这这select的这块进进行后续的一个修改,所以说别着急,现在呢,只是只查询,把所有我们想要的东西先都准备好啊先准备好啊行,那这个是time他再来。再往后,那再下一张表呢,是支付相关的信息,对吧,那这张表可以删掉了。哎,支付表,那支付表是他嘛,那就叫payment嘛啊payment然后as啊呃s select from数据来系他。
18:10
OK,然后条件先写这个也行啊,DT0310好,呃,然后呢,BY对吧,仍然是UUID,所以说第一个字段你找一下。都是用外地啊。OK,用户ID往这一放啊,别着急,那这里面一个呢,是支付次数啊,什么什么次数。来吧,哎,会养成习惯的是吧,哎直接靠的心完事,就把这个支付次数拿过来,那再来什么什么支付金额,那找啊找。找哪一个是对应的金额呢?支付金额对吧?哎,Payment amount,那你就需要用到啊,把它放进去。
19:05
好把它拿过来对吧,这样呢,就把所有的字段呢,全部找到了,你说所有的只查询同学应该诧异了啊,我这找到他有啥用啊,他们这几张表,哎呀。啊,这几张表之间目前还没有关系呢,一张表两张表,三张表,四张表是吧,20张表没有关系,那就需要在你后续的这里面把他们的关系对应的上,你说刚才子阳说的,那你这写了半天,你是不是就靠这个名字跟他匹配呢?当然他不知道,他不能按这个名字去匹配。那需要怎么匹配呢?哎,别着急来,这里面用到语法就是select from啊,From谁呢?第一张表。太。然后在这里面加上什么呢,第一个呢是用户ID问题。
20:03
第二个,下一个呢,是love count'拿过来。也没问题,那从这张表里面其他值你就取不到了,怎么取零。看好了,我先洗。就是在企业里面是怎么写代码的。叫控位不灵。这样呢,看起来呢,就是思路非常清晰。每一张表负责提供他所需要的字段,你提供不了的五零。啊,不需要装异常状。你看这第一张表里面,哎,我从这个time login里面提供了一个login count。和user ID user ID是手表都有的,对吧。
21:02
哎,那其他位怎么办,补零啊啊,其他位补零。那他跟下一张照怎么做呢?Unor unor不知道大家还记不记得啊,它是相当于哎,你这张表跟这里面unor之后,它的也有,它的也有,哎直接放在一张表里,哎,再UN再UN,所有的聚合在一起。那下一张表怎么写呢?哎,Select from,这也是。Time看,那从color里面取出哪些值呢?简单卡一复制拿过来,拿过来之后,首先它这里面提供不了登录次数变成零。Car的次数是可以提供的。卡金额我也是可以提供的。搞定了,哎,就这么快啊,那再来,那他下面要干什么呢,用尿。
22:01
都是拼接再来那就是select from,哎,Time card之后呢,是time order,对吧。好,那这里面把这个值拿过来。只需要修改一下,怎么修改呢?上面这个零啊,那这也是零,这呢也是零,我这张表负责提供两个字段,一个是下单次数,一个是下单金额,OK,搞定搞定。那再来UNIT2 select from,一个是time order是吧,那time payment应该是。对吧。然后中间这块复制过来。粘贴搞定,搞定之后放零。
23:06
放。零之后。干掉。干掉,哎,这样呢就OK了啊,这样就OK了,数一下表数啊,一张表,两张表,三张表,四张表啊四张表,然后呢这呢,我们统一给它起个名字啊。Us又是X又是X,这张表行,那后面呢,要加一个group by哈。因为我们所有的都是按照这个用户ID。进行分组的啊,把它们聚合在一起之后,再用这个ID进行分组啊,就是先先用那个进行分组聚合啊聚合,因为这里面啊得出的值呢,是什么样,长什么样哈,长什么样。
24:01
呃,长的样子呢,应该是你这里面,比如说这是用户ID有值。其他的都是0000用户ID,然后呢,再来一个用户ID。啊,0000,到最后这个pay的这块,什么二啊,什么三有数啊,这一个卦四五啊类似这样有数,然后再往下啊再这样七八。那日后我需要干的对,干一个什么事呢?比如说这个payment amount,我会把相同的ID的数据,这不都是一号账号的吗?聚合在一起,用ID聚合在一起,聚合在一起之后对它进行一个累加,那累加用什么呢?上。哎,就是一个累加,比如说这里面这个用户支付的次数金额,哎,就是1SUM就行了,把它加在一起,那行,那上面这块具体怎么写。啊,这么写其实就是some啊some啊,ID应该不用啊,ID不用上啊。
25:01
拿过来之后登录次数,那登录次数就是上啊上上谁呢。登录次数,那现在这个顺序,你这个处理这个顺序就要跟这边一一对应了。哎,就要一对应了,就是刚才子阳说的这个是不是按照这个表明字去对的,不是还是按照这个顺序去对的。啊,按照这个顺序,一个对一个哈,一个对一个,你看一下我这表里面,嗯,DWS。前面都是采用这种只查询的方式啊,只查询的方式,这是企业的一种标准用法啊,后来给你们看一看,然后这里面呢,是这个啊,这这不是sum吗?Sum这个登录次数count啊,还有这些值啊都可以哈,只是前面加了一个前缀,它这个我们一样哈,其他都一样,那我拿过来哈。都拿过来一个。
26:01
你说这块呢,我这是登录count,在前面加了一个user action,不是他吗。对吧,啊,刚起的名字哈。悠着艾克森斯啊,艾克森斯,我就艾克森斯吧,艾森斯,嗯。啊,这样代码就写完了,写完之后呢,我们稍微捋一下这个思路啊,捋一下这个思,那这种写法呢,其实是企业的一种标准写法啊,无论是像什么这个呃,饿了么呀啊,还有这个像这个啊去哪儿网啊,以及呢,还有这个,咱还有一个一家公司哈,都是这样的,我给大家看一下这个企业的代码哈。大家看一下暂停回来啊,好,这是这一块啊,让大家长见识啊,那我们来测试一下吧,看看这个效果怎么样是吧。
27:00
那这边。所以说我说这个东西啊,不难啊,真的不难啊,就是比较多刚开始的时候呢,会比较懵,但是呢,如果你在学校的时候,如果能体现出嗯,这么几十张表咔咔在一起超这个处理的话,那你们到了企业的时候,我告诉你们没有这么难,企业里面代码没这么难啊,咱们这个目前这个难度呢,是超过于企业的。我见过很多这个企业代码都是比较轻松加惬意的哈。拿过来,拿过来之后是这里吧,是放在这里吗?不是吧,这个是。别搞错了啊,这是我们刚才写的,应该是。是吧,没事啊,先先测试一下,报错了咱们再调啊,报出再调OK。诶,一遍成了啊,不太好意思啊,不太好意思,既然成功了啊,我希望大家啊,这个要要多写几遍就有这种感觉了啊,有这种感觉之后,你会发现这写起来好像也没这么难啊,对吧?嗯,你们在企业里面号称哈写过十写多少行呢,几千行。
28:12
几千行代码怎么来的啊,几千行怎么来的,你看我这里面轻轻松松,这不是100多行吗?啊100多行,那如果是再扩大个十几倍的话,你是十几张表,那不就1000行代码吗。哎,所以说写色Q你的老学员有的候跟你说,哎呀,我们公司写色Q那个老长了啊,1000多行的一个circle口啊,其实从这个逻辑上角度来说没有难度啊,上面就各种位置time wait time,一对位time啊,剩下的事儿就考考验你的一个眼力和手力了啊,练你的眼睛是吧,得得对着啊,一个一个对啊,这尤其这块别对错了。其他都不是问题,哎,执行完了哈,挺快,完的话我们查一下结果。查一下。
29:02
啊,不好意思啊,一下真成功了啊,那这个呢,我们这张表呢,就已经轻松的啊,搞定每日会员的行为啊,每日会员行为。
我来说两句