from bq

最新推荐文章于 2025-04-11 11:37:37 发布

原创最新推荐文章于 2025-04-11 11:37:37 发布 · 339 阅读

0 ·

CC 4.0 BY-SA版权

推荐算法专栏收录该内容

24 篇文章

订阅专栏

本文详细介绍了一种使用SQL查询和Python脚本来处理大规模玩家数据的方法。通过具体案例，展示了如何从数据库中提取特定时间段内的玩家充值状态，并利用Python进行数据清洗与转换，最终将处理后的数据导出为CSV文件，便于进一步的数据分析与可视化。

SELECT
    player_id,
    TO_HEX(MD5(CONCAT('f4ju8', CAST(player_id AS STRING)))) AS md5_str,
    CAST(CONCAT('0x', SUBSTR(TO_HEX(MD5(CONCAT('fme7q', CAST(player_id AS STRING)))),0, 8)) AS INT64) AS hash_int,
    MOD(CAST(CONCAT('0x', SUBSTR(TO_HEX(MD5(CONCAT('fme7q', CAST(player_id AS STRING)))),0, 8)) AS INT64), 100) AS bucket_id
  FROM
    mafia1.create_player

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Thu Oct 31 15:46:08 2019

@author: lg
"""

import pandas as pd
import datetime
from dateutil.parser import parse
import time 

t1=time.time()
def now_date():
    now=datetime.datetime.now()
    nowd=now.strftime('%Y-%m-%d')
    return nowd

def before_time(dt,n):
    dt1=parse(dt)
    future_time = dt1- datetime.timedelta(days=n)
    fu = future_time.strftime('%Y-%m-%d')
    return fu


def train_data(start,end):
    query="""
    SELECT * FROM `heidao-market.mafia1.charge_status_2` WHERE DATE(timestamp) >= '{}'   and 
    
    DATE(timestamp) <='{}'
    
    order by timestamp desc
    
    limit 60000
    """
#    and DATE(action_time) ='{}'
    sql=query.format(start,end)

    dfg= pd.read_gbq(sql, dialect='standard')

    return dfg


end=now_date()
start= before_time(end,2)


df=train_data(start,end)
#df= pd.read_gbq(sql, dialect='standard')

#import pandas as pd
#
#df=pd.read_csv('data.csv')
c1= ['id',   'player_id','giftbag_id', 'status','created_at']

c2=['seq_id','player_id','giftbag_id', 'status', 'timestamp']

df1=df[c2]

df2=df1.values

df3=pd.DataFrame(df2,columns=c1)

df3.to_csv('data.csv',index=False)

print(time.time() -t1)

df = pd.read_csv(path)