CODEWITHSUNDEEP
javamysqlsql
Montolide
Montolide

Reputation: 792

Getting data sample to plot

I have a table on mysql with lots and lots of data (like >10000), and it seems pretty slow to load all the data to Java and then plot a graphic. I would like to get just a few points to plot a graphic, but I have no idea how. Like, getting data every 10 or 100 rows. Any idea?

Upvotes: 1

Views: 104

Answers (2)

valex
valex

Reputation: 24144

Or you can divide your whole interval min(x) - max(x) to N intervals and get only one average point per interval. For instance (10 intervals here) something like this:

select round((x-@minx)/@step),avg(y) from PLOT_DATA,
(select @minx:=min(x),
        @maxx:=max(x),
        @intervals:=10, /*separated intervals count*/
        @step:=(@maxx-@minx)/@intervals
  from PLOT_DATA) t

group by round((x-@minx)/@step)
order by round((x-@minx)/@step)

Upvotes: 1

RollingCog
RollingCog

Reputation: 304

If you have an auto increment field you can simply select every 10 or 100 rows using remainder (mod):

SELECT actor_id, last_name FROM actor WHERE MOD(actor_id, 10) = 0;

You can also give each row a row number and limit your result on that:

SELECT actor_id, last_name, row_number FROM
(SELECT actor_id, last_name, @currentRow := @currentRow + 1 AS row_number FROM
actor JOIN (SELECT @currentRow := 0) Row) Data
WHERE MOD(row_number, 10) = 0;

The cool row number technique is by Daniel Vassallo. With MySQL, how can I generate a column containing the record index in a table?

Good luck :)

Upvotes: 2

Related Questions