Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The reason your listed query always gives 1 is because you're not grouping by date. Basically, you've asked the database to take the <code>MessageLog</code> rows that fall on a given day of the week. For each such row, count how many ids it has (always 1). Then take the average of all those counts, which is of course also 1.</p> <p>Normally, you would need to use a <a href="https://docs.djangoproject.com/en/dev/topics/db/aggregation/#values" rel="nofollow noreferrer"><code>values</code></a> clause to group your <code>MessageLog</code> rows prior to your <code>annotate</code> and <code>aggregate</code> parts. However, since your <code>logtime</code> field is a datetime rather than just a date, I am not sure you can express that directly with Django's ORM. You can definitely do it with an <code>extra</code> clause, as shown <a href="https://stackoverflow.com/questions/2278076/count-number-of-records-by-date-in-django">here</a>. Or if you felt like it you could declare a view in your SQL with as much of the aggregating and average math as you liked and declare an unmanaged model for it, then just use the ORM normally.</p> <p>So an <code>extra</code> field works to get the total number of records per actual day, but doesn't handle aggregating the average of the computed annotation. I think this may be sufficiently abstracted from the model that you'd have to use a raw SQL query, or at least I can't find anything that makes it work in one call.</p> <p>That said, you already know how you can get the total number of records per weekday in a simple query as shown in your question.</p> <p>And this query will tell you how many distinct date records there are on a given weekday:</p> <pre><code>MessageLog.objects.filter(logtime__week_day=i).dates('logtime', day').count() </code></pre> <p>So you could do the averaging math in Python instead, which might be simpler than trying get the SQL right.</p> <p>Alternately, this query will get you the raw number of messages for all weekdays in one query rather than a for loop:</p> <pre><code>MessageLog.objects.extra({'weekday': "dayofweek(logtime)"}).values('weekday').annotate(Count('id')) </code></pre> <p>But I haven't been able to get a nice query to give you the count of distinct dates for each weekday annotated to that - dates querysets lose the ability to handle annotate calls, and annotating over an <code>extra</code> value doesn't seem to work either.</p> <p>This has been surprisingly tricky, given that it's not that hard a SQL expression.</p>
    singulars
    1. This table or related slice is empty.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. This table or related slice is empty.
    2. VO
      singulars
      1. This table or related slice is empty.
 

Querying!

 
Guidance

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload