yota

import pandas  as pd

sql="""
select pay_user_id ,player_id,game_server_id ,pay_amount,offer_time from mafia.offer_purchase where offer_time>'2019-08-07'
"""

df= pd.read_gbq(sql, dialect='standard', project_id='heidao-market')

def get_payment_bq(min_day, max_day):
    query = """
        SELECT player_id, pay_amount, exchange, offer_time
        FROM mafia.offer_purchase
        WHERE offer_time >= '{}' and offer_time < '{}'
    """
    query = query.format(min_day, max_day)
    payment = pd.read_gbq(query, dialect='standard', project_id='heidao-market')

    payment['pay_dollar'] = payment['pay_amount']*payment['exchange']
    payment = payment[['player_id', 'pay_dollar', 'offer_time']]
    payment = payment.rename({'offer_time':'pay_timestamp'}, axis = 1)
    return payment

RBgh0yV4Pixa5cI96bLt4xje1gCfAoCm

def get_login_sql(min_day, max_day):
    query = '''
       SELECT player_id, action, login_time_length, action_time FROM mafia.login
        WHERE action_time > '{}' and action_time < '{}'
    '''
    query = query.format(min_day, max_day)
    return query

def get_last_login_sql(min_day, max_day):
    query = '''
        SELECT player_id, MAX(action_time) as last_login_time
        FROM mafia.login
        WHERE action_time >= '{}' and action_time < '{}'
        GROUP BY player_id
        '''
    query = query.format(min_day, max_day)
    return query


def get_purchase_event_sql(min_day, max_day, days = 3):
    max_day_time = pd.to_datetime(max_day) + pd.Timedelta(days=days)
    max_day_str = max_day_time.strftime('%Y-%m-%d')
    create_player_sql = get_create_player_sql(min_day, max_day)
    purchase_sql = get_purchase_sql(min_day, max_day_str)
    
    query = '''
        with create_player as ({}),
            purchase as ({})
        SELECT * FROM(
            SELECT TIMESTAMP_DIFF(offer_time, created_time, HOUR) as delta_time, purchase.* FROM(
                create_player LEFT JOIN purchase
                ON create_player.player_id = purchase.player_id)
        )
        WHERE delta_time < {}*24
        ORDER BY player_id, delta_time
    '''
    query = query.format(create_player_sql, purchase_sql, days)
    return query

def get_login_feature_sql(min_day, max_day, days = 7):
    max_day_time = pd.to_datetime(max_day) + pd.Timedelta(days=days)
    max_day_str = max_day_time.strftime('%Y-%m-%d')
    create_player_sql = get_create_player_sql(min_day, max_day)
    login_sql = get_login_sql(min_day, max_day_str)
    
    query = '''
        with create_player as ({}),
            login as ({})
        SELECT player_id, COUNT(*) as login_count, 
            MAX(action_time) as end_login_time,
            AVG(login_time_length) as login_length_mean,
            MAX(login_time_length) as login_length_max
            FROM(
            SELECT login.*, TIMESTAMP_DIFF(action_time, created_time, HOUR) as delta_time FROM(
                create_player LEFT JOIN login
                ON create_player.player_id = login.player_id)
        )
        WHERE delta_time < {}*24
        GROUP BY player_id
    '''
    query = query.format(create_player_sql, login_sql, days)
    return query


def player_uniq_template(sql):
    query = '''
        WITH tb as ({})
        SELECT player_id FROM tb
        GROUP BY player_id
    '''
    return query.format(sql)

def filter_player_template(filter_player_sql, change_sql):
    query = '''
        WITH filter_player as ({}),
            change_tb as ({})
        SELECT change_tb.* FROM(
            filter_player LEFT JOIN change_tb
            ON filter_player.player_id = change_tb.player_id
        )
    '''
    return query.format(filter_player_sql, change_sql)

SELECT  action_time,lev,kingdom_id,login_time_length ,action,language,player_id ,platform_id  FROM `heidao-market.mafia.login` WHERE DATE(action_time) = "2019-08-09" and action=-1 LIMIT 1000

SELECT distinct playerID FROM `heidao-market.analysis.purchase_records`  where money_value_in_total>9999

rsync -avz --progress --partial --password-file=/etc/rsynd.pwd test.txt game@35.196.46.13::ftp/