mafia1查询

最新推荐文章于 2024-08-17 21:14:04 发布

luoganttcc

最新推荐文章于 2024-08-17 21:14:04 发布

阅读量296

点赞数

分类专栏： PYTHON

本文链接：https://blog.csdn.net/luoganttcc/article/details/103908282

版权

PYTHON 专栏收录该内容

161 篇文章

订阅专栏

SELECT * FROM `heidao-market.mafia1_dmd.item_info` WHERE DATE(timestamp) > "2010-01-15"

ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY timestamp DESC) AS rn

import pandas as pd 
import json
qqq="""SELECT 
      * 
    FROM 
      `heidao-market.mafia1_ods.game_log_status_snapshot` 
    WHERE 
      DATE(timestamp) = "2020-01-09" 
      AND operation='buy_giftbag' 
      AND MOD(player_id, 33)=29
      order by timestamp desc  
    LIMIT 
      1000"""  
      
dfg= pd.read_gbq(qqq, dialect='standard')   

d1=json.loads(dfg.status[0])

print(d1['pdt_sorted_ids'])

print(d1['ori_sorted_ids'])

import pandas as pd    
sql="""    
   with rank as(   
   SELECT group_id, SUM(pay_dollar) as pay_dollars, count(*) as count FROM   
   (select mod(player_id, 33) as group_id,  pay_dollar FROM `heidao-market.mafia1_pf.v_paid_order`   
   WHERE pay_time >='2020-01-09 06:30:00' and pay_time <='2020-12-02 05:00:00' and player_id not in (select player_id from mafia1.v_internal_player))   
   # and player_id in (select player_id FROM mafia1.create_player where timestamp >='2019-07-01'))   
   #where pay_dollar >= 2 and pay_dollar <= 7   
   group by group_id   
   order by pay_dollars)   
      
   select group_id, pay_dollars/total_dollars as ratio, count, pay_dollars, pay_dollars/count as mean FROM rank, (select SUM(pay_dollars) as total_dollars FROM rank)   
   Order by ratio   
      
       
   """    
               #import pandas as pd    
  
dfg= pd.read_gbq(sql, dialect='standard')   
print(dfg)

import pandas as pd   
qqq="""  
  with rank as(     
  SELECT (CASE WHEN group_id < 90 then 'A' else 'B' END) as group_id, SUM(pay_dollar) as pay_dollars, count(*) as count FROM     
  (select  MOD(CAST(CONCAT('0x', SUBSTR(TO_HEX(MD5(CONCAT('so69p', CAST(player_id AS STRING)))),0, 8)) AS INT64), 100) as group_id,  pay_dollar FROM `heidao-market.mafia1_pf.v_paid_order`     
  WHERE pay_time >='2020-01-18 06:31:00' and pay_time <='2020-12-20 05:00:00' and player_id not in (select player_id from mafia1_pf.v_internal_player))     
  # and player_id in (select player_id FROM mafia1.create_player where timestamp >='2019-07-01'))     
  #where pay_dollar >= 2 and pay_dollar <= 7     
  group by group_id     
  order by pay_dollars)     
   
  select group_id, pay_dollars/total_dollars as ratio, count, pay_dollars, pay_dollars/count as mean FROM rank, (select SUM(pay_dollars) as total_dollars FROM rank)     
  Order by ratio    
   """    
   
df= pd.read_gbq(qqq, dialect='standard')     
print(df)

with dd as (    
select player_id ,giftbag_id , MOD(CAST(CONCAT('0x', SUBSTR(TO_HEX(MD5(CONCAT('so69p', CAST(player_id AS STRING)))),0, 8)) AS INT64), 100) as group_id,  pay_dollar FROM `heidao-market.mafia1_pf.v_paid_order`     
  WHERE pay_time >='2020-01-20 13:30:00' and pay_time <='2020-12-20 05:00:00' and player_id not in (select player_id from mafia1_pf.v_internal_player) ) 
select * from dd where group_id  >90