Django模型——聚合函数的练习代码

说说我本人的感觉吧，单表查询不是啥难点，练习一下也就会了，但外键关联是个难点，需要理解，并多加练习。

模型这一块我已经学了一周了，今天是第七天，真是知识点有点多呀。。。

---

models.py

from django.db import models


# Create your models here.
class Grade(models.Model):
    name = models.CharField('学生姓名', max_length=16, blank=True, default='')
    subject = models.CharField('科目', max_length=16, blank=True, default='')
    time = models.DateField('考试日期', blank=True, default='')
    score = models.SmallIntegerField('分数', blank=True, default=0)
    create_time = models.DateTimeField('创建时间', auto_now_add=True)
    update_time = models.DateTimeField('更新时间', auto_now=True)

    class Meta:
        db_table = 'grade'

    def __str__(self):
        return 'pk:{},name:{},score:{}'.format(self.pk, self.name, self.score)


class Staff(models.Model):
    name = models.CharField('员工姓名', max_length=16, blank=True, default='')
    staff_no = models.CharField('员工编号', max_length=32, blank=True, default='')
    sex = models.SmallIntegerField('员工性别', choices=((0, '女性'), (1, '男性')), blank=True, default=1)
    create_time = models.DateTimeField('创建时间', auto_now_add=True)
    update_time = models.DateTimeField('更新时间', auto_now=True)

    class Meta:
        db_table = 'staff'

    def __str__(self):
        return 'pk:{},name:{}', format(self.pk, self.name)


class Performance(models.Model):
    staff = models.ForeignKey(Staff, related_name='staff_perf', on_delete=models.CASCADE)
    year = models.SmallIntegerField('考评年份', blank=True, default=0)
    quarter = models.SmallIntegerField('考评季度', blank=True, default=0)
    score = models.SmallIntegerField('考评分数', blank=True, default=-1)
    create_time = models.DateTimeField('创建时间', auto_now_add=True)
    update_time = models.DateTimeField('更新时间', auto_now=True)

    class Meta:
        db_table = 'perf'

    def __str__(self):
        return 'pk:{},score:{}'.format(self.pk, self.score)

 

views.py

import random
from datetime import datetime

from django.db.models import Sum, Avg, Max, Min, Q
from django.http import HttpResponse
from django.shortcuts import render

# Create your views here.
from transaction.models import Grade, Staff, Performance


def insertUsers(request):
    '''造数据'''
    subject_dict = {1: '语文', 2: '化学', 3: '地理', 4: '数学', 5: '英语', 6: '美术', 7: '物理', 8: '政治'}
    for i in range(50):
        if i % 5 == 1:
            Grade.objects.get_or_create(name='林超', subject=subject_dict.get(random.randint(1, 8)),
                                        time=datetime(random.randint(2010, 2018), random.randint(1, 12),
                                                      random.randint(1, 30)),
                                        score=random.randint(0, 100))
        elif i % 5 == 2:
            Grade.objects.get_or_create(name='书美', subject=subject_dict.get(random.randint(1, 8)),
                                        time=datetime(random.randint(2010, 2018), random.randint(1, 12),
                                                      random.randint(1, 30)),
                                        score=random.randint(0, 100))
        elif i % 5 == 3:
            Grade.objects.get_or_create(name='良月', subject=subject_dict.get(random.randint(1, 8)),
                                        time=datetime(random.randint(2010, 2018), random.randint(1, 12),
                                                      random.randint(1, 30)),
                                        score=random.randint(0, 100))
        elif i % 5 == 4:
            Grade.objects.get_or_create(name='加彬', subject=subject_dict.get(random.randint(1, 8)),
                                        time=datetime(random.randint(2010, 2018), random.randint(1, 12),
                                                      random.randint(1, 30)),
                                        score=random.randint(0, 100))
        else:
            Grade.objects.get_or_create(name='刘坤', subject=subject_dict.get(random.randint(1, 8)),
                                        time=datetime(random.randint(2010, 2018), random.randint(1, 12),
                                                      random.randint(1, 30)),
                                        score=random.randint(0, 100))
    return HttpResponse('insert students done')


def aggregation(request):
    '''聚合函数的运用'''
    # 计算某位学生的成绩
    student_name = '刘坤'
    score_list = Grade.objects.filter(name=student_name).aggregate(Sum('score'))
    print('总分：', score_list['score__sum'])
    # 别名
    score_list1 = Grade.objects.filter(name=student_name).aggregate(total=Sum('score'))
    print('取别名，总分：', score_list1['total'])
    score_list = Grade.objects.filter(name=student_name).aggregate(Avg('score'))
    print('平均分：', score_list['score__avg'])
    num = Grade.objects.filter(name=student_name).count()
    print('数量：', num)
    score_list = Grade.objects.filter(name=student_name).aggregate(Max('score'))
    print('最高分：', score_list['score__max'])
    score_list = Grade.objects.filter(name=student_name).aggregate(Min('score'))
    print('最低分：', score_list['score__min'])
    return HttpResponse('test aggregation')


def insertStaff(request):
    Staff.objects.get_or_create(name='刘坤', staff_no='1401', sex=1)
    Staff.objects.get_or_create(name='陈青青', staff_no='1805', sex=0)
    Staff.objects.get_or_create(name='刘亦菲', staff_no='0001', sex=0)
    Staff.objects.get_or_create(name='郭美美', staff_no='0851', sex=0)
    Staff.objects.get_or_create(name='周一平', staff_no='0764', sex=1)
    Staff.objects.get_or_create(name='陈宇翔', staff_no='1205', sex=1)
    Staff.objects.get_or_create(name='张子洁', staff_no='1603', sex=0)
    Staff.objects.get_or_create(name='郭沫若', staff_no='1896', sex=1)
    Staff.objects.get_or_create(name='邓紫棋', staff_no='1151', sex=0)
    Staff.objects.get_or_create(name='李书美', staff_no='1963', sex=1)
    return HttpResponse('insert staff done')


def insertPerf(request):
    # 注意这边，因为使用的get_or_create新增数据，是可能造出一样的数据的，这个时候是不会新增的，所以数量可能和新增数量不一致
    for i in range(88):
        # 特意有一个员工没有造数据
        staff = Staff.objects.get(pk=random.randint(1, 9))
        Performance.objects.get_or_create(staff=staff, year=random.randint(2016, 2018), quarter=random.randint(1, 4),
                                          score=random.randint(50, 100))
    return HttpResponse('insert perf done')


def group(request):
    # 单表分组，使用values_list，就相当于group by
    perf_list = Performance.objects.values_list('staff').annotate(Avg('score'))
    for item in perf_list:
        print(item)
    '''这一块很绕，是难点更是重点'''
    # 外键关联的时候，可以直接对外键对象进行分组，然后查需要查询的数据
    staff_list = Staff.objects.annotate(Avg('staff_perf__score'))
    for staff in staff_list:
        print(staff.name, staff.staff_perf__score__avg)
    return HttpResponse('test group')


def testQ(request):
    '''查询员工姓名为刘坤，2018年第四季度的绩效分数'''
    staff_name = Staff.objects.get(name='刘坤')
    year = 2018
    quarter = 4
    query = Q(staff=staff_name) & Q(year=year) & Q(quarter=quarter)
    perf_list = Performance.objects.filter(query)
    print('员工姓名为刘坤，2018年第四季度的绩效列表：', perf_list)
    score = Performance.objects.filter(query).aggregate(Avg('score'))
    print('平均成绩：', score['score__avg'])

    '''接口获取参数进行查询
    具体需求如下：
    1.接口给什么参数，就以什么参数为条件进行查询；
    2.接口没给参数，就全量输出；
    3.参数包含，员工姓名——staff_name，考评年份——year，考评季度——quarter；
    注：水平有限，暂不考虑异常处理，即用户输入需要是正确的数据
    '''
    staff_name = request.GET.get('staff_name', None)
    year = request.GET.get('year', None)
    quarter = request.GET.get('quarter', None)
    query = Q()
    if staff_name is not None:
        try:
            staff = Staff.objects.get(name=staff_name)
        except:
            print('公司没有', staff_name)
            staff = Staff.objects.get(pk=1)
        query = query & Q(staff=staff)
    if year is not None:
        query = query & Q(year=year)
    if quarter is not None:
        query = query & Q(quarter=quarter)
    score = Performance.objects.filter(query).aggregate(Avg('score'))
    print(score['score__avg'])

    return HttpResponse('test Q')

 

 

 

 

print_r('点个赞吧')；
var_dump('点个赞吧')；
NSLog(@"点个赞吧！")
System.out.println("点个赞吧!");
console.log("点个赞吧!");
print("点个赞吧!");
printf("点个赞吧!\n");
cout << "点个赞吧!" << endl;
Console.WriteLine("点个赞吧!");
fmt.Println("点个赞吧!")
Response.Write("点个赞吧")；
alert（’点个赞吧’）