本文介绍了使用SQL的时间序列时间间隔计算的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个这样的MySQL表
I have a MySQL table like this
CREATE TABLE IF NOT EXISTS `vals` (
`DT` datetime NOT NULL,
`value` INT(11) NOT NULL,
PRIMARY KEY (`DT`)
);
DT是唯一的日期与时间
the DT is unique date with time
数据样本
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?
but it returns to many rows (29 instead of 9) in result and I cold not find any way to limit this using SQL. Is it Possible in MySQL?
推荐答案
使用子查询
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特定解决方案
You can also use a MySQL specific solution employing variables
SELECT CAST( @dt AS DATETIME ) AS `start` , @dt := DT AS `stop` , `value`
FROM (SELECT @dt := NULL) dt, vals
ORDER BY dt ASC
但是您需要精确地执行
- 必须出现ORDER,否则变量不能正确滚动
- 变量需要在查询中使用子查询来设置为空,否则如果连续两次运行,则第二次不能以NULL开始
这篇关于使用SQL的时间序列时间间隔计算的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!