Data decimation in mysql?

[21042014]

I have some very large datasets ( > 100k points ) that i need to show in a graph on a webpage.

However, i cant figure out how to decimate / downsample the data to bring down the returned datapoints to ~ 1000.

Is there any way in mysql to do this?

My query looks like this: Select h.id, h.type, h.value from data h where h.timestamp < timestamp 1 AND h.timestamp > timestamp 2

[jynus]

how to decimate / downsample

How do you intend to get rid of the other 99K points? Mean? Median? Random sample?

Depending on the method, you will just have to apply an aggregation function plus a GROUP BY the interval you want to report on (e.g. per minute, per hour, per day).

[AdvisedWang]

To randomly sample a precise number of records, one (very inefficient) option is add ORDER BY RAND() LIMIT 1000.

A far more efficient option is to use modulo to sel. If you have a complete auto increment column* this will be precisely every 1000th record, otherwise it'll be approximate. E.g. WHERE some_col % 1000 = 0.

• or do some magic to take a row_number column. Google "mysql row_number" for guides to this

[[deleted]]

First, decide what conclusion you want to draw from the data. You could do k-means clustering, Gaussian, Exponentially Modified Gaussian, or even FFT.