sql中的统计完全按照group by 的粒度统计

朴灿烈づ我的快乐病毒、 2023-07-15 15:19 61阅读 0赞

sql中的统计完全按照group by 的粒度统计，前面的字段多粒度就细，想要更粗的粒度可以再外面包一层字段少的，粗粒度的聚合

davinci中只选一个粗粒的维度相当于外包一层见粗粒度示例---维度就是聚合的粒度，维度越多，结果集基础上聚合越细

细粒度：

SELECT   
T.fun\_name as "模块名",  
T.event\_code as "事件编码",  
SUM(click\_records) as "点击数量",  
round(SUM(stay\_time)/60000,0) as "使用时长(单位：分钟)"  
FROM    
(SELECT m.\* FROM default.daily\_new\_clientrpt\_master m ) T   where  day  = today() GROUP BY "模块名" ,"事件编码";

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70][]

粗粒度：

SELECT  
"模块名",  
sum("点击数量"),  
sum("使用时长(单位：分钟)")  
FROM  
(  
SELECT   
T.fun\_name as "模块名",  
T.event\_code as "事件编码",  
SUM(click\_records) as "点击数量",  
round(SUM(stay\_time)/60000,0) as "使用时长(单位：分钟)"  
FROM    
(SELECT m.\* FROM default.daily\_new\_clientrpt\_master m ) T   where  day  = today() GROUP BY "模块名" ,"事件编码") GROUP BY "模块名";

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70 1][]

等价于细粒度sql+一个维度的图表统计：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70 2][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70]: /images/20230528/ccff8c55854c4646a0c87571b150eb7b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70 1]: /images/20230528/57a234e5f4234392b3dc5b25ef54ec42.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70 2]: /images/20230528/fe15fca498c54d0db24a0714b433afdc.png