CODEWITHSUNDEEP
sqldjangopostgresqldjango-modelsdjango-orm
minusf
minusf

Reputation: 1373

query to get count, subtotal and total over a date range

Say I have objects with a created_at attribute. I would like to have a query that will result in the counts of created objects on a given date (count_), subtotal of objects up to that date (sub), and the full total of all objects (total_):

  date      | count_ |   sub | total_
------------+--------+-------+-------
 2018-10-08 |      1 |     1 |    15
 2018-10-11 |      2 |     3 |    15
 2018-10-15 |      3 |     6 |    15
 2018-10-23 |      4 |    10 |    15
 2018-10-24 |      5 |    15 |    15

I managed to get count_ and total_:

Obj.objects.annotate(
    date=Trunc('created_at', 'day', output_field=DateField())
).values(
    'date'
).annotate(
    count_=Window(expression=Count('id'), partition_by=[F('date')]),
    total_=Window(expression=Count('id'))
).distinct()

generating this SQL:

SELECT DISTINCT DATE_TRUNC('day', "obj_obj"."created_at") AS "date",
       COUNT("obj_obj"."id") OVER (PARTITION BY DATE_TRUNC('day', "obj_obj"."created_at")) AS "count_",
       COUNT("obj_obj"."id") OVER () AS "total_"
  FROM "obj_obj"

Upvotes: 2

Views: 254

Answers (1)

S-Man
S-Man

Reputation: 23756

demo: db<>fiddle

You need the cumulative window function. This can be achieved by adding an ORDER BY clause. If this exists, the COUNT() will be cumulative as you expect. If it doesn't exist, the COUNT will be taken over the whole frame.

count(created_at) OVER (ORDER BY created_at) as subtotal

The query

SELECT DISTINCT
    created_at,
    count(created_at) OVER (PARTITION BY created_at) as count_,
    count(created_at) OVER (ORDER BY created_at) as subtotal_,
    count(created_at) OVER () as total_
FROM
    dates

So although I am not really into Django I believe you need a line like:

subtotal_=Window(expression=Count('id'), order_by=[F('date')]),

Upvotes: 2

Related Questions