sql sum嵌套查询+ group by

嵌套查询经典用法

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-14"
            )
SELECT
  SUM(army_num) AS sum_change,
  AVG(left_num ) AS m_left,
  SUM(army_num)/AVG(left_num +1 ) 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)) as mm_ratio,
  
  player_id,dt
  FROM  ps
  GROUP BY player_id,dt

提取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-07-01" 　and  DATE(action_time) < "2019-07-0２" 

               and action=-1)
SELECT
  SUM(login_time_length) AS sum_login,player_id,dt

  FROM  log
  GROUP BY player_id,dt

SELECT 

player_id , sum_time  
from (SELECT player_id ,sum(login_time_length)  as sum_time FROM `heidao-market.mafia.login`WHERE DATE(action_time) = "2019-08-10"  and action=-1 group by player_id ) 

where sum_time>3600

with 嵌套

with ps as (SELECT  player_id FROM `cloud-test-199409.ledi.big_player` )
SELECT * FROM `heidao-market.mafia.army` WHERE DATE(created_at) >= "2018-08-01" and player_id in (select player_id from ps)

聚合army表

       
       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-01" 
                        
                        and player_id in (select player_id from `cloud-test-199409.ledi.R5`)
                        
                        )
        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