大体系统已经完成,这段时间实现优秀学生数据分析和个人定制报告
主要是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)