显示平均执行时间

Question

我有一个sql格式的表，如下所示：

ID          JOB_NAME                START_DATE_TIME         END_DATE_TIME

748425  HOURLY_PLAZA_MISSING    2013-10-30 13:15:01.040 2013-10-30 13:15:14.420
748424  HOURLY_AUTOREPL         2013-10-30 13:05:00.950 2013-10-30 13:05:02.390
748423  HOURLY_LANE             2013-10-30 13:00:01.500 2013-10-30 13:00:10.080
748422  HOURLY_EMAIL            2013-10-30 13:00:01.460 2013-10-30 13:02:00.650
748421  HOURLY_EASYPAY          2013-10-30 13:00:01.410 2013-10-30 13:00:04.820
748420  HOURLY_PROCESS          2013-10-30 13:00:01.290 2013-10-30 13:25:11.450
748419  HOURLY_COPY_SUSPENSE    2013-10-30 13:00:01.240 2013-10-30 13:03:39.660
748418  HOURLY_I95              2013-10-30 13:00:01.230 2013-10-30 13:19:46.170
748414  HOURLY_PLAZA_MISSING    2013-10-30 12:15:01.020 2013-10-30 12:15:04.070
748413  HOURLY_PROCESS          2013-10-30 12:06:30.480 2013-10-30 12:31:41.610
748411  HOURLY_AUTOREPL         2013-10-30 12:05:01.070 2013-10-30 12:05:02.440
748409  HOURLY_I95              2013-10-30 12:00:01.430 2013-10-30 12:45:48.060
748408  HOURLY_EMAIL            2013-10-30 12:00:01.420 2013-10-30 12:02:14.620
748406  HOURLY_EASYPAY          2013-10-30 12:00:01.330 2013-10-30 12:00:04.640

如果您仔细注意，作业将在2013-10-30 12:06:30.480 HOURLY_PROCESS 2013-10-30 13:00:01.290每小时重复一次

我需要一个sql查询，它将显示像blow这样的输出：

JOB_NAME                Average_Time of Execution
HOURLY_PROCESS         (END_DATE_TIME-START_DATE_TIME)/Last 10 occurances of HOURLY_PROCESS

有没有人能帮我一下？

Answer 1

这里有一种方法，如果你满足于在几秒钟内显示出差异。

select job_name, (sum(diff_ms/1000.0) / count(*)) as avg_seconds
from (select t.*,
             datediff(ms, start_date_time, end_datetime) as diff_ms
             row_number() over (partition by job_name order by start_date_time desc) as seqnum
      from t
     ) t
where seqnum <= 10
group by job_name;