I searched all over and can't seem to find out how to take an average of
every x number of rows from a result set.
I have a table with over 700,000 rows, and I want to display an x and y
graph of that data on the web. What is happening now is I'm creating 2
arrays, one for the x axis and one for the y axis of the graph, each with ~
700,000 elements. All that data is used to plot a 600 pixel wide graph, so
in the process of creating the web images, most of the detail is lost, and
it takes ~ 30 seconds to render. (I'm using jpgraph in PHP, and have
confirmed that the majority of the time needed to render is creating the
image, not populating the large arrays.)
So, I figure rather than plot all that detail, only to have most of it
thrown out when the image is created, I need to throw out the data before
the image is created, in hopes that doing so will greatly decrease the
amount of time needed to render the graph.
So, what I need to do is get the average of a column ever x rows.
This is as far as I got:
SELECT AVG(value) AS every_ten_row_average FROM table GROUP BY [every 10
The part I'm stuck on is the [every 10 rows].
I think I could use a limit statement, and loop the query over and over,
each time adjusting the limit values by 10, but that would mean a whole lot
of queries. :)
My guess is that I need to create a temp table or a virtual column based on
current row, and use that as my group by.
Any help GREATLY appreciated.
If anyone has solutions for creating web image graphs from MySQL that would
be faster than using the php based jpgraph setup, I'd love to hear about
them as well.
New Media One Web Services, LLC