实际业务中遇到这样一个问题:在建立一张看板底表或者维度表时,统计了符合业务逻辑的明细数据,对记录按照某字段进行分组统计构建指标,但是个别指标在某几天可能会为0,反映在底表中是不存在这一指标的明细数据。业务上也需要构建这几个指标并将指标值标记为0,就需要在分组结果后加上这几个指标名称,并计算指标值为0。
情形一:明细表包含指标名称
这种情形是比较好处理的,因为有指标的分类,虽然直接进行分组因为sum
等聚合函数忽略null值结果不会存在,但只要将null值改为0或者使用union将空值指标结果显示出来即可。(count(null)可以返回0,sum(nvl(null,0))需要null值填充为0)
select
count(字段名),
sum(ifnull(字段名,0),
sum(nvl(字段名,0)
from tableName
group by <分组字段>;
情形二:明细表不包含指标名称
第二种情形是明细表中根本不存在包含指标名称的记录,这样不管怎样分组,不会出现名称包含这些的分组。这时的思路是在结果中加入这几个结果:一是使用union(因为会对结果去重)连接所有指标名称和结果0的查询;二是不用group by,直接自己在case when将分组条件固定再聚合(这样when后没有符合条件的记录返回null,null聚合后取值为0)。
方法1:返回结果是一个分组对应一行记录
思路:先进行分组统计,再使用union连接所有分组并将值记为0,最后再按照分组字段进行分组统计(sum(sum,0)返回值为sum结果,sum(0)返回结果为0)。但是需要注意的是这个方法在MySQL中可以正常使用,在其他数据库不可使用时需要加上from,后面跟着系统表名。
select
index_name,
sum(day_num) '日指标值',
sum(month_num) '月指标值',
sum(year_num) '年指标值'
from
(select
index_name,
sum(index_value) 'day_num',
sum(value_m) 'month_num',
sum(value_y) 'year_num'
from dm.tableName
group by index_name
union all
select '订单数量' index_name,0 'day_num',0 'month_num',0 'year_num' from dual
union all
select '入库数量' index_name,0 'day_num',0 'month_num',0 'year_num' from dual
union all
select '交付数量' index_name,0 'day_num',0 'month_num',0 'year_num' from dual
union all
select '回款数量' index_name,0 'day_num',0 'month_num',0 'year_num' from dual
union all
select '开票数量' index_name,0 'day_num',0 'month_num',0 'year_num' from dual
union all
select '实销数量' index_name,0 'day_num',0 'month_num',0 'year_num' from dual
)tmp
group by index_name
方法2:返回结果一个分组对应一列
思路:自己写分组条件限定在聚合函数内
select
count(case distribution when '0-20天' then 1 end) '0-20天',
count(case distribution when '20-40天' then 1 end) '20-40天',
count(case distribution when '40-60天' then 1 end) '40-60天',
count(case distribution when '60-80天' then 1 end) '60-80天',
count(case distribution when '80-100天' then 1 end) '80-100天',
count(case distribution when '100天以上' then 1 end) '100天以上'
from
finebi.dw_tmp_order_verify_distribution_df
count(null)可以返回0,如果求sum,可以写成nvl(sum(null))
select
nvl(sum(case index_name when '订单数量' then index_value end),0) '订单数量',
nvl(sum(case index_name when '订单预测数量' then index_value end),0) '订单预测数量',
nvl(sum(case index_name when '入库数量' then index_value end),0) '入库数量',
nvl(avg(case index_name when '入库及时率' then index_value end),0) '入库及时率',
nvl(sum(case index_name when '交付数量' then index_value end),0) '交付数量',
nvl(avg(case index_name when '送达及时率' then index_value end),0) '送达及时率',
nvl(sum(case index_name when '回款数量' then index_value end),0) '回款数量',
nvl(sum(case index_name when '开票数量' then index_value end),0) '开票数量',
nvl(sum(case index_name when '实销数量' then index_value end),0) '实销数量',
dm.tableName