Number of entries in Django per day

Question

I have a django application that makes some notes. My model looks like this:

class MessageLog(models.Model): logtime = models.DateTimeField(auto_now_add=True) user = models.CharField(max_length=50) message = models.CharField(max_length=512) 

What needs to be done is to get the average number of messages recorded on the day of the week so I can see which days are the most active. I was able to write a query that pulls out the total number of messages per day, which:

 for i in range(1, 8): MessageLog.objects.filter(logtime__week_day=i).count() 

But it's hard for me to calculate the average value in the query. Now I have:

 for i in range(1, 8): MessageLog.objects.filter(logtime__week_day=i).annotate(num_msgs=Count('id')).aggregate(Avg('num_msgs')) 

For some reason, this returns 1.0 for each day. I looked at the SQL that it generates, and this:

 SELECT AVG(num_msgs) FROM ( SELECT `myapp_messagelog`.`id` AS `id`, `myapp_messagelog`.`logtime` AS `logtime`, `myapp_messagelog`.`user` AS `user`, `myapp_messagelog`.`message` AS `message`, COUNT(`myapp_messagelog`.`id`) AS `num_msgs` FROM `myapp_messagelog` WHERE DAYOFWEEK(`myapp_messagelog`.`logtime`) = 1 GROUP BY `myapp_messagelog`.`id` ORDER BY NULL ) subquery 

I think the problem may arise from the GROUP BY identifier, but I'm not sure. Anyone have any ideas or suggestions? Thanks in advance!

Answer 1

The reason your specified query always gives 1 is because you are not grouping by date. Basically, you asked the database to take the MessageLog lines that fall on that day of the week. For each such line, count the number of identifiers (always 1). Then we take the average of all these samples, which, of course, is 1.

Usually you need to use values to group MessageLog lines before your annotate and aggregate parts. However, since your logtime field is a date-time, not just a date, I'm not sure if you can express it directly using Django ORM. You can definitely do this with the extra clause, as shown here . Or, if it seems to you, you can declare a view in your SQL with the same total and average math as you like, and declare an unmanaged model for it, and then just use ORM as usual.

So, the extra field works to get the total number of records for the actual day, but does not process aggregation of the average value of the calculated annotation. I think this can be quite abstract from the model, that you have to use an unprocessed SQL query, or at least I cannot find anything that would make it work in one call.

However, you already know how you can get the total number of entries on a weekday in a simple query, as shown in your question.

And this query will tell you how many different date entries there are on a given day of the week:

 MessageLog.objects.filter(logtime__week_day=i).dates('logtime', day').count() 

So, you can do the math averaging in Python, which could be easier than trying to get SQL correctly.

Alternatively, this request will deliver you an unprocessed number of messages for all weekdays in one request, and not in a for loop:

 MessageLog.objects.extra({'weekday': "dayofweek(logtime)"}).values('weekday').annotate(Count('id')) 

But I couldn’t get a good request to give you the number of separate dates for each weekday annotated by this: request dates lose their ability to handle annotate calls and annotating by value extra doesn’t seem to work either.

This was surprisingly complex, given that it is not that complicated an SQL expression.

Answer 2

I am doing something similar with the datetime field, but adding comments to additional values ​​works for me. I have a Record model with a datetime field "create_at" and a field "my_value" for which I want to get the average value.

 from django.db.models import Avg qs = Record.objects.extra({'created_day':"date(created_at)"}).\ values('created_day').\ annotate(count=Avg('my_value')) 

Above, the datetime value in the "create_at" field will be grouped by day.