创新实训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)