如何查找表中各行之间的平均时间差?

如果您的表是t,时间戳列是ts,并且您希望以秒为单位得到答案:

SELECT TIMESTAMPDIFF(SECOND, MIN(ts), MAX(ts) ) 
       /
       (COUNT(DISTINCT(ts)) -1) 
FROM t

对于大型表,这将加快数英里,因为它没有n平方联接

证明:连续行之间的平均距离是连续行之间的距离之和除以连续行数。但连续行之间的差值之和只是第一行和最后一行之间的距离(假设它们是按时间戳排序的)。连续行数是总行数-1。

然后我们只需将时间戳设置为不同的。

SELECT 
      a.ID
      , b.ID
      , a.Timestamp 
      , b.Timestamp 
      , b.timestamp - a.timestamp as Difference
FROM
     MyTable a
     JOIN MyTable b
          ON a.ID = b.ID + 1 AND a.Timestamp <> b.Timestamp

这将为您提供每个连续行对上的时间差列表。。。

然后你可以把它打包成一个平均分组。。。

select avg(timestampdiff(MINUTE,prev.datecol,cur.datecol))
from table cur
inner join table prev 
    on cur.id = prev.id + 1 
    and cur.datecol <> prev.datecol

这个 timestampdiff 函数允许您在天、月、秒等之间进行选择。

如果id不是连续的,则可以通过添加一条规则来选择上一行,即中间没有其他行:

select avg(timestampdiff(MINUTE,prev.datecol,cur.datecol))
from table cur
inner join table prev 
    on prev.datecol < cur.datecol
    and not exists (
        select * 
        from table inbetween 
        where prev.datecol < inbetween.datecol
        and inbetween.datecol < cur.datecol)
    )

根据本讨论改编为SQL Server。

使用的基本列有: cmis_加载_日期:与每条记录关联的日期/时间戳。 extract_file:加载记录的文件的完整路径。

评论: 每个文件中可能有许多记录。记录必须按“提取文件”列中加载的文件分组。一个文件和下一个正在加载的文件之间可能会间隔几天。任何列中都没有可靠的顺序值,因此分组的行按每个文件组中的最小加载日期进行排序,然后ROW_NUMBER()函数用作特殊顺序值。

SELECT 
AVG(DATEDIFF(day,  t2.MinCMISLoadDate, t1.MinCMISLoadDate)) as ElapsedAvg
FROM
(
SELECT 
ROW_NUMBER() OVER (ORDER BY MIN(cmis_load_date)) as RowNumber,  
MIN(cmis_load_date) as MinCMISLoadDate,
CASE WHEN NOT CHARINDEX('\', extract_file) > 0 THEN '' ELSE RIGHT(extract_file, CHARINDEX('\', REVERSE(extract_file)) - 1) END as ExtractFile
FROM
TrafTabRecordsHistory 
WHERE 
court_id = 17
and 
cmis_load_date >= '2019-09-01'
GROUP BY 
CASE WHEN NOT CHARINDEX('\', extract_file) > 0 THEN '' ELSE RIGHT(extract_file, CHARINDEX('\', REVERSE(extract_file)) - 1) END
) t1
LEFT JOIN 
(
SELECT 
ROW_NUMBER() OVER (ORDER BY MIN(cmis_load_date)) as RowNumber,  
MIN(cmis_load_date) as MinCMISLoadDate,
CASE WHEN NOT CHARINDEX('\', extract_file) > 0 THEN '' ELSE RIGHT(extract_file, CHARINDEX('\', REVERSE(extract_file)) - 1) END as ExtractFile
FROM
TrafTabRecordsHistory 
WHERE 
court_id = 17
and 
cmis_load_date >= '2019-09-01'
GROUP BY 
CASE WHEN NOT CHARINDEX('\', extract_file) > 0 THEN '' ELSE RIGHT(extract_file, CHARINDEX('\', REVERSE(extract_file)) - 1) END
) t2 on t2.RowNumber + 1 = t1.RowNumber

旧职位,但。。。。

SELECT 
   id, 
   TIMESTAMPDIFF('MINUTES', PREVIOUS_TIMESTAMP, TIMESTAMP) AS TIME_DIFF_IN_MINUTES
FROM (
   SELECT 
     id, 
     TIMESTAMP, 
     LAG(TIMESTAMP, 1) OVER (ORDER BY TIMESTAMP) AS PREVIOUS_TIMESTAMP
   FROM TABLE_NAME
)