CODEWITHSUNDEEP
mysqlchartsaverageintervals
devwebapp
devwebapp

Reputation: 135

MySQL get average grouped by intervals

I have a xy chart of a power curve. Power on the y axis and wind on x axis.

I get the values from a MySQL table.

How can I get the average Power between intervals of 1 in thew wind column.

Exemple:

Wind | Power
10.2   1245
10.2   1245
9.7    1145
8.7    1001
11.1   1345
9.3    1100
10.6   1284
8       987
5.5     352
...

What I need is:

Wind | Avg(Power)
0-1    ...
1-2    ... 
2-3    ...
...

Thank you in advance

EDIT:

Thank you all for your answwers!

For my particular case the wind is always between 0 and 25 m/s. The intervals that I need to get the average power from are 0.5.

So:

0 - 0,25
0,25 - 0,75
0,75 - 1,25
1,25 - 1,75
... - 25

Upvotes: 1

Views: 117

Answers (2)

jtate
jtate

Reputation: 2696

I think this in this case it is better to use a GROUP BY CASE statement.

SELECT CASE 
                WHEN Wind < 1 THEN '0-1'
                WHEN Wind >= 1 AND Wind < 2 THEN '1-2'
                WHEN Wind >= 2 AND Wind < 3 THEN '2-3'
                WHEN Wind >= 3 AND Wind < 4 THEN '3-4'
                WHEN Wind >= 4 AND Wind < 5 THEN '4-5'
                WHEN Wind >= 5 AND Wind < 6 THEN '5-6'
                WHEN Wind >= 6 AND Wind < 7 THEN '6-7'
                WHEN Wind >= 7 AND Wind < 8 THEN '7-8'
                WHEN Wind >= 8 AND Wind < 9 THEN '8-9'
                WHEN Wind >= 9 AND Wind < 10 THEN '9-10'
                WHEN Wind >= 10 AND Wind < 11 THEN '10-11'
                WHEN Wind >= 11 AND Wind < 12 THEN '11-12'
                ELSE 'Other'
            END as Wind,
        AVG(Power) as Power
FROM power
GROUP BY CASE 
                WHEN Wind < 1 THEN '0-1'
                WHEN Wind >= 1 AND Wind < 2 THEN '1-2'
                WHEN Wind >= 2 AND Wind < 3 THEN '2-3'
                WHEN Wind >= 3 AND Wind < 4 THEN '3-4'
                WHEN Wind >= 4 AND Wind < 5 THEN '4-5'
                WHEN Wind >= 5 AND Wind < 6 THEN '5-6'
                WHEN Wind >= 6 AND Wind < 7 THEN '6-7'
                WHEN Wind >= 7 AND Wind < 8 THEN '7-8'
                WHEN Wind >= 8 AND Wind < 9 THEN '8-9'
                WHEN Wind >= 9 AND Wind < 10 THEN '9-10'
                WHEN Wind >= 10 AND Wind < 11 THEN '10-11'
                WHEN Wind >= 11 AND Wind < 12 THEN '11-12'
                ELSE 'Other'
            END

This way if you want to change the range that each group covers you can do that fairly easily.

Upvotes: 2

O. Jones
O. Jones

Reputation: 108706

This can be an aggregate query in which you use an injective function to categorize your individual observations. The injective function is FLOOR(Wind).

Here's the query (http://sqlfiddle.com/#!2/9e64b6/9/0), including the display stuff for ranges.

SELECT CONCAT(FLOOR(Wind),'-',FLOOR(Wind)+1) AS Wind, 
       AVG(Power) AS Power
  FROM power
 GROUP BY FLOOR(Wind)
 ORDER BY FLOOR(Wind)

This, of course, uses the MySQL AVG() function, generating an arithmetic average. I think the kind of application you're working on prefers a geometric average, but I'm not sure about that.

It also yields a sparse result set.

Upvotes: 3

Related Questions