SQL-两个字段之间的最大和最小时间变化 [英] SQL - Max and Min time between two fields changing
问题描述
我有一个包含两列的PL/SQL表:log_date
(DATE)和value
(FLOAT).数据非常细粒度,log_dates
之间的差异可能是几毫秒. value
随时间变化.我想使用SQL查找value
增加所需的log_date
s之间的最大和最小时间.
I have a PL/SQL table with two columns: log_date
(DATE) and value
(FLOAT). The data is very fine-grained, the difference between log_dates
could be a few milliseconds. The value
changes over time. I want to find, using SQL, the maximum and minimum amount of time between log_date
s it takes for value
to increase.
示例
log_date | value
-------------------
15:00 | 10
15:01 | 10
15:02 | 11
15:03 | 11
15:04 | 11
15:05 | 11
15:06 | 12
在15:00和15:02之间value
增加了,但是在15:03和15:06之间也增加了,这花费了更长的时间,所以我想要一个查询,该查询将返回(在这种情况下)"3分钟"(作为DATE或NUMBER)-value
增加所需的最长时间.
Between 15:00 and 15:02 value
increased BUT it also increased between 15:03 and 15:06 which took longer, and so I want a query that would return (in this case) '3 minutes' (as a DATE or a NUMBER) - the longest amount of time it took for value
to increase.
推荐答案
我可以在T-SQL中给您答案,但是我不确定您使用的是哪种方言. TBH,这是我们首先想到的一个循环(其他人可能有更好的方法!):
I can give you an answer in T-SQL, but I'm not sure what dialect you're using. TBH, a loop here is the first thing that springs to mind (someone else may have a better way of doing it!):
DECLARE @temp TABLE ( log_date DATETIME, value FLOAT )
INSERT INTO @temp ( log_date, value ) SELECT log_date, value FROM <MyTableName>
DECLARE @diff TABLE ( time_diff INT, old_value FLOAT, new_value FLOAT )
-- the loop
DECLARE @prev_value FLOAT,
@cur_value FLOAT,
@prev_log_date DATETIME,
@cur_log_date DATETIME
WHILE EXISTS ( SELECT NULL FROM @temp )
BEGIN
SELECT TOP 1 @cur_log_date = log_date, @cur_value = value
FROM @temp
ORDER BY log_date
IF ( @prev_value IS NOT NULL AND @prev_log_date IS NOT NULL )
BEGIN
INSERT INTO @diff ( time_diff, old_value, new_value )
SELECT DATEDIFF('ms', @prev_log_date, @cur_log_date ),
@prev_value, @cur_value
END
SELECT @prev_log_date = @cur_log_date, @prev_value = @cur_value
DELETE FROM @temp WHERE log_date = @cur_log_date
END
SELECT MAX(time_diff), MIN(time_diff) FROM @diffs
这样,您最终得到一个包含所有差异的表,然后可以查询该表.
This way, you end up with a table of all differences that you can then query.
HTH
这篇关于SQL-两个字段之间的最大和最小时间变化的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!