Django + PostgreSQL:填写范围内的缺失日期 [英] Django + PostgreSQL: Fill missing dates in a range

问题描述

我有一个表，其中的列之一为 date .每个日期可以有多个条目.

I have a table with one of the columns as date. It can have multiple entries for each date.

date         .....
-----------  -----
2015-07-20     ..
2015-07-20     ..
2015-07-23     ..
2015-07-24     ..

我想使用Django ORM和PostgreSQL作为数据库后端以以下形式获取数据:

I would like to get data in the following form using Django ORM with PostgreSQL as database backend:

date         count(date)
-----------  -----------
2015-07-20        2
2015-07-21        0       (missing after aggregation)
2015-07-22        0       (missing after aggregation)
2015-07-23        1
2015-07-24        1

对应的PostgreSQL查询:

WITH RECURSIVE date_view(start_date, end_date) 
AS ( VALUES ('2015-07-20'::date, '2015-07-24'::date) 
     UNION ALL SELECT start_date::date + 1, end_date 
     FROM date_view 
     WHERE start_date < end_date ) 
SELECT start_date, count(date) 
FROM date_view LEFT JOIN my_table ON date=start_date 
GROUP BY date, start_date 
ORDER BY start_date ASC;

我无法将此原始查询转换为Django ORM查询.

I'm having trouble translating this raw query to Django ORM query.

如果有人可以使用PostgreSQL作为数据库后端，提供带有/不带 Common Table Expressions 变通办法的示例ORM查询，则非常有用.

It would be great if someone can give a sample ORM query with/without a workaround for Common Table Expressions using PostgreSQL as database backend.

在此处:

我的首选是在数据库中进行尽可能多的数据处理，而实际上并没有涉及到演示文稿.我不希望在应用程序代码中这样做，只要它是一次数据库访问即可.

My preference is to do as much data processing in the database, short of really involved presentation stuff. I don't envy doing this in application code, just as long as it's one trip to the database

根据此答案 django不支持CTE本身，但答案似乎已经过时.

As per this answer django doesn't support CTE's natively, but the answer seems quite outdated.

参考文献:

• MySQL:即使没有记录也选择一个范围内的所有日期

带有查询(公用表表达式)

谢谢

推荐答案

我不认为您可以使用纯Django ORM做到这一点，而且我什至不确定是否可以使用执行直接使用原始SQL ，或卸载由应用程序层完成的要求.

I do not think you can do this with pure Django ORM, and I am not even sure if this can be done neatly with extra(). The Django ORM is incredibly good in handling the usual stuff, but for more complex SQL statements and requirements, more so with DBMS specific implementations, it is just not quite there yet. You might have to go lower and down to executing raw SQL directly, or offload that requirement to be done by the application layer.

您始终可以使用Python生成缺少的日期，但是如果元素的范围和数量很大，则会非常缓慢.如果AJAX要求将其用于其他用途(例如图表制作)，则可以将其卸载到Javascript.

You can always generate the missing dates using Python, but that will be incredibly slow if the range and number of elements are huge. If this is being requested by AJAX for other use (e.g. charting), then you can offload that to Javascript.

这篇关于Django + PostgreSQL:填写范围内的缺失日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！