SQLAlchemy: несколько подсчетов в одном запросе

Мне трудно оптимизировать мои запросы 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, 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)

Пробовал комбинировать их по-разному, но не получилось получить то, что я хочу.

  • Опросите всех родителей, у которых более 80% непослушных детей? Изменить: непослушный может быть NULL.

Я предполагаю, что этот запрос будет основан на предыдущем, фильтруя по соотношению непослушные/все.

Любая помощь приветствуется.

EDIT: Благодаря помощи Антти Хаапала я нашел решение второго вопроса:

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, но должно работать и с другими.


python sql sqlalchemy orm querying
person kreo    schedule 23.07.2014    source источник

Ответы (2)


arrow_upward
11
arrow_downward

агрегатная функция 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
person SingleNegationElimination    schedule 23.07.2014

arrow_upward
5
arrow_downward

Если ваш запрос состоит только в том, чтобы получить родителей, у которых> 80% детей непослушны, вы можете в большинстве баз данных привести naughty к целому числу, а затем взять среднее значение; то 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()
person Antti Haapala    schedule 23.07.2014
comment
Большое спасибо, это помогло. Но я не упомянул, что в моей реальной модели непослушный может быть нулевым - извините, моя вина. avg игнорирует нулевые значения, поэтому это решение не совсем то, что мне нужно. - person kreo; 23.07.2014
comment
func.coalesce() помог мне это исправить ^^ - person kreo; 23.07.2014