lusansan 最近的时间轴更新 lusansan 最近的时间轴更新 |
lusansanV2EX 第 271355 号会员，加入于 2017-11-30 16:29:27 +08:00今日活跃度排名 7127 |
lusansan 最近回复了
88 天前 回复了 shuaishuaivip 创建的主题 › 程序员 › Hive SQL 实现问题,请大佬看下～ |
select
name1
,MIN(start_number) as start_number
,MAX(end_number) as end_number
,MIN(start_time) as start_time
,MAX(end_time) as end_time
from
(
SELECT
*
,case when start_number is null then row_number() over (partition by name1 order by start_time) -1 else row_number() over (partition by name1 order by start_time) end as rn
from
(
SELECT
name1
,start_number
,end_number
,start_time
,end_time
,lead(start_number,1) over (partition by name1 order by start_time) lead_start_number
FROM
(
select
name1,start_number,end_number,start_time,end_time
from
values
('LSJA24390MS139778' ,55.8,55.8 ,'2024-01-27 08:44:23' ,'2024-01-27 08:44:23')
,('LSJA24390MS139778' ,NULL,55.9 ,'2024-01-27 08:44:25' ,'2024-01-27 08:44:35')
,('LSJA24390MS139778' ,NULL,55.9 ,'2024-01-27 08:44:37' ,'2024-01-27 08:44:47')
,('LSJA24390MS139778' ,NULL,56 ,'2024-01-27 08:44:49' ,'2024-01-27 08:44:51')
,('LSJA24390MS139778' ,NULL,56.1 ,'2024-01-27 08:44:53' ,'2024-01-27 08:45:11')
,('LSJA24390MS139778' ,NULL,56.1 ,'2024-01-27 08:45:13' ,'2024-01-27 08:45:21')
,('LSJA24390MS139778' ,NULL,56.1 ,'2024-01-27 08:45:23' ,'2024-01-27 08:45:23')
,('LSJA24390MS139778' ,NULL,56.3 ,'2024-01-27 08:45:25' ,'2024-01-27 08:45:51')
,('LSJA24390MS139778' ,NULL,56.5 ,'2024-01-27 08:45:53' ,'2024-01-27 08:46:21')
,('LSJA24390MS139778' ,NULL,56.5 ,'2024-01-27 08:46:23' ,'2024-01-27 08:46:23')
,('LSJA24390MS139778' ,NULL,56.6 ,'2024-01-27 08:46:25' ,'2024-01-27 08:46:51')
,('LSJA24390MS139778' ,56.7,56.7 ,'2024-01-27 08:46:53' ,'2024-01-27 08:46:59')
,('LSJA24390MS139778' ,NULL,56.8 ,'2024-01-27 08:47:01' ,'2024-01-27 08:47:11')
,('LSJA24390MS139778' ,NULL,56.8 ,'2024-01-27 08:47:13' ,'2024-01-27 08:47:21')
,('LSJA24390MS139778' ,NULL,56.8 ,'2024-01-27 08:47:23' ,'2024-01-27 08:47:23')
,('LSJA24390MS139778' ,NULL,56.9 ,'2024-01-27 08:47:25' ,'2024-01-27 08:47:35')
,('LSJA24390MS139778' ,NULL,57 ,'2024-01-27 08:47:37' ,'2024-01-27 08:47:47')
,('LSJA24390MS139778' ,NULL,57 ,'2024-01-27 08:47:49' ,'2024-01-27 08:47:51')
,('LSJA24390MS139778' ,NULL,57 ,'2024-01-27 08:47:53' ,'2024-01-27 08:47:59')
,('LSJA24390MS139778' ,NULL,57.1 ,'2024-01-27 08:48:01' ,'2024-01-27 08:48:11')
,('LSJA24390MS139778' ,NULL,57.2 ,'2024-01-27 08:48:13' ,'2024-01-27 08:48:21')
,('LSJA24390MS139778' ,NULL,57.2 ,'2024-01-27 08:48:23' ,'2024-01-27 08:48:23')
,('LSJA24390MS139778' ,NULL,57.3 ,'2024-01-27 08:48:25' ,'2024-01-27 08:48:35')
,('LSJA24390MS139778' ,57.2,57.3 ,'2024-01-27 08:48:37' ,'2024-01-27 08:48:47') as t (name1,start_number,end_number,start_time,end_time)
) p1
) p2
WHERE
start_number is not null or lead_start_number is not null
) t1
group by name1,rn
;
name1 start_number end_number start_time end_time
LSJA24390MS139778 55.8 56.6 2024-01-27 08:44:23 2024-01-27 08:46:51
LSJA24390MS139778 56.7 57.3 2024-01-27 08:46:53 2024-01-27 08:48:35
LSJA24390MS139778 57.2 57.3 2024-01-27 08:48:37 2024-01-27 08:48:47
name1
,MIN(start_number) as start_number
,MAX(end_number) as end_number
,MIN(start_time) as start_time
,MAX(end_time) as end_time
from
(
SELECT
*
,case when start_number is null then row_number() over (partition by name1 order by start_time) -1 else row_number() over (partition by name1 order by start_time) end as rn
from
(
SELECT
name1
,start_number
,end_number
,start_time
,end_time
,lead(start_number,1) over (partition by name1 order by start_time) lead_start_number
FROM
(
select
name1,start_number,end_number,start_time,end_time
from
values
('LSJA24390MS139778' ,55.8,55.8 ,'2024-01-27 08:44:23' ,'2024-01-27 08:44:23')
,('LSJA24390MS139778' ,NULL,55.9 ,'2024-01-27 08:44:25' ,'2024-01-27 08:44:35')
,('LSJA24390MS139778' ,NULL,55.9 ,'2024-01-27 08:44:37' ,'2024-01-27 08:44:47')
,('LSJA24390MS139778' ,NULL,56 ,'2024-01-27 08:44:49' ,'2024-01-27 08:44:51')
,('LSJA24390MS139778' ,NULL,56.1 ,'2024-01-27 08:44:53' ,'2024-01-27 08:45:11')
,('LSJA24390MS139778' ,NULL,56.1 ,'2024-01-27 08:45:13' ,'2024-01-27 08:45:21')
,('LSJA24390MS139778' ,NULL,56.1 ,'2024-01-27 08:45:23' ,'2024-01-27 08:45:23')
,('LSJA24390MS139778' ,NULL,56.3 ,'2024-01-27 08:45:25' ,'2024-01-27 08:45:51')
,('LSJA24390MS139778' ,NULL,56.5 ,'2024-01-27 08:45:53' ,'2024-01-27 08:46:21')
,('LSJA24390MS139778' ,NULL,56.5 ,'2024-01-27 08:46:23' ,'2024-01-27 08:46:23')
,('LSJA24390MS139778' ,NULL,56.6 ,'2024-01-27 08:46:25' ,'2024-01-27 08:46:51')
,('LSJA24390MS139778' ,56.7,56.7 ,'2024-01-27 08:46:53' ,'2024-01-27 08:46:59')
,('LSJA24390MS139778' ,NULL,56.8 ,'2024-01-27 08:47:01' ,'2024-01-27 08:47:11')
,('LSJA24390MS139778' ,NULL,56.8 ,'2024-01-27 08:47:13' ,'2024-01-27 08:47:21')
,('LSJA24390MS139778' ,NULL,56.8 ,'2024-01-27 08:47:23' ,'2024-01-27 08:47:23')
,('LSJA24390MS139778' ,NULL,56.9 ,'2024-01-27 08:47:25' ,'2024-01-27 08:47:35')
,('LSJA24390MS139778' ,NULL,57 ,'2024-01-27 08:47:37' ,'2024-01-27 08:47:47')
,('LSJA24390MS139778' ,NULL,57 ,'2024-01-27 08:47:49' ,'2024-01-27 08:47:51')
,('LSJA24390MS139778' ,NULL,57 ,'2024-01-27 08:47:53' ,'2024-01-27 08:47:59')
,('LSJA24390MS139778' ,NULL,57.1 ,'2024-01-27 08:48:01' ,'2024-01-27 08:48:11')
,('LSJA24390MS139778' ,NULL,57.2 ,'2024-01-27 08:48:13' ,'2024-01-27 08:48:21')
,('LSJA24390MS139778' ,NULL,57.2 ,'2024-01-27 08:48:23' ,'2024-01-27 08:48:23')
,('LSJA24390MS139778' ,NULL,57.3 ,'2024-01-27 08:48:25' ,'2024-01-27 08:48:35')
,('LSJA24390MS139778' ,57.2,57.3 ,'2024-01-27 08:48:37' ,'2024-01-27 08:48:47') as t (name1,start_number,end_number,start_time,end_time)
) p1
) p2
WHERE
start_number is not null or lead_start_number is not null
) t1
group by name1,rn
;
name1 start_number end_number start_time end_time
LSJA24390MS139778 55.8 56.6 2024-01-27 08:44:23 2024-01-27 08:46:51
LSJA24390MS139778 56.7 57.3 2024-01-27 08:46:53 2024-01-27 08:48:35
LSJA24390MS139778 57.2 57.3 2024-01-27 08:48:37 2024-01-27 08:48:47
262 天前 回复了 candy22 创建的主题 › 酷工作 › 特斯拉内推（非猎头 HR） |
@candy22 多谢回复
268 天前 回复了 candy22 创建的主题 › 酷工作 › 特斯拉内推（非猎头 HR） |
Tesla 内部英语要求如何？
Select Language