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)

我想要：

• 查询每个父母的元组 (父母, 顽皮孩子的数量, 所有孩子的数量)？

经过一段时间的搜索，我找到了如何分别查询这些值的方法：

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

我尝试了不同的组合方式，但没有成功得到我想要的结果。

• 查询所有顽皮孩子超过80%的父母？补充说明：顽皮孩子的数量可以为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)

这个查询计算孩子的 naughty 变量的平均值，把False和NULL当作0，True当作1。已经在MySQL后端测试过，但应该在其他数据库上也能用。

Answer 1

如果你只是想找出那些有超过80%调皮孩子的家长，你可以在大多数数据库中把调皮这个字段转换成整数，然后计算它的平均值；接着用having来筛选出这个平均值大于0.8的记录。

这样你就能得到类似下面的结果：

from sqlalchemy.sql.expression import cast

naughtyp = func.avg(cast(Child.naughty, Integer))
session.query(Parent, func.count(Child.id), naughtyp).join(Child)\
    .group_by(Parent.id).having(naughtyp > 0.8).all()

Answer 2

在SQL中，count()这个聚合函数其实很简单；它会告诉你每个组里有多少个非空值。知道这一点后，我们可以调整你的查询，让它返回正确的结果。

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