在Flask-SQLAlchemy中使用联接获取某天的最后一条记录

Question

我有一张数据表，里面有时间戳和一些值，我想要获取每天的最后一个值。

如果用原始的SQL语句，我会这样写：

SELECT strftime('%Y-%m-%d', a1.created_at) AS created_at,
       a1.my_value
FROM my_table a1
JOIN
  (SELECT max(id) AS MAX
   FROM mytable
   GROUP BY strftime('%Y-%m-%d', created_at)) a2 ON a1.id = a2.MAX;

我正在开发一个Flask应用程序，想要使用Flask-SQLAlchemy这个扩展，而不是用sqlalchemy.orm.session.Session或者原始的SQL语句。定义的模型看起来是这样的：

class MyModel(SurrogatePK, Model):
  __tablename__ = 'my_table'
  id = Column(db.Integer(), nullable=False, primary_key=True)
  created_at = Column(db.DateTime(), nullable=False, default=dt.datetime.utcnow)
  my_value = Column(db.Integer(), nullable=True)

到目前为止，我有：

外部查询：

MyModel.query.with_entities(MyModel.created_at, MyModel.my_value)...

内部查询：

MyModel.query.with_entities(func.max(MyModel.id).label('max')).group_by(func.strftime('%Y-%m-%d', MyModel.created_at))

但是我找不到方法把这两部分结合起来，以得到我想要的结果。

Answer 1

这是我在使用sqlalchemy时的做法，

from sqlalchemy import literal_column

a2 = session.query(func.max(mytable.id).label("MAX"))
     .group_by(func.convert(literal_column('DATE'), mytable.created_at)).subquery()

session.query(func.convert(literal_column('DATE'),
              mytable.created_at).label("created_at"),
              my_table.my_value).join(a2, my_table.id==a2.c.id)