如何使用Django查询从不同字段获取每个值的计数? [英] How to get the count of each value from different fields using Django Queries?

问题描述

我有一个包含许多字段的Django模型.假设模型是这样的:

I have a Django model with many fields. Let's say the model is like this:

class Foo(models.Model):
    name = models.CharField(max_length=50)
    type = models.CharField(max_length=100, blank=True)
    foo_value = models.CharField(max_length=14, blank=True)
    # ... and many many other fields

现在，我需要运行查询以获取所有字段中的所有数据.这是Foo.objects.all()，对吗?

Now I need to run a query to get me all the data from all fields. This would be Foo.objects.all(), right?

现在我需要每个名称(这意味着我将按名称分组)来执行某些操作.首先，问题是如果我想按名称分组，我会这样做:Foo.objects.values('name')，对吗?但这只为所有记录提供了" 名称 "字段值.我需要所有其他字段，但按 名称 分组.

Now I need for each name (which means I will group by name) to do some things. First, the problem is if I want to group by name I would do this: Foo.objects.values('name'), right? But this gets me the 'name' field value only for all records. I need all the other fields but grouped by name.

第二个更重要的事情是，我需要获取" 类型 "字段中每个不同值的计数以及" 类型 "相关联的> foo_value "字段.我需要所有这些都成为返回结果中每个" 名称 "的记录.问题是，如果我尝试这样做:Foo.objects.values('name').annotate(c=Count('type'), s=Sum('foo_value'))，它将为我记录每个(' 名称 '，' 类型 ')对.
我需要的是如果我有这样的数据:

The second more important thing is, I need to get the count of each distinct value in the 'type' field as well as the sum of the 'foo_value' field associated with each 'type'. I need all this to be one record per 'name' in the returned result. The issue is, if I tried this: Foo.objects.values('name').annotate(c=Count('type'), s=Sum('foo_value')), it will get me a record for each ('name', 'type') pair.
What I need is that if I have data like this:

名称   类型     foo_value
x      t1          5.5 x                10.0
x     t2          20.0
y      t2                 15.23
y                17.0

name    type    foo_value
x        t1        5.5
x        t1        10.0
x        t2        20.0
y        t2        15.23
y        t1        17.0

我需要的结果是:

名字 x               nspsp              b ;           1    b&b ;     20.0
y n 1 1 1 1 1 1 1 1 1&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&               b ;           1    b&b ;     15.23

name    type_t1_count    type_t1_sum    type_t2_count    type_t2_sum
x                2                15.5                                1                20.0
y                1                17.0                                1                15.23

如何实现这样的目标?

How to achieve something like this?

推荐答案

由于django 1.8具有条件表达式 然后您可以尝试使用order_by通过阅读文档:

Since django 1.8 has conditional-expressions and you can try, and you shoul use order_by to exclude default group by read docs:

from django.db.models import Count, Case, When, IntegerField, FloatField

qs = Foo.objects.values('name'
                   ).annotate(type_t1_count=Count(
                        Case(
                           When(type='t1', then=1),
                           output_field=IntegerField()
                        )
                        )
                    ).annotate(type_t1_sum=Sum(
                        Case(
                           When(type='t1', then='foo_value'),
                           default=0.0,
                           output_field=FloatField()
                        )
                        )
                    ).annotate(type_t2_count=Count(
                        Case(
                           When(type='t2', then=1),
                           output_field=IntegerField()
                        )
                        )
                    ).annotate(type_t2_sum=Sum(
                        Case(
                           When(type='t2', then='foo_value'),
                           default=0.0,
                           output_field=FloatField()
                        )
                        )
                    ).order_by('name')

结果是:

[
    {'type_t1_sum': 15.5, 'type_t1_count': 2,
     'type_t2_count': 1, 'type_t2_sum': 20.0,
     'name': 'x'},
    {'type_t1_sum': 17.0, 'type_t1_count': 1,
    'type_t2_count': 1, 'type_t2_sum': 15.23,
    'name': 'y'}
]

这篇关于如何使用Django查询从不同字段获取每个值的计数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！