Databases
StackOverflow2013
Postsdbo
POHow to find the lowest sustained value
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to find the lowest sustained value
    primarykey
    data
    text
    <p>Is there a non brute force/efficient way to determine the minimum value sustained for x minutes from a sql table with a structure like below? This table will have 1 record per case_id, per channel_index, per second and there can be 20 channels per case and thousands of cases in this table. I will need to perform this query per case and per channel. I need to find the lowest, and highest, value that occurred for 3 consecutive minutes.</p> <p>value_duration is already calculated to make these types of queries a little faster. It is in seconds and can be completely random. This indicates the time between consecutive readings received from a channel.</p> <pre><code>case_id channel_index start_time dms_value value_duration ----------- ------------- ----------------------- ---------------------- -------------- 2668 0 2011-09-28 10:24:39.000 69.5769729614258 2 2668 0 2011-09-28 10:24:41.000 69.7469329833984 2 2668 0 2011-09-28 10:24:43.000 69.8547210693359 1 2668 0 2011-09-28 10:24:44.000 69.8475494384766 1 2668 0 2011-09-28 10:24:45.000 69.9703216552734 2 2668 0 2011-09-28 10:24:47.000 69.9699172973633 1 2668 0 2011-09-28 10:24:48.000 70.0099258422852 2 2668 0 2011-09-28 10:24:50.000 70.2449035644531 1 2668 0 2011-09-28 10:24:51.000 70.0424575805664 2 2668 0 2011-09-28 10:24:53.000 70.1216125488281 1 2668 0 2011-09-28 10:24:54.000 69.5616912841797 1 2668 0 2011-09-28 10:24:55.000 69.5902786254883 2 2668 0 2011-09-28 10:24:57.000 70.0330581665039 1 2668 0 2011-09-28 10:24:58.000 70.4709854125977 1 2668 0 2011-09-28 10:24:59.000 70.7001647949219 2 2668 0 2011-09-28 10:25:01.000 70.274040222168 1 2668 0 2011-09-28 10:25:02.000 69.7524795532227 1 2668 0 2011-09-28 10:25:03.000 69.4606552124023 2 2668 0 2011-09-28 10:25:05.000 69.6096954345703 1 2668 0 2011-09-28 10:25:06.000 69.8238906860352 1 </code></pre> <p>I am hoping not to have to run through a loop testing for a value, incrementing, and then testing the next, and so on.</p> <p>For example, from the above data set if I wanted to know the lowest value for 5 consecutive seconds it would be 69.8238906860352. If I needed it for 8 consecutive seconds it would be 69.9703216552734. </p> <p>Here is the full table structure:</p> <pre><code>CREATE TABLE [dbo].[continuous_data]( [case_id] [int] NOT NULL, [channel_index] [smallint] NOT NULL, [start_time] [datetime] NOT NULL, [dms_type] [char](1) NOT NULL, [dms_value] [float] NOT NULL, [value_duration] [smallint] NOT NULL, [error_code] [int] NULL ) ON [PRIMARY] </code></pre> <p>EDIT 3-5-12: SO I implemented a brute force way to calculate the lowest sustained values and it seems to work ok when a specific case has a few thousand records but when tested on a case that had 1.1 million I ended up cancelling it after 37 minutes... Here is the code I am using. Anyone have ideas on optimization?</p> <pre><code>ALTER procedure [dbo].[GetSustainedValues]( @case_id int, @time_limit int, @bypass_only bit = NULL) as begin DECLARE @time DateTime, @channelindex int, @lastchannelindex int DECLARE @tmin float, @tmax float, @min float, @max float, @caseid int DECLARE @results TABLE(case_id int, channel_index int, max float null, min float null) DECLARE CursorName CURSOR FAST_FORWARD FOR SELECT start_time, channel_index from continuous_data where case_id = @case_id order by channel_index, start_time OPEN CursorName FETCH NEXT FROM CursorName INTO @time, @channelindex SET @lastchannelindex = @channelindex WHILE @@FETCH_STATUS = 0 BEGIN --PRINT 'hello' --'Chennel:' + CONVERT (VARCHAR(50), @channelindex,128) + ' Time:' + CONVERT (VARCHAR(50), @time,128) IF @lastchannelindex != @channelindex BEGIN --PRINT 'Starting new channel:' + CONVERT (VARCHAR(50), @channelindex,128) -- we are starting on a new channel so insert that data into the results -- table and reset the min/max INSERT INTO @results(case_id, channel_index, max, min) VALUES(@case_id, @lastchannelindex, @max, @min) SET @max = null SET @min = null SET @lastchannelindex = @channelindex END Select @tmax = MAX(dms_value), @tmin = MIN(dms_value) from continuous_data where case_id = @case_id and channel_index = @channelindex and start_time between DATEADD(s, -(@time_limit-1), @time) and @time HAVING SUM(value_duration) &gt;= @time_limit IF @@ROWCOUNT &gt; 0 BEGIN IF @max IS null OR @tmin &gt; @max BEGIN --PRINT 'Setting max:' + CONVERT (VARCHAR(50), @tmin,128) + ' for channel:' + CONVERT (VARCHAR(50), @channelindex,128) set @max = @tmin END IF @min IS null OR @tmax &lt; @min BEGIN set @min = @tmax END END --PRINT 'Max:' + CONVERT (VARCHAR(50), @max,128) + ' Min:' + CONVERT (VARCHAR(50), @min,128) FETCH NEXT FROM CursorName INTO @time, @channelindex END CLOSE CursorName DEALLOCATE CursorName --PRINT 'Max:' + CONVERT (VARCHAR(50), @max,128) + ' Min:' + CONVERT (VARCHAR(50), @min,128) SELECT * FROM @results end </code></pre> <p>EDIT: 3-7-2012 Still have not found an answer. Is there a more efficient way to do this using a .Net dll that could be called from a stored proc? Looking for any suggestions here. Thanks!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmdutra
    1. USmdutra
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to find the lowest sustained value
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIs value duration in minutes, and is it always 1 or 2?
      singulars
      1. POHow to find the lowest sustained value
      1. USPhil
    2. CONo. The value_duration can be anything. The sample data set I selected just has 1 or 2. It is just the difference in time between samples in seconds.
      singulars
      1. POHow to find the lowest sustained value
      1. USmdutra
    3. COWhen you say lowest sustained value, do you mean that the dms_value has to be equal or lower during the entire timespan?
      singulars
      1. POHow to find the lowest sustained value
      1. USMartin
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload