我有一个国家/地区维度表COUNTRY_DIM,如下所示:
ID | SHORT_NAME | FULL_NAME | ALTERNATE_NAME |
---|---|---|---|
US | USA | United States of America | United States |
UK | UK | United Kingdom | Great Britain |
IN | India | Republic of India | |
ROK | South Korea | Republic of Korea | |
DE | Germany | Federal Republic of Germany | Deutschland |
我有另一个表COUNTRY_FACT,其中有国家名称和一些如下所示的值:
COUNTRY_NAME | VALUE |
---|---|
United States | 30 |
India | 6 |
United Kingdom | 10 |
South Korea | 2 |
Republic of Korea | 1 |
Germany | 4 |
USA | 2 |
我想首先加入SHORT_NAME上的两个表,如果不匹配,则FULL_NAME,如果不匹配,则ALTERNATE_NAME,并派生国家ID.
因此输出将如下所示:
COUNTRY_ID | VALUE |
---|---|
US | 32 |
IN | 6 |
UK | 10 |
ROK | 3 |
DE | 4 |
我正在使用以下查询:
select dim.country_id,sum(fact.value) value
from contry_fact fact
inner join country_dim dim
on
case when upper(country_name) = upper(short_name) then 1
when upper(country_name) = upper(full_name) then 1
when upper(country_name) = upper(alternate_name) then 1
else 0 end = 1
group by dim.country_id
这是获得所需输出的正确方法,还是我们可以通过其他方式实现?
我用Oracle 19c