davinci报表使用的sql参考

 

第一张表：

SELECT T.day as "日期", 

COUNT((

  CASE

    WHEN (fun_code='1011010003') THEN

  fun_code

  ELSE

   NULL

  end

)) as "总安装量",

COUNT((

  CASE

    WHEN (fun_code='1011010001') THEN

  fun_code

  ELSE

   NULL

  end

)) as "总注册量",

COUNT((

  CASE

    WHEN (is_month='1') THEN

  is_month

  ELSE

   NULL

  end

)) as "月活账号数量",

COUNT((

  CASE

    WHEN (is_week='1') THEN

  is_week

  ELSE

   NULL

  end

)) as "周活账号数量"

FROM  

(SELECT m.*, s.event_property_code,s.event_property_value FROM cms.daily_new_clientrpt_master m LEFT JOIN cms.daily_new_clientrpt_slave s on m.event_id= s.event_id ) T where DATE_SUB(NOW( ), INTERVAL 30 DAY) <= server_time GROUP BY "日期" 

 

 

 

 

 

 

 

第二张表：

SELECT 

T.dept_one as "用户对象",

AVG((

  CASE

     WHEN  (server_time =toDate($g$)) THEN

   member_count

  ELSE

   NULL

    end

 )) as "总人数",

COUNT((

  CASE

    WHEN (fun_code='1011010001') THEN

  fun_code

  ELSE

   NULL

  end

)) as "总注册量",

COUNT((

  CASE

    WHEN (fun_code='1011010001' and server_time =toDate($g$)) THEN

  fun_code

  ELSE

   NULL

  end

)) as "当日注册量",

COUNT((

  CASE

    WHEN (is_month='1') THEN

  is_month

  ELSE

   NULL

  end

)) as "月活账号数量",

COUNT((

  CASE

    WHEN (is_week='1') THEN

  is_week

  ELSE

   NULL

  end

)) as "周活账号数量"

 

FROM  

(SELECT m.*, s.event_property_code,s.event_property_value FROM cms.daily_new_clientrpt_master m LEFT JOIN cms.daily_new_clientrpt_slave s on m.event_id= s.event_id ) T    GROUP BY "用户对象" ;

 

 

 

 

 

第三张表：

 

SELECT 

T.fun_name as "模块名",

SUM(click_records) as "点击数量",

SUM(stay_time) as "使用时长"

FROM  

(SELECT m.*, s.event_property_code,s.event_property_value FROM cms.daily_new_clientrpt_master m LEFT JOIN cms.daily_new_clientrpt_slave s on m.event_id= s.event_id ) T   where  server_time  = toDate($g$) GROUP BY "模块名";

 

 

 

 

SELECT 

T.fun_name as "模块名",

SUM(click_records) as "点击数量",

SUM(stay_time)/1000 as "使用时长(单位：秒)"

FROM  

(SELECT m.*, s.event_property_code,s.event_property_value FROM cms.daily_new_clientrpt_master m LEFT JOIN cms.daily_new_clientrpt_slave s on m.event_id= s.event_id ) T   where  day  = today() GROUP BY "模块名";