def get_users_detail(users):
sql = '''
SELECT uid,brand,platform,os,os_version,device_size,network, ctype,
province, city, area from user_device where uid in ({})
'''
rows = pg.query(sql.format(','.join(str(u) for u in users)))
return rows
def get_item_topic(items_list, model_v='2017-04-07-10-49-37'):
sql = '''
select nid, topic_id, probability, model_v, ctime from news_topic_v2
where model_v= '{}' and nid in ({})
'''
rows = pg.query(sql.format(model_v, ','.join(str(i) for i in items_list)))
return rows
def enumerate_article_pname():
nt = datetime.datetime.now()
str_now = nt.strftime('%Y-%m-%d %H:%M:%S')
sql = '''
select count(1) from newspublisherlist_v2
'''
rows = pg.query(sql.format(str_now, 7))
return rows[0][0]
def get_users_topic(users, time_interval='15 days'):
nt = datetime.datetime.now()
str_now = nt.strftime('%Y-%m-%d %H:%M:%S')
sql = '''
SELECT uid, topic_id, probability, model_v, create_time from user_topics_v2
where create_time > to_timestamp('{}', 'yyyy-mm-dd hh24:mi:ss') - interval '{}'
and uid in ({}) and model_v = '2017-04-07-10-49-37'
'''
rows = pg.query(
sql.format(str_now, time_interval, ','.join(str(u) for u in users)))
return rows
def enumerate_user_attribute(attribute_name, active_users):
user_device_sql = '''
select distinct {}
from user_device
where uid in ({})
'''
rows = pg.query(
user_device_sql.format(attribute_name,
','.join(str(u) for u in active_users)))
feature_dict = OrderedDict((r[0], 0) for r in rows)
return feature_dict
def get_sample_user(time_interval='6 hours', click_times=1):
nt = datetime.datetime.now()
str_now = nt.strftime('%Y-%m-%d %H:%M:%S')
sql = '''
select n.uid from newsrecommendclick as n left join user_device as u on n.uid = u.uid
where n.ctime > to_timestamp('{}', 'yyyy-mm-dd hh24:mi:ss') - interval '{}' and
u.ctime < to_timestamp('{}', 'yyyy-mm-dd hh24:mi:ss') - interval '1 day' and n.uid not in (0)
group by n.uid HAVING "count"(*)>={}
'''
rows = pg.query(sql.format(str_now, time_interval, str_now, click_times))
a_users = [r[0] for r in rows]
return a_users
def get_active_user(time_interval='24 days',
time_active='1 hour',
click_times=1):
nt = datetime.datetime.now()
str_now = nt.strftime('%Y-%m-%d %H:%M:%S')
sql = '''
select uid from newsrecommendclick
where ctime > to_timestamp('{}', 'yyyy-mm-dd hh24:mi:ss') - interval '{}' and uid not in (0)
and uid in (select uid from newsrecommendclick
where ctime > to_timestamp('{}', 'yyyy-mm-dd hh24:mi:ss') - interval '{}'
and ctime < to_timestamp('{}', 'yyyy-mm-dd hh24:mi:ss'))
group by uid HAVING "count"(*)>={}
'''
rows = pg.query(
sql.format(str_now, time_interval, str_now, time_active, str_now,
click_times))
a_users = [r[0] for r in rows]
return a_users
def get_item_detail(items_list):
sql = '''
select * from newslist_v2 where nid in ({})
'''
rows = pg.query(sql.format(','.join(str(i) for i in items_list)))
return rows