def query_adgroups(self, email, adgroupId):
status = {}
status['status'] = 0
status['msg'] = "Success!"
result = None
try:
if adgroupId is not None:
#print "here"
sql_com = "select * from asm_adgroup where auto_id=" + adgroupId
adgroups = sql_appbk.mysql_com(sql_com)
self.handleAdGroup(adgroups)
else:
sql_com = "select id, auto_id from asm_campaign where localstat=1 and displayStatus=\"running\" and email=\"" + email + "\""
campaigns = sql_appbk.mysql_com(sql_com)
for campaign in campaigns:
sql_com = "select * from asm_adgroup where localstat=0 and appbk_campaign_id=" + str(
campaign['auto_id'])
adgroups = sql_appbk.mysql_com(sql_com)
self.handleAdGroup(adgroups)
except Exception, e:
self.logger.error("User " + email +
": create or update adgroup failed")
self.logger.error("Exception : " + str(e))
status['status'] = -1
status['msg'] = "Request Failed"
status['error'] = str(e)
def get_user_actions():
# 获得未处理的用户行为记录
sql_com = "select * from user_action where is_processed=0"
result = sql_appbk.mysql_com(sql_com)
# 更新已处理的记录
sql_com = "UPDATE user_action set is_processed = 1 WHERE is_processed=0"
sql_appbk.mysql_com(sql_com)
return result
def save_adgroup(self, adgroupJson, auto_id):
adgroup = {}
adgroup['id'] = adgroupJson['id']
adgroup['campaignId'] = adgroupJson['campaignId']
adgroup['name'] = adgroupJson['name']
if adgroupJson['defaultCpcBid'] is not None:
adgroup['defaultCpcBid'] = adgroupJson['defaultCpcBid']['amount']
if adgroupJson['cpaGoal'] is not None:
adgroup['cpaGoal'] = adgroupJson['cpaGoal']['amount']
adgroup['storefronts'] = "|".join(adgroupJson['storefronts'])
startTime = adgroupJson['startTime'].split("T")
adgroup['startTime'] = startTime[0] + " " + startTime[1].split(".")[0]
if adgroupJson['endTime'] is not None:
endTime = adgroupJson['endTime'].split("T")
adgroup['endTime'] = endTime[0] + " " + endTime[1].split(".")[0]
if adgroupJson['automatedKeywordsOptIn'] is not None:
if adgroupJson['automatedKeywordsOptIn']:
adgroup['automatedKeywordsOptIn'] = 1
else:
adgroup['automatedKeywordsOptIn'] = 0
modificationTime = adgroupJson['modificationTime'].split("T")
adgroup['modificationTime'] = modificationTime[
0] + " " + modificationTime[1].split(".")[0]
adgroup['status'] = adgroupJson['status']
adgroup['servingStatus'] = adgroupJson['servingStatus']
if adgroupJson['servingStateReasons'] is not None and len(
adgroupJson['servingStateReasons']):
adgroup['servingStateReasons'] = "|".join(
adgroupJson['servingStateReasons'])
adgroup['displayStatus'] = adgroupJson['displayStatus']
adgroup['updateTime'] = datetime.datetime.now().strftime(
"%Y-%m-%d %H:%M:%S")
adgroup['localstat'] = 1
sql_com = "update asm_adgroup set "
conditions = []
for item in adgroup:
conditions.append(str(item) + "=\"" + str(adgroup[item]) + "\"")
sql_com += " , ".join(conditions)
sql_com += " where auto_id=" + str(auto_id)
sql_appbk.mysql_com(sql_com)
return sql_com
def save_campaign(self, text, auto_id):
#print text
campaign = {}
campaign['id'] = text['id']
campaign['orgId'] = text['orgId']
campaign['name'] = str(text['name'])
campaign['budgetAmount'] = text['budgetAmount']['amount']
campaign['currency'] = str(text['budgetAmount']['currency'])
campaign['dailyBudgetAmount'] = text['dailyBudgetAmount']['amount']
campaign['adamId'] = text['adamId']
if text['dailyBudgetAmount'] is not None:
campaign['dailyBudgetAmount'] = text['dailyBudgetAmount']['amount']
if text['paymentModel'] is not None:
campaign['paymentModel'] = str(text['paymentModel'])
'''
if text['locInvoiceDetails'] is not None:
campaign['locInvoiceDetails'] = "|".join(text['locInvoiceDetails'])
'''
campaign['status'] = text['status']
campaign['servingStatus'] = str(text['servingStatus'])
if text['servingStateReasons'] is not None and len(
text['servingStateReasons']):
campaign['servingStateReasons'] = str("|".join(
text['servingStateReasons']))
if text['negativeKeywords'] is not None and len(
text['negativeKeywords']):
campaign['negativeKeywords'] = str("|".join(
text['negativeKeywords']))
campaign['displayStatus'] = str(text['displayStatus'])
modificationTime = text['modificationTime'].split("T")
campaign['modificationTime'] = str(modificationTime[0] + " " +
modificationTime[1].split(".")[0])
campaign['localstat'] = 1
campaign['updateTime'] = datetime.datetime.now().strftime(
"%Y-%m-%d %H:%M:%S")
sql_com = "update asm_campaign set "
conditions = []
for item in campaign:
conditions.append(str(item) + "=\"" + str(campaign[item]) + "\"")
sql_com += " , ".join(conditions)
sql_com += " where auto_id=" + str(auto_id)
sql_appbk.mysql_com(sql_com)
return sql_com
def get_user_tags(uid):
sql_com = "SELECT appbk_sub_category,appbk_tags FROM user_tags_ctr WHERE uid = '" + str(uid) + "'"
result = sql_appbk.mysql_com(sql_com)
if result:
return result[0]
else:
return {"appbk_sub_category": "", "appbk_tags": ""}
def get_content_based_videos(uid, limit=10):
vid_list = []
# step1，获得用户模型
user_model = get_user_model.get_user_tags(uid)
appbk_sub_category = user_model["appbk_sub_category"]
appbk_tags = user_model["appbk_tags"]
# step 2,根据类别，取数据
appbk_sub_category_sql = ",".join(
["'" + item + "'" for item in appbk_sub_category])
if appbk_sub_category: # 不为空
sql = "SELECT id,vid,title,thumbnail,link,duration, \
bigThumbnail,view_count,appbk_category,source,published \
FROM video_info WHERE appbk_sub_category in (" + appbk_sub_category_sql + ") limit 20"
result = sql_appbk.mysql_com(sql)
for item in result:
vid_list.append(str(item["id"]))
# step 3,根据关键词标签搜索结果
if len(appbk_tags) > 0:
search_result = ali_search.search(appbk_tags, 20)
for item in search_result:
vid_list.append(str(item["id"]))
# 随机打乱
random.shuffle(vid_list)
return vid_list[0:limit]
def get_videos_by_category(c, start=0, limit=10, uid=0, pid=0):
temp_result = [] # 中间结果
# 获得热门推荐
vid_list = get_hot_videos(c)
id_list_str = ",".join(vid_list)
sql = "SELECT id,vid,title,thumbnail,link,duration, \
bigThumbnail,view_count,appbk_category,source,published \
FROM video_info WHERE id in (" + id_list_str + ")"
result = sql_appbk.mysql_com(sql)
for item in result:
# 拼接play url
source = item["source"]
vid = item["vid"]
play_url = OSS_URL + source + "_" + vid + ".mp4"
item["play_url"] = play_url
temp_result.append(item)
# 随机打乱
random.shuffle(temp_result)
final_result = {
"status": 0,
"msg": "success",
"results": temp_result[int(start):int(start) + int(limit)]
}
return json.dumps(final_result, cls=sql_appbk.CJsonEncoder)
def createHttpsSession(email): #读取账号数据,读取cert字符串和key字符串,以及orgId sql = "select sshcert,sshkey,orgId from asm_member where email='" + email + "'" result = sql_appbk.mysql_com(sql) cert_file_str = result[0]['sshcert'] key_file_str = result[0]['sshkey'] org_id = result[0]['orgId'] #部分请求可能需要orgId构建http header #随机生成一个字符串,防止多个同时运行时出错误 rand_str = str(random.randint(0,1000000)) #cert字符串写入一个文件 cert_file_name = certPath + "/" + rand_str + ".pem" cert_output = open(cert_file_name, 'w') cert_output.write(cert_file_str) #必须close,否则ssl认证会出问题 cert_output.close() #key字符串写入一个文件 key_file_name = certPath + "/" + rand_str + ".key" key_output = open(key_file_name, 'w') key_output.write(key_file_str) key_output.close() return HTTPSHandler(key_file_name, cert_file_name, restfulUrl);
def get_item_based_rec(uid):
vid_list = []
sql = "SELECT vid,sum(sim) as score from ( \
( \
SELECT vid2 as vid,sim from item_based_rec WHERE vid1 in \
(SELECT * FROM \
( \
select source_vid from user_action where uid='" + uid + "' ORDER BY rand() limit 10 \
) as vid_list) \
) \
UNION \
( \
SELECT vid1 as vid,sim from item_based_rec WHERE vid2 in \
(SELECT * FROM \
( \
select source_vid from user_action where uid='" + uid + "' ORDER BY rand() limit 10 \
) as vid_list \
) \
) \
) as vid_score group by vid ORDER BY score desc LIMIT 100"
result = sql_appbk.mysql_com(sql)
for item in result:
vid_list.append(item["vid"])
return vid_list
def queryKeywordsReportByAdgroupId(self, email, adgroupId, starttime,
endtime):
status = {}
status['status'] = 0
status['msg'] = "Success!"
result = None
try:
sql_com = "select * from asm_targeted_keywords where status=\"ACTIVE\" and localstat=1 and appbk_group_id=" + adgroupId
keywords = sql_appbk.mysql_com(sql_com)
for keyword in keywords:
response = self.get_keyword_report(keyword['id'],
keyword['campaignId'],
starttime, endtime)
responseData = response.read()
if response.status != 200:
status['status'] = -1
status['msg'] = response.reason
status['error'] = json.loads(responseData)['error']
self.logger.error("User " + email +
": get keyword reports Error")
return json.dumps(status)
keywordReportJson = json.loads(responseData)['data']
result = self.saveKeywordsReports(keywordReportJson, starttime)
except Exception, e:
self.logger.error("User " + email + ": get keyword report failed")
self.logger.error("Exception : " + str(e))
status['status'] = -1
status['msg'] = "Exception occurred!"
def update_db(vid,appbk_sub_category,appbk_tags):
sql_com="update video_info_ctr set appbk_sub_category='" \
+ appbk_sub_category +"', appbk_tags='" + appbk_tags \
+ "' where id=" + str(vid)
result = sql_appbk.mysql_com(sql_com)
return result
def get_video_id():
video_id_list = []
sql = "select id from video_info where is_download=1 limit 1700"
result = sql_appbk.mysql_com(sql)
for item in result:
video_id_list.append(item["id"])
return video_id_list
def query_all_campaigns(self, email, campaignId, starttime, endtime):
status = {}
status['status'] = 0
status['msg'] = "Success!"
result = None
try:
sql_com = ''
if campaignId is not None:
sql_com = "select id from asm_campaign where localstat=1 and displayStatus=\"running\" and email=\"" + email + "\" and auto_id=" + str(
campaignId)
else:
sql_com = "select id from asm_campaign where localstat=1 and displayStatus=\"running\" and email=\"" + email + "\""
campaigns = sql_appbk.mysql_com(sql_com)
for campaign in campaigns:
response = self.get_campaign_report(campaign['id'], starttime,
endtime)
responseData = response.read()
if response.status != 200:
status['status'] = -1
status['msg'] = response.reason
status['error'] = json.loads(responseData)['error']
self.logger.error("User " + email +
": get campaign reports Error")
return json.dumps(status)
campaignReportJson = json.loads(responseData)['data']
result = self.saveCampaignReports(campaignReportJson,
starttime)
except Exception, e:
self.logger.error("User " + email + ": get campaign report failed")
self.logger.error("Exception : " + str(e))
status['status'] = -1
status['msg'] = "Exception occurred!"
def queryAdgroupReportById(self, email, adgroupId, starttime, endtime):
status = {}
status['status'] = 0
status['msg'] = "Success!"
try:
sql_com = "select * from asm_adgroup where localstat=1 and auto_id=" + adgroupId
adgroups = sql_appbk.mysql_com(sql_com)
for adgroup in adgroups:
response = self.get_adgroup_report(adgroup['id'],
adgroup['campaignId'],
starttime, endtime)
responseData = response.read()
if response.status != 200:
status['status'] = -1
status['msg'] = response.reason
status['error'] = json.loads(responseData)['error']
self.logger.error("User " + email +
": get campaign reports Error")
return json.dumps(status)
adGroupReportJson = json.loads(responseData)['data']
result = self.saveAdgroupReports(adGroupReportJson, starttime)
except Exception, e:
self.logger.error("User " + email + ": get adgroup report failed")
self.logger.error("Exception : " + str(e))
status['status'] = -1
status['msg'] = "Exception occurred!"
def get_user_based_rec(uid):
vid_list = []
sql = "select vid from user_based_rec where uid='" + uid +"'"
result = sql_appbk.mysql_com(sql)
if result:
vids = result[0]["vid"]
vid_list = vids.split(",")
return vid_list
def get_video_link():
# job_info = get_job_info()
# 获得未下载的视频链接,根据id取模,获得本机需要下载的link信息,取3天内的
# sql_com = "select id,vid,link,source FROM video_info_test where DATEDIFF(NOW(),fetch_time)<3 and duration <301 and (public_type='all' or public_type is null) and (id%"+str(job_info['len'])+")="+str(job_info['index'])+" and down_action_time IS NULL"
sql_com = "select id,vid,link,source FROM video_info where DATEDIFF(NOW(),fetch_time)<10 and duration <301 and (public_type='all' or public_type is null) and is_download=0"
result = sql_appbk.mysql_com(sql_com)
for row in result:
filename = row["source"] + "_" + row["vid"]
print(row["link"] + "\t" + filename)
def query_all_targeted_keywords(self, email):
status = {}
status['status'] = 0;
status['msg'] = "Success!"
try:
sql_com = "select id from asm_campaign where displayStatus=\"running\" and email=\"" + email + "\""
campaigns = sql_appbk.mysql_com(sql_com)
for campaign in campaigns:
sql_com = "select * from asm_targeted_keywords where localstat=0 and campaignId=" + campaign['id']
result = sql_appbk.mysql_com(sql_com)
for ret in result:
if ret['id'] is not None:
response = self.update_targeted_keywords(ret)
responseJson = response.read();
if response.status != 200:
status['status'] = -1
status['msg'] = response.reason
status['error'] = json.loads(responseJson)['error']
self.logger.error("User " + email + ": update a target keyword " + str(ret['auto_id']) + " Error")
return json.dumps(status)
responseJson = json.loads(responseJson)['data']
self.saveTargetedKeywords(responseJson[0], ret['auto_id'])
else:
response = self.create_targeted_keywords(ret)
responseJson = response.read();
if response.status != 200:
status['status'] = -1
status['msg'] = response.reason
status['error'] = json.loads(responseJson)['error']
self.logger.error("User " + email + ": create a target keyword " + str(ret['auto_id']) + " Error")
return json.dumps(status)
responseJson = json.loads(responseJson)['data']
self.saveTargetedKeywords(responseJson[0], ret['auto_id'])
except Exception,e:
self.logger.error("User " + email + ": create or update a target keyword " + str(ret['auto_id']) + " failed")
self.logger.error(Exception + " : " + e)
status['status'] = -1
status['msg'] = "Exception occurred!"
def get_user_action(uid):
vid_list = []
start_day = time.strftime('%Y-%m-%d',
time.localtime(time.time() - 3 * 24 * 60 * 60))
sql = "select * from user_action where uid='" + str(
uid) + "' and update_time>='" + start_day + "'"
result = sql_appbk.mysql_com(sql)
for item in result:
vid_list.append(str(item["source_vid"]))
return vid_list
def saveAdgroupReports(self, reportJson, starttime=None):
reportData = reportJson['reportingDataResponse']['row'][0]
today = datetime.datetime.now()
totalData = reportData['total']
metaData = reportData['metadata']
report = {}
report['adGroupId'] = metaData['adGroupId']
report['localSpend'] = totalData['localSpend']['amount']
report['ttr'] = totalData['ttr']
report['avgCPT'] = totalData['avgCPT']['amount']
report['avgCPA'] = totalData['avgCPA']['amount']
report['taps'] = totalData['taps']
report['impressions'] = totalData['impressions']
report['conversions'] = totalData['conversions']
report['conversionRate'] = totalData['conversionRate']
report['fetch_date'] = (
today + datetime.timedelta(days=-1)).strftime("%Y-%m-%d")
if starttime is not None:
report['fetch_date'] = starttime
report['cpaGoal'] = metaData['cpaGoal']['amount']
report['defaultCpcBid'] = metaData['defaultCpcBid']['amount']
key_str = ''
key_list = []
value_list = []
for item in report:
key_list.append(item)
value_list.append("'" + MySQLdb.escape_string(str(report[item])) +
"'")
#sqlcom
key_str = ",".join(key_list)
value_sql = ",".join(value_list)
sql_com = "insert into asm_adgroup_reports_daily (" + key_str + ") values (" + value_sql + ") on duplicate key update localSpend=values(localSpend), ttr=values(ttr), avgCPT=values(avgCPT), avgCPA=values(avgCPA), taps=values(taps), impressions=values(impressions), conversions=values(conversions),conversionRate=values(conversionRate), cpaGoal=values(cpaGoal), defaultCpcBid=values(defaultCpcBid);"
#print sql_com
sql_appbk.mysql_com(sql_com)
return sql_com
def get_video_info(vid):
video_info = {}
sql_com = "select * from video_info_ctr where id =" + str(vid)
result = sql_appbk.mysql_com(sql_com)
if result:
video_info["appbk_sub_category"] = result[0]["appbk_sub_category"]
video_info["appbk_tags"] = result[0]["appbk_tags"]
video_info["source"] = result[0]["source"]
video_info["duration"] = result[0]["duration"]
video_info["title_length"] = len(result[0]["title"])
return video_info
def getMemberInfo(self, email):
if email in memberDict:
return HTTPSHandler(memberDict[email][0], memberDict[email][1],
restfulUrl)
sql_com = "select * from asm_member where email='" + email + "'"
result = sql_appbk.mysql_com(sql_com)
return generateAuthentication(email,
cert_file=result[0]['sshcert'],
key_file=result[0]['sshkey'])
def update_db(uid, appbk_new_sub_category, appbk_new_tags):
# 获得当前时间
update_time = time.strftime('%Y-%m-%d %H:%M:%S',
time.localtime(time.time()))
sql_com = "replace into user_tags(uid ,appbk_sub_category ,appbk_tags,update_time)\
VALUES('" + str(
uid
) + "' , '" + appbk_new_sub_category + "' , '" + appbk_new_tags + "' , '" + update_time + "')"
result = sql_appbk.mysql_com(sql_com)
return result
def update_video_info(filename):
video_file = filename.split("/")[-1] # 去掉可能包含的路径
video_file = video_file.split(".")[0] # 去掉可能的扩展名
item_list = video_file.split('_')
source = item_list[0]
vid = item_list[1]
down_action_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
sql_com = "update video_info set down_action_time='" + down_action_time + "',is_download=1 where source='" + str(
source) + "' and vid='" + str(vid) + "'"
ret = sql_appbk.mysql_com(sql_com)
return ret
def get_user_info(uid):
#获得用户标签
user_info = get_user_tags(uid)
#获得用浏览视频数
try:
sql_com = "select count(*) as num from user_action_ctr where uid =" + str(uid)
result = sql_appbk.mysql_com(sql_com)
user_info["uv"] = result[0]["num"]
except:
user_info["uv"] = 0
return user_info
def saveTargetedKeywords(self, keywordJson, auto_id):
keyword = {}
keyword["id"] = keywordJson['id']
keyword["adGroupId"] = keywordJson['adGroupId']
keyword["text"] = keywordJson['text']
keyword["status"] = keywordJson['status']
keyword["matchType"] = keywordJson['matchType']
keyword["bidAmount"] = keywordJson['bidAmount']['amount']
keyword['updateTime'] = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
keyword['localstat'] = 1
sql_com = "update asm_targeted_keywords set "
conditions = [];
for item in keyword:
conditions.append(str(item) + "=\"" + str(keyword[item]) + "\"")
sql_com += " , ".join(conditions)
sql_com += " where auto_id=" + str(auto_id)
sql_appbk.mysql_com(sql_com)
#print sql_com
return sql_com
def get_videos_by_category(c, start=0, limit=10, uid="0", pid="0"):
temp_result = [] #中间结果
#获得热门推荐
vid_list = get_hot_videos(c, 30)
#获得基于内容的推荐
content_based_videos = get_content_based_videos(uid, 30)
vid_list.extend(content_based_videos)
#获得协同过滤推荐
co_based_videos = get_co_based_videos(uid, 30)
vid_list.extend(co_based_videos)
#去掉用户3天内的访问记录,同时去重复
user_viewed_vid = get_user_action(uid)
vid_list = list(set(vid_list) - set(user_viewed_vid))
#ctr预估重新排序
vid_list = get_ctr.get_ctr_recommend(uid, vid_list)
#查询视频元数据
if len(vid_list) > 0:
id_list_str = ",".join(vid_list)
sql = "SELECT id,vid,title,thumbnail,link,duration, \
bigThumbnail,view_count,appbk_category,source,published \
FROM video_info WHERE id in (" + id_list_str + ")"
else:
sql = "SELECT id,vid,title,thumbnail,link,duration, \
bigThumbnail,view_count,appbk_category,source,published \
FROM video_info limit 100" #如果没有推荐，随机选择一些
result = sql_appbk.mysql_com(sql)
for item in result:
#拼接play url
source = item["source"]
vid = item["vid"]
play_url = OSS_URL + source + "_" + vid + ".mp4"
item["play_url"] = play_url
temp_result.append(item)
#随机打乱
random.shuffle(temp_result)
#进行规则处理
final_result = {
"status": 0,
"msg": "success",
"results": temp_result[int(start):int(start) + int(limit)]
}
return json.dumps(final_result, cls=sql_appbk.CJsonEncoder)
def get_hot_videos(c, limit=400):
# 取70天内的结果, limit取足够大
start_day = time.strftime('%Y-%m-%d', time.localtime(time.time() - 70 * 24 * 60 * 60))
sql = "SELECT id,vid,title,thumbnail,link,duration, \
bigThumbnail,view_count,appbk_category,source,published \
FROM video_info WHERE down_action_time>'" + start_day + "' \
and appbk_category='" + c + "' \
ORDER BY view_count DESC \
limit " + str(limit)
result = sql_appbk.mysql_com(sql)
vid_list = []
for item in result:
vid_list.append(str(item["id"]))
return vid_list
def query_all_keywords(self, email, starttime, endtime):
status = {}
status['status'] = 0
status['msg'] = "Success!"
result = None
try:
sql_com = "select id from asm_campaign where localstat=1 and displayStatus=\"running\" and email=\"" + email + "\""
campaigns = sql_appbk.mysql_com(sql_com)
for campaign in campaigns:
sql_com = "select id from asm_targeted_keywords where localstat=1 and status=\"ACTIVE\" and campaignId=\"" + campaign[
'id'] + "\""
keywords = sql_appbk.mysql_com(sql_com)
for keyword in keywords:
response = self.get_keyword_report(keyword['id'],
campaign['id'],
starttime, endtime)
responseData = response.read()
if response.status != 200:
status['status'] = -1
status['msg'] = response.reason
status['error'] = json.loads(responseData)['error']
self.logger.error("User " + email +
": get keyword reports Error")
return json.dumps(status)
keywordReportJson = json.loads(responseData)['data']
#print keywordReportJson
result = self.saveKeywordsReports(keywordReportJson,
starttime)
#result = self.saveCampaignReports(campaignReportJson)
except Exception, e:
self.logger.error("User " + email + ": get keyword report failed")
self.logger.error("Exception : " + str(e))
status['status'] = -1
status['msg'] = "Exception occurred!"
def get_user_tags(uid):
sql_com = "SELECT appbk_sub_category,appbk_tags FROM user_tags WHERE uid = '" + str(
uid) + "'"
result = sql_appbk.mysql_com(sql_com)
if result:
appbk_sub_category_list = result[0]["appbk_sub_category"].split(
",") #类别标签
appbk_tag_list = result[0]["appbk_tags"].split(",") #关键词标签
#随机取3个
category_list = random.sample(appbk_sub_category_list, 3)
tag_list = random.sample(appbk_tag_list, 3)
return {"appbk_sub_category": category_list, "appbk_tags": tag_list}
else:
return {"appbk_sub_category": [], "appbk_tags": []}
def recommend(uid, limit=10):
sql = "SELECT source_vid, count(*) as score from user_action right JOIN \
( \
SELECT uid,count(*) as num from user_action where source_vid in \
(select source_vid FROM user_action where uid='" + uid + "') and uid!='" + uid + "' \
GROUP BY uid \
ORDER BY num DESC limit 50 \
) as simliar_user \
on user_action.uid=simliar_user.uid \
GROUP BY source_vid ORDER BY score DESC limit " + str(limit)
result = sql_appbk.mysql_com(sql)
vid_list = []
for item in result:
vid_list.append(item["source_vid"])
return vid_list
