models
class People(models.Model):
name = models.CharField(max_length=30)
age = models.CharField(max_length=30)
def __str__(self):
return self.name
insert into app03_people (name,age) values('m1','1');
查出最小的age
使用annotate(Min('age')出错
In [1]: from django.db.models import Max,Min,Sum,Avg,Count
In [2]: from app03.models import People
In [3]: People.objects.all()
Out[3]: <QuerySet [<People: m1>, <People: m2>, <People: m3>, <People: m4>]>
In [6]: People.objects.annotate(Min('age'))
Out[6]: <QuerySet [<People: m1>, <People: m2>, <People: m3>, <People: m4>]>
In [10]: print(People.objects.annotate(Min('age')).query)
SELECT `app03_people`.`id`, `app03_people`.`name`, `app03_people`.`age`, MIN(`app03_people`.`age`) AS `age__min` FROM `app03_people` GROUP BY `app03_people`.`id` ORDER BY NULL
小结: People.objects.annotate(Min('age'))默认group by id;
使用aggregate完美解决
In [13]: print(People.objects.aggregate(Min('age'))) # 主这里无法query, 因为他返回的是字典类型.
{'age__min': '1'}
等同的sql,(聚合函数可以单独使用)
mysql> SELECT MIN(`app03_people`.`age`) AS `age__min` FROM `app03_people`;
+----------+
| age__min |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
查询age最大最小
aggregate解决
In [15]: People.objects.aggregate(Min('age'),Max('age'))
Out[15]: {'age__min': '1', 'age__max': '4'}
等价的sql
mysql> SELECT MIN(`app03_people`.`age`) AS `age__min`,MAX(`app03_people`.`age`) AS `age__max` FROM `app03_people`;
+----------+----------+
| age__min | age__max |
+----------+----------+
| 1 | 4 |
+----------+----------+
1 row in set (0.00 sec)
annotate分组查询
In [6]: People.objects.values('part').annotate(Min('age'))
Out[6]: <QuerySet [{'part': 'UI', 'age__min': '10'}, {'part': 'python', 'age__min': '30'}, {'part': 'java', 'age__min': '40'}]>
In [7]: print(People.objects.values('part').annotate(Min('age')).query)
SELECT `app03_people`.`part`, MIN(`app03_people`.`age`) AS `age__min` FROM `app03_people` GROUP BY `app03_people`.`part` ORDER BY NULL
默认值显示两个字段(正常)
mysql> SELECT `app03_people`.`part`, MIN(`app03_people`.`age`) AS `age__min` FROM `app03_people` GROUP BY `app03_people`.`part` ORDER BY NULL
-> ;
+--------+----------+
| part | age__min |
+--------+----------+
| UI | 10 |
| python | 30 |
| java | 40 |
+--------+----------+
3 rows in set (0.00 sec)
In [17]: print(People.objects.values('part').annotate(Min('age'),Max('age')).query);
SELECT `app03_people`.`part`, MIN(`app03_people`.`age`) AS `age__min`, MAX(`app03_people`.`age`) AS `age__max` FROM `app03_people` GROUP BY `app03_people`.`part` ORDER BY NULL
mysql> SELECT `app03_people`.`part`, MIN(`app03_people`.`age`) AS `age__min`, MAX(`app03_people`.`age`) AS `age__max` FROM `app03_people` GROUP BY `app03_people`.`part` ORDER BY NULL;
+--------+----------+----------+
| part | age__min | age__max |
+--------+----------+----------+
| UI | 10 | 20 |
| python | 30 | 50 |
| java | 40 | 40 |
+--------+----------+----------+
3 rows in set (0.00 sec)
In [19]: print(People.objects.values('part').annotate(Min('age')).annotate(Max('age')).query);
SELECT `app03_people`.`part`, MIN(`app03_people`.`age`) AS `age__min`, MAX(`app03_people`.`age`) AS `age__max` FROM `app03_people` GROUP BY `app03_people`.`part` ORDER BY NULL
mysql> SELECT `app03_people`.`part`, MIN(`app03_people`.`age`) AS `age__min`, MAX(`app03_people`.`age`) AS `age__max` FROM `app03_people` GROUP BY `app03_people`.`part` ORDER BY NULL;
+--------+----------+----------+
| part | age__min | age__max |
+--------+----------+----------+
| UI | 10 | 20 |
| python | 30 | 50 |
| java | 40 | 40 |
+--------+----------+----------+
3 rows in set (0.00 sec)
以2个字段为准 分类
model
In [157]: print ChainLog.objects.values('src_svc_id', 'dst_svc_id').annotate(
...: is_success_total_count=Count('is_success')).query
SELECT `home_application_chainlog`.`src_svc_id`, `home_application_chainlog`.`dst_svc_id`, COUNT(`home_application_chainlog`.`is_success`) AS `is_success_total_count` FROM `home_application_chainlog` GROUP BY `home_application_chainlog`.`src_svc_id`, `home_application_chainlog`.`dst_svc_id` ORDER BY NULL
mysql> SELECT `home_application_chainlog`.`src_svc_id`, `home_application_chainlog`.`dst_svc_id`, COUNT(`home_application_chainlog`.`is_success`) AS `is_success_total_count` FROM `home_application_chainlog` GROUP BY `home_application_chainlog`.`src_svc_id`, `home_application_chainlog`.`dst_svc_id` ORDER BY NULL;
+------------+------------+------------------------+
| src_svc_id | dst_svc_id | is_success_total_count |
+------------+------------+------------------------+
| 1 | 1 | 2 |
| 1 | 2 | 1 |
| 2 | 3 | 1 |
| 2 | 4 | 1 |
| 3 | 5 | 1 |
+------------+------------+------------------------+
5 rows in set (0.00 sec)
如果是py字典,以2个字段为维度,不好统计
total = [
{'from':1,'to':2,'is_success':1},
{'from':1,'to':3,'is_success':0},
{'from':1,'to':4,'is_success':1}
]
faild = [
{'from':1,'to':2,'is_success':1},
{'from':1,'to':3,'is_success':0},
{'from':1,'to':4,'is_success':1}
]