abtest

# 主入口推荐 开始时间2020-10-29 16:15:48    keys 是65b7i
with
create_player as(
select player_id, max(timestamp) as timestamp from `mafia1_ods.game_log_create_player`
where timestamp >= '2018-01-01'
group by player_id),

rank as(     
 SELECT CAST(FLOOR(group_id/5) as int64) as group_id, SUM(pay_dollar) as pay_dollars, count(*) as count FROM     
    (select  MOD(CAST(CONCAT('0x', SUBSTR(TO_HEX(MD5(CONCAT('65b7i', CAST(player_id AS STRING)))),0, 8)) AS INT64), 100) as group_id,  pay_dollar as pay_dollar FROM `heidao-market.mafia1_pf.v_paid_order`     
WHERE pay_time >='2020-10-30 08:50:48' and pay_time < '2020-12-21 09:00:00' and player_id not in (select player_id from mafia1_pf.v_internal_player)
#last_timestamp = None
#and player_id in (select player_id from create_player where timestamp between '2020-06-12 0:00:00' and  '2020-07-01')
 )        
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 desc
order by group_id desc

# 主入口推荐 开始时间2020-10-29 16:16:02    keys 是5rv6e

with
create_player as(
select player_id, max(timestamp) as timestamp from `mafia1_ods.game_log_create_player`
where timestamp >= '2018-01-01'
group by player_id),

rank as(     
 SELECT CAST(FLOOR(group_id/5) as int64) as group_id, SUM(pay_dollar) as pay_dollars, count(*) as count FROM     
    (select  MOD(CAST(CONCAT('0x', SUBSTR(TO_HEX(MD5(CONCAT('5rv6e', CAST(player_id AS STRING)))),0, 8)) AS INT64), 100) as group_id,  pay_dollar as pay_dollar FROM `heidao-market.mafia1_pf.v_paid_order`     
WHERE pay_time >='2020-10-30 08:50:48' and pay_time < '2020-12-21 09:00:00' and player_id not in (select player_id from mafia1_pf.v_internal_player)
#last_timestamp = None
#and player_id in (select player_id from create_player where timestamp between '2020-06-12 0:00:00' and  '2020-07-01')
 )        
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 desc
order by group_id desc