StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow do I calculate n averages over a datetime range?
text
Body
copied!<p>I have an application that samples data every 15 seconds from several (right now 48) different data points. These data points are represented in a table by two different foreign keys (MeasurementTypeId and GeneratorId), a sample value (Value) and a datetime (MeasurementDate).</p> <p>So, the table looks like this (I'm only including 10 rows and only a few different foreign key combinations)</p> <pre><code>+------+-------------------+-------------+-------+------------------------+ | ID | MeasurementTypeId | GeneratorId | Value | MeasurementDate | +------+-------------------+-------------+-------+------------------------+ | 1 | PSI | SG-52 | 0.19 | 2013-08-27 09:19:30 | | 2 | PH | SG-52 | 6.39 | 2013-08-27 09:19:30 | | 3 | DEG | SG-52 | 90.85 | 2013-08-27 09:19:30 | | 4 | PSI | PT-14 | 0.33 | 2013-08-27 09:19:30 | | 5 | PH | PT-14 | 6.41 | 2013-08-27 09:19:30 | | 6 | DEG | PT-14 | 89.20 | 2013-08-27 09:19:30 | | 7 | PSI | SG-52 | 0.20 | 2013-08-27 09:19:45 | | 8 | PH | SG-52 | 6.37 | 2013-08-27 09:19:45 | | 9 | DEG | SG-52 | 90.84 | 2013-08-27 09:19:45 | | 10 | PSI | PT-14 | 0.34 | 2013-08-27 09:19:45 | +------+-------------------+-------------+-------+------------------------+ </code></pre> <p>I'm looking to create a stored procedure that can return the average values (grouped by the foreign keys) for an interval over a datetime range. However, I don't want to define the interval, rather leave that up to the user invoking the stored procedure. </p> <p>I would like the stored procedure to take parameters of </p> <pre><code>@numberOfIntervals int, @startDateRange datetime, @endDateRange datetime </code></pre> <p>And divide the datediff of the startDateRange and endDateRange by the numberOfIntervals to determine the interval over which to do the average.</p> <p>For example if the procedure is invoked with 3, '2013-08-26 00:00:00', '2013-08-27 00:00:00' </p> <p>The results would be 3 records for each group by combination with the value equal to the average of the 8 hours interval (only 10 results included for brevity...Actual results would include 18)</p> <pre><code>+-------------------+-------------+-------+------------------------+ | MeasurementTypeId | GeneratorId | Value | MeasurementDate | +-------------------+-------------+-------+------------------------+ | PSI | SG-52 | 0.20 | 2013-08-26 08:00:00 | | PH | SG-52 | 7.11 | 2013-08-26 08:00:00 | | DEG | SG-52 | 90.02 | 2013-08-26 08:00:00 | | PSI | PT-14 | 0.44 | 2013-08-26 08:00:00 | | PH | PT-14 | 6.98 | 2013-08-26 08:00:00 | | DEG | PT-14 | 90.31 | 2013-08-26 08:00:00 | | PSI | SG-52 | 0.21 | 2013-08-26 16:00:00 | | PH | SG-52 | 7.12 | 2013-08-26 16:00:00 | | DEG | SG-52 | 90.01 | 2013-08-26 16:00:00 | | PSI | PT-14 | 0.44 | 2013-08-26 16:00:00 | +-------------------+-------------+-------+------------------------+ </code></pre> <p>I have done some work to calculate the moving averages, but that is for a specific time frame and a specific number of intervals. With this, I want the user to be able to select a daterange and a specified number of intervals and graph the results.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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