值为x的表中的SQLAlchemy only func.rank（）行

Question

在我的previous question中，我根据一个表在该表中的值获得了该表的排名。我现在可以根据给定的分数对电影进行排名（使用下表和方法）：

class MoviePersonScores(db.Model):
     movie_id = db.Column(db.Integer, db.ForeignKey('movie.id'), primary_key=True)
     person_id = db.Column(db.Integer, db.ForeignKey('person.id'), primary_key=True)
     score = db.Column(db.Integer)
     person = relationship("Person", back_populates="movies")
     movie = relationship("Movie", back_populates="people")

以及@YaakovBressler提供的答复：

from sqlalchemy import func

query = session.query(
    MoviePersonScores, 
    func.rank()\
        .over(
            order_by=MoviePersonScores.score.desc()
        )\
        .label('rank')
)
# now filter
query = query.filter_by(movie_id=movie_id)
# now execute:
my_movie = query.all()

这对于获得所有行相对的排名非常有用但现在我想根据电影中的人物进行排名。所以person_id可能是导演的ID，所以现在如果我想要昆汀·塔伦蒂诺所有电影的排名，我首先需要过滤数据库中他所有的电影，然后应用排名，那么，对于已经查询和过滤的行集合，我将如何使用上述方法呢

提前谢谢你

Answer 1

此新查询的解决方案将与上一个类似，只是您将包括一个类似于groupby方法的partition_by参数，以及一个用于指定给定person_id的filter_by：

query = session.query(
    MoviePersonScores, 
    func.rank()\
        .over(
            order_by=MoviePersonScores.score.desc(),
            partition_by=MoviePersonScores.person_id,
        )\
        .label('rank')
)

# If you want to filter for a specific person, filter your query
my_person_id = 1 # substitute this for the correct id
query = query.filter(MoviePersonScores.person_id == my_person_id)

# I'd suggest ordering by the person_id, so you get neat results when debugging
query = query.order_by(MoviePersonScores.person_id, 'rank')

# Convert to a subquery so you can apply more filters
all_movies = query.subquery()

# Now filter your subquery based on a specific movie
movie_id = 1 # some movie ID
query = session.query(all_movies).filter(all_movies.c.movie_id == movie_id)

# Get all results
result = query.all()