我有个问题要问 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
1条答案
按热度按时间vi4fp9gy1#
您应该创建一个包含帐号和名称的表(我们称之为
accounts
). 那你就可以了LEFT JOIN
将其添加到其他表以获得所需的结果:如果确实不想为帐户创建表,可以使用帐户数据的派生表重写查询: