from django.db import models

class Author(models.Model):

    name = models.CharField(max_length=100)

    age = models.IntegerField()

    class Meta:

        db_table = 'author'

class Publisher(models.Model):

    name = models.CharField(max_length=300)

    num_awards = models.IntegerField()

    class Meta:

        db_table = 'publisher'

class Book(models.Model):

    name = models.CharField(max_length=300)

    pages = models.IntegerField()

    price = models.DecimalField(max_digits=10, decimal_places=2)

    rating = models.FloatField()

    authors = models.ManyToManyField(Author)

    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)

    pubdate = models.DateField()

    class Meta:

        db_table = 'book'

class Store(models.Model):

    name = models.CharField(max_length=300)

    books = models.ManyToManyField(Book)

    registered_users = models.PositiveIntegerField()

    class Meta:

        db_table = 'store'

# 需要导入的模块

>>> from django.db.models import Count, Avg, Sum, F, FloatField

>>> from django.db import connections

>>> from books.models import *

#1. 聚合返回的是字典值

统计计数

>>> Publisher.objects.aggregate(num_books=Count('book'))

>>> connections['default'].queries

SELECT

    COUNT(`book`.`id`) AS `num_books`

FROM

    `publisher` LEFT OUTER JOIN `book`

    ON (`publisher`.`id` = `book`.`publisher_id`);

求平均

>>> Book.objects.all().aggregate(Avg('price'))

>>> Book.objects.aggregate(Avg('price'))

SELECT

    AVG(`book`.`price`) AS `price__avg`

FROM

    `book`;

求每页价格
>>> Book.objects.aggregate( price_per_page=Sum(F('price')/F('pages'), output_field=FloatField()) )
SELECT
    SUM((`book`.`price` / `book`.`pages)) AS `price_per_page`
FROM
　　`book`; 

# 导入涉及的模块

from django.db.models import Count, Sum, FloatField

from django.db import connections

from books.models import *

# annotate是分组、聚合的含义，annotate返回的是新的queryset

# aggregate则是对全表进行聚合的含义，没有分组的概念，返回的是字典
>>> Book.objects.annotate(Count('authors'))  # authors是Book中的属性
SELECT
    `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`,
    `book`.`rating`, `book`.`publisher_id`, `book`.`pubdate`,
    COUNT(`book_authors`.`author_id`) AS `authors__count`
FROM
    `book`
LEFT OUTER JOIN
    `book_authors`
ON
    (`book`.`id` = `book_authors`.`book_id`)
GROUP BY
    `book`.`id`
ORDER BY
    NULL;