00:00
好,那接下来我们把第二个需求路径分析实现一下啊,我们先来分析一下它的思路啊,前面咱提到过啊,咱说用户路径分析的可视化呢,通常使用商机图没错吧?啊,那我们要想做一个商机图,我们应该给他提供什么样的数据呢?啊,其实很简单,它是不是只需要我们提供每个页面跳转的次数就可以了。对吧,那一个页面的跳转是不是由一个S再加一个target表示啊。没错吧,那所以说这个需求的大体思路就是这样的啊,我们得先获取所有的跳转,也就是获取所有的source到target的,没错吧,那之后呢,咱是不是再按照S加target进行分组之后再进行count就可以了。这就是咱这个需求的大体思路,好,那这个思路呢,听起来比较简单对吧?哎,但是我们实际去做的时候呢,哎,大家需要注意两个事项啊,那第一个事项呢,就是桑基图的source不允许为空啊,但是target是可以为空的,这个必须得注意,还有一个事项啊,什么呢?就是桑基图所展示的流程是不允许存在还的,OK,那这个呢,就是我们要注意的两个事项,好,那接下来呢,我们就把这个需求实现一下,那和之前一样啊,咱这儿还是先暂时不考虑recent计算啊,我们现在先只针对于最近一天的数据进行计算啊,等我们算完之后呢,再去考虑最近七天和最近30天啊,OK,那接下来我们就开始去实现这个需求啊,那咱要做的第一件事应该是啥呀?
01:28
是不是就得先获取所有的页面跳转呀?没错吧?哎,那我们从哪能拿到这个页面跳转呢?哎,大家回忆一下啊,咱们的DWD层的page置log这张表当中,是不是就记录了咱们所有的页面跳转?没错吧,因为在DW配置log这张表当中啊,有两个字段,一个字段呢是last配置ID,一个是配置ID。那last配置ID呢,指的是上页ID,配置ID指的是本页ID对吧?啊,那从上页到本页,这是不是就算做一次跳转没错吧?所以说大家自然而然的就能够想到,诶,Last配置ID就相当于咱们基图当中的source对吧?而配置ID呢,是不是就相当于桑基图当中的target呀?
02:10
OK,那接下来呢,我们就去获取一下我们所学的这些页面跳转啊,来打开data gra吧,来我们直接select啊,然后from哪张表应该是DWD配置log对吧?在这儿呢,我们先只针对于最近一日的数据进行计算,哎,DT等于零六杠幺四对吧?啊好,那完之后呢,获取我们所需的这个所谓的页面跳转对不对?那咱们的一个页面跳转是不是应该有一个last配置ID,再加上一个配置ID进行表示啊。没错吧,那现在呢,我们把这个circle执行一下,好,大家来看一下这个结果啊,你看这是不是就是source,这是不是就相当于是他变下对吧?啊好,那接下来我们继续往下进行啊,所有跳转我们都拿到了,那接下来呢,接下来是不是就统计每个跳转的次数啊,对吧?那那怎么做呀,很简单,是不是就是一个分组统计对吧?那在这儿呢,我们需要加上一个BY,再加上一个last page ID配置ID,然后上边咱再进行一个count芯是不是就可以了?来我们来个count芯啊OK,那接下来再执行一下。
03:06
好,那现在呢,我们就得到了咱所需的结果了,没错吧,你看啊,Last配置ID配置I,这是不是表示一个跳转,后边不是就是这个跳转的次数啊,那好像咱这个需求就已经完成了对吧?但是呢,我们仔细观察啊,就会发现这个结果其实是有问题的啊,有什么问题呢?我们来分析一下,首先第一个桑基图要求source是不能为闹的,对吧?啊,那现在咱的last配置ID是不是相当于source啊,对吧?啊,但是显然我们这里出现了S为闹的情况。啊,对吧,这是第一个问题,第二个问题啊,桑基图它要求路径当中不能存在环对吧?那显然呢,咱这儿是存在环的啊,哪儿啊,大家来看一下啊,大家注意观察,这儿是不是有一个从log in到good detail的跳转,对吧?那下边呢,是不是还有一个good detail到log in的跳转呀?对吧,这就相当于什么,是不是从A跳到了B,没错吧?啊,又从B跳到了A,那这是不是就是一个环啊,然后还有一个问题啊,就是我们从这个结果当中啊,只能获取到页面跳转的信息啊,什么意思,也就我们只能获知它是从哪个页面跳到了哪个页面。
04:16
对吧,诶,但是我们呢,是获取不到它的页面的访问次序的,也就是我不知道这个页面是他浏览的第几个页面,那这个页面呢,又是他浏览的第几个页面。没错吧,啊,也就在这儿呢,咱们一共存在这样三个问题,那接下来呢,我们就需要把这三个问题解决一下,那我们先来看第一个问题啊,也就是south维闹的问题,对不对啊,好,我们现在看一下原始数据来分析一下,就是咱的south它为什么会出现闹啊,OK,我们把这个count芯给它去掉啊,把这个groupby也去掉,哎,我们看一下原始数据啊,来,咱把数据查一下来,大家注意观察一下啊,那咱的source呢,使用的是last配置ID对吧?啊,那大家都知道啊,我们一个绘画的第一个页面访问记录的上页ID是不是肯定是闹啊?
05:01
没错吧,啊,那所以说我们最终的结果当中,South出现闹,那肯定是必然的,没错吧?啊,那接下来我们就来分析一下这个问题,咱们应该如何解决。那要想解决这个问题呢,我们就得做一个转变啊,做一个什么转变呢?啊,我们目前是不是将上页ID作为source,然后呢,将本页I作为target呀,对吧?那如果我们能够给它转换成本页ID作为source,下页ID作为target,是不是就能避免source为闹的情况了呀?啊OK,也在这儿呢,诶我们需要去做一个转换,咱们需要将诶上页ID到本页ID转换成本页ID到下页ID。OK,那接下来我们分析一下这个转换应该如何实现,那其实呢,这个本页ID是很容易获取的,没错吧,配置ID是不是就是所谓的本页ID啊啊,那关键是如何获取到本页ID的下页ID,这个其实也不难啊,大家想想啊,如果我现在把所有的页面访问记录按时间排好序,那下一行的配置ID是不是就是当前行的下页ID啊啊,OK,那关键是咱们如何能够获取到下一行的配置ID呢?这个其实也不难,咱是不是有一个开装函数叫做力的呀,对吧?力的函数那是不是正好能帮我们去实现这个功能啊?OK,那接下来我们按照这个思路去写一下色。
06:23
来,我们先把这个last配置ID删除,咱已经不需要它了,对吧?啊,然后呢,我们再使用诶力的函数对不对?去获取下一行的配置ID没错吧?大家注意一下力的函数的参数都有啥?在这儿呢?我们一共需要传三个参数,第一个呢,是你要获取的这一列的列名,没错吧?那第二一个参数呢,是你想获取下边第几行的数据啊?那再有一个呢,就是如果获取不到的话,默认值应该是啥?那在这儿呢,我是不是应该获取下边一行的配置ID的值啊对吧?如果获取不到诶,那咱们就返回闹没错吧?OK,那接下来写一下啊,那首先第一个应该是配置ID1,然后呢,就是闹没错吧?那后边呢,后边是不是得有分区,得有排序啊,那咱们按谁分居啊?
07:07
那在这儿咱是不是得按照绘画分区啊,没错吧?那所以说我们现在呢,还得先对绘画进行划分,这个绘画的划分思路呢,和之前是一样的啊,来我们快速的划分一下,那首先我们把这个先删除,在这呢,我们需要选择出来TS,然后呢,还得选择出来我们所需的mid,没错吧,那mid好,那下边呢,我们还得再选一个session start point,对吧?那if last page ID is now,那我们就返回TS,否则就返回now,没错吧?那那这个呢,我们给它起一个名字啊,叫做session start point。QOK,那完之后呢,我们以它作为一个子查询来,CTRLX来括号来放进来,好,那给它起一个别名叫做T。来,我们select from这个题。啊,弄完之后呢,我们把这些字段给它选择出来,来CTRLC来放在这个位置。
08:01
好,那我们现在先获取每个绘画的起始时间,没错吧?那在这儿怎么获取啊,是不是仍然是使用呃,Last value获取,这里边呢,我们需要放上session start point,然后呢,再给他写一个处跳过闹值,对吧?后边来一个over over里边写啥呀?哎,是不是写分区,按谁分区mid,那按谁排序呢?哎,是不是仍然是按照TS排序啊啊,OK,那这样一来我们就能得到每个绘画的起始时间了,那接下来呢,我们还得再给他拼上一个mid才行,没错吧,来,咱们用慷开的拼一下。来,把mid放进来,那后边呢,我们给它拼一个横线啊,然后呢,再拼上,哎,这个起始时间啊,好了,那这样一来我们就得到了咱所需的绘画ID了,Sessl session ID OK,我们先把这个circle执行一下,看一下效果啊来。OK,这个绘画已经划分完毕了,好,那接下来我们就可以继续去做这个转换了,对吧?把上页到本页转换成本页到下页,没错吧,那接下来我们继续啊。
09:00
好,那现在我们以它作为一个自杀学来CTRLX来括号。来CTRLV,好,我们把这个呢往下推一下啊,那给它起个别名叫做T2,好,我们select from t2。From第二,OK,那现在我们就可以选择咱们所需的配置ID,这是不是就是咱所谓的source啊,对吧?OK,那接下来我们再获取这个target,那target应该怎么获取,是不是应用力的函数获取,没错吧?Lead里边呢,我们同样需要传的是配置ID1,然后呢,No,那后边我们继续写啊,那现在我们就可以按照30ID进行分区了,对吧?诶,30ID后边呢,是不是应该按照TS进行排序啊?那这样一来我们就得到了咱所需的这个诶target了,没错吧,那现在呢,我们把这个circle执行一下,看一下效果走。来大家注意观察一下啊,你看我们现在是不是就已经把啊上页到本页转换成了从本页到下一页了呀,没错吧,然后你注意观察S是不是已经没有闹了啊对吧?这个闹呢,已经被我们转换到了target里边了啊OK,好,那现在我们实际上就已经解决了第一个问题了,然后大家别忘了啊,我们还有第二个和第三个问题没有解决呢啊,我们再来观察一下这个数据啊,就这个数据呢,它目前仍然不能体现页面的访问次序。
10:16
对吧,那我拿到这个页面访问记录之后,我仍然不知道这是第几个页面,这是第几个页面。没错吧,而且除此之外呢,那我们的路径当中仍然存在环啊,你注意观察看这个位置啊,看这是不是要从good detail到log in,又有从log in到good detail的呀,那就是这是不是还是存在环啊,OK,那接下来呢,我们就来想一想啊,这两个问题到底应该怎么解决?那其实在这儿呢,我们只需要做一步操作就能把这两个问题都解决了,那具体是什么操作呢?很简单啊,我们只需要给每个页面访问记录加上一个序号就可以了,比如说咱这儿举一个例子啊,大家看一下,那这几个页面访问记录是不是应该是属于同一个绘画呀,对吧,大家应该能看出来吧?啊,咱们简单看一眼啊,它是不是首先浏览了后页,然后从后面页跳到了search啊,从search呢又跳到了good list,从good list呢又跳到了good detail,没问题吧,诶,这应该就是一个会话,那那我们在这儿只需要做什么操作就可以了呢?我们只需要给每一个页面加上一个序号就可以了,比如说这是他浏览的第一个页面对吧?那OK,我们就给它加一个一。
11:21
啊,那当然这个是第二个页面,那在这儿呢,我们就得加一个二,没错吧,那当然这这个是几啊,这个是不是也应该是二啊对吧?它和它是不是应该是同一个页面对吧?应该从一跳到二,又从二跳到三,没错吧,那也是它应该是三,那这个呢,是不是也是三对吧?那一次往下来推,那这个是四,那这个是五,这个是六,那这边呢,是不是应该分别是A4,然后五,哎,然后六。没错吧?啊,OK,那只要我们把这个序号给它加上,那我们刚才的两个问题就都解决了,那他是如何解决的呢?我们来分析一下啊,那你想啊,我们有了序号了,咱是不是就能够清楚的知道这个页面是它浏览的第几个,这个页面是浏览第几个了?没错吧?所以说页面返回的次序咱就搞定了,那接下来我们再来看一下啊,那加了序号,那为什么就能够解决这个环的问题呢?来看一下啊,那原来我没有序号的时候,此处是不是从good detail跳到log in啊,又从log in跳到了good detail对吧?但是我们现在加上序号了,那就变成了啥?是不是变成了从good detail4跳到LOGIN5,又从LOG5跳到了good detail6啊,对吧?那这样一来是不是就没有环了呀?
12:30
OK,那这就是我们解决这两个问题的思路,就在这儿呢,我们需要给它加上一个序号啊,那具体怎么加这个序号呢?其实很简单,咱们have当中是不是有开通函数,有rank,有row number,有density rank是不是都可以加序号啊,没错吧,那在这儿呢,我们应该用谁啊?那要知道应该用谁,那咱是不是得对比一下这三个函数啊,没错吧?那我们现在先看第一个row number row number其实最简单,它就是啥?是不是就是行号对吧?不管3721,就是123456,那rank呢?啊,这个rank什么情况下会与row number不一样了啊,很简单,那如果说出现并列的情况,那rank与row number的处理逻辑就不一样了,没错,比如说咱这儿呢,有一个并列第一对吧?那这时候rank得到的序号就是啥?是不是就是一,一节下边就是三了,没错吧?啊,这是rank啊,那还有一个density rank对吧?Density rank跟rank的区别是啥啊?
13:20
N4D是啥意思?是不是密度的意思啊,是不是更密集的排名,没错吧,那所以说出现并列的时候呢,它它的排序应该是啥样呢?应该是一一下一个几,不是三,而是二,因为它更加密集一些嘛,OK,那这就是这三个函数的一个区别啊,在这儿我们应该用谁啊,实际上在这儿呢,我们用谁都可以,为啥?那大家想一想啊,我们一会儿去加序号的时候,是不是得有分区,得有排序,对吧?那咱按谁分区啊,是不是还是按照绘画分区,那按谁排序呢?是不是应该按照TS排序啊,没错吧,那我这个TS是不可能有相同的,不可能有相同的,那这三个函数其实用谁是不是效果都是一样的呀?没错吧,OK,那一会儿呢,咱们就用这个函数去加序号,然后呢,在咱加的时候要注意一下啊,S这一列咱是从几开始的,是不是从一开始123456,没错吧,那target这一列呢,是不是从二开始了呀,对吧,23456,这个一定要注意一下啊好,那接下来我们把这个circle去写一下。
14:16
那这呢,我们需要再来一个开装函数对吧?啊,那咱用谁啊,咱是不是可以用row number或者rank,或者是分词状态对吧?在这呢,我用row number啊,后边来写over over里边写啊partan by是不是仍是30ID,那order by呢,是不是仍然是TS啊对吧?那OK,那这个咱起个名叫做RN啊好,那现在我们看一下这个效果是啥样的,来回车。大家注意观察啊,咱这是不是又多了一列啊,没错吧,那一会儿我们是不是就可以直接把source与RN拼接在一起,对吧?HOME1 MY2 orders安PA3,没错吧?然后要注意这个target与R拼的时候得怎么是不是得给RN加个一啊,对吧?加个一再跟他拼,因为target是从二开始的,没错吧?啊好了,那接下来我们再来一个子查询,把这一步给它做一下,好,那现在我们给它来个子查询,CTRLX。
15:06
来括号。CTRL位,那现在我们把它往前推一下。来我们select,然后呢,From这个T几啊,这是啊T3对吧?来T3 OK,那接下来我们就可以给它加序号了啊,其实加序号的时候咱是不是就用慷K的拼接一下就行了对吧?来我们慷开啊来,首先慷开看看谁啊,那我在这儿呢,我给他写一个前缀吧,S TP step完之后呢,我来一个横盖啊,在这儿呢,我先加上谁是不是得先加上咱那个序号啊对吧?来应该就是RN没错吧,那后边呢,我再给它拼一个冒号,那再往后呢,才是咱的页面啊,也就是咱那个source没错吧,SOC,这就我们最终的source了啊那接下来呢,Target跟它其实是一样的操作,对吧?在这儿呢,我也给他来一个前缀s TB step来横线,那后边呢,应该是啥?应该是RN加一,没错吧,那后边也来也给它来一个冒号,那后边呢,就是target啊,这个就是我们最终的target了啊OK,那现在我们再把这个词给执行一下走。
16:06
好,大家注意观察,你看现在我们是不是就加上这个序号了呀?没错吧,那有了这个序号,我们的第二一个以及第三一个问题也就解决了,OK,那得到了最终的source和target之后呢?我们是不是就只剩下分组count了?没错吧,来,我们把这个circle再写一下啊,那现在我们以它作为一个子查询来CTRLX来括号。来CTRL我们把它往前推一下啊呃,这个起别名为T4啊T4。啊,好,那现在我们select from t。Select from t4啊,那下边呢,我们需要是不是得分组统计啊,对吧?需要分组go by谁是不是就是source加上哎,Target呀,对吧?好,那现在我们再往上面走,在这儿呢,我们把source和target选择出来source,然后呢,诶,Target后边呢,是不是直接看得清就可以了啊好,那现在呢,我们相当于就得到了每个跳转的次数了啊,那这就是我们最终的结果啊好了,那到目前为止,我们这个需求的核心业务就算是完成了,然后大家别忘了啊,咱是不是还有一个recent date字段呀,没错吧?啊,那在这儿呢,我们同样需要去分别统计最近一天,最近七天,最近30天的,而我们下面呢,是不是只统计了最近一天的呀,对吧?那怎么去分别统计最近七天,最近30天的呢?诶,其实跟之前是一样的啊,在这儿同样是有两个思路啊,那要么呢,我们把这个circle课写三遍对吧,然后呢,把另外的两个circle呢,诶日期的过滤条件改一下,那对吧,那或者说呢,在这是不是也可以使用UDTF函数那个套路啊,对吧,那现在我们给它改一下啊在这。
17:40
我们首先需要先来一个啥是不是先来一个let review you,来一个e prode explode对吧?这里边写啥?是不是写一个A瑞啊,里边呢,写一,然后七,然后30,没错吧?那后边我们来一个t as recent days,没错吧,Re,然后days,好,那接下来呢,我们需要把下边的过滤条件改一下,对吧?在这呢,我们需要给它改成大于等于什么东西啊,来ctrl s data I的没错吧?把当前日期放进去,后面的减一个recent days,再加上一个一,OK,那这样一来呢,我们相当于就把原始数据是不是扩张为了三份啊,并且呢,分别选择出来了最近一天的数据,最近七天的数据和最近30天的数据。
18:23
没错吧,啊,好,那现在呢,我们就可以按照recent this字段进行分组统计了,没错吧?好,那现在我们现在最终的结果当中选择一个recent this res。没错吧,那你这儿要想选择recent days,那咱下边是不是得对它进行分组,没错吧?所以在这儿呢,我们需要分组,那当然了,这儿要想对recent death分组,那是不是得保证T4这个子查询里边得有recent days字段呀,对吧?啊,那所以说我们在上面呢,需要选择一个recent days字段啊,在这儿呢,给它选择出来啊,那当然T4要想有,那就得保证哪有啊,是不是就得保证T3里边有,没错吧?我们来看一下啊,从T3选的时候,我们没有分组,所以说是不是直接在T3这个位置写一个re就可以了,没错吧,那T3要想有,那T2是就得有,那就怎样了,也得加一个,嗯,那同理,T2想有,那是不是得保证T1里边有啊,没错吧,所以在这儿呢,我们得把它选择出来才可以。
19:19
好了,那到目前为止,这个recent this我们就加上了,那同样啊,加上之后有些地方咱得改一下,这里边咱主要改哪就行了,诶主要改开窗函数的分区就可以了啊,为什么还是那个道理啊,咱的数据是不是由原来的一份变成了三份啊,对吧?啊,那我们开窗函数它的分区是不是得把这三份数据区分开,没错吧,要不然就混在一起了,所以现在啊,我们是不是需要为每个开窗函数的分区字段加上一个recent this啊,对吧?好,那我们现在分别加一下,OK,这儿需要先加一个,那上边还有两个对吧?这儿也要加来CTRLV逗号,这个也是一样的CTRLV逗号啊,OK,那到目前为止啊,咱这个recent days才算是加上了啊,好了,那最后我们还有一个字段啊,就是DTDT同样的在这我们写一个常量值就可以了,哎,我们来一个2020杠零六杠,诶幺四啊,来一个逗号,OK,那到现在呢,我们就已经拿到了所需的所有字段了啊,那最后呢,我们是不是就只剩下一个IN4了呀,对吧,来IN4的。
20:19
Right table ADS,那后边呢,再接一个select from as pass,然后呢,再来一个union,是不是就完事了啊?OK,那到目前为止这个需求我们就全部完成了。
我来说两句