Aggregation question

[Viktor Bale]

This is confusing me. 

In the topic guide on Aggregation (https://docs.djangoproject.com/en/1.10/topics/db/aggregation/#cheat-sheet), there is an example in the cheat sheet as follows:

# Cost per page
>>> from django.db.models import F, FloatField, Sum
>>> Book.objects.all().aggregate(
...    price_per_page=Sum(F('price')/F('pages'), output_field=FloatField()))
{'price_per_page': 0.4470664529184653}

Does this mean:

i) Calculate the price per page for each book (F('price')/F('pages')) and then aggregate these over all books using the Sum function, or

ii) Aggregate the price using the Sum function, and also the number of pages using the Sum function, and them divide the former by the latter. 

?

Interpretation (i) is what the statement seems to say but this will result in a fairly meaningless number (the sum of cost per page per book, which is dependant on the number of books and doesn't mean much, and is certainly not an average price per page or anything similar).

Interpretation (ii) would make more sense (it calculates the overall price per page over all books), but the fact that the expression (F('price')/F('pages')) is inside the Sum function suggests that this is not what is happening.

Additionally, to aid my understanding, depending on which interpretation is correct, what would the statement be to implement the other interpretation? e.g. is interpretation (i) is what the above statement is doing, how would interpretation (ii) be implmented? Or vice versa....

Thanks

[Viktor Bale]

Hi all

No takers, eh? Does this means it also confuses other more experienced people, or that it's a silly question? If the aggration function was Avg instead of Sum, then it would make perfect sense (the average price per page over all books)... but it doesn't, so it doesn't :-) 

Thanks

[Tim Graham]

As for me, this statement from that page applies, "It’s difficult to intuit how the ORM will translate complex querysets into SQL queries so when in doubt, inspect the SQL with str(queryset.query) and write plenty of tests."

I'm not good at figuring out at a glance what an aggregation query will do. Do some experimentation and you should be able to figure it out yourself.

[Viktor Bale]

Ok, you spurred me on to actually try it out. And the answer is interpretation (i) is correct. Which means the example in the cheat sheet is somewhat misleading, since it does't calculate price per page, rather the sum of prices per page for individual books.

BTW, while playing with that little conundrum, I couldn't work out how to display in the shell the SQL query the aggregate() function will execute before actually executing it. You can find it after the act using "from django.db import connection; print(connection.queries)". And printing str(queryset.query) doesn't work like for annotate(), since aggregate() is terminal and returns a dict (unlike annotate() which returns another queryset). Any ideas?

[Tim Graham]

Please submit a pull request to fix the documentation if you can.

There's an open ticket for a way to get the SQL for a terminal queryset method: https://code.djangoproject.com/ticket/18631

[刘是]

I have submitted a ticket: https://code.djangoproject.com/ticket/28233

在 2017年2月9日星期四 UTC+8上午7:24:24，Tim Graham写道：