• From: New Zealand
  • Registered: 2011-12-01
  • Posts: 2

Topic: MySQL Trend Queries ... anyone?

I have a database of weather information, which is updated from a sensor array every minute ... and uploaded etc.
What I would like to do is insert a column into a query that would return whether a value was trend upwards or downwards. In other words lets say the Barometer reading in the last row recorded is reading 1020.0 whereas in 10 rows previous it was reading 1019.5, the result would be a rising trend ... Has anyone got a grasp of conditional IF query application in MySQL.?

  • From: Los Angeles CA
  • Registered: 2008-03-17
  • Posts: 1,701

Re: MySQL Trend Queries ... anyone?

That's a very interesting question.  Are you trying to do this in a single SQL Query?  I think you'd need to develop a stored procedure, possible in combination with a stored function or two. 

I don't think it's possible with a single SQL statement to get a previous record to do the comparison,  you'd have to create a stored procedure with a CURSOR, select your records then loop through them using the CURSOR mechanism,  then do the logic inside the loop.  Once you get the CURSOR loop set up,  there is sufficient conditional logic available to do your trending analysis.

Joe got a job, on the day shift, at the Utility Muffin Research Kitchen, arrogantly twisting the sterile canvas snout of a fully charged icing anointment utensil.
  • From: New Zealand
  • Registered: 2011-12-01
  • Posts: 2

Re: MySQL Trend Queries ... anyone?

Yup thats where I was heading to get something to work ... the problem I am just an enthusiast :-) and stored procedures is new territory. The logic of your answer is where I got too. There is no way whilst limiting the query to the last row that I can compare a field to previous rows, so yes It needs to be some sort of Mechanisation outside the current query that can be referred to for the information to include in the query result. So stored Procedure using cursors ... thanks heaps, I reserve the right to come back with more questions though.
Thanks