def salary_top10():
data = db.session.query(func.avg((JobModel.salary_lower + JobModel.salary_upper)/2), JobModel.city).group_by(JobModel.city).all()
data = sorted(data, reverse=True)
x = '城市'
y = '薪资'
todict = Todict(y, x)
return list(map(todict.to_dict,data[:10])), x, y
def position_top10():
data = db.session.query(func.count(JobModel.city), JobModel.city).group_by(JobModel.city).all()
data = sorted(data, reverse=True)
x = '城市'
y = '职位数'
todict = Todict(y, x)
return list(map(todict.to_dict,data[:10])), x, y
def salary_by_city_and_education():
data = db.session.query(func.avg((JobModel.salary_lower + JobModel.salary_upper)/2), JobModel.education, JobModel.city).group_by(JobModel.city, JobModel.education).all()
# data = sorted(data, reverse=True)
x = '学历'
y = '薪资'
z = '城市'
todict = Todict(y, x, z)
return list(map(todict.to_dict,data)), x, y, z
def quantity_top10():
data = db.session.query(func.count(HouseModel.community),
HouseModel.community).group_by(
HouseModel.community).all()
data = sorted(data, reverse=True)
x = '小区'
y = '房源数'
todict = Todict(y, x)
return list(map(todict.to_dict, data[:10])), x, y
def consumption_district_top10():
data = db.session.query(func.avg(RestaurantModel.consumption),
RestaurantModel.business_district).group_by(
RestaurantModel.business_district).all()
data = sorted(data, key=lambda x: x[0], reverse=True)
x = '商圈名'
y = '人均消费'
todict = Todict(y, x)
return list(map(todict.to_dict, data[:10])), x, y
def consumption_categories_top10():
data = db.session.query(func.avg(RestaurantModel.consumption),
RestaurantModel.classification).group_by(
RestaurantModel.classification).all()
data = sorted(data, key=lambda x: x[0], reverse=True)
x = '餐馆分类'
y = '人均消费'
todict = Todict(y, x)
return list(map(todict.to_dict, data[:10])), x, y
def popular_restaurant():
data = db.session.query(
RestaurantModel.taste + RestaurantModel.ambience +
RestaurantModel.service, RestaurantModel.title).filter(
RestaurantModel.review_count > 500).all()
data = sorted(data, key=lambda x: x[0], reverse=True)
x = '餐馆名'
y = '总分'
todict = Todict(y, x)
return list(map(todict.to_dict, data[:10])), x, y
def education_stat():
data = db.session.query(func.count(JobModel.education), JobModel.education).group_by(JobModel.education).all()
data = sorted(data, reverse=True)
x = '学历'
y = '统计次数'
percent = '占比'
df = pd.DataFrame(data, columns=[y, x])
per = round(df[y]/df[y].sum(), 2)
df = pd.concat([df,per], axis=1)
data = list(df.values)
todict = Todict(y, x, percent)
return list(map(todict.to_dict,data)), x, y, percent
def house_type_destributed():
data = db.session.query(func.count(HouseModel.house_type),
HouseModel.house_type).group_by(
HouseModel.house_type).all()
data = sorted(data, reverse=True)
x = '户型'
y = '房源数'
percent = '占比'
df = pd.DataFrame(data, columns=[y, x])
per = round(df[y] / df[y].sum(), 2)
df = pd.concat([df, per], axis=1)
data = list(df.values)
todict = Todict(y, x, percent)
return list(map(todict.to_dict, data)), x, y, percent
def price_type_top10():
data = db.session.query(func.avg(HouseModel.price), HouseModel.house_type,
HouseModel.community).group_by(
HouseModel.community,
HouseModel.house_type).all()
data = sorted(data, reverse=True)
x = '户型'
y = '租金'
z = '小区'
todict = Todict(y, x, z)
df = pd.DataFrame(list(map(todict.to_dict, data)))
df = df.sort_values(by=['户型', '租金'], ascending=False)
df1 = pd.DataFrame(columns=df.columns)
count_type = set(df['户型'].values)
for i in count_type:
df1 = pd.concat([df1, df[df['户型'] == i].iloc[:10, :]])
data = list(df1.values)
return list(map(todict.to_dict, data)), x, y, z
def consumption_compared():
classification = sorted(db.session.query(
func.count(RestaurantModel.classification),
RestaurantModel.classification).group_by(
RestaurantModel.classification).all(),
reverse=True)
classification = [x[1] for x in classification[:10]]
result = []
x = '餐馆分类'
y = '人均消费'
z = '商圈名'
for i in classification:
data = db.session.query(
func.avg(RestaurantModel.consumption),
RestaurantModel.classification,
RestaurantModel.business_district).filter(
RestaurantModel.classification == i).group_by(
RestaurantModel.business_district).all()
data = sorted(data, key=lambda x: x[0], reverse=True)
todict = Todict(y, x, z)
result += list(map(todict.to_dict, data[:10]))
return result, x, y, z
def hot_tags(format='png'):
from seiya.web.app import config_dict
sql = 'SELECT (tags) from jobmodel'
df = pd.read_sql(sql=sql, con=config_dict['SQLALCHEMY_DATABASE_URI'])
data = df.to_csv(index=0).split()
data_list = []
for tag in set(data):
data_list.append((data.count(tag), tag))
x = '职位标签'
y = '出现次数'
data_list = sorted(data_list, reverse=True)
todict = Todict(y, x)
df = pd.DataFrame(data_list[:10], columns=['出现次数', '职位标签'])
# 设置中文字体，如果系统里没有 SimHei 字体，可以替换为其它任何中文字体
mpl.rcParams['font.sans-serif'] = ['SimHei']
# 避免 - 号显示为方块
mpl.rcParams['axes.unicode_minus'] = False
# 设置画布宽高，单位为英寸
mpl.rcParams['figure.figsize'] = 11, 5
plt.bar(range(len(df)),df['出现次数'], color='c', tick_label=df['职位标签'])
fig = io.BytesIO()
plt.savefig(fig, format=format)
return list(map(todict.to_dict, data_list[:10])), x, y, fig.getvalue()