SQLAlchemy group_by日期并按计数聚合，如何填写缺失的日期

Question

我有一个看起来像这样的sqlalchemy查询。

首先，我按时间戳对 Pomo 模型进行分组，然后按创建 Pomo 的日期进行分组。

db.session.query(Pomo.timestamp, sa.func.count(Pomo.id))
               .group_by(sa.func.date(Pomo.timestamp)).all()

这将返回如下所示的数据

[(datetime.datetime(2018, 3, 2, 0, 0), 1),
(datetime.datetime(2018, 3, 7, 0, 0), 1),
(datetime.datetime(2018, 3, 8, 0, 0), 6)]

如何填写日期，使输出类似于

[(datetime.datetime(2018, 3, 2, 0, 0), 1),
(datetime.datetime(2018, 3, 3, 0, 0), 0),
(datetime.datetime(2018, 3, 4, 0, 0), 0),
(datetime.datetime(2018, 3, 5, 0, 0), 0),
(datetime.datetime(2018, 3, 6, 0, 0), 0),
(datetime.datetime(2018, 3, 7, 0, 0), 1),
(datetime.datetime(2018, 3, 8, 0, 0), 6)]

Answer 1

使用 generate_series() 生成所需范围内的所有日期，并向左连接数据，将缺失值合并为 0：

In [24]: series = db.session.query(
    ...:         db.func.generate_series(db.func.min(Pomo.timestamp),
    ...:                                 db.func.max(Pomo.timestamp),
    ...:                                 timedelta(days=1)).label('ts')).
    ...:     subquery()
    ...:                             
In [25]: values = db.session.query(Pomo.timestamp,
    ...:                           db.func.count(Pomo.id).label('cnt')).
    ...:     group_by(Pomo.timestamp).
    ...:     subquery()
In [26]: db.session.query(series.c.ts,
    ...:                  db.func.coalesce(values.c.cnt, 0)).
    ...:     outerjoin(values, values.c.timestamp == series.c.ts).
    ...:     order_by(series.c.ts).
    ...:     all()
    ...: 
Out[26]: 
[(datetime.datetime(2018, 3, 2, 0, 0), 1),
 (datetime.datetime(2018, 3, 3, 0, 0), 0),
 (datetime.datetime(2018, 3, 4, 0, 0), 0),
 (datetime.datetime(2018, 3, 5, 0, 0), 0),
 (datetime.datetime(2018, 3, 6, 0, 0), 0),
 (datetime.datetime(2018, 3, 7, 0, 0), 1),
 (datetime.datetime(2018, 3, 8, 0, 0), 6)]