综合性的环比报表sql

浅浅的花香味﹌ 2023-07-10 15:57 11阅读 0赞

10：

1，取整或者固定小数round(AVG(click\_records),0)

2，any(today())聚合函数避免聚合列结合排序就是分组序列第一个的值----------聚合函数的聚合字段，算的级别根据此字段在group by 中位置，越在前，聚合的维度粒度越大

3，用CAST做类型转化，一般百分比用float可以处理nan值，自动变为空，CAST(multiIf(isNull("昨日安装量"),1,Round(("今日安装量"-"昨日安装量")/"昨日安装量",4)),'Float32')

4，UNION ALL  用来累加结果集，需列名，列的类型一致  toInt64(multiIf(isNull("最大数量"),0,"最大数量"))

SELECT   
any(  
 today()  
) as "日期",   
COUNT((  
  CASE  
    WHEN (fun\_code='1011010012') THEN  
  fun\_code  
  ELSE  
   NULL  
  end  
))+668  as "累计安装量",  
COUNT((  
  CASE  
    WHEN (fun\_code='1011010012' and day=today()) THEN  
  fun\_code  
  ELSE  
   NULL  
  end  
)) as "今日安装量",  
COUNT((  
  CASE  
    WHEN (fun\_code='1011010012' and day=today()-1) THEN  
  fun\_code  
  ELSE  
   NULL  
  end  
)) as "昨日安装量",  
CAST(multiIf(isNull("昨日安装量"),1,Round(("今日安装量"-"昨日安装量")/"昨日安装量",4)),'Float32')  
 AS "安装量环比",  
COUNT((  
  CASE  
    WHEN (event\_code='101101000100016') THEN  
  event\_code  
  ELSE  
   NULL  
  end  
))+3495 as "累计注册量",  
COUNT((  
  CASE  
    WHEN (event\_code='101101000100016' and day=today()) THEN  
  event\_code  
  ELSE  
   NULL  
  end  
))as "今日注册量",  
COUNT((  
  CASE  
    WHEN (event\_code='101101000100016' and day=today()) THEN  
  event\_code  
  ELSE  
   NULL  
  end  
))as "昨日注册量",  
CAST(multiIf(isNull("昨日注册量"),1,Round(("今日注册量"-"昨日注册量")/"昨日注册量",4)),'Float32')  
 AS "注册量环比",  
COUNT(  
  DISTINCT(  
  CASE  
    WHEN (is\_month='1') THEN  
  name  
  ELSE  
   NULL  
  end  
)) as "月活账号数",  
COUNT(  
  DISTINCT(  
  CASE  
    WHEN (is\_week='1') THEN  
  name  
  ELSE  
   NULL  
  end  
)) as "周活账号数",  
round(AVG(click\_records),0) as "功能平均点击量",  
round(AVG(stay\_time)/60000,0) as "使用时长(单位：分钟)"  
FROM    
(SELECT m.\*, s.event\_property\_code,s.event\_property\_value FROM default.daily\_new\_clientrpt\_master m LEFT JOIN default.daily\_new\_clientrpt\_slave s on m.event\_id= s.event\_id ) T   ORDER BY "日期" desc

11，

12，

SELECT  
"部门", toInt64(multiIf(isNull("最大数量"),0,"最大数量")) AS "最大数量","类型"  
FROM  
(  
  SELECT   
  any("部门") AS "部门", max("最大数量") AS "最大数量",any("类型") AS "类型"  
  FROM  
  (SELECT  dept\_one AS "部门",  
                COUNT(DISTINCT(             CASE  
                            WHEN (event\_code='101101000200003' AND day >=today()) THEN  
                                name   
                                  ELSE   
                                  NULL   
                               end  
                                )) as "最大数量",  
                                '登录人数最多部分' AS  "类型"  
                                  
                FROM  
                (SELECT m.\*, s.event\_property\_code,s.event\_property\_value FROM daily\_new\_clientrpt\_master m LEFT JOIN daily\_new\_clientrpt\_slave s on m.event\_id= s.event\_id  ORDER BY server\_time DESC) T   WHERE  auth\_type IN('2')    GROUP BY  
                "部门"  ORDER BY "最大数量" DESC)) T  
   UNION ALL               
SELECT  
   "部门",toInt64(multiIf(isNull("最大数量"),0,"最大数量")) AS "最大数量" ,"类型"  
  FROM  
  (  
 SELECT  
 any("部门") AS "部门", max("最大数量") AS "最大数量",any("类型") AS "类型"  
FROM (  
SELECT "部门" ,sum("最大数量") AS "最大数量",'点击数最高部门' AS  "类型"  
FROM  
(  
SELECT  dept\_one AS "部门", name,fun\_code,  
                max((case  
                        when(server\_time >=today()) then  
                         click\_records  
                        else  
                           NULL  
                      end  
                     )) as "最大数量",   
                                '点击数最高部门' AS  "类型"  
                                  
                FROM  
                (SELECT m.\*, s.event\_property\_code,s.event\_property\_value FROM daily\_new\_clientrpt\_master m LEFT JOIN daily\_new\_clientrpt\_slave s on m.event\_id= s.event\_id  ORDER BY server\_time DESC) T   WHERE  auth\_type IN('2')   GROUP BY  
                "部门" ,name,fun\_code,"类型" ORDER BY "最大数量" DESC) GROUP BY "部门","类型" ORDER BY "最大数量" DESC))  
UNION ALL

SELECT  
   "部门",toInt64(multiIf(isNull("最大数量"),0,"最大数量")) AS "最大数量" ,"类型"  
  FROM  
  (  
 SELECT  
 any("部门") AS "部门", max("最大数量") AS "最大数量",any("类型") AS "类型"  
FROM (  
SELECT "部门" ,sum("最大数量")/any("部门人数") AS "最大数量",'点击数占比最高部门' AS  "类型"  
FROM  
(  
SELECT  dept\_one AS "部门", name,fun\_code,  
                max((case  
                        when(server\_time >=today()) then  
                         click\_records  
                        else  
                           NULL  
                      end  
                     )) as "最大数量",   
                      any(member\_count) AS "部门人数",  
                                '点击数占比最高部门' AS  "类型"  
                                  
                FROM  
                (SELECT m.\*, s.event\_property\_code,s.event\_property\_value FROM daily\_new\_clientrpt\_master m LEFT JOIN daily\_new\_clientrpt\_slave s on m.event\_id= s.event\_id  ORDER BY server\_time DESC) T   WHERE  auth\_type IN('2')   GROUP BY  
                "部门" ,name,fun\_code,"类型" ORDER BY "最大数量" DESC) GROUP BY "部门","类型" ORDER BY "最大数量" DESC))