我很难优化我的SQLAlchemy查询.我的SQL知识非常基础,而且我无法从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.
假设以下非常基本的一对多关系:
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)我怎么做:
- 查询每个父级的(Parent, count_of_naughty_children, count_of_all_children)元组吗?
- Query tuples of (Parent, count_of_naughty_children, count_of_all_children) for each parent?
在花费大量时间进行谷歌搜索之后,我发现了如何分别查询这些值:
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.
- 查询所有顽皮孩子超过80%的父母吗?顽皮可能为NULL.
我猜想此查询将基于上一个查询,并按顽皮/所有比率进行过滤.
I guess this query is going to be based on the previous one, filtering by naughty/all ratio.
感谢您的帮助.
:感谢Antti Haapala的帮助,我找到了第二个问题的解决方案:
EDIT : Thanks to Antti Haapala's help, I found solution to the second question:
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后端进行了测试,但也应该适用于其他变量.
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.
推荐答案count() sql aggretate函数非常简单;它为您提供每个组中非空值的总数.考虑到这一点,我们可以调整您的查询以为您提供适当的结果.
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.
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:
Which produces the following 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更多推荐
SQLAlchemy:一个查询中有多个计数
发布评论