日常写数据处理任务，主要方法是用SQL。首先是因为自己对SQL掌握的很好（十几年的数据开发经验，不敢跟别人说我这几个关键字不行），所以MR和函数涉及的不多。在接触MaxCompute的这些年里，我写的函数应该不超过10个，主要是我的JAVA水平不高。记得早年写过一个身份证号验证功能。当时一个项目报了一条SQL，以前用2分钟，用我的功能用了12分钟。当时项目组也找上了MaxCompute的研发，研发负责人又找到我，让我调优我的代码。我很害怕，我是个什么样的人渣，我心里有数。最后还是厚着脸皮让研发帮我优化了一下，性能终于提升了。之后就不敢再胡乱做函数了。毕竟MaxCompute官方还是建议尽量使用SQL。SQL是一种优化的方法。使用MR和自定义函数的性能很难保证。这也导致我在这方面很渣。当然，我不认为这是我的错。我只是听“妈妈”说的。最近很神奇，接连两个项目都遇到了序列值计算的问题，而且都要求函数和MR都不能用。同事发给我的题目，我发现题目理解的时间比较长（题目有点绕），太久没上一线也有点生疏了。同样的问题，第一次搞了一天，第二次搞了半天，而且没说很快解决，有点不合常理。所以，总结一下，分享给大家。先说说什么是序列值的处理。表中的记录是无序的，但业务数据是有序的。一般来说，时间是一个自然序列。比如用我每天作息时间的时间点记录，算出我一天吃多少次，吃多久。乍一看，好像是在写一个函数。问题模拟如下：问题：你吃了多少次，吃了多久？条件：1-两次“吃”状态间隔在1小时以内，算一顿饭2-上一次“吃”状态后的下一个其他状态的开始时间为“吃”的结束时间通过以上分析，我们可以得出结果：我大概吃了四次，因为晚上吃饭的时间比较长，按规矩算作两顿饭（第四次好像是串）。我该怎么做？第一步，我先剔除了不相关的信息，第一行，第四行，最后一行。在第二步，我终于利用数据是连续时间的特点找到了状态的结束时间。第三步，我识别出状态间隔为1小时的特征，识别出混在一个“吃”中的其他无关状态，同时分析出第三个“吃”和第四个“吃”状态是两个独立的状态。地位。那么如何用SQL来实现呢？排序是一定的。要对状态进行排序和处理，您必须使用窗口函数。可以选择的窗函数好像只有lag和lead。窗口函数：LAG根据偏移量取当前行前一行的值。LEAD获取当前行之后偏移量的行的值。官方文档：https://help.aliyun.com/document_detail/34994.html即使有了这个功能，还是很头疼，这个功能需要指定偏移量，而且这个问题不知道会出现多少状态。也是没用的吗？让我们来看看。问题分解如下：使用LAG\LEAD函数获取上一条记录和下一条记录的状态和时间，分析记录：1-当前状态不是“吃”，之前状态不是“吃”，不保留记录。2-当前状态不是“吃”，上一个状态是“吃”，为上一个状态提供结束时间，不保留记录。3-当前状态为“吃”，前后状态记录为“吃”，不保留记录。4-当前状态为“吃”，记录下一个状态的时间作为当前状态的结束时间，并保留记录。如下图所示：然后我们得到了下表：显然，这最终不是我们需要的。虽然我们已经找到了状态为“eating”的那一行，并且通过窗口函数为它找到了状态的结束。但是表格还是需要重新处理，才能变成我们想要的结果。再次使用LAG\LEAD函数，我们需要合并间隔在1小时以内的“吃”状态。问题再次分解如下：使用LAG\LEAD函数获取上一条记录和下一条记录的起止时间，分析记录：1-当前记录的“开始时间”减去“结束时间”time”为上一个时间点的时间，如果小于1小时，则不保留该行记录。这行记录的状态需要和上一行合并成一个“吃”的状态。下图中用绿色标记的线。2-下一个时间点的“开始时间”减去当前记录的“结束时间”，如果小于1小时，则将本行记录与下一行记录合并。修改当前时间点“吃”状态的结束时间为下一个时间点的结束时间。下图中橙色标记线。然后我们得到了下表：不管我们之前想的有多复杂，需要实现什么循环或者递归逻辑，现在问题都解决了。我们使用此表格来回答原始主题的问题。此人共进餐4次，分别在7点10分、12点25分、17点40分和19点45分开始，每次进餐时间约1小时。这个过程就是一个找到需要的信息，剔除不相关信息的过程，但是这个where有点复杂。其实从分析问题的角度来说，问题本身是有点复杂的，理解问题一般需要一定的时间。从实现问题来看，用高级语言JAVA或者python来实现比较容易，没有什么是循环一次解决不了的（一次做一遍不行）。用SQL实现，好像有点复杂（可能因为常年使用SQL语言，感觉分析问题的过程和实现的过程是一样的。），但是代码量肯定是最少（性能也可能是最好的）。从可维护性综合比较，还是用SQL比较好。所以，如果以后遇到类似的问题，应该是可以解决的。如果有点难度，至少你可以回头看看这个例子，毕竟我设计了很久。SQL问答：withtaas(select*fromvalues(1001,'06:05:00','sleep'),(1001,'07:10:00','eat'),(1001,'08:15:00','电话'),(1001,'11:20:00','电话'),(1001,'12:25:00','吃'),(1001,'12:40:00','电话'),(1001,'13:30:00','吃'),(1001,'13:35:00','睡觉'),(1001,'17:40:00','吃')'),(1001,'18:05:00','吃'),(1001,'18:25:00','吃'),(1001,'18:30:00','电话'),(1001,'19:45:00','吃'),(1001,'20:55:00','电话'),(1001,'22:00:00','睡觉')t(id,stime,stat))--5根据记录前后的时间计算，判断记录是否合并selectid,stime,casewhens2<=60thentime2elseetimeendasetime,statfrom(--4计算selectid前后记录的时间差id,stime,etime,stat,datediff(stime,etime1,'mi')ass1,datediff(stime2,etime,'mi')ass2,etime2from(--3计算时间记录前后selectid,stime,etime,stat,lag(stime,1)over(partitionbyidorderbystimeasc)asstime1,lag(etime,1)over(partitionbyidorderbystimeasc)asetime1,lead(stime,1)over(partitionbyidorderbystimeasc)asstime2,lead(etime,1)over(partitionbyidorderbystimeasc)asetime2from(--2标识之前的记录状态和之后，找到状态结束时间selectid,stime,stat,lead(stime,1)over(partitionbyidorderbystimeasc)asetime,lag(stat,1)over(partitionbyidorderbystimeasc)asstat1,lead(stat,1)over(partitionbyidorderbystimeasc)asstat2from(--1convertstringtotimeselectid,to_date(concat('2021-06-29',stime),'yyyy-mm-ddhh:mi:ss')asstime,statfromta)t1)t2wherestat='eat'andnot(stat='eat'andstat1='eat'andstat2='eat'))t3)t4wheres1>60ors1为空；
