测试

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-03-16 09:38:00' and pay_time <='2020-12-15 05:40:00' and player_id not in (select player_id from mafia1_pf.v_internal_player))                    
  # and player_id in (select playe_id FROM mafia1.create_player where timestamp >='2019-07-01'))                    
  #wherepay_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 

  with rank as(                    
  SELECT (CASE WHEN ((group_id >= 80)and(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-03-16 09:38:00' and pay_time <='2020-12-15 05:40:00' and player_id not in (select player_id from mafia1_pf.v_internal_player))                    
  # and player_id in (select playe_id FROM mafia1.create_player where timestamp >='2019-07-01'))                    
  #wherepay_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