def save_owner_shops(two_mysql3, logger3, save_data1):
in_sql = """
INSERT INTO cb_hotshop_owner_rec_shops (
owner_code,
shop_code_list,
updated_time
)
VALUES
(% s, % s, s %)
"""
# update_sql = "INSERT INTO cb_hotshop_owner_rec_shops" + \
# " VALUES (0, %s, %s, %s) ON DUPLICATE KEY UPDATE " \
# "`owner_code` = VALUES(`owner_code`), " \
# "`shop_code_list` = VALUES(`shop_code_list`), " \
# "`updated_time` = VALUES(`updated_time`)"
update_sql = """
INSERT INTO cb_hotshop_owner_rec_shops (
owner_code,
shop_code_list,
updated_time
)
VALUES (% s, % s, % s)
ON DUPLICATE KEY UPDATE
`owner_code` = VALUES(`owner_code`),
`shop_code_list` = VALUES(`shop_code_list`),
`updated_time` = VALUES(`updated_time`)
"""
db_table = "cb_hotshop_owner_rec_shops"
sqyn_write = DatabaseHandle(two_mysql3['sqyn_mysql'], logger3)
sqyn_write.insert_table(in_sql, update_sql, db_table, save_data1)
def __init__(self, two_mysql, logger, begin_time, end_time):
self.read_hisense = DatabaseHandle(two_mysql["hisense_mysql"], logger)
self.read_sqyn = DatabaseHandle(two_mysql["sqyn_mysql"], logger)
self.begin_time = begin_time # 时间条件的起始时间
self.end_time = end_time # 时间条件的终止时间
self.new_order_owner = []
self.new_collect_owner = []
self.new_eval_owner = []
self.new_owner_list = None
self.total_new_owner()
def __init__(self, two_mysql1, logger1, shop_list):
self._logger = logger1
self.hisense_mysql = two_mysql1['hisense_mysql']
self.sqyn_mysql = two_mysql1['sqyn_mysql']
self.read_hisense = DatabaseHandle(self.hisense_mysql, logger1)
self.read_sqyn = DatabaseHandle(self.sqyn_mysql, logger1)
self.shop_list = shop_list # shop_list为通过ShopRead类得到的排序后的商家排序列表
self.order_data = None
self.total_shop_data = None
self.collect_data = None
self.spu_eval_data = None
self.order_eval_data = None
self.owner_list = None
self.total_owner()
def __init__(self, two_mysql1, logger1):
self._logger = logger1
self.hisense_mysql = two_mysql1['hisense_mysql']
self.sqyn_mysql = two_mysql1['sqyn_mysql']
self.read_hisense = DatabaseHandle(self.hisense_mysql, logger1)
self.read_sqyn = DatabaseHandle(self.sqyn_mysql, logger1)
self.total_shop_data = None
self.shop_list = None
self.shop_sale_score = []
self.shop_collect_score = []
self.shop_eval_score = []
self.new_shop_score = []
self.sort_shop_list = []
self.read_shop_info()
self.sort_shop()
class FindNewOwner(object):
"""
发现新用户类：
根据历史下单数据，获取符合时间条件的下单用户列表
根据历史收藏数据，获取符合时间条件的收藏用户列表
根据历史评价数据，获取符合时间条件的评价用户列表
最后得到一个总的用户列表
"""
def __init__(self, two_mysql, logger, begin_time, end_time):
self.read_hisense = DatabaseHandle(two_mysql["hisense_mysql"], logger)
self.read_sqyn = DatabaseHandle(two_mysql["sqyn_mysql"], logger)
self.begin_time = begin_time # 时间条件的起始时间
self.end_time = end_time # 时间条件的终止时间
self.new_order_owner = []
self.new_collect_owner = []
self.new_eval_owner = []
self.new_owner_list = None
self.total_new_owner()
# 发现新的订单用户列表
def find_new_order_owner(self):
mysql = '''
SELECT
owner_code
FROM
cb_owner_buy_goods_info
WHERE
paid_time
BETWEEN
'%s'
AND
'%s'
''' % (self.begin_time, self.end_time)
order_data = self.read_sqyn.read_mysql_multipd(mysql)
if len(order_data) != 0:
self.new_order_owner = order_data["owner_code"].tolist()
# 发现新的收藏用户列表
def find_new_collect_owner(self):
mysql = '''
SELECT
user_code
FROM
user_collect_record
WHERE
created_time
BETWEEN
'%s'
AND
'%s'
''' % (self.begin_time, self.end_time)
collect_data = self.read_hisense.read_mysql_multipd(mysql)
if len(collect_data) != 0:
self.new_collect_owner = collect_data["user_code"].tolist()
# 发现新的评价用户列表
def find_new_eval_owner(self):
mysql = '''
SELECT
owner_code
FROM
goods_spu_eval
WHERE
eval_time
BETWEEN
'%s'
AND
'%s'
''' % (self.begin_time, self.end_time)
eval_data = self.read_hisense.read_mysql_multipd(mysql)
if len(eval_data) != 0:
self.new_eval_owner = eval_data["owner_code"].tolist()
# 总新用户列表
def total_new_owner(self):
self.find_new_order_owner()
self.find_new_collect_owner()
self.find_new_eval_owner()
total_new_owner_list = self.new_order_owner + \
self.new_collect_owner + \
self.new_eval_owner
self.new_owner_list = list(set(total_new_owner_list))
return self.new_owner_list
class DataRead(object):
"""
基础数据读取类：
读取sqyn库中的历史下单表，获取历史下单数据和下单用户列表
读取hisense库中的历史收藏表，获取历史收藏数据和收藏用户列表
读取hisense库中的历史评价表，获取历史评价数据和评价用户列表
最后得到一个总的用户列表
"""
def __init__(self, two_mysql1, logger1, shop_list):
self._logger = logger1
self.hisense_mysql = two_mysql1['hisense_mysql']
self.sqyn_mysql = two_mysql1['sqyn_mysql']
self.read_hisense = DatabaseHandle(self.hisense_mysql, logger1)
self.read_sqyn = DatabaseHandle(self.sqyn_mysql, logger1)
self.shop_list = shop_list # shop_list为通过ShopRead类得到的排序后的商家排序列表
self.order_data = None
self.total_shop_data = None
self.collect_data = None
self.spu_eval_data = None
self.order_eval_data = None
self.owner_list = None
self.total_owner()
# 读取用户的历史订单数据，返回历史订单用户列表
def read_order_table(self):
mysqlcmd = '''
SELECT
owner_code,
shop_code,
count(owner_code) AS count
FROM
cb_owner_buy_goods_info
GROUP BY
owner_code,
shop_code
'''
self.order_data = self.read_sqyn.read_mysql_multipd(mysqlcmd)
order_owner_list = self.order_data["owner_code"].tolist()
return order_owner_list
# 读取用户的历史收藏数据，返回历史收藏用户列表
def read_user_collect(self):
mysqlcmd = '''
SELECT
user_code,
content_code
FROM
user_collect_record
WHERE
collect_type = '02'
'''
self.collect_data = self.read_hisense.read_mysql_multipd(mysqlcmd)
collect_owner_list = self.collect_data["user_code"].tolist()
return collect_owner_list
# 读取用户的历史评价数据，返回历史评价用户列表
def read_spu_eval(self):
mysqlcmd = '''
SELECT
owner_code,
shop_code,
eval_level,
count(owner_code) AS count
FROM
goods_spu_eval
GROUP BY
owner_code,
shop_code,
eval_level
'''
self.spu_eval_data = self.read_hisense.read_mysql_multipd(mysqlcmd)
spu_eval_owner = self.spu_eval_data["owner_code"].tolist()
return spu_eval_owner
# 读取用户的历史订单评价，返回历史订单评价用户列表
# 此方法暂时不用
def read_order_eval(self):
mysqlcmd = '''
SELECT
c.owner_code,
a.shop_code,
a.serve_eval_level,
count(c.owner_code) AS count
FROM
goods_child_orders_eval a,
sale_order_sku b,
sale_order c
WHERE
a.order_code = b.sub_bill_no
AND
b.bill_no = c.bill_no
GROUP BY
c.owner_code,
a.shop_code,
a.serve_eval_level
'''
self.order_eval_data = self.read_hisense.read_mysql_multipd(mysqlcmd)
order_eval_owner = self.order_eval_data["owner_code"].tolist()
return order_eval_owner
# 返回总的用户列表，用于后续的个性化商家计算
def total_owner(self):
order_owner_list = self.read_order_table()
collect_owner_list = self.read_user_collect()
spu_eval_owner_list = self.read_spu_eval()
self.owner_list = list(
set(order_owner_list + collect_owner_list + spu_eval_owner_list))
class HotShopRead(object):
"""
热门商家读取类：
计算商家的历史下单数量占比作为商家的销售热度，
计算商家的历史收藏占比作为商家的收藏热度，
计算商家的历史好评率和差评率作为商家的评价热度，
计算商家是否是最新打开作为商家的最新热度，
sort_shop_list为最后返回的排序结果。
"""
def __init__(self, two_mysql1, logger1):
self._logger = logger1
self.hisense_mysql = two_mysql1['hisense_mysql']
self.sqyn_mysql = two_mysql1['sqyn_mysql']
self.read_hisense = DatabaseHandle(self.hisense_mysql, logger1)
self.read_sqyn = DatabaseHandle(self.sqyn_mysql, logger1)
self.total_shop_data = None
self.shop_list = None
self.shop_sale_score = []
self.shop_collect_score = []
self.shop_eval_score = []
self.new_shop_score = []
self.sort_shop_list = []
self.read_shop_info()
self.sort_shop()
# 读取所有的店铺列表，同时按照销量降序排序
def read_shop_info(self):
mysqlcmd = "SELECT * FROM shop_info WHERE shop_code_type != 4"
self.total_shop_data = self.read_hisense.read_mysql_multipd(mysqlcmd)
self.shop_list = self.total_shop_data["shop_code"].tolist()
# 计算所有店铺的销售热度，将店铺下单数量与总下单数量之比作为该热度评分
# 为减小店铺的销售热度在总热度评分中的影响，该热度评分增加弱权重0.1
def count_shop_order(self):
mysqlcmd = """
SELECT
shop_code,
count(shop_code) AS count
FROM
cb_owner_buy_goods_info
GROUP BY
shop_code
ORDER BY
count DESC
"""
order_data = self.read_sqyn.read_mysql_multipd(mysqlcmd)
sum1 = order_data["count"].sum()
self.shop_sale_score = [0 for _ in range(len(self.shop_list))]
for i in range(len(self.shop_list)):
try:
self.shop_sale_score[i] = \
order_data[order_data.shop_code ==
self.shop_list[i]]["count"].values[0] / sum1 * 0.1
except IndexError:
pass
continue
# 计算所有店铺的收藏热度，将店铺收藏数量与总收藏数量之比作为该热度评分
# 为增加店铺的收藏热度在总热度评分中的影响，该热度评分增加强权重20
def count_shop_collect(self):
mysqlcmd = """
SELECT
content_code,
count(content_code) AS count
FROM
user_collect_record
WHERE
collect_type = '02'
GROUP BY
content_code
ORDER BY
count DESC
"""
collect_data = self.read_hisense.read_mysql_multipd(mysqlcmd)
sum1 = collect_data["count"].sum()
self.shop_collect_score = [0 for _ in range(len(self.shop_list))]
for i in range(len(self.shop_list)):
try:
self.shop_collect_score[i] = \
collect_data[collect_data.content_code ==
self.shop_list[i]]["count"].values[0] * 20 / sum1
except IndexError:
pass
continue
# 计算所有店铺的评价热度，分为两部分：好评率和差评率
# 好评率和差评率均是指店铺好评、差评数量在总好评、差评数量中所占比例
# 为区分好评和差评，好评在总热度评分权重为0.1，差评在总热度评分权重为1
def count_shop_eval(self):
mysqlcmd = """
SELECT
shop_code,
count(shop_code) AS count
FROM
goods_spu_eval
WHERE
eval_level = 5
OR
eval_level = 4
GROUP BY
shop_code
ORDER BY
count DESC
"""
good_eval_data = self.read_hisense.read_mysql_multipd(mysqlcmd)
mysqlcmd = """
SELECT
shop_code,
count(shop_code) AS count
FROM
goods_spu_eval
WHERE
eval_level != 5
AND
eval_level != 4
GROUP BY
shop_code
ORDER BY
count DESC
"""
bad_eval_data = self.read_hisense.read_mysql_multipd(mysqlcmd)
sum1 = good_eval_data["count"].sum()
sum2 = bad_eval_data["count"].sum()
shop_good_eval_score = [0 for _ in range(len(self.shop_list))]
shop_bad_eval_score = [0 for _ in range(len(self.shop_list))]
for i in range(len(self.shop_list)):
try:
shop_good_eval_score[i] = \
good_eval_data[good_eval_data.shop_code ==
self.shop_list[i]]["count"].values[0] / sum1 * 0.1
shop_bad_eval_score[i] = \
bad_eval_data[bad_eval_data.shop_code ==
self.shop_list[i]]["count"].values[0] / sum2
except IndexError:
pass
continue
self.shop_eval_score = np.subtract(
np.array(shop_good_eval_score),
np.array(shop_bad_eval_score)).tolist()
# 计算所有店铺的最新热度，根据是否是最近10天新开店铺
# 店铺若为最新开店铺，其最新热度评分为5
def new_shop(self):
current_time = datetime.datetime.now()
begin_time = current_time + datetime.timedelta(days=-7)
begin_time = begin_time.strftime("%Y-%m-%d")
end_time = current_time.strftime("%Y-%m-%d")
mysqlcmd = """
SELECT
shop_code
FROM
shop_info
WHERE
created_time
BETWEEN
'%s'
AND
'%s'
""" % (begin_time, end_time)
new_shop_data = self.read_hisense.read_mysql_multipd(mysqlcmd)
new_shop_list = new_shop_data["shop_code"].tolist()
self.new_shop_score = [0.0 for _ in range(len(self.shop_list))]
for i in range(len(self.shop_list)):
if self.shop_list[i] in new_shop_list:
self.new_shop_score[i] = 0.5
# 计算所有店铺的总热度评分，根据总热度评分进行排序
# 总热度评分包括销售热度、收藏热度、评价热度和最新热度
def sort_shop(self):
self.count_shop_order()
self.count_shop_collect()
self.count_shop_eval()
self.new_shop()
shop_total_score = [
w + x + y + z for w, x, y, z in np.array([
self.shop_sale_score, self.shop_collect_score,
self.shop_eval_score, self.new_shop_score
]).T
]
shop_score_dict = dict(zip(self.shop_list, shop_total_score))
shop_score_dict = dict(
tuple(
sorted(shop_score_dict.items(),
key=lambda x: x[1],
reverse=True)))
self.sort_shop_list = list(shop_score_dict.keys())