00:00
好的同学们,那我们接下来看这个里边的第一题对吧?呃,那它呢,是一个连续问题,我们先看一下这个题目所描述的内容啊呃,那在这个当中呢,我们来看一下,首先呢,他说的是蚂蚁森林当中用户领取的减少的碳排放量,呃,那我们在支付宝当中多多少少的都种果树,特别是前几年特别火的时候,但是现在呢,好像关注的人比较少了,呃,那上面呢,就会有小绿圈对吧?那我们点一下诶,大家就收集了我们减少的碳排放量,平时我们走路啊,或者骑自行车啊,购买火车票啊等等的这些方式都可以给我们积累这样的一个碳排放量啊啊那么呢,我们每点一次,它就会生成一条记录啊,都会生成一条记录,这个就是,呃,这个数字呢,就是相当于那个绿色的圆圈当中所表示的这个碳排放量,减少的碳排放量啊,呃,那么呢,他要求我们找出连续三天及以上减少碳排放量均在这个100以上的。
01:02
啊,均在100以上的用户,好,那我们把这个数据呢,我给他拿过来啊,拿过来呃,拿到我们这个文档当中啊,到时候呢,我们这这个给它放在这儿给大家去发下去啊,然后呢,我把这个变成S,因为这样的话,到时候我们可以写这个内容啊,Data time呃,那这个字段呢,我还是改成叫DT吧,啊可能更好一点,因为data time呃,有可能会跟我们的关键词有冲突,对吧?啊所以呢,我们改成这个名字,同时呢,我在这边也已经建好了表了啊,那每一个题呢,我分别对应的是TEST1 test2 test3 test4,所以第一题呢,就是我们看到这个地方会有一个TEST1啊,这个数据其实跟我们数据呢,是完全一样的文档当中啊,这个大家不用担心,好呃,那刚才的问题我也拿过来一下啊,这样的话方便我们看问题来解决分析啊,因为等会儿呢,我们在这儿要去加工写这个SQ语句,呃,他要要求我们找出连续三天及以上节。
02:02
减少碳排放量在100以上的用户,呃,那首先给我们的数据记录呢,是一天当中会有怎么样多条记录,因为它会涉及到多次领取这个啊低碳的排放量,OK,那那我把这个呢,稍微改一下啊,43我记得应该是改过了,诶这个地方是43对不对?呃,因为这样的话,我就能保证13号1001这个用户呢,也是达到了100的啊,那它就会有连续的啊,那你比方说这边1001是第一天12号123,呃,13号呢是这个。累加一下超过100了,对不对啊,那14号是二百三啊,15号呢是23,那这个是不满足的,也就是说整个来说一号用户是怎么样是满足我们的条件的啊,一号用户呢,是满足条件的对不对啊,是满足条件的OK,那是这样的一种情况啊嗯。好,那那那关键的问题在于呢,那我们知道一号,那二号满足吗?二号我们看一下二号呢,呃,这个45A不满足对吧,二号只有两条数据啊,三条数据啊,都是45都不满足,所以说呢,其实我们这道题的答案应该是1001这一个用户对不对?呃,那这个没问题,但是这个S应该如何去书写呢?
03:22
大家可以思考一下,那其实刚才我们分析的过程当中,大家应该看出来,我在勾选1001的时候,说他第一天123,第二天呢,我勾了三条数据说超过了100,对吧?大家可以算一下啊,这个呢,应该是111版对不对啊,111OK,那其实呢,这个地方我们要知道,那首先我们是不是得根据ID和DT呢,将它的总和求出来,然后再考虑连续不连续的问题,对不对?所以这个第一步其实是相对说比较简单的,那就是呃,按照用户ID及。即什么内容叫DT时间字段分组对不对,计算每个用户啊。
04:12
单日减少的碳排放量。啊,单日减少的碳排放量OK,呃,那这个色呢,想必大家都对大家来说都不太贪啊,再来个呢,OK,然后呢,第一个ID啊,第二个呢,诶这个是应该是英文的逗号啊,然后呢是DT字段,我们要保留,第三个呢是some叫low carbon字段,那我们还是取名叫flow carbon表示啊然后呢,From我们的表名呢,刚才大家看到了,我是取名叫TEST1,然后呢,Group啊,ID,然后呢是我们的DT字段这样子的,这个呢我们就T表也是一个子查询的表啊啊,那我们可以把这个拿过去运行一下,我们接着讨论我们的问题对不对?好把它拿过来运行啊呃,那么接下来呢,讨论第二问题,现在的数据大概大家也能看得出来,也能想象得到123 111 230,然后呢,23这样的数据对吧?呃,那接下来呢,我们要求连续问题,这个连续问题就比较复杂。
05:18
啊,它大于100的,我们要过滤出大于100的,对不对,我们可以同时做一个过滤吧,因为它只要大于100的,那我们在这个基础上能不能做过滤,那我们可以用having。Having,呃,当前呢,应该是low carbon啊,大于什么100啊,超过100,也就是说类似于这样的数据啊呃,那这个结果算出来了,刚才呢,我们其实最后呢,得到了这样的一个数据啊,对吧?啊,那运行啊,我们重新运行一下,其实应该只有只剩下这么三条数据了,过滤出来以后啊呃,那接下来这三条数据我可以稍微的去写一写啊,其实就是它我们不用看运行结果,我们都知道对不对,第二第三个这个呢是111,这个呢是230,呃,然后呢,这个是13号,这个是14号。
06:08
13啊14号,呃,那对着这个数据呢,我们要求它这个连续,那当然我们看到它确实是连续的,这是我们肉眼观察出来的,但是我们如何通过hicker来得到它是连续的一个问题呢。对不对,大家可以稍微的去思考一下啊,或者说有同学呃,拿到这个题的时候,自己尝试去做了一下,对不对,那第二步呢?呃,其实就是计算这个连续,呃,那我们采用一种什么样的思路呢?叫等差数列法。等差数列数列法啊呃,那你要清楚的一个事情啊,如果两个注意啊,这个理论基础呢,在于两个等差数列,如果等差相同,则。
07:04
相同位置的数据相减。相减得到的结果相同啊呃,那这是什么意思呢?你比方说我现在有一个等差数列为等差为一啊,它呢是23456对吧?好另外呢,我还有一个等差数列,等差呢,注意这边有个等差相同,它的等差呢是也是一,那12345,好,那你平行数据相减,你看一下结果是什么样子,诶它它们相减结果都为。一好,那也就是说只要你日期是连续的,我只要在后面弄一个类似于12345这样的等差数列,那我用日期减掉它,那必然是相同,那相同的话,我就认为减到结果相同,那我就认为你怎么样。
08:02
是相同的对吧,它是连续的,那你比方说我等差数列呢,234,它是连续日期突然断了一天,诶六七好,那我后面呢,还是弄一个正常的等差数列,我们看一下相减结果什么样子,前三条相减都等于一,从这条开始等于二,那我们就知道了,这三条是连续的,而这两条也是怎么样连续的,其实它的一个理论基础就在于这句话,叫两个等差数列。如果等差相同对吧?那么相同位置上相减得到的结果相同,哎,这个地方写错了,应该是一个叫数列对吧?好,那我们就第二步就用等差数列法采用的啊,那这块呢,我们可以把这个拿过来啊,我们整个的分析过程要用到等差数列法,那第二步当中我们要怎么去做呢?对不对?那其实要给它复制一个类似于12345的一个等差数列对吧?好,那也就是说,呃,按照用户分组,同时按照时间排序啊嗯,计算每条数据的rank值,哎,那天生的rank是它是不是就是12345这样的一个内容啊,所以我们就得到了这个等差数列对不对?好,那接下来呢,我们就是select,哎,这个就比较简单ID啊,然后呢,DT。
09:30
保留,嗯,接下来呢,是还是我们的low cup,嗯,接下来我们就是一个rank值括号,我们开窗over啊,然后呢,Partition by part by我们的ID字段,同时all啊,BY我们的什么叫这个DT字段啊,然后呢,这个我们叫RK,给它一个别名,因为最后呢,我们要对这个RK进行一个相减啊,然后from哪张表,我们的T1表对吧?那如果说你要想看一下结果,你也可以去运行,那我就不看了,大概的大家能想到就在这后面加了一个123啊,那么第三步呢,就比较简单了,那就将日期减去这个rank值啊将。
10:14
每行数据中的日期减去。Rank值啊,减去rank值啊,得到一个结果,呃,那我们来可以看到select ID逗号,然后呢是DT,呃,漏卡保留,然后呢,日期减rank,呃,那这是一个解法,那data塔萨括号,那我们用DT么?然后呢是这个RK这个值,好,那我们就得到了一个贝塔D。啊,Data它相差的一个值,或者说我们把它称为叫flag对吧?啊flag一个标记啊然后呢,From明的T2表,因为这张表呢,我们把紫砂星叫做T2表啊呃,来自我们的T2表,两个相减,那其实我们就得到值是相同的,呃,然后接下来第四步就很简单,按照用户及flag分组啊求每个组每个组有多少条数据啊呃,并找出大于B,找出大于三条的,三条的啊因为最终呢,我们要大于等于三对吧?啊大于等于那我们这边加一个啊叫大于等于三的数据对吧?啊那这个我们想必大家都会了,就是计算。
11:51
成一个count值啊,Select的这个地方呢,是一个ID,因为我们为什么还要找大于等于三的,因为我们刚好数据量比较小啊,那比方说有一个1002,它有两天连续呢,它不够三天对不对?或者有一个1004,它有四天连续呢?啊,那这种数据我们都要给他找到,OK,呃,那在这个过程当中呢,Select ID,然后DT,嗯,接下来呢,是low carbon,我们保留这个数据量对吧,然后是呃,Flag,那我们去保留一下啊,这个low carbon,还有这个DT,我们其实就可以不要了啊,那我们就只要这个flag啊呃,最后呢,我们要的是这个。
12:29
Count c啊,那这个呢,我们称取名叫CT啊,然后from我们的叫T3表啊,这个呢,我们叫T3啊T3表,然后接下来注意要分组条件,要写上group by ID和谁呀,和我们的flag对吧?好,那之后呢,我们要过滤出来大于三的heavy,这个CT大于等于三啊,大于等于三好,那这种情况下来说呢,我们就把整个的四个搞定了,所以最后呢,这个都是单步来写的,所以最后呢,我们要把这个色客做一个拼接啊,就是我们写一下,最终开口对吧?好,那这块呢,我们可以这样拼接一下,把这个拿过来放在这,然后呢,我把这个移下来啊,呃,移到这,然后前面加一个我们的括号,注意英文的,呃,然后这个是T3表,把这个拿过来往这一粘啊这个T2呢,我也给它移下来加一个括号,然后呢,把T2对应的这个T1表拿过来啊。
13:29
他从T表组词查询的,同理,这边加一个括号,然后呢,把这个T表拿过来,OK。啊,这是我们的,诶,From t表这个拿错了啊,应该是拿到我们的第一张表是它了啊,T表是它OK,呃,然后呢,放到这个前面,然后之后这就是我们的完整的一个SQL,我们可以把这个尝试运行一下,是不是出来1001这个数据啊,这是刚才我们第二次运行的结果啊,没有什么好说的,对吧,走运行一下。
14:15
我们只要等待它的一个结果,其实就好了,对吧?啊,那我们稍微等一下,因为我这边默认用的是MR的引擎,所以相对来说呢,会比较慢一点啊,但是没关系啊,这个数据量呢也不是很大,其实整体的速度来说呢,应该还可以。到后面其实我可以设置一下用local模式,不用这个雅安模式,对吧,这样的话我们数据量小的时候测试呢,就会更快一些。好的,那我们最后就得到1001,诶它呢是这个三,那这个11是我们对应的那个flag,呃,也就是说拿着我们这个数据去减一二三都得到11对吧,这个是没有问题的啊,就是我们的flag,好,那这样的话,我们第一个问题就解决了啊,其实就用了这样的一个思想来解决我们的问题,对吧。
我来说两句