工作常用sql

提取某段时间依然还在活跃的用户

with ps as (SELECT  player_id as player_id   FROM `cloud-test-199409.lediw7.R10` )
SELECT  player_id FROM `heidao-market.mafia.login` WHERE DATE(action_time) >= "2019-08-01" 
    and  DATE(action_time) < "2019-08-8" 
    and player_id in (select player_id from ps)
    group by player_id

聚合login数据

with log as (SELECT action_time,DATE(action_time) as dt,lev ,kingdom_id ,login_time_length,player_id,platform_id
                       FROM `heidao-market.mafia.login`
                       WHERE  DATE(action_time) < "2019-08-01" 
                       and player_id in (SELECT  player_id as player_id   FROM `cloud-test-199409.lediw7.R10`)
                       and action=-1)
        
SELECT
      SUM(login_time_length) AS sum_login,player_id,dt
        
       FROM  log
       GROUP BY player_id,dt

聚合arm 数据

       
       WITH ps AS (SELECT  DATE(created_at )AS dt,  army_num,  mid, army_id,   left_num, kingdom_id,created_at, player_id,platform_id
                        FROM   `heidao-market.mafia.army`
                        
                        where 　 DATE(created_at) <"2019-08-21" 
                        
                        and player_id in (select player_id from `cloud-test-199409.lediw7.R10`)
                        
                        )
        SELECT
              SUM(army_num) AS sum_change,
              AVG(left_num ) AS m_left,
              SUM(army_num)/AVG(left_num +1.1234567) AS ratio,
              COUNTIF( army_num<=0) AS ct_m,
              COUNTIF(army_num>0) AS ct_p,
              COUNTIF( army_num<=0)/( COUNTIF( army_num<=0)+COUNTIF(army_num>0))  as m_p,
              
              max( army_num)  as max_change,
              min( army_num)  as min_change,
              max( army_num)/( min( army_num)+1.1234567) as mm_ratio,
              
              player_id,dt
              FROM  ps
              GROUP BY player_id,dt

聚合offer_perchase

--SELECT * FROM `heidao-market.mafia.offer_purchase` WHERE DATE(offer_time) = "2019-08-20" LIMIT 1000
       
WITH ps AS (SELECT  DATE(offer_time )AS dt,  pay_amount ,  exchange ,   player_id,platform_id
                        FROM   `heidao-market.mafia.offer_purchase`
                        
                        where 　 DATE(offer_time ) = "2019-08-01" 
                        
                        and player_id in (select player_id from `cloud-test-199409.ledi7.R5`)
                        
                        )
SELECT
              sum(pay_amount* exchange) as sum_money  ,
              
              player_id,dt
              FROM  ps
              GROUP BY player_id,dt

简单join

SELECT ins.uid ,ins.player_id  from  `heidao-market.mafia.create_player`  as  ins
JOIN   `heidao-market.mafia.internal_user`  as  cp
ON ins.uid =cp.uid

三个表join 用with 嵌套

with  A as (SELECT kingdom_id,player_id,created_time,uid FROM `heidao-market.mafia.create_player` WHERE player_id in 
           (SELECT player_id FROM `cloud-test-199409.lediw7.R10`) ),
           
      B as (select device_id, uid ,attribute_lang	 from `mafia.registration` ),
      
    
      myevent as (  select A.uid,B.device_id as de_id , B.attribute_lang as language,
                  A.created_time as created_time, A.kingdom_id,A.player_id from B  inner join  A on B.uid=A.uid )
      

select 
--distinct C.time as device_time,
date(myevent.created_time)  as created_date,
myevent.player_id,
myevent.language ,
C.country_code,
C.source,
C.device_type,
C.os_type,
C.system_lang,kingdom_id 
from `mafia.device_registration` as C


INNER JOIN  myevent on C.device_id=myevent.de_id

聚合cha

with ps as (SELECT  *  FROM `heidao-market.mafia1.chat` WHERE Date(timestamp)>='2019-11-28'),
     gs as (SELECT   count(message) as cntmsge,player_id ,message,length(ps.message ) as lmm  from ps
            group by message ,player_id )
    
    
select message ,player_id ,cntmsge  from gs where cntmsge >30 and lmm >25 order by cntmsge desc
    

SELECT * FROM `cloud-test-199409.ledi7.message_count`   where length(message  )>20  and cntmsge >5
order by cntmsge desc