StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POFinding entire fluctuation in a dataset
text
Body
copied!<p>I have a table of historic data for a set of tanks in a MySQL database. I want to find fluctuations in the volume of tank contents of greater than 200 gallons/hour. My SQL statement thus far is:</p> <pre><code>SELECT t1.tankhistid as start, t2.tankhistid as end FROM (SELECT * from tankhistory WHERE tankid = ? AND curtime BETWEEN ? AND ?) AS t1 INNER JOIN (SELECT * from tankhistory WHERE tankid = ? AND curtime BETWEEN ? AND ?) AS t2 ON t1.tankid = t2.tankid AND t1.curtime < t2.curtime WHERE TIMESTAMPDIFF(HOUR, t1.curtime, t2.curtime) < 1 AND ABS(t1.vol - t2.vol) > 200 ORDER BY t1.tankhistid, t2.tankhistid </code></pre> <p>In the code above, curtime is a timestamp at the time of inserting the record, tankhistid is the table integer primary key, tankid is the individual tank id, and vol is the volume reading.</p> <p>This returns too many results since data is collected every 5 minutes and fluctuations could take hours (multiple rows with the same id in an end and then start column) , or just over 10 minutes (multiple rows with the same start or end id). Example output:</p> <pre><code>7514576,7515478 7515232,7515478 7515314,7515478 7515396,7515478 7515478,7515560 7515478,7515642 7515478,7515724 </code></pre> <p>Note that all of these rows should just be one: 7514576,7515724. The query takes 4 minutes for just one day of a tank's data, so any speed up would be great as well. I am guessing there is a way to take the current query and use it as a subquery, but I am not sure how to do the filtering.</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