sqlalchemy结合分组行的总和和计数 [英] sqlalchemy join with sum and count of grouped rows

问题描述

我正在使用flask-sqlalchemy在Flask中进行一个小预测游戏，我有一个用户模型:

Hi i am working on a little prediction game in flask with flask-sqlalchemy I have a User Model:

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    nick = db.Column(db.String(255), unique=True)
    bets = relationship('Bet', backref=backref("user"))

和我的投注模型

class Bet(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    uid = db.Column(db.Integer, db.ForeignKey('user.id'))
    matchid = db.Column(db.Integer, db.ForeignKey('match.id'))
    points = db.Column(db.Integer)

两者都不是完整的类，但应该针对问题进行处理.用户可以收集分数以预测比赛结果，并获得不同数量的分数以预测确切的结果，获胜者或与众不同. 现在，我想列出最重要的用户，在这里我必须总结我通过这些观点所做的要点

Both are not the full classes but it should do it for the question. A user can gather points for predicting the match outcome and gets different amount of points for predicting the exact outcome, the winner or the difference. I now want to have a list of the top users, where i have to sum up the points which i'm doing via

toplist = db.session.query(User.nick, func.sum(Bet.points)).\
    join(User.bets).group_by(Bet.uid).order_by(func.sum(Bet.points).desc()).all()

这很好，现在可能有两个玩家得到相同的总积分的情况.在这种情况下，正确的预测量(奖励3分)将确定获胜者.我可以通过以下方式获得此列表

This works quite good, now there maybe the case that two players have the same sum of points. In this case the amount of correct predictions (rewarded with 3 points) would define the winner. I can get this list by

tophits = db.session.query(User.nick, func.count(Bet.points)).\
            join(User.bets).filter_by(points=3).all()

它们都很好地工作，但是我认为必须有一种方法可以将两个查询放在一起，并获得一个包含用户名，点数和"hitcount"的表.我以前在SQL中已经做到了，但是我对SQLAlchemy和大脑中的思想难题并不那么熟悉.如何将两个查询合而为一?

They both work well, but I think there has to be a way to get both querys together and get a table with username, points and "hitcount". I've done that before in SQL but i am not that familiar with SQLAlchemy and thought knots in my brain. How can I get both queries in one?

推荐答案

在查询tophits的过程中，只需将COUNT/filter_by构造替换为等效的SUM(CASE(..))而没有filter，这样两者的WHERE子句就是相同.下面的代码应该做到这一点:

In the query for tophits just replace the COUNT/filter_by construct with equivalent SUM(CASE(..)) without filter so that the WHERE clause for both is the same. The code below should do it:

total_points = func.sum(Bet.points).label("total_points")
total_hits = func.sum(case(value=Bet.points, whens={3: 1}, else_=0)).label("total_hits")
q = (session.query(
        User.nick,
        total_points,
        total_hits,
        )
    .join(User.bets)
    .group_by(User.nick)
    .order_by(total_points.desc())
    .order_by(total_hits.desc())
    )

---

请注意，我更改了group_by子句以使用SELECT中的列，因为某些数据库引擎可能会抱怨.但是您不需要这样做.

---

Note that i changed a group_by clause to use the column which is in SELECT, as some database engines might complain otherwise. But you do not need to do it.

这篇关于sqlalchemy结合分组行的总和和计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！