0

I have a MySQL DB with a floating point data (one value for every minute) which should be displayed in an html canvas line chart.

Now to not overload the chart with the huge amount of data, I would like to sample the data values when doing the query so that the total number of rows gets averaged to x rows.

Can the AVG function be used for this or should I write a php function that averages the data "manually" ?

clamp
  • 33,000
  • 75
  • 203
  • 299
  • Please explain what YOU mean by "sample the data". Providing example data and desired results would help. – Gordon Linoff Feb 23 '20 at 18:17
  • *Can the AVG function be used for this* Easily. Define the amount of points to be combined into one value (or amount of sample points safe for a chart), and perfirm according query. – Akina Feb 23 '20 at 18:17
  • @GordonLinoff imagine one float per minute. i.e. the current temperature in Celsius. – clamp Feb 23 '20 at 18:19
  • @Akina Thanks, but how would i do that exactly? Do you have an example? – clamp Feb 23 '20 at 18:24
  • Yes you can group by time and let mysql calculate the numbers in that time frame. If you give some sample data, we can give you a mysql example to build on – nbk Feb 23 '20 at 18:29
  • @nbk: Thanks, how exactly would that look like? – clamp Feb 23 '20 at 18:30
  • that this answer https://stackoverflow.com/a/2794020/5193536 and put another column withg AVG(temperature), MAX and so on Se also aggreation functions https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html – nbk Feb 23 '20 at 18:33
  • Use [NTILE()](https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_ntile) and group by it. The amount of groups = the amount of values in the chart. – Akina Feb 23 '20 at 18:48
  • 1
    @clamp . . . I don't want to "imagine" anything. I would like to see sample data and desired results that illustrate what your intention is. – Gordon Linoff Feb 23 '20 at 20:25

0 Answers0