更改bigquery中的列值

问题描述

我有一个表，其中有一列具有不同值的列，例如美国，韩国，日本等.我想将这些值替换为America = USA，韩国= SA，日本= JP.代码是什么?

I have a table where there is a column with different values like America, South Korea, Japan and so on. I would like to replace the values with America=USA, South Korea=SA, Japan= JP like these. What would be the code?

推荐答案

解决此问题的最佳方法是维护一个单独的表，该表将完整的国家名称映射到其两个字母代码:

The best way to probably handle this would be to maintain a separate table which maps full country names to their two letter codes:

country_full | country_abbr
America      | USA
South Korea  | SA
Japan        | JP

然后，您可以将当前表连接到该查找表以输入代码:

Then, you may join your current table to this lookup table to bring in the codes:

SELECT
    t1.*,
    t2.country_abbr
FROM yourTable t1
LEFT JOIN country_codes t2
    ON t1.country = t2.country_full;

处理此问题的另一种方法虽然不是很可扩展，但是将使用CASE表达式来引入代码:

Another way to handle this, though not very scalable, would be to use a CASE expression to bring in the codes:

SELECT
    country,
    CASE country WHEN 'America'     THEN 'USA'
                 WHEN 'South Korea' THEN 'SA'
                 WHEN 'Japan'       THEN 'JP'
                 ELSE 'Unknown' END As code
FROM yourTable;

这篇关于更改bigquery中的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！