大查询-如何写入Google表格中的现有单元格？

可以使用带有正则表达式的SQL来快速编译所有国家/地区的SQL：

select line,
ifnull(REGEXP_EXTRACT(line,r'(Andorra|United Arab Emirates|Afghanistan|Antigua and Barbuda|Anguilla|Albania|Armenia|Angola|Antarctica|Argentina|American Samoa|Austria|Australia|Aruba|Aland Islands|Azerbaijan|Bosnia and Herzegovina|Barbados|Bangladesh|Belgium|Burkina Faso|Bulgaria|Bahrain|Burundi|Benin|Saint Barthelemy|Bermuda|Brunei|Bolivia|"Bonaire, Saint Eustatius and Saba "|Brazil|Bahamas|Bhutan|Bouvet Island|Botswana|Belarus|Belize|Canada|Cocos Islands|Democratic Republic of the Congo|Central African Republic|Republic of the Congo|Switzerland|Ivory Coast|Cook Islands|Chile|Cameroon|China|Colombia|Costa Rica|Cuba|Cape Verde|Curacao|Christmas Island|Cyprus|Czech Republic|Germany|Djibouti|Denmark|Dominica|Dominican Republic|Algeria|Ecuador|Estonia|Egypt|Western Sahara|Eritrea|Spain|Ethiopia|Finland|Fiji|Falkland Islands|Micronesia|Faroe Islands|France|Gabon|United Kingdom|Grenada|Georgia|French Guiana|Guernsey|Ghana|Greenland|Gambia|Guinea|Guadeloupe|Equatorial Guinea|Greece|South Georgia and the South Sandwich Islands|Guatemala|Guam|Guinea-Bissau|Guyana|Hong Kong|Heard Island and McDonald Islands|Honduras|Croatia|Haiti|Hungary|Indonesia|Ireland|Israel|Isle of Man|India|British Indian Ocean Territory|Iraq|Iran|Iceland|Italy|Jersey|Jamaica|Jordan|Japan|Kenya|Kyrgyzstan|Cambodia|Kiribati|Comoros|Saint Kitts and Nevis|North Korea|South Korea|Kosovo|Kuwait|Cayman Islands|Kazakhstan|Laos|Lebanon|Saint Lucia|Liechtenstein|Sri Lanka|Liberia|Lesotho|Lithuania|Luxembourg|Latvia|Libya|Morocco|Moldova|Montenegro|Saint Martin|Madagascar|Macedonia|Mali|Myanmar|Mongolia|Macao|Northern Mariana Islands|Martinique|Mauritania|Montserrat|Malta|Mauritius|Maldives|Malawi|Mexico|Malaysia|Mozambique|Namibia|New Caledonia|Niger|Nigeria|Nicaragua|Netherlands|Norway|Nepal|Nauru|Niue|New Zealand|Oman|Panama|Peru|French Polynesia|Papua New Guinea|Philippines|Pakistan|Poland|Saint Pierre and Miquelon|Pitcairn|Puerto Rico|Palestinian Territory|Portugal|Palau|Paraguay|Qatar|Reunion|Romania|Serbia|Russia|Rwanda|Saudi Arabia|Solomon Islands|Seychelles|Sudan|South Sudan|Sweden|Saint Helena|Slovenia|Svalbard and Jan Mayen|Slovakia|Sierra Leone|Senegal|Somalia|Suriname|Sao Tome and Principe|El Salvador|Sint Maarten|Syria|Swaziland|Turks and Caicos Islands|Chad|French Southern Territories|Togo|Thailand|Tajikistan|Tokelau|East Timor|Turkmenistan|Tunisia|Tonga|Turkey|Trinidad and Tobago|Tuvalu|Taiwan|Tanzania|Ukraine|Uganda|United States Minor Outlying Islands|United States|Uruguay|Uzbekistan|Saint Vincent and the Grenadines|Venezuela|British Virgin Islands|U.S. Virgin Islands|Vietnam|Vanuatu|Wallis and Futuna|Samoa|Yemen|Mayotte|South Africa|Zambia|Zimbabwe|Serbia and Montenegro|Netherlands Antilles)'),'') as country
from 
(select "1. It's nice in France during Summer" as line),
(select "2. When I go on holiday" as line),
(select "3. When it rains in Spain, the rain etc" as line),
(select "4. Icecream is great" as line),
(select "5. England is always cold in winter" as line) as input

这将返回：

+-----+-----------------------------------------+---------+---+
| Row |                  line                   | country |   |
+-----+-----------------------------------------+---------+---+
|   1 | 1. It's nice in France during Summer    | France  |   |
|   2 | 2. When I go on holiday                 |         |   |
|   3 | 3. When it rains in Spain, the rain etc | Spain   |   |
|   4 | 4. Icecream is great                    |         |   |
|   5 | 5. England is always cold in winter     |         |   |
+-----+-----------------------------------------+---------+---+

如您所见，England它不在列表中，因此您需要适应列表。我也使用了静态数据，但是您可以用表中的select替换它。

在这里，您可以使用“ Web UI”选项将结果导出到Google表格，因此您将获得所需的两列。如果结果太大，您可以将CSV格式导出为GCS，然后将CSV格式上载到Google表格中。

我使用以下查询编译了国家/地区列表：

SELECT group_concat(country,"|") FROM [gdelt-bq:extra.countryinfo]

这是一个公共数据集，您可以将其添加到自己的项目中https://bigquery.cloud.google.com/table/gdelt-bq:extra.countryinfo