def combine_where_clause(self, input_dict, use_channels=True, use_zoneids=True): # only applied for standard input dict is_legal_input(input_dict) where_clause = '' if len(input_dict['channel_list']) and use_channels: where_clause = where_clause + 'and channel in (' + self.sql_value_str(input_dict['channel_list']) + ')' if len(input_dict['zone_list']) and use_zoneids: where_clause = where_clause + 'and zoneid in (' + self.sql_value_str(input_dict['zone_list']) + ')' return where_clause
def tech_avglevel(input_dict): data_dict = dict() for y in Y_list: data_dict[y] = dict() head_name = "科技人均等级" easy_mysql = EasyMysql.EasyMysql() is_legal_input(input_dict) date_list = [max(input_dict['date_list'])] date_list = sorted(date_list) if len(date_list) == 0: msg = 'Not date selected, len(date_list)' + str(len(date_list)) raise RuntimeError(msg) zoneid_list = input_dict['zone_list'] channel_list = input_dict['channel_list'] cursor = input_dict['cursor'] sql_where = ' where date =' + easy_mysql.sql_value_str(date_list) if len(zoneid_list) > 0: sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str( zoneid_list) + ') ' if len(channel_list) > 0: sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str( channel_list) + ') ' sql_str = 'select mvip,count(uid) from(select uid,max(level) as mlevel,max(vip_level) as mvip from user_active ' + sql_where + ' group by uid) as a group by mvip;' cursor.execute(sql_str) all_data = cursor.fetchall() if all_data: for rec in all_data: vip = int(rec[0]) user = int(rec[1]) data_dict['UserCount'][vip] = user # sql_str = 'select techid,mvip,mtech,count(uid),sum(mtech)/count(uid) from (select uid,techid,max(viplevel) as mvip,max(techlevel) as mtech from TechInfo'+sql_where+' group by uid,techid) as a group by techid,mvip,mtech;' # sql_str = 'select techid,mvip,count(uid),sum(mtech)/count(uid) from (select uid,techid,max(viplevel) as mvip,max(techlevel) as mtech from TechInfo '+sql_where+' group by uid,techid) as a group by techid,mvip;' sql_str = 'select techid,viplevel,sum(peoplenum),sum(techlevel*peoplenum)/sum(peoplenum) from TechInfoNew ' + sql_where + ' and techlevel>0 group by techid,viplevel;' cursor.execute(sql_str) all_data = cursor.fetchall() if all_data: for rec in all_data: data_dict[rec[0]][rec[1]] = rec[3] for key in data_dict.keys(): if X_first[0] not in data_dict.keys(): data_dict[key]['trans'] = Y_trans[key] res_dict = { 'data_dict': data_dict, 'X_list': X_list, 'Y_list': Y_list, 'head_name': head_name, 'X_trans': X_trans, 'default_value': default_value } # print(res_dict) res_dict["note"] = "统计最后日的各VIP段的激活科技的人的平均等级*" return res_dict
def get_diamond_times(input_dict): global X_list global X_trans X_list = [] data_dict = dict() for y in Y_list: data_dict[y] = dict() head_name = "钻石消耗次数" easy_mysql = EasyMysql.EasyMysql() is_legal_input(input_dict) date_list = input_dict['date_list'] date_list = sorted(date_list) if len(date_list) == 0: msg = 'Not date selected, len(date_list)' + str(len(date_list)) raise RuntimeError(msg) zoneid_list = input_dict['zone_list'] channel_list = input_dict['channel_list'] cursor = input_dict['cursor'] sql_where = ' where date in (' + easy_mysql.sql_value_str(date_list) + ') ' if len(zoneid_list) > 0: sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str(zoneid_list) + ') ' if len(channel_list) > 0: sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str(channel_list) + ') ' sql_str = 'select mvip,count(uid) from(select uid,max(level) as mlevel,max(vip_level) as mvip from user_active '+sql_where+' group by uid) as a group by mvip;' cursor.execute(sql_str) all_data = cursor.fetchall() if all_data: for rec in all_data: vip = int(rec[0]) user = int(rec[1]) data_dict['UserCount'][vip]=user X_list.append(vip) X_list = sorted(list(set(X_list))) X_listtrans =['翻译'] + map(lambda x: 'VIP' + str(x), X_list) X_list = X_first + X_list X_trans = dict(zip(X_list, X_listtrans)) sqlstr='select moneytype,mvip,count(distinct(a.uid)),count(a.uid),sum(cash),sum(totalcost) from (select uid,moneytype,sum(cash) as cash,sum(diamond) as diamond,sum(cash+diamond) as totalcost from diamond_detail'+sql_where+' and keyword="CostDiamond" group by uid,moneytype) as a inner join (select uid,max(vip_level) as mvip from user_active'+sql_where+' group by uid) as b on a.uid=b.uid group by moneytype,mvip; ' cursor.execute(sqlstr) alldata = cursor.fetchall() if alldata: for rec in alldata: if int(rec[0]) not in data_dict.keys(): data_dict[int(rec[0])] = dict() data_dict[int(rec[0])][rec[1]] = rec[3] for key in data_dict.keys(): if X_first[0] not in data_dict.keys(): data_dict[key]['trans'] = Y_trans.setdefault(key,key) res_dict={'data_dict':data_dict,'X_list':X_list,'Y_list':Y_list,'head_name':head_name,'X_trans':X_trans,'default_value':default_value} # print(res_dict) res_dict['note'] = "*起始日期-终止日期的各VIP段的钻石消耗次数*" return res_dict
def buy_oil_times(input_dict): data_dict = dict() for y in Y_list: data_dict[y] = dict() head_name = "VIP等级购买原油次数" easy_mysql = EasyMysql.EasyMysql() is_legal_input(input_dict) date_list = input_dict['date_list'] date_list = sorted(date_list) if len(date_list) == 0: msg = 'Not date selected, len(date_list)' + str(len(date_list)) raise RuntimeError(msg) zoneid_list = input_dict['zone_list'] channel_list = input_dict['channel_list'] cursor = input_dict['cursor'] sql_where = ' where date in(' + easy_mysql.sql_value_str(date_list) + ')' if len(zoneid_list) > 0: sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str( zoneid_list) + ') ' if len(channel_list) > 0: sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str( channel_list) + ') ' sql_str = 'select mvip,count(uid) from(select uid,max(level) as mlevel,max(vip_level) as mvip from user_active ' + sql_where + ' group by uid) as a group by mvip;' cursor.execute(sql_str) all_data = cursor.fetchall() if all_data: for rec in all_data: vip = int(rec[0]) user = int(rec[1]) data_dict['UserCount'][vip] = user sql_str = 'select mvip,count(uid),sum(ttimes),sum(tcost) from(select uid,max(viplevel) as mvip,sum(times) as ttimes,sum(cash+diamond) as tcost from OilBuy ' + sql_where + ' group by uid) as a group by mvip;' cursor.execute(sql_str) all_data = cursor.fetchall() if all_data: for rec in all_data: data_dict[Y_list[1]][rec[0]] = rec[2] for key in data_dict.keys(): if X_first[0] not in data_dict.keys(): data_dict[key]['trans'] = Y_trans[key] res_dict = { 'data_dict': data_dict, 'X_list': X_list, 'Y_list': Y_list, 'head_name': head_name, 'X_trans': X_trans, 'default_value': default_value } res_dict["note"] = "*起始日期-终止日期的各VIP段的原油购买次数*" # print(res_dict) return res_dict
def everyday_active(input_dict, *flag): data_dict = dict() for y in Y_list: data_dict[y] = dict() head_name = "每日活跃" easy_mysql = EasyMysql.EasyMysql() is_legal_input(input_dict) date_list = input_dict['date_list'] date_list = sorted(date_list) if len(date_list) == 0: msg = 'Not date selected, len(date_list)' + str(len(date_list)) raise RuntimeError(msg) zoneid_list = input_dict['zone_list'] channel_list = input_dict['channel_list'] cursor = input_dict['cursor'] sql_where = ' where date in (' + easy_mysql.sql_value_str(date_list) + ') ' if len(zoneid_list) > 0: sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str( zoneid_list) + ') ' if len(channel_list) > 0: sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str( channel_list) + ') ' #select date,viplevel,sum(num) from DailyDiscount where rechargeType=1 group by date,viplevel; # if len(flag)>0: sqlstr = 'select date,vip_level,count(uid) from user_active ' + sql_where + ' group by date,vip_level;' print(sqlstr) cursor.execute(sqlstr) alldata = cursor.fetchall() if alldata: for rec in alldata: data_dict[str(rec[0])][rec[1]] = rec[2] for key in data_dict.keys(): data_dict[key]['trans'] = Y_trans[key] res_dict = { 'data_dict': data_dict, 'X_list': X_list, 'Y_list': Y_list, 'head_name': head_name, 'X_trans': X_trans, 'default_value': default_value } # print(res_dict) res_dict['note'] = "每日VIP活跃" return res_dict
def vip_level(input_dict): data_dict = dict() for y in Y_list: data_dict[y] = dict() head_name = "VIP等级分布" easy_mysql = EasyMysql.EasyMysql() is_legal_input(input_dict) date_list = [max(input_dict['date_list'])] date_list = sorted(date_list) if len(date_list) == 0: msg = 'Not date selected, len(date_list)' + str(len(date_list)) raise RuntimeError(msg) zoneid_list = input_dict['zone_list'] channel_list = input_dict['channel_list'] cursor = input_dict['cursor'] sql_where = ' where date <=' + easy_mysql.sql_value_str(date_list) if len(zoneid_list) > 0: sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str(zoneid_list) + ') ' if len(channel_list) > 0: sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str(channel_list) + ') ' sql_str = 'select mvip,count(uid) from(select uid,max(level) as mlevel,max(vip_level) as mvip from user_active '+sql_where+' group by uid) as a group by mvip;' cursor.execute(sql_str) all_data = cursor.fetchall() if all_data: for rec in all_data: vip = int(rec[0]) user = int(rec[1]) data_dict['UserCount'][vip] = user sql_str = 'select mlevel,mvip,count(uid) from(select uid,max(level) as mlevel,max(vip_level) as mvip from user_active '+sql_where+' group by uid) as a group by mlevel,mvip;' cursor.execute(sql_str) all_data = cursor.fetchall() if all_data: for rec in all_data: data_dict[int(rec[0])][rec[1]] = rec[2] # for key in data_dict.keys(): # if X_first[0] not in data_dict.keys(): # pass # # data_dict[key]['trans'] = Y_trans[key] res_dict={'data_dict':data_dict,'X_list':X_list,'Y_list':Y_list,'head_name':head_name,'X_trans':X_trans,'default_value':default_value} # print(res_dict) res_dict["note"] = "*从开服-截止终止日期的各VIP段的等级分布*" return res_dict
def get_item_buy(input_dict): global file_name is_legal_input(input_dict) data_dict = dict() # where clause easy_mysql = EasyMysql.EasyMysql() is_legal_input(input_dict) date_list = input_dict['date_list'] date_list = sorted(date_list) if len(date_list) == 0: msg = 'Not date selected, len(date_list)' + str(len(date_list)) raise RuntimeError(msg) date_compute_str = easy_mysql.sql_value_str(date_list) # automatically compute the last date zoneid_list = input_dict['zone_list'] channel_list = input_dict['channel_list'] cursor = input_dict['cursor'] sql_where = ' where date in (' + date_compute_str + ') ' if len(zoneid_list) > 0: sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str(zoneid_list) + ') ' if len(channel_list) > 0: sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str(channel_list) + ') ' # sql item buying user_buy = dict() vip_list = list() item_list = list() cmd = 'select viplevel, itemid, sum(buy_times) from (select uid,itemid,(select max(vip_level) from user_active ' + sql_where + ' and uid=a.uid ) as viplevel,count(uid) as buy_times from pay_syn_day_extend as a ' + sql_where + ' group by uid,itemid)a group by viplevel,itemid' print(cmd) if cursor == None: msg = 'Cursor is None. Return empty.' return None cursor.execute(cmd) all_data = cursor.fetchall() if all_data: for rec in all_data: vip = str(rec[0]) item_id = str(rec[1]) uid_count = int(rec[2]) if vip not in vip_list: vip_list.append(vip) if item_id not in item_list: item_list.append(item_id) if item_id not in user_buy.keys(): user_buy[item_id] = dict() user_buy[item_id][vip] = uid_count vip_list = sorted(vip_list, key=lambda x: int(x)) item_list = sorted(item_list, key=lambda x: int(x)) # shop table shop_ref_dict = __read_table(file_name) # compute data dict for item in user_buy.keys(): user_buy[item]['trans'] = shop_ref_dict.setdefault(item, dict()).setdefault('trans', '') user_buy[item]['value'] = int(shop_ref_dict.setdefault(item, dict()).setdefault('value', '0')) # compute other elements y_list = item_list x_list = ['trans', 'value'] + vip_list x_trans = dict() x_trans['trans'] = '翻译' x_trans['value'] = '价值' for vip in vip_list: x_trans[vip] = 'vip' + str(vip) # load res res_data_raw = dict() res_data_raw['data_dict'] = user_buy res_data_raw['X_list'] = x_list res_data_raw['Y_list'] = y_list res_data_raw['X_trans'] = x_trans res_data_raw['default_value'] = 0 res_data_raw['head_name'] = '各VIP档商品购买笔数' res_data_raw['note'] = '*取一段时间的各VIP档购买各货品的笔数合计(VIP取时间段内单位UID的最大VIP, 按活跃表计算),翻译和价值是根据配表的,如果发现翻译或价值问题,请提示我更新配表,商品ID是实际的商品ID' return res_data_raw
def get_module_attend_rate(input_dict): global file_name is_legal_input(input_dict) data_dict = dict() data_dict_ratio = dict() # where clause easy_mysql = EasyMysql.EasyMysql() is_legal_input(input_dict) date_list = input_dict['date_list'] date_list = sorted(date_list) if len(date_list) == 0: msg = 'Not date selected, len(date_list)' + str(len(date_list)) raise RuntimeError(msg) date_compute_str = easy_mysql.sql_value_str( date_list) # automatically compute the last date zoneid_list = input_dict['zone_list'] channel_list = input_dict['channel_list'] cursor = input_dict['cursor'] sql_where = ' where date in (' + date_compute_str + ') ' if len(zoneid_list) > 0: sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str( zoneid_list) + ') ' if len(channel_list) > 0: sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str( channel_list) + ') ' # sql check dau user_active_dict = dict() cmd = 'select date,level,count(uid) from user_active ' + sql_where + ' group by date,level' cursor.execute(cmd) all_data = cursor.fetchall() if all_data: for rec in all_data: date = str(rec[0]) level = int(rec[1]) uid_count = int(rec[2]) if date not in user_active_dict.keys(): user_active_dict[date] = dict() user_active_dict[date][level] = uid_count # sql check attending user_attend = dict() cmd = 'select date,keyword,sum(countuid) from behavior_template ' + sql_where + ' group by date,keyword' cursor.execute(cmd) all_data = cursor.fetchall() if all_data: for rec in all_data: date = str(rec[0]) keyword = str(rec[1]) uid_count = int(rec[2]) if date not in user_attend.keys(): user_attend[date] = dict() user_attend[date][keyword] = uid_count # attending table attend_ref_dict = __read_module_table(file_name) key_list = attend_ref_dict.keys() # compute data dict for date in date_list: if date not in data_dict.keys(): data_dict[date] = dict() if date not in data_dict_ratio.keys(): data_dict_ratio[date] = dict() user_active = __get_above_users(user_active_dict, 0, date) data_dict[date]['UserActive'] = user_active data_dict_ratio[date]['UserActive'] = user_active for key in key_list: if date in user_attend.keys(): attend_user = user_attend[date].setdefault(key, 0) data_dict[date][key] = attend_user refer_level = int(attend_ref_dict[key]['level']) refer_user = __get_above_users(user_active_dict, refer_level, date) if refer_user > 0: data_dict_ratio[date][key] = round( float(attend_user) / refer_user, 2) y_list = date_list x_list = ['UserActive'] + key_list x_trans = dict() x_trans['UserActive'] = '活跃用户' for key in key_list: x_trans[key] = attend_ref_dict[key]['trans'] # load res res_data_raw = dict() res_data_ratio = dict() res_data_raw['data_dict'] = data_dict res_data_raw['X_list'] = x_list res_data_raw['Y_list'] = y_list res_data_raw['X_trans'] = x_trans res_data_raw['default_value'] = 0 res_data_raw['head_name'] = '活动参与度-参与人数' res_data_raw['note'] = '*各活动的实际参与人数' res_data_ratio['data_dict'] = data_dict_ratio res_data_ratio['X_list'] = x_list res_data_ratio['Y_list'] = y_list res_data_ratio['X_trans'] = x_trans res_data_ratio['default_value'] = 0 res_data_ratio['head_name'] = '活动参与度-比例' res_data_ratio['note'] = '*各活动的参与比例,注意分母是符合条件的用户,即活跃且等级达到指定等级的用户' return [res_data_raw, res_data_ratio]
def shop_exchange(input_dict, *flag): Y_list = [] Y_trans = dict() data_dict = dict() head_name = "商店" + str(flag[0]) easy_mysql = EasyMysql.EasyMysql() is_legal_input(input_dict) date_list = [max(input_dict['date_list'])] date_list = sorted(date_list) if len(date_list) == 0: msg = 'Not date selected, len(date_list)' + str(len(date_list)) raise RuntimeError(msg) zoneid_list = input_dict['zone_list'] channel_list = input_dict['channel_list'] cursor = input_dict['cursor'] sql_where = ' where date <=' + easy_mysql.sql_value_str(date_list) if len(zoneid_list) > 0: sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str( zoneid_list) + ') ' if len(channel_list) > 0: sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str( channel_list) + ') ' if len(flag) > 0: sqlstr = 'select mvip,itemid,sum(times) from(select uid,max(viplevel) as mvip from shopexchange' + sql_where + ' and shopid=' + str( flag[0] ) + ' group by uid) as a left join (select uid,itemid,sum(times) as times from shopexchange' + sql_where + ' and shopid=' + str( flag[0] ) + ' group by uid,itemid) as b on a.uid=b.uid group by mvip,itemid;' # print(sqlstr) cursor.execute(sqlstr) alldata = cursor.fetchall() if alldata: for rec in alldata: Y_list.append(str(rec[1])) Y_list = sorted(list(set(Y_list))) for y in Y_list: data_dict[y] = dict() if alldata: for rec in alldata: data_dict[str(rec[1])][int(rec[0])] = rec[2] Y_trans_origin = ReadTable.ReadTable( "./conf/item.txt").read_table_file_coupled([1, 1]) for i in Y_list: Y_trans[i] = Y_trans_origin.setdefault(i, dict()).setdefault( "游戏内名称", "-") for key in data_dict.keys(): data_dict[key]['trans'] = Y_trans[key] res_dict = { 'data_dict': data_dict, 'X_list': X_list, 'Y_list': Y_list, 'head_name': head_name, 'X_trans': X_trans, 'default_value': default_value } # print(res_dict) res_dict['note'] = "开始-终止日的" + str(flag[0]) + "商城购买次数" return res_dict
def get_ship_info(input_dict): data_dict = dict() # prepare sql easy_mysql = EasyMysql.EasyMysql() is_legal_input(input_dict) date_list = input_dict['date_list'] date_list = sorted(date_list) if len(date_list) == 0: msg = 'Not date selected, len(date_list)' + str(len(date_list)) raise RuntimeError(msg) date_compute = date_list[len(date_list) - 1] # automatically compute the last date zoneid_list = input_dict['zone_list'] channel_list = input_dict['channel_list'] cursor = input_dict['cursor'] sql_where = ' where date=' + easy_mysql.sql_value_str([date_compute]) if len(zoneid_list) > 0: sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str( zoneid_list) + ') ' if len(channel_list) > 0: sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str( channel_list) + ') ' # ship_dict ship_dict = __read_ship_table('ship_info_korea.txt') for ship_id in ship_dict.keys(): if ship_id not in data_dict.keys(): data_dict[ship_id] = dict() if 'ShipName' not in data_dict[ship_id].keys(): data_dict[ship_id]['ShipName'] = ship_dict.setdefault( ship_id, dict()).setdefault('ship_name', ship_id) if 'ShipRank' not in data_dict[ship_id].keys(): data_dict[ship_id]['ShipRank'] = ship_dict.setdefault( ship_id, dict()).setdefault('ship_rank', ship_id) # Y & X Y_list = ['UserCount'] for key in ship_dict.keys(): Y_list.append(key) Y_trans = {'UserCount': '用户数量'} X_list = ['ShipName', 'ShipRank', 'TotalCount'] # + sorted(vip_list) X_trans = { 'ShipName': '船名称', 'ShipRank': '评级', 'TotalCount': '用户总量' } # trans of vip # --- vip active vip_dict = dict() vip_list = list() total_user = 0 sql_cmd_active = 'select vip_level, count(distinct uid) from user_active' + sql_where + ' group by vip_level' cursor.execute(sql_cmd_active) all_data = cursor.fetchall() if all_data: for rec in all_data: vip = int(rec[0]) user = int(rec[1]) total_user += user if vip not in vip_list: vip_list.append(vip) if 'UserCount' not in data_dict.keys(): data_dict['UserCount'] = dict() data_dict['UserCount'][vip] = user data_dict['UserCount']['TotalCount'] = total_user data_dict['UserCount']['ShipName'] = '-' data_dict['UserCount']['ShipRank'] = '-' vip_list = sorted(vip_list) X_list = X_list + vip_list for vip in vip_list: X_trans[vip] = 'VIP' + str(vip) # --- ship info sql_cmd_ship = 'select viplevel,shipid,sum(countuid) from ShipInfo ' + sql_where + ' and shipid>0 and shipstar>2 and shipgrade>2 group by viplevel,shipid' cursor.execute(sql_cmd_ship) all_data = cursor.fetchall() if all_data: for rec in all_data: vip = int(rec[0]) ship_id = str(rec[1]) user = int(rec[2]) if ship_id not in data_dict.keys(): data_dict[ship_id] = dict() data_dict[ship_id][vip] = user data_dict[ship_id]['TotalCount'] = data_dict[ship_id].setdefault( 'TotalCount', 0) + user # -- compile return value res_dict = dict() res_dict['data_dict'] = data_dict res_dict['X_list'] = X_list res_dict['Y_list'] = Y_list res_dict['X_trans'] = X_trans res_dict['Y_trans'] = Y_trans res_dict['default_value'] = 0 res_dict['head_name'] = '用户阵容' res_dict['note'] = '*取最后计算日的,各VIP用户的战役上阵用阵容,仅3星且3阶以上船被统计了' return res_dict
def get_module_time_cost(input_dict): global module_list global module_trans easy_mysql = EasyMysql.EasyMysql() is_legal_input(input_dict) date_list = input_dict['date_list'] date_list = sorted(date_list) if len(date_list) == 0: msg = 'Not date selected, len(date_list)' + str(len(date_list)) raise RuntimeError(msg) date_compute = date_list[len(date_list) - 1] # automatically compute the last date zoneid_list = input_dict['zone_list'] channel_list = input_dict['channel_list'] cursor = input_dict['cursor'] sql_where = ' where date=' + easy_mysql.sql_value_str([date_compute]) if len(zoneid_list) > 0: sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str( zoneid_list) + ') ' if len(channel_list) > 0: sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str( channel_list) + ') ' # vip active vip_dict = dict() vip_list = list() sql_cmd = 'select vip_level, count(distinct uid) from user_active' + sql_where + ' group by vip_level' cursor.execute(sql_cmd) all_data = cursor.fetchall() if all_data: for rec in all_data: vip = int(rec[0]) user = int(rec[1]) if vip not in vip_list: vip_list.append(vip) if vip not in vip_dict.keys(): vip_dict[vip] = user vip_list = sorted(vip_list) # module time module_dict = dict() sql_cmd = 'select ui_type,vip_level,sum(time)/(60*count(uid)) from user_exit_ui_time' + sql_where + ' group by ui_type,vip_level' cursor.execute(sql_cmd) all_data = cursor.fetchall() if all_data: for rec in all_data: ui_type = str(rec[0]) vip = int(rec[1]) data = round(float(rec[2]), 1) if ui_type not in module_dict.keys(): module_dict[ui_type] = dict() if vip not in module_dict[ui_type].keys(): module_dict[ui_type][vip] = data # add user_count module_dict['UserCount'] = dict() for vip in vip_list: module_dict['UserCount'][vip] = vip_dict[vip] # add trans for module_id in module_list: if module_id not in module_dict.keys(): module_dict[module_id] = dict() module_dict[module_id]['trans'] = module_trans[module_id] module_dict['UserCount']['trans'] = '用户数量' # X_trans X_trans = dict() X_trans['trans'] = '翻译' for vip in vip_list: X_trans[vip] = 'VIP' + str(vip) # compile return value res_dict = dict() res_dict['data_dict'] = module_dict res_dict['X_list'] = ['trans'] + vip_list res_dict['Y_list'] = ['UserCount'] + module_list res_dict['X_trans'] = X_trans res_dict['default_value'] = 0 res_dict['head_name'] = '模块时长' res_dict['note'] = '*取最后计算日的,各VIP用户各项目人均游戏时长(单位:分钟)' return res_dict