两个相似值之间的时差

I want to find the time difference between two function names in my database. the database looks like this:

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

what I want to do is to find the time difference between two consecutive function names who have the same name. for example the output will be for "getPrice" at row number "2" and row number "3" and then time difference for "getPrice"at row "3" and row "5" and so on for all other times and all other function names. Please help me and thanks a lot!

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

I tried

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;

The problem is, it gives time difference for any consecutive function names even if they are not identical!

问题是,它为任何连续的函数名称提供时间差,即使它们不相同!

@tombom

there is a table I use for testing and have different variable names than the example I provided earlier. the table looks like this:

有一个我用于测试的表,并且具有与我之前提供的示例不同的变量名。表格如下:

and after applying your code (and of course change the variable names to match with this table) the output looks like this:

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

as you can see the "getTax" is subtracted from "getPrice" although they are different. how can I solve this problem?? Thanks a lot.

正如您所看到的,“getTax”从“getPrice”中减去,尽管它们不同。我怎么解决这个问题??非常感谢。

the schema I'm trying to build is:

我正在尝试构建的架构是:

 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`) );

and the insert is :

并且插入是:

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');

thanks.

@tombom the operation I want to perform on the table is like the following image:

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

where I start from the first record X1 getPrice which have no record before it. so no operation is required. then check number two getTax have no getPrice before it which are not identical so again no operation will be performed. then number 3 getPrice have getTax before it so it ignores it and check above getTax to find getPrice here it will do the time difference between getPrice(#3) and getPrice(#1). next getPrice at row 4 will check the rows above it, and it find the one directly above it is getPrice so time difference between getPrice*(#4) and getPrice(#3) will be found. then getTax at row 5 will check the rows above it until it finds a similar functionName (getTax) which is at row #2. then the time difference between getTax at row 5 and getTax at row 2 will be found.

我从第一个记录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之间的时差。

thanks a lot..

非常感谢..

1 个解决方案

#1

Please have a try with this one:

请试试这个:

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

I like to use user defined variables in such cases as I made amazing experiences regarding performance, since no self-join is needed.

我喜欢在这种情况下使用用户定义的变量,因为我在性能方面做了很多经验,因为不需要自联接。

Also note that I used the timestampdiff function and sec_to_time to polish the output. Timestampdiff is the correct way to subtract different dates(+times). Only downside is, that sec_to_time only allows a range from '00:00:00' to '23:59:59'. If this can lead to problems, remove the function again. Read more about both functions on this site.

另请注意,我使用timestampdiff函数和sec_to_time来抛光输出。 Timestampdiff是减去不同日期(+次)的正确方法。唯一的缺点是,sec_to_time只允许范围从'00:00:00'到'23:59:59'。如果这可能导致问题,请再次删除该功能。阅读有关本网站上两个功能的更多信息

UPDATE (less complicated than necessary):

更新(不必要复杂):

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

UPDATE 2:

This one resets the timedifference to 00:00:00 when functionName differs to previous one.

当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

UPDATE 3:

Okay, what a difficult birth. Just a minor tweak.

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

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

I order by function name and id again (or time if you prefer) in the subquery, do all the calculations, then sort it by id again in the outer query. That's it.

我在子查询中再次按函数名称和id(或者您喜欢的时间)进行排序,执行所有计算,然后在外部查询中再次按id对其进行排序。而已。