SQLAlchemy：一个查询中有几个计数

I am having hard time optimizing my SQLAlchemy queries. My SQL knowledge is very basic, and I just can't get the stuff I need from the SQLAlchemy docs.

我很难优化我的SQLAlchemy查询。我的SQL知识非常基础,我无法从SQLAlchemy文档中获取所需的东西。

Suppose the following very basic one-to-many relationship:

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

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)

How could I:

我怎么能:

Query tuples of (Parent, count_of_naughty_children, count_of_all_children) for each parent?

查询每个父级的(Parent,count_of_naughty_children,count_of_all_children)元组?

After decent time spent googling, I found how to query those values separately:

花了大量时间用谷歌搜索后,我发现了如何分别查询这些值:

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

I tried to combine them in different ways, but didn't manage to get what I want.

我尝试以不同的方式将它们组合在一起,但却无法得到我想要的东西。

Query all parents which have more than 80% naughty children? Edit: naughty could be NULL.

查询所有顽皮孩子超过80%的父母?编辑:顽皮可能是NULL。

I guess this query is going to be based on the previous one, filtering by naughty/all ratio.

我猜这个查询将基于前一个查询,按淘气/所有比例过滤。

Any help is appreciated.

任何帮助表示赞赏。

EDIT : Thanks to Antti Haapala's help, I found solution to the second question:

编辑:感谢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)

It finds average if children's naughty variable, treating False and NULLs as 0, and True as 1. Tested with MySQL backend, but should work on others, too.

它找到平均值,如果孩子的顽皮变量,将False和NULLs视为0,将True视为1.使用MySQL后端测试,但也应该在其他人上工作。

2 个解决方案

#1

the count() sql aggretate function is pretty simple; it gives you the total number of non-null values in each group. With that in mind, we can adjust your query to give you the proper result.

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

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

Which produces the following sql:

哪个产生以下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

#2

If your query is only to get the parents who have > 80 % children naughty, you can on most databases cast the naughty to integer, then take average of it; then having this average greater than 0.8.

如果你的查询只是为了让那些有80%以上孩子的父母顽皮,你可以在大多数数据库中将淘气变成整数,然后取其平均值;那么这个平均值大于0.8。

Thus you get something like

因此你得到类似的东西

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

#1