复制with ta as(  select*  fromvalues (1001,挑战06:05:00,sleep)  ,(1001,07:10:00,eat)  ,(1001,08:15:00,phone)  ,(1001,11:20:00,phone)  ,(1001,12:25:00,eat)  ,(1001,12:40:00,phone)  ,(1001,13:30:00,eat)  ,(1001,13:35:00,sleep)  ,(1001,17:40:00,eat)  ,(1001,18:05:00,eat)  ,(1001,18:25:00,eat)  ,(1001,18:30:00,phone)  ,(1001,19:45:00,eat)  ,(1001,20:55:00,phone)  ,(1001,22:00:00,sleep)  t(id,stime,stat))   -- 5 计算根据前后记录的时间，判断记录是香港云服务器使用否要被合并 selectid,stime ,case whens2<=60 thenetime2 else etime end asetime,stat from( -- 4 计算前后记录的时间差 selectid,stime,etime,stat ,datediff(stime,etime1,mi) ass1 ,datediff(stime2,etime,mi) ass2 ,etime2 from( -- 3 计算前后记录的网站模板时间 selectid,stime,etime,stat ,lag (stime,1) over(partition byid order by stime asc)as stime1 ,lag (etime,1) over(partition byid order by stime asc)as etime1 ,lead(stime,1) over(partition byid order by stime asc)as stime2 ,lead(etime,1) over(partition byid order by stime asc)as etime2 from( -- 2 识别前后记录状态，找到状态结束时间 selectid,进据处stime,stat ,lead(stime,1) over(partition byid order by stime asc)as etime ,lag (stat,1) over(partition byid order by stime asc)as stat1 ,lead(stat,1) over(partition byid order by stime asc)as stat2 from( -- 1 把字符串转时间 selectid,to_date(concat(2021-06-29 ,stime),yyyy-mm-dd hh:mi:ss) asstime,stat fromta)t1)t2 wherestat=eat and not(stat=eat andstat1=eat andstat2=eat))t3)t4 wheres1 >60 ors1 is null ; 1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.云服务器提供商