SQLAlchemy: get average of greatest

I'm trying to create a Flask app (Postgresql database) that shows the latest score from individual players, including the average scores and goals for the team. So a player can have multiple scores, but on the leaderboard I only want to show her most recent score, and show an average number of goals for the whole team, based on the latest score per player.

My 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)

My 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)

'Latest score per player' is properly showing the latest entered score per player. But 'Average goals per player' returns an error:

AttributeError: 'BaseQuery' object has no attribute 'func'

How can I query for the average goals across all players for only the latest entered score per player?

Update: I feel I'm closer with:

#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()

But not really there yet. Struggling to get the func.avg to work together with the distinct and order_by.

---

If I understood you correctly, then you simply need to use latest scores per player as a subquery and average the results. Since you store the Query object in latest scores per player, you can use that:

@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)

This will do 2 trips to the DB. You could also just query the results of latest scores per player and calculate the average in Python. With a recent enough version of Python you can use the statistics module:

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)

Or do it yourself (add necessary conversions to float, if using Python 2):

    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/38720.html

上一篇: Lucene，Sphinx，Postgresql，MySQL？

下一篇: SQLAlchemy：获得最大的平均值