python - SQLAlchemy:一个查询中的多个计数

Question

我很难优化我的 SQLAlchemy 查询。我的 SQL 知识非常基础，我只是无法从 SQLAlchemy 文档中获得我需要的东西。

假设以下非常基本的一对多关系:

class Parent(Base):
    __tablename__ = "parents"
    id = Column(Integer, primary_key = True)
    children = relationship("Child", backref = "parent")

class Child(Base):
    __tablename__ = "children"
    id = Column(Integer, primary_key = True)
    parent_id = Column(Integer, ForeignKey("parents.id"))
    naughty = Column(Boolean)

我怎么能:

• 查询每个 parent 的 (Parent, count_of_naughty_children, count_of_all_children) 的元组？

在谷歌搜索上花了不少时间后，我发现了如何分别查询这些值:

# The following returns tuples of (Parent, count_of_all_children):
session.query(Parent, func.count(Child.id)).outerjoin(Child, Parent.children).\
    group_by(Parent.id)
# The following returns tuples of (Parent, count_of_naughty_children):
al = aliased(Children, session.query(Children).filter_by(naughty = True).\
    subquery())
session.query(Parent, func.count(al.id)).outerjoin(al, Parent.children).\
    group_by(Parent.id)

我尝试以不同的方式组合它们，但没能得到我想要的。

• 查询 child 调皮率超过80%的所有家长？编辑:naughty 可以为 NULL。

我猜这个查询将基于前一个查询，按顽皮/所有比率过滤。

感谢任何帮助。

编辑:感谢 Antti Haapala 的帮助，我找到了第二个问题的解决方案:

avg = func.avg(func.coalesce(Child.naughty, 0)) # coalesce() treats NULLs as 0
# avg = func.avg(Child.naughty) - if you want to ignore NULLs
session.query(Parent).join(Child, Parent.children).group_by(Parent).\
    having(avg > 0.8)

如果 child 的 naughty 变量，将 False 和 NULL 视为 0，将 True 视为 1，它会找到平均值。已使用 MySQL 后端测试，但也应该适用于其他后端。

Answer 1

count() sql 聚合函数非常简单；它为您提供每组中非空值的总数。考虑到这一点，我们可以调整您的查询以提供正确的结果。

print (Query([
    Parent,
    func.count(Child.id),
    func.count(case(
        [((Child.naughty == True), Child.id)], else_=literal_column("NULL"))).label("naughty")])

    .join(Parent.children).group_by(Parent)
    )

生成以下 sql:

SELECT 
 parents.id AS parents_id, 
 count(children.id) AS count_1, 
 count(CASE WHEN (children.naughty = 1) 
       THEN children.id 
       ELSE NULL END) AS naughty 
FROM parents 
JOIN children ON parents.id = children.parent_id 
GROUP BY parents.id