def get_3407(date, gindex, database=UgcUserOn3407):
# 轻橙用户数(玩过轻橙作品的用户数)
user_playuser = []
for i in range(0, 100):
print('轻橙用户数', i)
sql = """SELECT gindex,COUNT(DISTINCT uid) FROM platform_count_01.user_game_record_{0} where gindex in {1} GROUP BY gindex
""".format(i, tuple(gindex['gindex']))
user_playuser.append(df_from_sql(database, sql, ['gindex', '总游玩人数']))
user_playuser = pd.concat(user_playuser, ignore_index=True)
user_playuser = user_playuser.groupby(['gindex'], as_index=False).agg({'总游玩人数': np.sum})
dt['总轻橙用户数'] = user_playuser["总游玩人数"].sum()
# 轻橙用户数(玩过轻橙作品的用户数全去重)
user_playuser = []
tup = tuple(gindex['gindex'])
for i in range(0, 100):
print('轻橙用户数去重', i)
sql = """ SELECT COUNT(DISTINCT uid) FROM platform_count_01.user_game_record_{0} where gindex in {1}""".format(i, tup)
user_playuser.append(df_from_sql(database, sql, ['总游玩人数']))
user_playuser = pd.concat(user_playuser, ignore_index=True)
dt['总轻橙用户数（全去重）'] = user_playuser["总游玩人数"].sum()
# 当日游玩人数和总时长
sql = """
SELECT COUNT(DISTINCT uid),sum(run_time)/3600 FROM platform_count_01.org_game_daily_by_user_{0} where gindex in {1}
""".format(date, tuple(gindex['gindex']))
res = get_res(database, sql)
dt['每日去重用户数'] = res[0][0]
dt['每日用户总游玩时长（小时）'] = int(res[0][1])
def get_qingcheng(d):
get_3510(d)
gindex, gindex_check = get_gindex(d)
get_3406(d, gindex, gindex_check)
get_3408(d, gindex)
get_3513(d, gindex)
get_3407(d, gindex)
uid = get_author_count(d)
# 新发布工程的签约作者人数
tt_sum1 = []
for i in range(0, 100):
print('签约作者数', i)
sql = f"""SELECT uid, gguid from sae_project.orange_uid_guid_map_{i} where source=2 and DATE_FORMAT(create_time,"%Y%m%d")<= {d}"""
tt_sum1.append(df_from_sql(UgcUserOn3510, sql, ['作者UID', 'guid']))
tt_sum1 = pd.concat(tt_sum1, ignore_index=True)
project_uid = list(set(list(tt_sum1['作者UID'])))
project_uid_sign = [v for v in project_uid if v in uid]
dt['新建轻橙工程签约作者总数'] = len(project_uid_sign)
dt['新建轻橙工程非签约作者总数'] = len(project_uid) - len(project_uid_sign)
data_all = pd.DataFrame([dt])
data_all = data_all[['日期', '新建轻橙工程数量', '轻橙新发布的作品数量', '正式发布过的新增轻橙作品数量',
'正式发布过字数大于0的新增轻橙作品数量', '新过审轻橙作品数量', 'L1作品数', 'L2作品数',
'L3作品数', 'L4作品数', 'L5作品数', '轻橙完结作品总数量', '当日更新字数',
'当日鲜花总数', '分享', '收藏', '点赞', '评论', '正式发布轻橙作品签约作者总数',
'正式发布轻橙作品非签约作者总数', '新建轻橙工程签约作者总数', '新建轻橙工程非签约作者总数',
'总轻橙用户数', '总轻橙用户数（全去重）', '每日去重用户数', '每日用户总游玩时长（小时）']]
data_all.to_excel(f'P:/a/轻橙/月报/{d}_轻橙.xlsx', index=False)
data_all = pd.DataFrame.stack(data_all, level=-1, dropna=True)
return data_all
def get_3510(_date):
# 新建轻橙工程数量
tt_sum = 0
for i in range(0, 100):
print('3510', i)
sql = f"""SELECT count(uid) from sae_project.orange_uid_guid_map_{i} where source=2 and DATE_FORMAT(create_time,"%Y%m%d")= {_date}"""
tt_sum += df_from_sql(UgcUserOn3510, sql, [''])[''][0]
dt['新建轻橙工程数量'] = tt_sum
def get_list():
database = js.UgcUserOn3406
sql = """SELECT tid, tname
FROM dbbh_website.org_tag_summary
WHERE tname like '#%#'
AND status in (0, 9)
"""
df_list = js.df_from_sql(database, sql, ['tag', 'tname'])
return list(df_list['tag']), list(df_list['tname'])
def get_nickname(tup):
dataframe = []
for idx in range(100):
print(idx)
res = df_from_sql(UgcUserOn3402,
f"SELECT uid,nick_name FROM dbbh_passport.uc_user_info_{idx} WHERE uid in {tup}", ['uid', 'name'])
dataframe.append(res)
dataframe = pd.concat(dataframe, ignore_index=True)
return dataframe
def get_gindex_level(tup):
sql = f"""
SELECT
gindex,
`level`
FROM
dbbh_website.org_auto_promo_game
WHERE
gindex in {tup}
"""
res = df_from_sql(UgcUserOn3406, sql, ['gindex', 'level'])
return res
def get_nickname(df, st):
# 获取昵称
uid = tuple(df[f'{st}ID'])
df = []
for i in range(100):
print(i)
res = df_from_sql(
UgcUserOn3402,
f"SELECT uid,nick_name FROM dbbh_passport.uc_user_info_{i} WHERE uid in {uid}",
[f'{st}ID', f'{st}昵称'])
df.append(res)
df = pd.concat(df, ignore_index=True)
return df
def get_gindex(_date, _tag):
# 带轻橙标签的游戏编号
database = js.AnalysisUser3406
sql = """SELECT gindex, guid
FROM dbbh_website.org_game_summary
WHERE gindex IN
(
SELECT gindex
FROM dbbh_website.org_tag_game
WHERE tid = {1}
)
AND FROM_UNIXTIME(passed_time, "%Y%m%d") <={0} and check_level >=2 """.format(
_date, _tag)
return js.df_from_sql(database, sql, ['gindex', 'guid'])
def get_gindex(_date, database=UgcUserOn3406):
# 带轻橙标签的游戏编号
sql = f"""SELECT gindex, guid, check_level
FROM dbbh_website.org_game_summary
WHERE gindex IN (SELECT gindex
FROM dbbh_website.org_tag_game
WHERE tid = 12337)
AND FROM_UNIXTIME(create_time, "%Y%m%d") <={_date}
"""
gindex = df_from_sql(database, sql, ['gindex', 'guid', 'check_level'])
# 带轻橙标签的游戏编号过审作品
gindex_check = gindex[gindex['check_level'] >= 2][['gindex', 'guid']]
return gindex, gindex_check
def get_data(e):
sql = f"""
SELECT
uid,
gindex,
flower_num,
invest_status,
create_time
FROM
dbbh_website.org_user_game_invest
WHERE
invest_status != 2 AND
create_time < '{e}'
"""
res = df_from_sql(UgcUserOn3406, sql, ['uid', 'gindex', 'flower_num', 'status', 'create_time'])
return res
def get_deal(_month):
# 交易记录, 画师姓名
sql = f"""SELECT
a.order_id,
a.buyer_id,
a.seller_id,
a.price*a.buy_num,
a.buy_num,
a.goods_name,
FROM_UNIXTIME(a.pay_time,'%Y-%m-%d %H:%i:%s'),
b.user_name
FROM
c2c_order.c2c_order_{_month} AS a ,
c2c_user.user_identity AS b
WHERE
a.seller_id = b.uid AND
a.pay_status = 1"""
df = df_from_sql(
UgcUserOn3501, sql,
['订单ID', '买家ID', '画师ID', '订单金额', '购买数量', '商品名称', '交易时间', '画师姓名'])
return df
def get_gain(tup):
sql = f"""
SELECT
uid,
gindex,
flower_num,
voucher_1_num,
voucher_2_num,
voucher_3_num,
voucher_4_num,
create_time
FROM
dbbh_website.org_user_game_invest_dividend_award
WHERE
gindex in {tup} and
id in (SELECT min(id)
from dbbh_website.org_user_game_invest_dividend_award
WHERE flower_num != 0
GROUP BY uid)
"""
res = df_from_sql(UgcUserOn3406, sql, ['uid', 'gindex', 'flower', 'v1', 'v2', 'v3', 'v4', 'create_time'])
return res
def get_qingcheng(_date, _tag, _tname):
gindex = get_gindex(_date, _tag)
data_count = pd.DataFrame(
{
'日期': ['{0}'.format(_date)],
'收录作品总数量': [len(gindex)]
},
columns=['日期', '收录作品总数量'])
if len(gindex) == 0:
data_all = pd.DataFrame(
{
'日期': ['{0}'.format(_date)],
'标签': ['{0}_截止统计日期无过审作品'.format(_tname)]
},
columns=['日期', '标签'])
return data_all
else:
# 当日过审轻橙作品
sql = """SELECT {0} AS p, COUNT(*)
FROM dbbh_website.org_game_summary
WHERE gindex IN {1}
AND FROM_UNIXTIME(passed_time, "%Y%m%d") = {0}""".format(
_date, tuple(list(gindex['gindex'])))
data_pass_gindex = js.df_from_sql(js.UgcUserOn3406, sql,
['日期', '当日新过审作品数量'])
# 当日更新字数
sql = '''select distinct(gindex) from dbbh_website.org_game_version
where pub_day = {0} and gindex in {1} AND pub_mode = 1 '''.format(
_date, tuple(list(gindex['gindex'])))
result = js.df_from_sql(js.UgcUserOn3406, sql, ['gid'])
game = list(result['gid'])
update_con = []
for i in range(len(game)):
print(i)
sql = '''select gindex,word_sum,pub_day from dbbh_website.org_game_version
where gindex = %s AND pub_mode = 1 ''' % game[i]
df = js.df_from_sql(js.UgcUserOn3406, sql, ['作品id', '总字数', '发布日期'])
index_number = df[df['发布日期'] == _date].index.tolist()
if index_number[0] > 0:
df_new = df[index_number[0] - 1:index_number[-1] + 1]
word_list = list(df_new['总字数'])
differ = list(
map(
lambda x: x[0] - x[1],
zip(word_list[1:len(word_list)],
word_list[0:len(word_list) - 1])))
num_above0 = [i for i in differ if i > 0]
if len(num_above0) == 0:
pass
else:
update_con.append((game[i], sum(num_above0)))
else:
df_new = df[index_number[0]:index_number[-1] + 1]
word_list = list(df_new['总字数'])
word_list.insert(0, 0)
differ = list(
map(
lambda x: x[0] - x[1],
zip(word_list[1:len(word_list)],
word_list[0:len(word_list) - 1])))
num_above0 = [i for i in differ if i > 0]
if len(num_above0) == 0:
pass
else:
update_con.append((game[i], sum(num_above0)))
df_update = pd.DataFrame(update_con, columns=['gindex', '当日更新字数'])
data_word = pd.DataFrame(
{
'日期': ['{0}'.format(_date)],
'当日更新字数': [df_update["当日更新字数"].sum()]
},
columns=['日期', '当日更新字数'])
# 鲜花、人气、分享、收藏、点赞、评论
db3408, cur3408 = js.get_conn_cur_by_class(js.UgcUserOn3408,
_try_times=5)
sql = """ SELECT SUM(CASE WHEN coin_type=3 THEN coin_num/100 ELSE coin_num END) AS flower FROM website_userinfo.`org_log_user_coin_{0}`
WHERE coin_type IN (3,4,5,6,7,8,10,11)
AND TYPE IN (50,52,144,137,138,139) AND create_date ={1} AND gindex IN {2} """.format(
str(_date)[0:6], _date, tuple(list(gindex['gindex'])))
cur3408.execute(sql)
result = cur3408.fetchall()
flower_num = pd.DataFrame(
{
'日期': ['{0}'.format(_date)],
'当日鲜花总数': [int(result[0][0] or 0)]
},
columns=['日期', '当日鲜花总数'])
db3505, cur3505 = js.get_conn_cur_by_class(js.UgcUserOn3505,
_try_times=5)
sql = """ SELECT SUM(var4 + var6 + var7 + var8) AS '总人气'
FROM game_record.orange_log_vars_{0}
WHERE guid in {2} and day = {1}
""".format(
str(_date)[0:6], _date, tuple(list(gindex['guid'])))
cur3505.execute(sql)
result = cur3505.fetchall()
renqi_num = pd.DataFrame(
{
'日期': ['{0}'.format(_date)],
'当日人气总数': [int(result[0][0] or 0)]
},
columns=['日期', '当日人气总数'])
db3513, cur3513 = js.get_conn_cur_by_class(js.UgcUserOn3513,
_try_times=5)
sql = """
SELECT {1} AS p,SUM(share_num) as s from user_act.share_user_share_game_{0}
WHERE share_date = {1} AND gindex IN {2}
""".format(str(_date)[0:6], _date, tuple(list(gindex['gindex'])))
cur3513.execute(sql)
result = cur3513.fetchall()
share_num = pd.DataFrame(list(result), columns=['日期', '分享'])
db3408, cur3408 = js.get_conn_cur_by_class(js.UgcUserOn3408,
_try_times=5)
sql = """SELECT {1} AS p,SUM(num) FROM (SELECT gindex ,COUNT(*) as num FROM website_userinfo.org_user_fav_game_0 WHERE gindex IN {0} and add_day = {1} group by gindex UNION ALL
SELECT gindex ,COUNT(*) as num FROM website_userinfo.org_user_fav_game_1 WHERE gindex IN {0} and add_day = {1} group by gindex UNION ALL
SELECT gindex ,COUNT(*) as num FROM website_userinfo.org_user_fav_game_2 WHERE gindex IN {0} and add_day = {1} group by gindex UNION ALL
SELECT gindex ,COUNT(*) as num FROM website_userinfo.org_user_fav_game_3 WHERE gindex IN {0} and add_day = {1} group by gindex UNION ALL
SELECT gindex ,COUNT(*) as num FROM website_userinfo.org_user_fav_game_4 WHERE gindex IN {0} and add_day = {1} group by gindex ) as a
""".format(tuple(list(gindex['gindex'])), _date)
cur3408.execute(sql)
result = cur3408.fetchall()
fav_num = pd.DataFrame(list(result), columns=['日期', '收藏'])
db6066, cur6066 = js.get_conn_cur_by_class(js.UgcUserOn3513,
_try_times=5)
sql = """SELECT {1} AS p,SUM(num) FROM ( select count(gindex) as num from game_outer_attr.org_game_star_record_0 where gindex in {0} AND FROM_UNIXTIME(add_time, "%Y%m%d")={1} UNION ALL
select count(gindex) as num from game_outer_attr.org_game_star_record_1 where gindex in {0} AND FROM_UNIXTIME(add_time, "%Y%m%d")={1} UNION ALL
select count(gindex) as num from game_outer_attr.org_game_star_record_2 where gindex in {0} AND FROM_UNIXTIME(add_time, "%Y%m%d")={1} UNION ALL
select count(gindex) as num from game_outer_attr.org_game_star_record_3 where gindex in {0} AND FROM_UNIXTIME(add_time, "%Y%m%d")={1} UNION ALL
select count(gindex) as num from game_outer_attr.org_game_star_record_4 where gindex in {0} AND FROM_UNIXTIME(add_time, "%Y%m%d")={1} UNION ALL
select count(gindex) as num from game_outer_attr.org_game_star_record_5 where gindex in {0} AND FROM_UNIXTIME(add_time, "%Y%m%d")={1} UNION ALL
select count(gindex) as num from game_outer_attr.org_game_star_record_6 where gindex in {0} AND FROM_UNIXTIME(add_time, "%Y%m%d")={1} UNION ALL
select count(gindex) as num from game_outer_attr.org_game_star_record_7 where gindex in {0} AND FROM_UNIXTIME(add_time, "%Y%m%d")={1} UNION ALL
select count(gindex) as num from game_outer_attr.org_game_star_record_8 where gindex in {0} AND FROM_UNIXTIME(add_time, "%Y%m%d")={1} UNION ALL
select count(gindex) as num from game_outer_attr.org_game_star_record_9 where gindex in {0} AND FROM_UNIXTIME(add_time, "%Y%m%d")={1} UNION ALL
select count(gindex) as num from game_outer_attr.org_game_star_record_10 where gindex in {0} AND FROM_UNIXTIME(add_time, "%Y%m%d")={1} UNION ALL
select count(gindex) as num from game_outer_attr.org_game_star_record_11 where gindex in {0} AND FROM_UNIXTIME(add_time, "%Y%m%d")={1} UNION ALL
select count(gindex) as num from game_outer_attr.org_game_star_record_12 where gindex in {0} AND FROM_UNIXTIME(add_time, "%Y%m%d")={1} UNION ALL
select count(gindex) as num from game_outer_attr.org_game_star_record_13 where gindex in {0} AND FROM_UNIXTIME(add_time, "%Y%m%d")={1} UNION ALL
select count(gindex) as num from game_outer_attr.org_game_star_record_14 where gindex in {0} AND FROM_UNIXTIME(add_time, "%Y%m%d")={1} ) as a
""".format(tuple(list(gindex['gindex'])), _date)
cur6066.execute(sql)
result = cur6066.fetchall()
dianzan_num = pd.DataFrame(list(result), columns=['日期', '点赞'])
# 作者人数
db3406, cur3406 = js.get_conn_cur_by_class(js.UgcUserOn3406,
_try_times=5)
sql = """SELECT COUNT(DISTINCT author_uid) FROM dbbh_website.org_game_summary WHERE gindex in {0} AND gindex in (SELECT gindex from dbbh_website.org_game_version WHERE pub_mode = 1 AND gindex in {0} )
""".format(tuple(list(gindex['gindex'])))
cur3406.execute(sql)
res1 = cur3406.fetchall()
pd.DataFrame({
'日期': ['{0}'.format(_date)],
'发布轻橙作品总作者数': [res1[0][0]]
},
columns=['日期', '发布轻橙作品总作者数'])
# 签约作者人数
db3408, cur3408 = js.get_conn_cur_by_class(js.UgcUserOn3408,
_try_times=5)
sql = """ SELECT uid FROM website_userinfo.org_user_sign WHERE `status` = 2 """
cur3408.execute(sql)
result = cur3408.fetchall()
uid = [i[0] for i in result]
db3406, cur3406 = js.get_conn_cur_by_class(js.UgcUserOn3406,
_try_times=5)
sql = """SELECT COUNT(DISTINCT author_uid) FROM dbbh_website.org_game_summary WHERE gindex in {0} AND gindex in (SELECT gindex from dbbh_website.org_game_version WHERE pub_mode = 1 AND gindex in {0} )
AND author_uid in {1}""".format(tuple(list(gindex['gindex'])),
tuple(uid))
cur3406.execute(sql)
res2 = cur3406.fetchall()
data_signauthor = pd.DataFrame(
{
'日期': ['{0}'.format(_date)],
'正式发布轻橙作品签约作者总数': [res2[0][0]]
},
columns=['日期', '正式发布轻橙作品签约作者总数'])
data_unsignauthor = pd.DataFrame(
{
'日期': ['{0}'.format(_date)],
'正式发布轻橙作品非签约作者总数': [res1[0][0] - res2[0][0]]
},
columns=['日期', '正式发布轻橙作品非签约作者总数'])
# 轻橙用户数(玩过轻橙作品的用户数)
db3407, cur3407 = js.get_conn_cur_by_class(js.UgcUserOn3407,
_try_times=5)
sql = """
SELECT COUNT(DISTINCT uid) FROM platform_count_01.org_game_daily_by_user_{0} where gindex in {1}
""".format(_date, tuple(list(gindex['gindex'])))
cur3407.execute(sql)
result = cur3407.fetchall()
data_userall = pd.DataFrame(
{
'日期': ['{0}'.format(_date)],
'每日用户数': [result[0][0]]
},
columns=['日期', '每日用户数'])
flower_num[["日期"]] = flower_num[["日期"]].astype(int)
share_num[["日期"]] = share_num[["日期"]].astype(int)
fav_num[["日期"]] = fav_num[["日期"]].astype(int)
dianzan_num[["日期"]] = dianzan_num[["日期"]].astype(int)
renqi_num[["日期"]] = renqi_num[["日期"]].astype(int)
data_signauthor[["日期"]] = data_signauthor[["日期"]].astype(int)
data_unsignauthor[["日期"]] = data_unsignauthor[["日期"]].astype(int)
data_word[["日期"]] = data_word[["日期"]].astype(int)
data_userall[["日期"]] = data_userall[["日期"]].astype(int)
data_count[["日期"]] = data_count[["日期"]].astype(int)
data_all = pd.merge(data_count, data_pass_gindex, on='日期', how='left')
data_all = pd.merge(data_all, data_word, on='日期', how='left')
data_all = pd.merge(data_all, flower_num, on='日期', how='left')
data_all = pd.merge(data_all, share_num, on='日期', how='left')
data_all = pd.merge(data_all, fav_num, on='日期', how='left')
data_all = pd.merge(data_all, dianzan_num, on='日期', how='left')
data_all = pd.merge(data_all, renqi_num, on='日期', how='left')
data_all = pd.merge(data_all, data_signauthor, on='日期', how='left')
data_all = pd.merge(data_all, data_unsignauthor, on='日期', how='left')
data_all = pd.merge(data_all, data_userall, on='日期', how='left')
data_all['标签'] = '{0}'.format(_tname)
data_all.to_csv('P:\\a\\轻橙\\每日数据\\{0}_轻橙_{1}.csv'.format(_date, _tag),
sep=',',
index=False)
return data_all
def get_3406(date, gindex, gindex_check, database=UgcUserOn3406):
sql = f"""SELECT count(DISTINCT(gindex))
FROM dbbh_website.org_game_version
WHERE channel_id=2
AND version=1
AND pub_day = {date}
GROUP BY pub_day"""
dt['轻橙新发布的作品数量'] = get_res(database, sql)[0][0]
sql = f"""SELECT count(DISTINCT(gindex))
FROM dbbh_website.org_game_version
WHERE channel_id = 2
AND pub_mode > 0
AND pub_day = {date}
AND gindex not in(SELECT DISTINCT(gindex)
FROM dbbh_website.org_game_version
WHERE channel_id = 2
AND pub_mode > 0
AND pub_day < {date})"""
dt['正式发布过的新增轻橙作品数量'] = get_res(database, sql)[0][0]
sql = f"""SELECT count(DISTINCT(gindex))
FROM dbbh_website.org_game_version
WHERE channel_id=2
AND word_sum > 0
AND pub_mode > 0
AND pub_day = {date}
AND gindex not in(SELECT DISTINCT(gindex)
FROM dbbh_website.org_game_version
WHERE channel_id=2
AND word_sum > 0
AND pub_mode > 0
AND pub_day < {date})"""
dt['正式发布过字数大于0的新增轻橙作品数量'] = get_res(database, sql)[0][0]
# 当日过审轻橙作品
sql = f"""SELECT COUNT(*)
FROM dbbh_website.org_game_summary
WHERE gindex IN {tuple(gindex['gindex'])}
AND FROM_UNIXTIME(passed_time, "%Y%m%d") = {date}"""
dt['新过审轻橙作品数量'] = get_res(database, sql)[0][0]
# 各等级作品分布
sql = f"""SELECT COUNT(gindex) from dbbh_website.org_auto_promo_game WHERE gindex IN {tuple(gindex_check['gindex'])} GROUP BY LEVEL"""
res = get_res(database, sql)
dt['L1作品数'] = res[0][0]
dt['L2作品数'] = res[1][0]
dt['L3作品数'] = res[2][0]
dt['L4作品数'] = res[3][0]
dt['L5作品数'] = res[4][0]
# 当日更新字数
sql = f'''select distinct(gindex)
from dbbh_website.org_game_version
where pub_day = {date}
and gindex in {tuple(gindex['gindex'])}
AND pub_mode = 1'''
game = list(df_from_sql(database, sql, [''])[''])
update_con = []
for i in range(len(game)):
print(i)
sql = f'''select gindex,word_sum,pub_day from dbbh_website.org_game_version
where gindex = {game[i]} AND pub_mode = 1 '''
df = df_from_sql(database, sql, ['作品id', '总字数', '发布日期'])
index_number = df[df['发布日期'] == date].index.tolist()
if index_number[0] > 0:
df_new = df[index_number[0] - 1:index_number[-1] + 1]
word_list = list(df_new['总字数'])
chazhi = list(map(lambda x: x[0] - x[1], zip(word_list[1:len(word_list)], word_list[0:len(word_list) - 1])))
num_above0 = [i for i in chazhi if i > 0]
if len(num_above0) == 0:
pass
else:
update_con.append((game[i], sum(num_above0)))
else:
df_new = df[index_number[0]:index_number[-1] + 1]
word_list = list(df_new['总字数'])
word_list.insert(0, 0)
chazhi = list(map(lambda x: x[0] - x[1], zip(word_list[1:len(word_list)], word_list[0:len(word_list) - 1])))
num_above0 = [i for i in chazhi if i > 0]
if len(num_above0) == 0:
pass
else:
update_con.append((game[i], sum(num_above0)))
data_word = pd.DataFrame(update_con, columns=['gindex', '当日更新字数'])
dt['当日更新字数'] = data_word["当日更新字数"].sum()
sql = f"""SELECT sum(num) from (select count(a.cid) as num from dbbh_website.org_game_comment_summary_0 a, dbbh_website.org_game_comment_detail_0 b where a.cid = b.cid and a.gindex in {tuple(gindex['gindex'])} and a.add_day= {date} union all
select count(a.cid) as num from dbbh_website.org_game_comment_summary_1 a, dbbh_website.org_game_comment_detail_1 b where a.cid = b.cid and a.gindex in {tuple(gindex['gindex'])} and a.add_day= {date} union all
select count(a.cid) as num from dbbh_website.org_game_comment_summary_2 a, dbbh_website.org_game_comment_detail_2 b where a.cid = b.cid and a.gindex in {tuple(gindex['gindex'])} and a.add_day= {date} union all
select count(a.cid) as num from dbbh_website.org_game_comment_summary_3 a, dbbh_website.org_game_comment_detail_3 b where a.cid = b.cid and a.gindex in {tuple(gindex['gindex'])} and a.add_day= {date} union all
select count(a.cid) as num from dbbh_website.org_game_comment_summary_4 a, dbbh_website.org_game_comment_detail_4 b where a.cid = b.cid and a.gindex in {tuple(gindex['gindex'])} and a.add_day= {date} union all
select count(a.cid) as num from dbbh_website.org_game_comment_summary_5 a, dbbh_website.org_game_comment_detail_5 b where a.cid = b.cid and a.gindex in {tuple(gindex['gindex'])} and a.add_day= {date} union all
select count(a.cid) as num from dbbh_website.org_game_comment_summary_6 a, dbbh_website.org_game_comment_detail_6 b where a.cid = b.cid and a.gindex in {tuple(gindex['gindex'])} and a.add_day= {date} union all
select count(a.cid) as num from dbbh_website.org_game_comment_summary_7 a, dbbh_website.org_game_comment_detail_7 b where a.cid = b.cid and a.gindex in {tuple(gindex['gindex'])} and a.add_day= {date} union all
select count(a.cid) as num from dbbh_website.org_game_comment_summary_8 a, dbbh_website.org_game_comment_detail_8 b where a.cid = b.cid and a.gindex in {tuple(gindex['gindex'])} and a.add_day= {date} union all
select count(a.cid) as num from dbbh_website.org_game_comment_summary_9 a, dbbh_website.org_game_comment_detail_9 b where a.cid = b.cid and a.gindex in {tuple(gindex['gindex'])} and a.add_day= {date}) as a """
dt['评论'] = get_res(database, sql)[0][0]
# 轻橙完结作品总数
sql = """SELECT COUNT(*)
FROM dbbh_website.org_game_summary
WHERE gindex IN (SELECT gindex
FROM dbbh_website.org_game_summary
WHERE gindex IN (SELECT gindex
FROM dbbh_website.org_tag_game
WHERE tid = 12337)
AND FROM_UNIXTIME(create_time, "%Y%m%d") <={0})
AND complete_date <= {0} and complete_date >0""".format(date)
dt['轻橙完结作品总数量'] = get_res(database, sql)[0][0]