我使用的是Postgresql。我有型号:
class EventsList(CreatedUpdatedMixin):
start = models.DateTimeField()
end = models.DateTimeField()
is_inner = models.BooleanField()
假设我有这些DB条目:
开始 | 结束 | is_inner | |
---|---|---|---|
2021-12-09 14:30:12 | 2021-12-09 15:00:21 | true||
2021-12-09 14:00:05 | 2021-12-10 21:00:15 | 错误 | |
2021-12-10 09:00:39 | 2021-12-10 09:30:50 | 真 | |
2021-12-10 14:00:00 | 2021-12-11 15:00:00 | true | |
2021-12-14 10:00:00 | 2021-12-14 11:00:00 | true | |
2021-12-13 13:30:00 | 2021-12-16 14:30:00 | false | |
2021-12-14 13:10:00 | 2021-12-15 00:30:00 | 真[/tr>||
2021-12-14 10:30:00 | 2021-12-16 13:34:00 | 错误 | |
2021-12-15 13:30:00 | 2021-12-15 18:30:00 | 真 |
使用原始SQL,首先使用generate_series
和lateral join
展开日期列表,然后进行条件聚合。所以它在这里,有点冗长,但我希望很容易阅读。SQL Fiddle
with t as
(
select is_inner, d::date from the_table
cross join lateral generate_series
(
date_trunc('day', "start"),
date_trunc('day', "end"),
interval '1 day'
) as d
)
select d as event_date,
count(*) filter (where not is_inner) external_events,
count(*) filter (where is_inner) internal_events
from t
group by d order by d;
您也可以使用jsonb_build_object
来形成问题中的JSON结构,如下所示:
with t as
(
select is_inner, d::date from the_table
cross join lateral generate_series
(
date_trunc('day', "start"),
date_trunc('day', "end"),
interval '1 day'
) as d
)
select jsonb_build_object
(
d::text,
jsonb_build_object('external_events', count(*) filter (where not is_inner),
'internal_events', count(*) filter (where is_inner))
) as date_info
from t
group by d order by d;
date_info |
---|
{〃2021-12-09〃:{〃external_events〃:1,"internal_events":1}} |
{"2021-12-10":{"外部事件":1,"内部事件":2}} |
{"2021-12-11":{"外部事件":0,"内部事件":1}} |
{"2021-12-13":{"外部事件":1,"内部事件":0}} |
{"2021-12-14":{"外部事件":2,"内部事件":2}} |
{"2021-12-15":{"外部事件":2,"内部事件":2}} |
{"2021-12-16":{"外部事件":2,"内部事件":0}} |
首先,要获取数据库中没有的所有日期,您需要在最大和最小日期之间循环以获取所有日期,并为每个日期运行查询以匹配所需的过滤器。