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（’點個贊吧’）