def hot_res():
session = Session()
rows = session.query(
FoodModel.name,
((FoodModel.kouwei + FoodModel.huanjin + FoodModel.fuwu) *
FoodModel.reviewNum).label('sum')).order_by('sum desc').limit(10)
return [row._asdict() for row in rows]
def count_top10():
session = Session()
rows = session.query(
HouseModel.xiaoqu,
func.count(HouseModel.xiaoqu).label('count')
).group_by(HouseModel.xiaoqu).order_by('count desc').limit(10)
return [row._asdict() for row in rows]
def count_top10_lei():
"""商圈前十的分类
"""
session = Session()
rows = session.query(FoodModel.fenlei,
func.count(FoodModel.fenlei).label('count')).group_by(
FoodModel.fenlei).order_by('count desc').limit(10)
return [row._asdict() for row in rows]
def education_stat():
session = Session()
rows = session.query(JobModel.education,
func.count(
JobModel.education).label('count')).group_by(
'education').order_by('count desc')
return [row._asdict() for row in rows]
def experience_stat():
session = Session()
rows = session.query(
func.concat(JobModel.experience_lower, '-',
JobModel.experience_upper).label('experience'),
func.count('experience').label('count')).group_by(
'experience').order_by('count desc')
return [row._asdict() for row in rows]
def exp_top10_lei():
"""人均消费前十的分类
"""
session = Session()
rows = session.query(
FoodModel.fenlei,
func.avg(FoodModel.agvExp).cast(Float).label('agvExp')).group_by(
FoodModel.fenlei).order_by('agvExp desc').limit(10)
return [row._asdict() for row in rows]
def count_top10():
"""职位数排名前十的城市
"""
session = Session()
rows = session.query(
JobModel.city,
func.count(JobModel.city).label('count')
).group_by(JobModel.city).order_by('count desc').limit(10)
return [row._asdict() for row in rows]
def salary_top10():
session = Session()
rows = session.query(
JobModel.city,
func.avg((JobModel.salary_lower + JobModel.salary_upper) /
2).cast(Float).label('salary')).filter(
and_(JobModel.salary_lower > 0,
JobModel.salary_upper > 0)).group_by(
JobModel.city).order_by('salary desc').limit(10)
return [row._asdict() for row in rows]
def education_stat():
"""学历要求统计
"""
session = Session()
rows = session.query(
JobModel.education,
func.count(JobModel.education).label('count')
).group_by('education').order_by(desc('count'))
学历要求统计 = [row._asdict() for row in rows]
return 学历要求统计
def count_top10_lei_1():
"""商圈前十的分类
"""
session = Session()
rows = session.query(FoodModel.fenlei,
func.count(FoodModel.fenlei).label('count')).group_by(
FoodModel.fenlei).order_by('count desc').limit(10)
res = []
for row in rows:
res.append(row.fenlei)
return res
def count_top10_quan():
"""商圈前十的商圈
rows = session.query(
JobModel.city,
func.count(JobModel.city).label('count')
).group_by(JobModel.city).order_by('count desc').limit(10)
"""
session = Session()
rows = session.query(FoodModel.quan,
func.count(FoodModel.quan).label('count')).group_by(
FoodModel.quan).order_by('count desc').limit(10)
return [row._asdict() for row in rows]
def fenlei_and_quan():
"""热门分类在热门商圈的人均消费对比
"""
session = Session()
rows = session.query(
FoodModel.fenlei, FoodModel.quan,
func.avg(FoodModel.agvExp).cast(Float).label('agvExp')).filter(
and_(FoodModel.fenlei.in_(count_top10_lei_1()),
FoodModel.quan.in_(count_top10_quan_1()))).group_by(
FoodModel.fenlei,
FoodModel.quan).order_by(FoodModel.quan.desc())
return [row._asdict() for row in rows]
def salary_by_city_and_education():
session = Session()
rows = session.query(
JobModel.city, JobModel.education,
func.avg(
(JobModel.salary_lower + JobModel.salary_upper) /
2).cast(Float).label('salary')).filter(
and_(JobModel.salary_lower > 0,
JobModel.salary_upper > 0)).group_by(
JobModel.city,
JobModel.education).order_by(JobModel.city.desc())
return [row._asdict() for row in rows]
def all_huxing():
session = Session()
rows = session.query(
(HouseModel.huxing).label('item'),
func.count(HouseModel.huxing).label('count'),
func.count(HouseModel.huxing).label('percent')
).group_by(HouseModel.huxing).order_by('count desc')
result = [row._asdict() for row in rows]
total = 0
for row in result:
total += int(row['count'])
for row in result:
row['percent'] = round(row['count']/total,2)
results = {'total':total,'data':result}
return results
def count_top10_quan_1():
"""商圈前十的商圈
rows = session.query(
JobModel.city,
func.count(JobModel.city).label('count')
).group_by(JobModel.city).order_by('count desc').limit(10)
"""
session = Session()
rows = session.query(FoodModel.quan,
func.count(FoodModel.quan).label('count')).group_by(
FoodModel.quan).order_by('count desc').limit(10)
res = []
for row in rows:
res.append(row.quan)
return res
def salary_top10():
"""薪资排名前十的城市
"""
session = Session()
rows = session.query(
JobModel.city,
func.avg(
(JobModel.salary_lower + JobModel.salary_upper) / 2
).label('salary')
).filter(
and_(JobModel.salary_lower > 0, JobModel.salary_upper > 0)
).group_by(JobModel.city).order_by(desc('salary')).limit(10)
薪资排名列表 = [row._asdict() for row in rows]
# 列表中每个元素是字典，字典的 salary 值的数据类型是 Decimal
# 须将其转换为 float 类型
for d in 薪资排名列表:
d['salary'] = float(format(d['salary'], '.2f'))
return 薪资排名列表
def salary_by_city_and_education():
"""同等学历不同城市薪资对比
"""
session = Session()
rows = session.query(
JobModel.city,
JobModel.education,
func.avg(
(JobModel.salary_lower + JobModel.salary_upper) / 2
).cast(Float).label('salary')
).filter(
and_(JobModel.salary_lower > 0, JobModel.salary_upper > 0)
).group_by(JobModel.city, JobModel.education).order_by(JobModel.city.desc())
#return [row._asdict() for row in rows]
同等学历不同城市薪资对比 = [row._asdict() for row in rows]
for i in 同等学历不同城市薪资对比:
i['salary'] = float(format(i['salary'],'.2f'))
return 同等学历不同城市薪资对比
def all_area():
session = Session()
rows = session.query(HouseModel.mianji)
return [float(row._asdict()['mianji']) for row in rows]