我有个问题要问 Hive 就像下面一样。这个查询对我来说是正常的。

select 
case 
when account_number = '4122268949' then 'Operating'
when account_number = '4127343176' then 'Reserve'
when account_number = '4125604850' then 'sFBo'
when account_number = '2651133000' then 'IDEAL'
when account_number = '4496851031' then 'PwB'
end as account_name,
account_number, 
count(distinct(file_name)) as file_name_count, 
count(*) as total_file_count, 
case when count(*) >=1 then 'Minimum_expected_files_processed' 
else 'Files_not_received_today' 
end as Files_validation
from 
database.table 
where 
account_number in (
'4122268949', 
'4127343176', 
'4125604850', 
'2651133000', 
'4496851031'
) 
group by 
account_number) tb1;

问题：
如果表中没有任何记录 4496851031 以及 4127343176 账号。
我希望结果是

----------------------------------------------------------------------------------------------------
|account_name| account_number| file_name_count| total_file_count|                  Files_validation|
----------------------------------------------------------------------------------------------------
| Operating  |     4122268949|              2 |              20 | Minimum_expected_files_processed | 
| Reserve    |     4127343176|              0 |              0  |         Files_not_received_today |
| sFBo       |     4125604850|              3 |              22 | Minimum_expected_files_processed |
| IDEAL      |     2651133000|              1 |              1  | Minimum_expected_files_processed |
| PwB        |     4496851031|              0 |              0  |         Files_not_received_today |
----------------------------------------------------------------------------------------------------

编辑
当我过滤 database.table 添加仅返回的额外where子句 3 账号，我无法实现我想要的 sample query ```
select accounts.name as account_name,
accounts.number as account_number,
count(distinct b.file_name) as file_name_count,
count(b.file_name) as total_file_count,
case when count(b.file_name) >= 1 then 'Minimum_expected_files_processed'
else 'Files_not_received_today'
end as Files_validation
from accounts
left join db.table b on b.account_number = accounts.number
where b.load_date >= '2019-08-20'
group by accounts.number, accounts.name