sql - 查询以获取日期范围内的计数、小计和总计

Question

假设我有一个带有 created_at 属性的对象。我想拥有将导致在给定日期创建的对象计数的查询(count_)、截至该日期 (sub) 的对象的小计，以及完整的所有对象的总数(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

我设法获得了 count_ 和 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()

生成此 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"

Answer 1

demo: db<>fiddle

您需要 cumulative window function .这可以通过添加 ORDER BY 子句来实现。如果存在，COUNT() 将如您所料累积。如果不存在，则 COUNT 将接管整个帧。

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

查询

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

所以虽然我不是很喜欢 Django，但我相信你需要这样一行:

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