SQLAlchemy：获得最大的平均值

我正在尝试创建一个Flask应用程序（Postgresql数据库），显示各个玩家的最新成绩，包括团队的平均得分和目标。 所以玩家可以有多个分数，但是在排行榜上，我只想显示她最近的分数，并根据每个玩家的最新分数显示整个团队的平均目标数。

我的Models.py

class Player(db.Model):
    __tablename__ = 'player'
    id = db.Column(db.Integer, primary_key=True)
    firstname = db.Column(db.String, nullable=False)
    score = db.relationship('Score', backref='player', lazy='dynamic')

    def __init__(self, firstname):
        self.firstname = firstname

    def __repr__(self):
        return '<id {}>'.format(self.id)


class Score(db.Model):
    __tablename__ = 'score'
    id = db.Column(db.Integer, primary_key=True)
    timestamp = db.Column(db.DateTime, nullable=False)
    score = db.Column(db.Integer, nullable=True)
    goals = db.Column(db.Integer, nullable=True)
    player_id = db.Column(db.Integer, db.ForeignKey('player.id'))

    def __init__(self, score, player_id):
        self.timestamp = datetime.now()
        self.score = score
        self.player_id = player_id

    def __repr__(self):
        return '<id {}>'.format(self.id)

我的app.py：

@app.route('/', methods=['GET', 'POST'])
@login_required
def home():

    #Latest score per player
    latest_scores_per_player = db.session.query(Player, Score).
        join(Score).
        distinct(Player.id).
        filter(Player.user_id == current_user.id).
        order_by(Player.id, Score.timestamp.desc())

    #Average goals per player
    avarage_goals = db.session.query(Player, Score).
        join(Score).
        distinct(Player.id).
        filter(Player.user_id == current_user.id).
        order_by(Player.id, Score.timestamp.desc()).
        func.avg(Score.goals).label('average').scalar()

    return render_template(
        'home.html',
        latest_scores_per_player=latest_scores_per_player,
        avarage_goals=avarage_goals)

'每名球员的最新得分'正确地显示了每位球员的最新输入分数。 但是“每名球员的平均进球数”会带来一个错误：

AttributeError：'BaseQuery'对象没有属性'func'

我如何才能查询所有球员的平均进球数，仅限每名球员最近输入的分数？

更新：我觉得我更接近：

#Average goals per player
avarage_goals = db.session.query(Player, Score, func.avg(Score.goals)).
  join(Score).
  distinct(Player.id).
  filter(Player.user_id == current_user.id).
  order_by(Player.id, Score.timestamp.desc()).scalar()

但还不是真的。 努力让func.avg与distinct和order_by一起工作。

---

如果我正确地理解了你，那么你只需要使用每个玩家的最新分数作为子查询并对结果进行平均。 由于您将查询对象存储在每个玩家的最新分数中，因此您可以使用该对象：

@app.route('/', methods=['GET', 'POST'])
@login_required
def home():

    # Latest score per player
    latest_scores_per_player = db.session.query(Player, Score).
        join(Score).
        distinct(Player.id).
        filter(Player.user_id == current_user.id).
        order_by(Player.id, Score.timestamp.desc())

    # Create a subquery that queries only the goals from the groups
    subq = latest_scores_per_player.
        with_entities(Score.goals).
        subquery()

    #Average goals *across all players*
    average_goals = db.session.query(func.avg(subq.c.goals)).scalar()

    return render_template(
        'home.html',
        latest_scores_per_player=latest_scores_per_player,
        avarage_goals=average_goals)

这将做2次去DB。 你也可以查询每个玩家最近得分的结果并用Python计算出平均值。 使用最新版本的Python，您可以使用统计模块：

import statistics

@app.route('/', methods=['GET', 'POST'])
@login_required
def home():

    # Latest score per player
    # Materialize the results to a list here for calculating average
    latest_scores_per_player = db.session.query(Player, Score).
        join(Score).
        distinct(Player.id).
        filter(Player.user_id == current_user.id).
        order_by(Player.id, Score.timestamp.desc()).
        all()

    average_goals = None
    if latest_scores_per_player:
        average_goals = statistics.mean(
            score.goals for player, score in latest_scores_per_player)

    return render_template(
        'home.html',
        latest_scores_per_player=latest_scores_per_player,
        avarage_goals=average_goals)

或者自己动手（如果使用Python 2，添加必要的转换为float）：

    average_goals = None
    if latest_scores_per_player:
        average_goals = sum(
            score.goals for player, score in latest_scores_per_player)
        average_goals /= len(latest_scores_per_player)

链接地址: http://www.djcxy.com/p/38719.html

上一篇: SQLAlchemy: get average of greatest

下一篇: Reorder of SQLAlchemy Query results based on external ranking