创新实训7.1（优秀学生数据分析+个人报告定制）

大体系统已经完成，这段时间实现优秀学生数据分析和个人定制报告
主要是sql和前后端交互的结合
1.优秀学生数据分析
（1）优秀学生本学年各地点人均消费情况

SELECT place,round(sum(price)/54) s 
from consume where id in 
(select id 
from score_rank 
where ranking<54) 
group by place order by s desc

（2）优秀学生学年消费分布

SELECT id,sum(price) s 
from consume where id in 
(select id 
from score_rank where ranking<54) 
group by id 
order by s

（3）优秀学生本学年借书情况（类型）

SELECT genre,count(*) s 
from book_record a,book_genre b  
where a.bookid=b.bookid and id in 
(select id 
from score_rank 
where ranking<54) 
group by genre 
order by s

（4）优秀学生图书馆门禁情况

SELECT bookid,count(*) s 
from book_record 
where id in 
(select id 
from score_rank 
where ranking<54) 
group by bookid 
order by s desc

（5）优秀学生最爱书籍top10

SELECT id,count(*) s 
from lib_record 
where id in 
(select id from score_rank where ranking<54) 
group by id order by s desc

2.个人定制报告
前端设计为邮箱界面，但不是真的邮箱
这里主要是做一些对个人的数据分析，高亮部分数据通过大量sql获得

@app.route('/annual')
def annual():
    stuid=getcookie()
    
    #人均去图书馆
    lib_count=113
    #去图书馆次数
    sql='select count(*) from lib_record  where  id='+stuid
    cursor.execute(sql)
    mylib_count = cursor.fetchall()[0][0]
    #图书馆击败
    sql='select count(*) from (select id,count(*) c from lib_record   group by id having count(*)<'+str(mylib_count)+') a'
    cursor.execute(sql)
    mylib_defeat = round(100*cursor.fetchall()[0][0]/538)
    #喜欢去图书馆的时间
    sql='select substring(ctime,1,2) from lib_record  where id='+stuid
    cursor.execute(sql)
    res= cursor.fetchall()
    a=0
    b=0
    c=0
    for i in res:
        t=i[0]
        if(t>'00' and t<='12'):
            a+=1
        elif(t>'12' and t<='17'):
            b+=1
        elif(t>'17' and t<='23'):
            c+=1
    percent=[[a,'上午'],[b,'下午'],[c,'晚上']]
    percent.sort(reverse=True)
    mylib_time=percent[0][1]
    #喜欢去图书馆的月份
    sql='select count(*),cmonth c from (select id,SUBSTR(cdate,1,2) cmonth from lib_record  where  id='+stuid+' ) a group by cmonth'
    cursor.execute(sql)
    result= cursor.fetchall()
    res=[]
    for r in result:
        res.append(r)
    res.sort(reverse=True)
    mylib_month=int(res[0][1])
    mylib_month_c=res[0][0]
    mylib=Mylib(lib_count,mylib_count,mylib_defeat,mylib_time,mylib_month,mylib_month_c)

    #人均借书
    
    book_count=30
    #借书本数
    sql='select count(*) from book_record where  id='+stuid
    cursor.execute(sql)
    mybook_count= cursor.fetchall()[0][0]
    #超过人数
    sql='select round(100*count(*)/538,2) from (select id,count(*) c from book_record  group by id) a where c<'+str(mybook_count)
    cursor.execute(sql)
    mybook_defeat= cursor.fetchall()[0][0]
    #最喜欢借的书类型
    sql='select count(*) c,genre from book_record a,book_genre b where a.bookid=b.bookid and id='+stuid+' group by genre'
    cursor.execute(sql)
    result= cursor.fetchall()
    res=[]
    for r in result:
        res.append(r)
    res.sort(reverse=True)
    mybook_genre=res[0][1]
    mybook_genre_c=res[0][0]
    mybook=Mybook(book_count,mybook_count,mybook_defeat,mybook_genre,mybook_genre_c)

    #平均消费

    price_count=2978.5
    #我的消费
    sql='select round(sum(price),1) from consume where  id='+stuid
    cursor.execute(sql)
    myprice_count= cursor.fetchall()[0][0]
    #超过人数
    sql='select round(count(*)/538,2)*100 from (select round(sum(price),1) p,id from consume  group by id) a where p<'+str(myprice_count)
    cursor.execute(sql)
    myprice_defeat= cursor.fetchall()[0][0]
    #花钱最多的地方
    sql='select round(sum(price),1),place from consume where id='+stuid+' group by place'
    cursor.execute(sql)
    result= cursor.fetchall()
    res=[]
    for r in result:
        res.append(r)
    res.sort(reverse=True)
    myprice_place=res[0][1]
    myprice_place_c=res[0][0]
    myprice=Myprice(price_count,myprice_count,myprice_defeat,myprice_place,myprice_place_c)
    #排名
    sql='select round(avg(ranking)) from score_rank where id='+stuid
    cursor.execute(sql)
    myrank= cursor.fetchall()[0][0]
    rank=[myrank,100*round((538-myrank)/538,2)]
    return render_template('annual.html',mylib=mylib,mybook=mybook,myprice=myprice,rank=rank)