当数据值为 0 时缺少行(以 TB 为单位)

Question

我正在尝试在查询中进行分组，但每次数据为 0 时，分组依据都不会显示整行。我该如何解决这个问题？

这是常规查询

select 
COUNT(DISTINCT sr.sr_number) AS NEW_CASES
FROM table sr
where 
sr.business_unit in('BU1')
and OPENED_DATE < trunc(current_Date)
and OPENED_DATE > trunc(current_date -2)
and sr.status = 'Open'

输出为

NEW_CASES
0

但是当我做一个小组时。整行都消失了。

select 
COUNT(DISTINCT sr.sr_number) AS NEW_CASES,
sr.business_unit
FROM table sr
where 
sr.business_unit in('BU1','BU2','BU3' )
and OPENED_DATE < trunc(current_Date)
and OPENED_DATE > trunc(current_date -2)
and sr.status = 'Open'
group by sr.business_unit

按输出分组为

New_CASES    BUSINESS_UNIT
200    BU2
300    BU3

期望输出：

New_CASES    BUSINESS_UNIT
0    BU1
200    BU2
300    BU3

Answer 1

一种选择是从固定的值列表开始，然后带一个带有left join的表，如下所示：

select b.business_unit, count(distinct t.sr_number) as new_cases
from (
select 'bu1' business_unit from dual
union all select 'bu2' from dual
union all select 'bu3' from dual
) b
left join mytable t
on  t.business_unit = b.business_unit
and t.opened_date > trunc(current_date -2)
and t.opened_date < trunc(current_date)
and t.status = 'open'
group by b.business_unit

在 Teradata 中，语法有些繁琐：

select b.business_unit, count(distinct t.sr_number) as new_cases
from (
select * from (select 'bu1' as business_unit) x
union all select * from (select 'bu2' as business_unit ) x
union all select * from (select 'bu3' as business_unit ) x
) b
left join mytable t
on  t.business_unit = b.business_unit
and t.opened_date > trunc(current_date -2)
and t.opened_date < trunc(current_date)
and t.status = 'open'
group by b.business_unit

Answer 2

使用left join. 在甲骨文中，这看起来像

select b.business_unit, COUNT(DISTINCT sr.sr_number) AS NEW_CASES
from (select 'BU1' as business_unit from dual union all
select 'BU2' as business_unit from dual union all
select 'BU3' as business_unit from dual 
) b left join
sr
on sr.business_unit = b.business_unit and
sr.OPENED_DATE < trunc(current_Date) and
sr.OPENED_DATE > trunc(current_date -2) and
sr.status = 'Open'
group by b.business_unit

编辑：

Teradata 没有非常方便的方法来创建常量值的派生表，但您可以这样做：

select b.business_unit, COUNT(DISTINCT sr.sr_number) AS NEW_CASES
from (select 'BU1' as business_unit from (select 1 as dummy) t union all
select 'BU2' as business_unit from (select 1 as dummy) t  union all
select 'BU3' as business_unit from (select 1 as dummy) t  
) b left join
sr
on sr.business_unit = b.business_unit and
sr.OPENED_DATE < trunc(current_Date) and
sr.OPENED_DATE > trunc(current_date -2) and
sr.status = 'Open'
group by b.business_unit