在SQLAlchemy中按行号筛选

Question

如何在以下查询中筛选行号(row_number)等于1的结果:

query = session.query(Foo, func.row_number().over(
        partition_by=Foo.foo_field, order_by=desc(Foo.foo_date_time)).label("row_number"))

    query = query.filter(Foo.time_key <= time_key)
    # query = query.filter(row_number == 1)
    result = query.all()

- Mehrdad

Answer 1

我找到了它：

    row_number_column = func.row_number().over(partition_by=Foo.foo_field, order_by=desc(Foo.foo_date_time)).label('row_number')
    query = self.session.query(Foo)
    query = query.filter(Foo.time_key <= time_key)
    query = query.add_column(row_number_column)
    query = query.from_self().filter(row_number_column == 1)

- Mehrdad

Answer 2

也可以使用子查询来实现：

sub_query = session.query(
Foo,
func.row_number().over(partition_by=Foo.foo_field, order_by=desc(Foo.foo_date_time)).label("row_number")
)
sub_query = sub_query.filter(Foo.time_key <= time_key).subquery()
query = session.query(sub_query).filter(sub_query.c.row_number == 1)

它基本上生成一个相当于将 row_number 作为列添加的 SQL 语句。

编译该语句。

sub_query = session.query(
Foo,
func.row_number().over(partition_by=Foo.foo_field, order_by=desc(Foo.foo_date_time)).label("row_number")
)
sub_query = sub_query.filter(Foo.time_key <= time_key).subquery()
query = session.query(sub_query).filter(sub_query.c.row_number == 1)

str(query.statement.compile())

将生成以下内容：

-- SubQuery function
SELECT anon_1.time_key, 
       anon_1.foo_field, 
       anon_1.foo_date_time, 
       anon_1.row_number 
FROM (
        SELECT  foo.time_key AS time_key,
                foo.foo_field AS foo_field, 
                foo.foo_date_time AS foo_date_time, 
                row_number() OVER (PARTITION BY foo.foo_field ORDER BY foo.foo_date_time DESC) AS row_number 
        FROM foo 
        WHERE foo.time_key <= %(time_key_1)s
    ) AS anon_1 
WHERE anon_1.row_number = %(row_number_1)s

更新: 请注意，自SQLAlchemy 1.4起，Query.from_self方法已被弃用，并将在2.0中删除，根据官方文档

如果有人正在使用该方法，请查阅迁移指南

- ivcuello

---

由于某种原因，使用subquery()会导致模型类丢失，并且以元组形式返回一行数据。有没有办法让它保留其类型？例如，在运行query.all()之后仍然是Foo类型？ - undefined

@phoxd。不确定在当前版本中是否会有相同的行为，我记得以前尝试过将整个对象添加到子查询中，以保持对模型类的引用，而不需要额外的参数，但那是很久以前的事情，对我来说并不是一个常见的用例，你需要自己测试一下。 - undefined