test

alter table `daily_active_wide_table_2019_01` add column poundage BIGINT(20) default 0 not null after dragon_tiger
alter table `daily_active_wide_table_2019_01` add column current_value BIGINT(20) default 0 not null after poundage

 

 

INSERT INTO `dailyact_match_config` (`id`, `match_name`, `created_at`, `update_at`)
VALUES
(1, '西部牛仔', '2018-12-28 11:40:04', '2018-12-28 11:40:04'),
(2, '金鲨银鲨', '2018-12-28 11:40:02', '2018-12-28 11:40:02'),
(3, '德州扑克', '2018-12-28 11:39:56', '2018-12-28 11:39:56'),
(4, '大富豪', '2018-12-28 11:39:46', '2018-12-28 11:39:46'),
(5, '斗地主', '2018-12-28 11:39:37', '2018-12-28 11:39:37'),
(6, '捕鱼', '2018-12-28 11:39:31', '2018-12-28 11:39:31'),
(7, '大闹天宫', '2018-12-28 11:39:23', '2018-12-28 11:39:23'),
(8, '开心农场', '2018-12-28 11:39:15', '2018-12-28 11:39:15'),
(9, '水果机', '2018-12-28 11:39:03', '2018-12-28 11:39:03'),
(10, '奔驰宝马', '2018-12-28 11:39:01', '2018-12-28 11:39:01'),
(11, '拼十', '2018-12-28 11:38:53', '2018-12-28 11:38:53'),
(12, '万人拼十', '2018-12-28 11:38:32', '2018-12-28 11:38:32'),
(13, '十三张房费', '2018-12-28 11:38:27', '2018-12-28 11:38:27'),
(14, '跑得快', '2018-12-28 11:38:17', '2018-12-28 11:38:17'),
(100, '修改昵称', '2018-12-28 11:35:42', '2018-12-28 11:35:42'),
(101, '破产奖励', '2018-12-28 11:35:47', '2018-12-28 11:35:52'),
(102, '在线奖励', '2018-12-28 11:35:57', '2018-12-28 11:36:01'),
(103, '连续登录奖励', '2018-12-28 11:36:14', '2018-12-28 11:36:14'),
(104, '补单', '2018-12-28 11:36:23', '2018-12-28 11:36:23'),
(105, '任务奖励', '2018-12-28 11:36:31', '2018-12-28 11:36:31'),
(106, '注册赠送', '2018-12-28 11:36:38', '2018-12-28 11:36:38'),
(220, '道具商城', '2018-12-28 11:36:46', '2018-12-28 11:36:46'),
(221, '兑换商城', '2018-12-28 11:36:57', '2018-12-28 11:36:57'),
(223, '金币商城购买', '2018-12-28 11:37:05', '2018-12-28 11:37:05'),
(225, '钻石商城购买', '2018-12-28 11:37:14', '2018-12-28 11:37:14'),
(1500, '邮件提取', '2018-12-28 11:37:21', '2018-12-28 11:37:21'),
(1507, '捕鱼比赛', '2018-12-28 11:37:29', '2018-12-28 11:37:29'),
(1510, '地主比赛', '2018-12-28 11:37:36', '2018-12-28 11:37:36'),
(1801, '夺宝', '2018-12-28 11:37:55', '2018-12-28 11:37:55');

def save_wide_table_to_db(self, df):
    sql = list()
    sql.append("replace into %s (account_id,app_id,target_date,current_value,cow_boy,gold_shark,texas_poke,dfh,"
               "landlord,fish,xiyouji,farm,fruit,benz,pin_ten,million_pin_ten,thirteen_piece,run_fast,"
               "change_nickname,broken_up,online_reward,continue_login_reward,add_order,task_reward,"
               "register_reward,poundage,tool_shop, exchange_shop,gold_shop,diamond_shop,emall,fish_match,"
               "landlord_match,get_treasure,dragon_tiger,red_black) values "
               % self.table_name)
    if df.shape[0] <= 0:
        return
    for row_index, row in df.iterrows():
        sql.append("(%s,'%s','%s',%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,"
                   "%s,%s,%s,%s,%s,%s),"
                   % (row['account_id'], row['app_id'], row['target_date'], row['current_value'], row['cow_boy'],
                      row['gold_shark'], row['texas_poke'], row['dfh'], row['landlord'], row['fish'],
                      row['xiyouji'], row['farm'], row['fruit'], row['benz'], row['pin_ten'],
                      row['million_pin_ten'], row['thirteen_piece'], row['run_fast'], row['change_nickname'],
                      row['broken_up'], row['online_reward'], row['continue_login_reward'], row['add_order'],
                      row['task_reward'], row['register_reward'], row['poundage'], row['tool_shop'],
                      row['exchange_shop'], row['gold_shop'], row['diamond_shop'], row['emall'], row['fish_match'],
                      row['landlord_match'], row['get_treasure'], row['dragon_tiger'], row['red_black']))
    sql = "".join(sql)[:-1]
    self.mysql_con.excute(sql)

def build_total_gold_frame(self):
    table_name = "%s_%s_%02d" % ("zjh_user_gold_statistics_daily", self.current_date.year, self.current_date.month)
    sql1 = "select account_id,app_id,target_date,current_value" \
           ",sum(if(match_type=1,total_win-total_lose,0)) as cow_boy" \
           ",sum(if(match_type=2,total_win-total_lose,0)) as gold_shark" \
           ",sum(if(match_type=3,total_win-total_lose,0)) as texas_poke"\
           ",sum(if(match_type=4,total_win-total_lose,0)) as dfh"  \
           ",sum(if(match_type=5,total_win-total_lose,0)) as landlord"  \
           ",sum(if(match_type=6,total_win-total_lose,0)) as fish"  \
           ",sum(if(match_type=7,total_win-total_lose,0)) as xiyouji"  \
           ",sum(if(match_type=8,total_win-total_lose,0)) as farm"  \
           ",sum(if(match_type=9,total_win-total_lose,0)) as fruit"  \
           ",sum(if(match_type=10,total_win-total_lose,0)) as benz"  \
           ",sum(if(match_type=11,total_win-total_lose,0)) as pin_ten"  \
           ",sum(if(match_type=12,total_win-total_lose,0)) as million_pin_ten"  \
           ",sum(if(match_type=13,total_win-total_lose,0)) as thirteen_piece"  \
           ",sum(if(match_type=14,total_win-total_lose,0)) as run_fast" \
           ",sum(if(match_type=15,total_win-total_lose,0)) as dragon_tiger" \
           ",sum(if(match_type=16,total_win-total_lose,0)) as red_black" \
           ",sum(if(match_type=100,total_win-total_lose,0)) as change_nickname"  \
           ",sum(if(match_type=101,total_win-total_lose,0)) as broken_up"  \
           ",sum(if(match_type=102,total_win-total_lose,0)) as online_reward"  \
           ",sum(if(match_type=103,total_win-total_lose,0)) as continue_login_reward"  \
           ",sum(if(match_type=104,total_win-total_lose,0)) as add_order"  \
           ",sum(if(match_type=105,total_win-total_lose,0)) as task_reward"  \
           ",sum(if(match_type=106,total_win-total_lose,0)) as register_reward" \
           ",sum(if(match_type=151,total_win-total_lose,0)) as poundage" \
           ",sum(if(match_type=220,total_win-total_lose,0)) as tool_shop"  \
           ",sum(if(match_type=221,total_win-total_lose,0)) as exchange_shop"  \
           ",sum(if(match_type=223,total_win-total_lose,0)) as gold_shop"  \
           ",sum(if(match_type=225,total_win-total_lose,0)) as diamond_shop"  \
           ",sum(if(match_type=1500,total_win-total_lose,0)) as emall"  \
           ",sum(if(match_type=1507,total_win-total_lose,0)) as fish_match"  \
           ",sum(if(match_type=1510,total_win-total_lose,0)) as landlord_match"  \
           ",sum(if(match_type=1801,total_win-total_lose,0)) as get_treasure "  \
           "from %s where target_date='%s' group by account_id,app_id,target_date" % (table_name, self.current_date)
    columns = ['account_id', 'app_id', 'target_date', "current_value", "cow_boy", "gold_shark", 'texas_poke',
               'dfh', 'landlord', 'fish', 'xiyouji', 'farm', 'fruit', 'benz', 'pin_ten', 'million_pin_ten',
               'thirteen_piece', 'run_fast', 'dragon_tiger', 'red_black', 'change_nickname', 'broken_up',
               'online_reward', 'continue_login_reward', 'add_order', 'task_reward', 'register_reward', 'poundage',
               'tool_shop', 'exchange_shop', 'gold_shop', 'diamond_shop', 'emall', 'fish_match', 'landlord_match',
               'get_treasure']
    df1 = DataFrame(list(self.mysql_con.query(sql1)), columns=columns)
    return df1

转载于:https://www.cnblogs.com/niuniuc/p/10497685.html