Using mysql query or equivalent in Django

Question

Hi I have a query which is very straightforward to write in mysql but I'm having difficulty translating to Django:

SELECT year, month, AVG(mean_air_temp), AVG(min_air_temp), AVG(max_air_temp) from climate WHERE recorded_on >= '1978-09-09' AND recorded_on <= '1988-09-09' GROUP by month;

My data looks like this:

+----+------+-------+-------------+---------------+--------------+--------------+----------+--------------+---------------+
| id | year | month | recorded_on | mean_air_temp | min_air_temp | max_air_temp | sea_temp | mean_rel_hum | precipitation |
+----+------+-------+-------------+---------------+--------------+--------------+----------+--------------+---------------+
|  1 | 1964 |    12 | 1964-12-31  |          26.1 |         22.2 |         30.8 |     25.8 |           82 |             0 |
|  2 | 1965 |     1 | 1965-01-01  |          23.9 |           21 |         25.8 |     22.7 |           84 |           0.7 |
|  3 | 1965 |     1 | 1965-01-02  |          23.4 |         21.1 |         26.2 |     22.4 |           83 |             0 |

I am basically trying to get averages by month, and with an another query, by year in the most efficient way possible. The fields to be averaged will be python variables in the view, as will be the dates and group by method.

Is there a native Django solution or do I need to do something with .extra() (although I would like to avoid this so that I'm not tied to mysql)? I have looked at the docs for aggregate but they aren't helping in this situation.

Any help would be much, much appreciated.

Nix · Accepted Answer · 2012-04-03 11:46:49Z

1

Have you tried:

from django.db.models import Count
from django.db.models import Avg, Max, Min, Count


un_test_able = Climate.objects.filter(
                           recorded_on__gte='1978-09-09',
                           recorded_on__lte='1988-09-09')
                      .values('month')
                      .annotate(mean_air_temp=AVG('mean_air_temp'), 
                                min_air_temp=AVG('min_air_temp'), 
                                max_air_temp=AVG('max_air_temp')
                      )

Dynamic Version, not sure how dynamic you need but you can build a keyword arg dictionary however you like and pass it to the annotate function.

Example of 1 dynamic key...

key_mean = 'mean_air_temp'
query_value_mean='mean_air_temp'
kwargs = {
            key_mean:AVG(query_value_mean), 
            'min_air_temp':AVG('min_air_temp'), 
            'max_air_temp':AVG('max_air_temp')
}
un_test_able = Climate.objects.filter(
                           recorded_on__gte='1978-09-09',
                           recorded_on__lte='1988-09-09')
                      .values('month')
                      .annotate(**kwargs)
                      )

edited Apr 3, 2012 at 11:46

answered Apr 2, 2012 at 21:12

Nix

58.8k31 gold badges154 silver badges206 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

agf Over a year ago

Might want to specify names in annotate.

Darwin Tech Over a year ago

One further question - if I need dynamic annotation queries - how do I achieve that?That is, if I don't know which parameters I am going to query ahead of time, how can I add those clauses to annotate on the fly?

Collectives™ on Stack Overflow

Using mysql query or equivalent in Django

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related