mysql - 两个相似值之间的时间差

Question

我想找出数据库中两个函数名之间的时间差。数据库看起来像这样:

我想做的是找出两个连续的同名函数名之间的时间差。例如，输出将是行号“2”和行号“3”处的“getPrice”，然后是行“3”和行“5”处的“getPrice”的时间差，依此类推所有其他时间和所有其他函数名称。请帮助我，非常感谢!

我试过了

SELECT a.lid, a.date, (b.date-a.date) as timeDifference 
FROM myTable a 
INNER JOIN myTable b ON b.lid = (a.lid+1) 
ORDER BY a.lid ASC;

问题是，它给出了任何连续函数名称的时间差，即使它们不相同!

@tombom

有一个我用于测试的表，它的变量名称与我之前提供的示例不同。表格如下所示:

在应用您的代码(当然还更改变量名称以匹配此表)后，输出如下所示:

如您所见，“getTax”是从“getPrice”中减去的，尽管它们是不同的。我怎么解决这个问题？？非常感谢。

我要构建的架构是:

 CREATE  TABLE `test` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `nserviceName` VARCHAR(45) NULL ,
  `functionName` VARCHAR(45) NULL ,
  `time` TIMESTAMP NULL ,
  `tps` INT NULL ,
  `clientID` INT NULL ,
  PRIMARY KEY (`id`) );

插入的是:

INSERT INTO `test` (`id`, `nserviceName`, `functionName`, `time`, `tps`, `clientID`) VALUES ('1', 'X1', 'getPrice', '2013-05-23 00:36:08', '22', '0');
INSERT INTO `test` (`id`, `nserviceName`, `functionName`, `time`, `tps`, `clientID`) VALUES ('2', 'X2', 'getTax', '2013-05-23 00:38:00', '33', '0');
INSERT INTO `test` (`id`, `nserviceName`, `functionName`, `time`, `tps`, `clientID`) VALUES ('3', 'X1', 'getPrice', '2013-05-23 00:35:00', '12', '0');
INSERT INTO `test` (`id`, `nserviceName`, `functionName`, `time`, `tps`, `clientID`) VALUES ('4', 'X1', 'getPrice', '2013-05-23 00:35:00', '11', '0');
INSERT INTO `test` (`id`, `nserviceName`, `functionName`, `time`, `tps`, `clientID`) VALUES ('5', 'X2', 'getTax', '2013-05-23 00:35:00', '88', '0');
INSERT INTO `test` (`id`, `nserviceName`, `functionName`, `time`, `tps`, `clientID`) VALUES ('6', 'X1', 'getPrice', '2013-05-23 00:35:00', '33', '0');

谢谢。

@tombom我要对表执行的操作如下图所示:

我从第一个记录 X1 getPrice 开始，它之前没有记录。所以不需要任何操作。然后检查第二个 getTax 之前没有 getPrice，它们不相同，因此不会再执行任何操作。然后数字 3 getPrice 在它之前有 getTax，所以它忽略它并检查上面的 getTax 以在此处找到 getPrice 它将计算 getPrice(#3) 和 getPrice(#1) 之间的时间差。接下来第 4 行的 getPrice 将检查其上方的行，它发现它正上方的行是 getPrice，因此将找到 getPrice*(#4) 和 getPrice(#3) 之间的时间差。然后第 5 行的 getTax 将检查它上面的行，直到找到与第 2 行相似的 functionName (getTax)。然后将找到第 5 行的 getTax 和第 2 行的 getTax 之间的时间差。

非常感谢..

Answer 1

请试试这个:

SELECT lid, `date`, serviceName, functionName, responseTime, sid, timeDifference FROM (
SELECT
IF(@prevFname = functionName, SEC_TO_TIME(TIMESTAMPDIFF(SECOND, `date`, @prevDate)), 'functionName differs') AS timeDifference,
@prevFname := functionName AS a,
@prevDate := `date` AS b,
yt.*
FROM
yourTable yt
, (SELECT @prevFname:=NULL, @prevDate:=NULL) vars
ORDER BY functionName, `date`
) subquery_alias

我喜欢在这种情况下使用用户定义的变量，因为我在性能方面取得了惊人的体验，因为不需要自连接。

另请注意，我使用了 timestampdiff 函数和 sec_to_time 来完善输出。 Timestampdiff 是减去不同日期(+时间)的正确方法。唯一的缺点是，sec_to_time 只允许从“00:00:00”到“23:59:59”的范围。如果这会导致问题，请再次删除该功能。在 this site 上阅读有关这两个功能的更多信息.

更新(没有必要那么复杂):

SELECT lid, `date`, serviceName, functionName, responseTime, sid, timeDifference FROM (
SELECT
SEC_TO_TIME(TIMESTAMPDIFF(SECOND, @prevDate, `date`)) AS timeDifference,
@prevDate := `date` AS b,
yt.*
FROM
yourTable yt
, (SELECT @prevDate:=NULL) vars
ORDER BY lid
) subquery_alias

更新 2:

当 functionName 与前一个不同时，此函数将时差重置为 00:00:00。

SELECT * /*choose here only the columns you need*/ FROM (
SELECT
IF(@prevFunction = functionName, SEC_TO_TIME(TIMESTAMPDIFF(SECOND, @prevDate, `time`)), '00:00:00') AS timeDifference,
@prevFunction := functionName AS a,
@prevDate := `time` AS b,
yt.*
FROM
test yt
, (SELECT @prevDate:=NULL, @prevFunction:=NULL) vars
ORDER BY id
) subquery_alias

更新 3:

好吧，多么艰难的出生。只是一个小调整。

SELECT * /*choose here only the columns you need*/ FROM (
SELECT
IF(@prevFunction = functionName, SEC_TO_TIME(TIMESTAMPDIFF(SECOND, @prevDate, `time`)), '00:00:00') AS timeDifference,
@prevFunction := functionName AS a,
@prevDate := `time` AS b,
yt.*
FROM
test yt
, (SELECT @prevDate:=NULL, @prevFunction:=NULL) vars
ORDER BY functionName, id#, `time`
) subquery_alias
ORDER BY id

我在子查询中再次按函数名称和 id(或者时间，如果你愿意)排序，进行所有计算，然后在外部查询中再次按 id 排序。就是这样。