1. 查询两个什么时间没有执行中的任务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SELECT date_format(previous_endDate, '%H:%i:%S') time, a.*
from (select JOW.NAME,
             instance.CREATION_DATE,
             instance.END_DATE,
             LAG(instance.END_DATE) OVER w                                                          AS previous_endDate,
             TIME_TO_SEC(TIMEDIFF(instance.CREATION_DATE, LAG(instance.CREATION_DATE) OVER w)) / 60 as create_def,
             TIME_TO_SEC(TIMEDIFF(instance.CREATION_DATE, LAG(instance.END_DATE) OVER w)) / 60      as update_def
      from SCH_WORK_INSTANCES instance
               join dehoop.JOB_OUTLINE_WORKS JOW on instance.WORK_ID = JOW.id and JOW.type = 'SparkSQL'
      where date_format(instance.CREATION_DATE, '%Y-%m-%d') > '2023-9-05'
        and name <> 'test001'
      WINDOW w as (ORDER BY instance.id)
      order by instance.id) a
where a.create_def > 30
  and update_def > 30;