104_尚硅谷_业务数仓_用户购买商品明细表（宽表）

好，接下来呢，我们看一下需求三，品牌覆盖率啊，品牌购率，那这个呢，比如说需求这样的，在以月为单位去统计，购买两次以上的商品的用户有多少，比如在这个一个月时间内有多少人他买了两次啊，十号买一次，20买一次。啊，这叫复购啊，复购好，那我们来看一下。像电商里面这个复购率问的，呃，这个做的比较多，那这里面是某一个品牌，那这个品牌呢，是属于它的一级分类，以及它分类的这个名称是什么啊，在这里面，这里面就决定了这个唯一的一个商品，然后呢，针对这一个商品，它被第一次购买的人数。比如说品牌A他被第一次购买有100人，那品牌B呢，第一次购买就有200人，那之后在这一个月内第二次购买，或者两两次以上，那针对品牌有80，品牌B120，那么它的这个复购率就是80除以一百百分之八十，一百二除以两百百分之六十，啊这是两次，那之后呢，同时购买三次及以上的人数，那50是不是，那么它的复合率呢，就是50除以140除以200。

啊，那他复活率就有20%啊，就这么一个过程啊，算这个转化率啊，复活率好，那这个有什么思路吗。这里面主要涉及到了这个购买多少次的人数，购买多少次的人数。啊，那只要算什么什么人数。正常情况就用的就是萨意啊，这个很关键啊，萨姆一先判断，然后再求和，比如说判断大于两次啊，义五判断大于两次我记忆啊，这个这个呢是大于三次我记忆啊。

就OK了，好，那还要分析一下整个这些字段当中又要用到哪些表的，第一个用户的表啊，你说什么用户，针对这个用户，他购买它几十几十几次，所以说要用到这个用户表，同时呢，还要用到这个订单明细表啊，具体的这个下的订单，它的品牌呀，这还有这个啊，不是这个这商面表里面是包括这些信息对吧？啊这个明细表啊，还有订单一些信息啊，订单的一些信息啊，这三张表。好，那之后呢，DWX层首先呢，要聚合一张宽点啊，围绕这个这一个宽点，把这三张点聚合在一起。啊，然后之后adx层最终开始出泡表啊，那d adx层的出报表主要难的地方就是大于等于一次，那是啊，大于等于一次是这个吧。啊，不对啊，它是算这个人数对吧？啊，大于等于一次的人数啊，大于等于两次的人数，三次的人数啊，还是刚才说的啊，这个购买过几次的，这个人数怎么算，这是一个难点啊，把它搞定就OK了。

行，那这是复合率的一个分析，那下面呢，我们来看一下。看这个字段很多啊，其实呢，并不难啊。首先看一下这个报表，这是未来我们要做的一张宽表，包含了政策知道啊，前面像什么用户ID，商品ID，用户姓名，用户年龄等级是什么表，用户表吗？啊，用户表user之后商品价格，商品名称，品类三级分类，二级分类，一级分类，这块是是不是三个表啊，三个表那下面还有什么三面SK购买个数，当日下单次数，当日下单金额啊，订单详情对吧？把订单详表，所以说这三张表组成了这么一个宽表，那好拿过来，这下边呢，就是往这个用户行为宽表里面去插入数据，那插入数据呢，其实也是三张表的一个招引，上面这张表就是这个订单详情表，那下面这个像用户表。

三个点对吧啊，三张点进行了一个，照理只不过呢，这里面单独把这个订单详情啊拿出来进行一个处理啊，然后下面这些就是用他们的ID拼接在一起了啊，同时日期进行了一个过滤。啊，那这是拿到这些东西。那我们看一下单独拿出来这个订单详情，主要来处理谁呢？处理下面这几个字段。啊，那下面这一个，这是SQ个数，购买个数，然后还有一个order Co，那就是下单数啊，还有一个呢，Order amount来处理这三个值啊，因为这三个值需要进一步的一个均衡运算啊，所以说单拎出来他们就算了一下，那剩下的像上面这个用户点和上面点就是一个简单的聚合，然后把上面这个字段补齐了。

吃完了。啊，最终一个过程呢。好，那我们来自己写一下。这个可挺费劲的，三张大表好，那还是老规矩，既然兴趣了三张表，那你就把三张表的数据拿过来。第一个是DWD层用户表是吧。用户表。拿到这儿好。用户表一个下一个。订订单详情，订单详情。拿过来的时候，记得拿那个分区啊，因为这个分区它也算一个字段，那还差一个是。

商品表啊，这个增强型的这个商品表啊。OK，那这样呢，这三张表呢，我们就拿过来了，拿过来之后呢，下边呢，我们就做这张用户行为宽表啊好，首先看一下这个地方分区点对吧，那这边设right。STRT。In射allright黑吗？下这张表里插数据。滴滴。当月。2019杠零二杠102月十号好OK，然后嗯，我这里面是用到了。From。

From上面这张表对吧，要前面定义一个位置。为。订单详情，Detail detail。比如说先在上边提前处理好一张表，然后把它拿过来。啊，然后用它去照应啊，照应这里面是left的招应，也就说只要这个订单详情里面有，那我这边就有啊，他去life招第一张表，U in for u for，那这个是UI啊，他把专的条件啊。他的ID，那这里面还没有拿到这个ID是吧？啊先放在这，然后再left抓。下一张表呢，是。PU1份啊，商品一份好，仍然是啊条件啊，条件一会在起，主要是通过他们的ID，现在的ID呢，它还没有达到啊，我们一点点做。

From，就是这个订单详情。好防守量表，那通过这张表里面，我们主要解决这上面的字段，购买个数，当日下单次数和当日下单金额对吧？啊，那这里面我们要根据得有优势ID吗。UID。啊，那同时这里面涉及到购买某一个商品，是不是还得有一个SQD。之前写的吗？这是前下啊。抵达乱象。啊，也写了是吧，也写了啊，那有了这两只的下面第一个购买的个数，那购买个数是哪一个呀。

你是这张表，这张表找到这儿。购买热手。拿到上面。这里面SQ name，这个SQ number对吧，SQ number，那这里面需要算一下。那就查呗。好，你说这是咱拿到，那下面呢，解决一下当日下单的次数，那是怎样count的下星他是几对。看看行。好在这里，那就是它。那之后再来当日下单的金额，那下单的金额这里边啊，涉及到这个本身这个订单的价格乘以数量，对吧？啊，它的价格乘以它的数量，那是。

乘以SQ。S q number。好，这两个相乘，然后就得到了它。找他。那这里面，然后条件。哎，怎么办？再加一个SQID。OK，那在这里面，这里面加时间吗？这要加一个时间吗？DT。时间它是。啊，这这个DT是吧，啊，有个DT好DT过滤一下。等于。2019杠零二杠幺零啊好，那也就是说现在呢，就把这个订单，嗯，订单下单次数，下单金额，购买个数啊就搞定了，比如说第一张表啊处理完，比如说到了这个time detail这里面去，然后下面呢，这还有两张表正在这个准备招引，那他的条件就是靠的是什么ID对吧？啊那这里面比如说他。

就它它点U之I Du等于等于它的UI的ID啊，UI是谁呀？U音符，U音符在这呢，是不是它的字段是ID啊好，它的ID。然后按的条件，他们的日期最好是不是也是今天呢？啊都是今天，那也是它。我这里面有日期。应该是不是它啊，是UI的日期吧，ui.dt等于2019杠零二杠幺零，OK，然后下一个条件，下一个条件呢，仍然是它。

点uidd等于这个得写一个别名SI，变成别名SI点看它有啥啊。有个ID吧。商品的ID是吧。这个吗？这个不是吧，就是就是ID前面这个吗，前面。这个用我用的这个是吧，下面那个下面那个他呀。对是是这个是吧，对，这样呢会更靠谱一些啊OK，然后呢，按的条件，按的条件是SI点还有DT吗？应该有吧，S这呢dt si.dt。

等于219杠零二杠幺零啊，那这样呢，就把这个三张表呢，就终于照应起照应起来了，照应起来之后，剩下的事就简单了，就填这些字段啊，一个一个填，比如说第一个硫剂。那就它点6ID啊，再来SKYD，那还剩太阳吗。1.2CPU。完毕。再继续下面呢，是这个说性别，性别是在用户表也是UI表里面。用户名点站着。好，再来之后呢，是用户年龄，那用户年龄这块他给的是生日吧，解释birthday好像是，嗯，这birthday，那么既然他是birthday，那这块就得算一下他多大，比如说这个给你birthday，那是1986年啊，那现在呢，是在2019年，那你要算一下这其中有多少个月，然后再除以12啊，就是它的年龄知道吧？好，那这里面用到了一个函数叫between是吧，Th moth s bewk，那这里面第一个日期是。

今天2019杠零二杠幺零啊，然后里面呢是birthday birthday是UI点。不提车。Birthday。好，然后把它去除以12，哎，这一个啊，这块要算一下，好，那下面是用户等级，那仍然是UI点等级等级。

这有事来吧。那再来下一个呢，是用户的商品，商品价格是吧？商品价格得是SI点一在哪呢？价格price对吧，Price。所以说这里面剩下的事就是一个一个对，那在之后I之后下面是商品的名称SI点商品的名称SK内。再往下下面呢是品牌idi，点这里面是不是t m midd t m midd好，那再往下下面呢是商品三级分类。我天呐。三级是他吗？

然后二级today。SM点。一级分类。好，然后三级分类名称。这就是字段头，你说你之后你在企业当当中啊，有些字段就是这样的啊，他没用，但是给你放在这了，那你必须得给他加上。SI。好，然后下一个呢，是商品SPUPUID在在这里面啊对吧，这是S里面。S点知道吧，然后之后下面的是购买个数，那就到了它的台了，购买个数它点它SQ3。再继续。Time。第六，第二。

然后下一个呢，是这个订单下单啊，下单次数。再来。第。奥特曼。知道吧，啊，作品啊，非常难记，其实这里面的逻辑呢，就是三张表的一个照应啊，然后呢，每张表负责各处的制造啊，这么简单，只不过这里面呢，是呃，这个time detail，也就是说订单详情里面解决的这三个字段啊，其他字段都是正常的啊，贴过来就OK了啊，看着挺多，其实呢，不难啊，来，我们来写一下。OK。然后呢，向里面插入数据。OK，穿到一边成不容易一样，23杠七啊，来看一下啊，终于有日月惊喜。

哪呢，他没有这个东西吗？我看I里面是啊，直接哎呀粘的。那就删掉呗。来交他一个大招。Out加shift。看到了吗？嗯，对，这是跟一个学生学的啊，每个班都有这种大什么对不对吧，就是放在这统一操作，你看after加shift，然后按住上别动，然后鼠标这往哪一拖行统一处理往前。非常非常好啊，这个啊。走。因为因为这个技能，我我给同学引这个技能还是很可以的啊。

提高收入嘛，你看刚才那个，呃，上午上午是那个魏琪魏老师，他那个提那个bug，就是换两行那个数据嘛，我那两行数据每一行都是几十个啊，如果要是一行要粘，那老费劲了，我就刷一下搞定。这很快啊。科技是第一生产力。这个新技能不能懒人创造。聪明。聪明，查一下节目吧。啊，那我是因为那个咱照那个数据不行啊对行，那这个呢，就是呃，DW省用户购买商品明细就搞定了，搞完之后呢，下面呢，还要把它做成这个脚本啊脚本。表情。

就这个宽表是比较难的啊，像我跟你们说过吧，在企业当中做这么一个宽表大比咱的大啊，可能表是再加个二二十多个多长时间。哎。对，一个月啊，一个月，咱们娱老学员就做这个东西做了一个月。就给他一个月的任务，因为他刚去公司啊，一方面他也要熟悉这些表的业务阶段，对吧，嗯，这是很正常的。你想每个每个字段的含义，你是不是得略微了解一下啊，方便你之后去分析一些指标啊，啊，包括这张表是干啥的，而且这时候他没有一个详细的文档，需要你干什么，需要问问啊，问的时候是你最痛苦的。

想张嘴也不敢张啊，不敢张，不不问呢，就难受。这时候心里是。对，没办法，那这时候怎么办呢？不老，这老员工不爱搭理你啊，我滴答滴答告诉你啊，人家没有，每个人都有自己的活呀，对，洗脚走起是吧，就那么简单。你告诉他你去哪我都是住对吧？你坚守我坚守，你住哪我住哪，这在保定。之前我有个同事，那是超级厉害，他是那个别人的技术还一般啊，但是是情商极高。啊，他是北航的一个硕士啊，点名他是什么情况呢？只要先来一个同事，第一顿饭一定是他请，哪怕是一碗面都是。

那你想想，你一个新员工去企业的时候。最虚是应该是你最虚的时候了吧，是吧，举目无亲是吧？啊，然后突然间有一个大哥一样的人说在你在面前说来找我请你吃顿饭，有啥困难找我是吧？然后你就觉得这哥们儿真好，什么是心情暖了，然后以后呢，你有啥事愿意跟他一说，那久而久之，那整和这个公司，那跟他的关系都很好。那他的这个关系好了之后，那你想想老板。敢动他吗？动完他，他把所有人都带走了。是吧？这么简单。行，那这个呢，就是这个ADS层导出空表。