Django聚合只计算True值

Question

我使用aggregate来获取一列布尔值的计数。我想要True值的个数

DJANGO代码:

count = Model.objects.filter(id=pk).aggregate(bool_col=Count('my_bool_col')

返回所有行的计数。

SQL语句应该是:

SELECT count(CASE WHEN my_bool_col THEN 1 ELSE null END) FROM <table_name>

下面是我的实际代码:

stats = Team.objects.filter(id=team.id).aggregate(
    goals=Sum('statistics__goals'),
    assists=Sum('statistics__assists'),
    min_penalty=Sum('statistics__minutes_of_penalty'),
    balance=Sum('statistics__balance'),
    gwg=Count('statistics__gwg'),
    gk_goals_avg=Sum('statistics__gk_goals_avg'),
    gk_shutout=Count('statistics__gk_shutout'),
    points=Sum('statistics__points'),
)

感谢Peter DeGlopper建议使用django-aggregate-if

解决方案如下:

from django.db.models import Sum
from django.db.models import Q
from aggregate_if import Count
stats = Team.objects.filter(id=team.id).aggregate(
    goals=Sum('statistics__goals'),
    assists=Sum('statistics__assists'),
    balance=Sum('statistics__balance'),
    min_penalty=Sum('statistics__minutes_of_penalty'),
    gwg=Count('statistics__gwg', only=Q(statistics__gwg=True)),
    gk_goals_avg=Sum('statistics__gk_goals_avg'),
    gk_shutout=Count('statistics__gk_shutout', only=Q(statistics__gk_shutout=True)),
    points=Sum('statistics__points'),
)

Answer 1

为Django 1.10更新。现在可以执行条件聚合了:

from django.db.models import Count, Case, When
query_set.aggregate(bool_col=Count(Case(When(my_bool_col=True, then=1))))

更多信息见:

<
• https://docs.djangoproject.com/en/1.11/ref/models/conditional-expressions/案例/gh>

Answer 2

更新:

从Django 1.10开始，你可以:

from django.db.models import Count, Case, When
query_set.aggregate(
    bool_col=Count(
        Case(When(my_bool_col=True, then=Value(1)))
    )
)

阅读条件表达式类

老答案。

看起来你想做的是某种"条件聚合"。现在Aggregation函数不支持查找filter或exclude: fieldname__lt, fieldname__gt，…

你可以试试:

django-aggregate-if

描述摘自官方页面。

Django查询的条件聚合，就像Excel中著名的SumIf和CountIf。

您还可以首先为每个团队注释所需的值，我的意思是为每个团队计算您感兴趣的领域中True的数量。然后做所有你想做的聚合

Answer 3

count Bool的另一种解决方案是:

from django.db.models import Sum, IntegerField
from django.db.models.functions import Cast
Model.objects.filter(id=pk).annotate(bool_col=Sum(Cast('my_bool_col', IntegerField())))

将False和True分别转换为0和1，然后再转换为Sum