时间间隔计算在时间序列上使用SQL

Question

我有一个MySQL表像这样

CREATE TABLE IF NOT EXISTS `vals` (
    `DT` datetime NOT NULL, 
    `value` INT(11) NOT NULL, 
    PRIMARY KEY (`DT`) 
); 

的DT是唯一的日期与时间

数据样本：

INSERT INTO `vals` (`DT`,`value`) VALUES 
('2011-02-05 06:05:00', 300), 
('2011-02-05 11:05:00', 250), 
('2011-02-05 14:35:00', 145), 
('2011-02-05 16:45:00', 100), 
('2011-02-05 18:50:00', 125), 
('2011-02-05 19:25:00', 100), 
('2011-02-05 21:10:00', 125), 
('2011-02-06 00:30:00', 150); 

我需要得到像这样：

start|end|value 
NULL,'2011-02-05 06:05:00',300 
'2011-02-05 06:05:00','2011-02-05 11:05:00',250 
'2011-02-05 11:05:00','2011-02-05 14:35:00',145 
'2011-02-05 14:35:00','2011-02-05 16:45:00',100 
'2011-02-05 16:45:00','2011-02-05 18:50:00',125 
'2011-02-05 18:50:00','2011-02-05 19:25:00',100 
'2011-02-05 19:25:00','2011-02-05 21:10:00',125 
'2011-02-05 21:10:00','2011-02-06 00:30:00',150 
'2011-02-06 00:30:00',NULL,NULL 

我想下面的查询：

SELECT T1.DT AS `start`,T2.DT AS `stop`, T2.value AS value FROM (
    SELECT DT FROM vals 
) T1 
LEFT JOIN (
    SELECT DT,value FROM vals 
) T2 
ON T2.DT > T1.DT ORDER BY T1.DT ASC 

但它返回到许多行（29，而不是9）的结果，我冷没有找到任何方式来限制这种使用SQL。在MySQL中可能吗？有两种不同的

select @myvar as start, end, value, @myvar := end as next_rows_start 
from vals 

变量从序列左右的解释，所以两个引用@myvar（启动和next_rows_start）将输出：

Answer 1

使用子查询

SELECT 
    (
    select max(T1.DT) 
    from vals T1 
    where T1.DT < T2.DT 
) AS `start`, 
    T2.DT AS `stop`, 
    T2.value AS value 
FROM vals T2 
ORDER BY T2.DT ASC 

你也可以使用一个MySQL专用的解决方案，采用变量

SELECT CAST(@dt AS DATETIME) AS `start` , @dt := DT AS `stop` , `value` 
FROM (SELECT @dt := NULL) dt, vals 
ORDER BY dt ASC 

但是你需要d ·其精确

• 的ORDER BY必须存在，否则变量不正确地滚动
• 的变量需要使用子查询来设置它在查询中无效，否则，如果您在运行它两次，排，第二次就不会用NULL

Answer 2

您可以使用服务器端的变量来模拟它值。

只记得重置@myvar之前，空和/或查询后，否则第二次及以后的运行将有一个错误的第一行：

select @myvar := null 

Answer 3

如果表有对应于次DT（顺序相同）正在运行的ID列这将是更容易启动。如果你不想改变表，你可以使用温度：

drop table if exists temp; 

CREATE TABLE temp (
    `id` INT(11) AUTO_INCREMENT, 
    `DT` datetime NOT NULL, 
    `value` INT(11) NOT NULL, 
    PRIMARY KEY (`id`) 
); 

insert into temp (DT,value) select * from vals order by DT asc; 

select t1.DT as `start`, t2.DT as `end`, t2.value 
from temp t2 
left join temp t1 ON t2.id = t1.id + 1;