嵌入式字典相关函数 - ClickHouse Documentation

要使以下函数正常工作，服务器配置中必须指定获取所有嵌入式字典所需的路径和地址。这些字典会在首次调用其中任一函数时加载。如果无法加载参考列表，则会抛出异常。因此，除非预先完成配置，否则本节中的示例默认会在 ClickHouse Fiddle 以及快速发布版和生产部署中抛出异常。

有关创建参考列表的信息，请参见”字典”部分。

多个地理库

ClickHouse 支持同时使用多个可选的 geobase (区域层级) ，以便支持从不同视角看待某些区域属于哪个国家。 ‘clickhouse-server’ 配置中指定了区域层级文件： <path_to_regions_hierarchy_file>/opt/geo/regions_hierarchy.txt</path_to_regions_hierarchy_file> 除该文件外，它还会在相邻位置查找文件名中带有 _ 符号并追加任意后缀的文件 (位于文件扩展名之前) 。例如，如果存在，它也会找到文件 /opt/geo/regions_hierarchy_ua.txt。其中，ua 称为字典键。对于没有后缀的字典，键为空字符串。所有字典都会在运行时重新加载 (按 builtin_dictionaries_reload_interval 配置参数定义的间隔，每隔若干秒加载一次；默认情况下为每小时一次) 。不过，可用字典的列表只会在服务器启动时确定一次。所有用于处理区域的函数在末尾都有一个可选参数——字典键。它称为 geobase。示例：

regionToCountry(RegionID) – 使用默认字典：/opt/geo/regions_hierarchy.txt
regionToCountry(RegionID, '') – 使用默认字典：/opt/geo/regions_hierarchy.txt
regionToCountry(RegionID, 'ua') – 使用 'ua' 键对应的字典：/opt/geo/regions_hierarchy_ua.txt

regionToName

接受一个区域 ID 和 geobase，返回对应语言的区域名称字符串。如果指定 ID 的区域不存在，则返回空字符串。语法

regionToName(id\[, lang\])

参数

id — geobase 中的区域 ID。UInt32。
geobase — 字典键。参见多个地理库。String。可选。

返回值

geobase 指定的对应语言的区域名称。String。
否则为空字符串。

示例

Query

SELECT regionToName(number::UInt32,'en') FROM numbers(0,5);

Response

┌─regionToName(CAST(number, 'UInt32'), 'en')─┐
│                                            │
│ World                                      │
│ USA                                        │
│ Colorado                                   │
│ Boulder County                             │
└────────────────────────────────────────────┘

regionToCity

接受 geobase 中的区域 ID。如果该区域是某个城市或某个城市的一部分，则返回对应城市的区域 ID。否则返回 0。语法

regionToCity(id [, geobase])

参数

id — geobase 中的区域 ID。UInt32。
geobase — 字典键。参见多个地理库。String。可选。

返回值

对应城市的区域 ID (如果存在) 。UInt32。
0，如果不存在对应城市。

示例

Query

SELECT regionToName(number::UInt32, 'en'), regionToCity(number::UInt32) AS id, regionToName(id, 'en') FROM numbers(13);

Response

┌─regionToName(CAST(number, 'UInt32'), 'en')─┬─id─┬─regionToName(regionToCity(CAST(number, 'UInt32')), 'en')─┐
│                                            │  0 │                                                          │
│ World                                      │  0 │                                                          │
│ USA                                        │  0 │                                                          │
│ Colorado                                   │  0 │                                                          │
│ Boulder County                             │  0 │                                                          │
│ Boulder                                    │  5 │ Boulder                                                  │
│ China                                      │  0 │                                                          │
│ Sichuan                                    │  0 │                                                          │
│ Chengdu                                    │  8 │ Chengdu                                                  │
│ America                                    │  0 │                                                          │
│ North America                              │  0 │                                                          │
│ Eurasia                                    │  0 │                                                          │
│ Asia                                       │  0 │                                                          │
└────────────────────────────────────────────┴────┴──────────────────────────────────────────────────────────┘

regionToArea

将区域转换为 area (geobase 中的类型 5) 。除此之外，该函数与 ‘regionToCity’ 相同。语法

regionToArea(id [, geobase])

参数

id — geobase 中的区域 ID。UInt32。
geobase — 字典键。参见多个地理库。String。可选。

返回值

如果存在，则返回对应区域的区域 ID。UInt32。
如果不存在，则返回 0。

示例

Query

SELECT DISTINCT regionToName(regionToArea(toUInt32(number), 'ua'))
FROM system.numbers
LIMIT 15

Response

┌─regionToName(regionToArea(toUInt32(number), \'ua\'))─┐
│                                                      │
│ Moscow and Moscow region                             │
│ St. Petersburg and Leningrad region                  │
│ Belgorod region                                      │
│ Ivanovsk region                                      │
│ Kaluga region                                        │
│ Kostroma region                                      │
│ Kursk region                                         │
│ Lipetsk region                                       │
│ Orlov region                                         │
│ Ryazan region                                        │
│ Smolensk region                                      │
│ Tambov region                                        │
│ Tver region                                          │
│ Tula region                                          │
└──────────────────────────────────────────────────────┘

regionToDistrict

将区域转换为联邦区 (geobase 中的类型 4) 。除此之外，该函数与 ‘regionToCity’ 完全相同。语法

regionToDistrict(id [, geobase])

参数

id — geobase 中的区域 ID。UInt32。
geobase — 字典键。参见多个地理库。String。可选。

返回值

对应城市的区域 ID (如果存在) 。UInt32。
如果不存在，则返回 0。

示例

Query

SELECT DISTINCT regionToName(regionToDistrict(toUInt32(number), 'ua'))
FROM system.numbers
LIMIT 15

Response

┌─regionToName(regionToDistrict(toUInt32(number), \'ua\'))─┐
│                                                          │
│ Central federal district                                 │
│ Northwest federal district                               │
│ South federal district                                   │
│ North Caucases federal district                          │
│ Privolga federal district                                │
│ Ural federal district                                    │
│ Siberian federal district                                │
│ Far East federal district                                │
│ Scotland                                                 │
│ Faroe Islands                                            │
│ Flemish region                                           │
│ Brussels capital region                                  │
│ Wallonia                                                 │
│ Federation of Bosnia and Herzegovina                     │
└──────────────────────────────────────────────────────────┘

regionToCountry

将区域转换为国家 (geobase 中的类型 3) 。除此之外，此函数与 ‘regionToCity’ 完全相同。语法

regionToCountry(id [, geobase])

参数

id — geobase 中的区域 ID。UInt32。
geobase — 字典键。参见多个地理库。String。可选。

返回值

如果存在，则返回对应国家的区域 ID。UInt32。
如果不存在，则返回 0。

示例

Query

SELECT regionToName(number::UInt32, 'en'), regionToCountry(number::UInt32) AS id, regionToName(id, 'en') FROM numbers(13);

Response

┌─regionToName(CAST(number, 'UInt32'), 'en')─┬─id─┬─regionToName(regionToCountry(CAST(number, 'UInt32')), 'en')─┐
│                                            │  0 │                                                             │
│ World                                      │  0 │                                                             │
│ USA                                        │  2 │ USA                                                         │
│ Colorado                                   │  2 │ USA                                                         │
│ Boulder County                             │  2 │ USA                                                         │
│ Boulder                                    │  2 │ USA                                                         │
│ China                                      │  6 │ China                                                       │
│ Sichuan                                    │  6 │ China                                                       │
│ Chengdu                                    │  6 │ China                                                       │
│ America                                    │  0 │                                                             │
│ North America                              │  0 │                                                             │
│ Eurasia                                    │  0 │                                                             │
│ Asia                                       │  0 │                                                             │
└────────────────────────────────────────────┴────┴─────────────────────────────────────────────────────────────┘

regionToContinent

将区域转换为大洲 (即 geobase 中的类型 1) 。除此之外，此函数与 ‘regionToCity’ 相同。语法

regionToContinent(id [, geobase])

参数

id — geobase 中的区域 ID。UInt32。
geobase — 字典键。参见多个地理库。String。可选。

返回值

对应大洲的区域 ID (如果存在) 。UInt32。
如果不存在，则返回 0。

示例

Query

SELECT regionToName(number::UInt32, 'en'), regionToContinent(number::UInt32) AS id, regionToName(id, 'en') FROM numbers(13);

Response

┌─regionToName(CAST(number, 'UInt32'), 'en')─┬─id─┬─regionToName(regionToContinent(CAST(number, 'UInt32')), 'en')─┐
│                                            │  0 │                                                               │
│ World                                      │  0 │                                                               │
│ USA                                        │ 10 │ North America                                                 │
│ Colorado                                   │ 10 │ North America                                                 │
│ Boulder County                             │ 10 │ North America                                                 │
│ Boulder                                    │ 10 │ North America                                                 │
│ China                                      │ 12 │ Asia                                                          │
│ Sichuan                                    │ 12 │ Asia                                                          │
│ Chengdu                                    │ 12 │ Asia                                                          │
│ America                                    │  9 │ America                                                       │
│ North America                              │ 10 │ North America                                                 │
│ Eurasia                                    │ 11 │ Eurasia                                                       │
│ Asia                                       │ 12 │ Asia                                                          │
└────────────────────────────────────────────┴────┴───────────────────────────────────────────────────────────────┘

regionToTopContinent

返回该地区在层级结构中所属的最高层大洲。语法

regionToTopContinent(id[, geobase])

参数

id — geobase 中的区域 ID。UInt32。
geobase — 字典键。参见多个地理库。String。可选。

返回值

最高层级大洲的标识符 (即沿区域层级向上追溯后得到的大洲) 。UInt32。
如果不存在，则为 0。

示例

Query

SELECT regionToName(number::UInt32, 'en'), regionToTopContinent(number::UInt32) AS id, regionToName(id, 'en') FROM numbers(13);

Response

┌─regionToName(CAST(number, 'UInt32'), 'en')─┬─id─┬─regionToName(regionToTopContinent(CAST(number, 'UInt32')), 'en')─┐
│                                            │  0 │                                                                  │
│ World                                      │  0 │                                                                  │
│ USA                                        │  9 │ America                                                          │
│ Colorado                                   │  9 │ America                                                          │
│ Boulder County                             │  9 │ America                                                          │
│ Boulder                                    │  9 │ America                                                          │
│ China                                      │ 11 │ Eurasia                                                          │
│ Sichuan                                    │ 11 │ Eurasia                                                          │
│ Chengdu                                    │ 11 │ Eurasia                                                          │
│ America                                    │  9 │ America                                                          │
│ North America                              │  9 │ America                                                          │
│ Eurasia                                    │ 11 │ Eurasia                                                          │
│ Asia                                       │ 11 │ Eurasia                                                          │
└────────────────────────────────────────────┴────┴──────────────────────────────────────────────────────────────────┘

regionToPopulation

获取某个区域的人口数量。人口数据可以记录在包含 geobase 的文件中。请参见“字典”一节。如果该区域未记录人口数据，则返回 0。在 geobase 中，人口数据可能记录在子区域中，而父区域中则没有记录。语法

regionToPopulation(id[, geobase])

参数

id — geobase 中的区域 ID。UInt32。
geobase — 字典键。参见多个地理库。String。可选。

返回值

该区域的人口。UInt32。
如果不存在，则为 0。

示例

Query

SELECT regionToName(number::UInt32, 'en'), regionToPopulation(number::UInt32) AS id, regionToName(id, 'en') FROM numbers(13);

Response

┌─regionToName(CAST(number, 'UInt32'), 'en')─┬─population─┐
│                                            │          0 │
│ World                                      │ 4294967295 │
│ USA                                        │  330000000 │
│ Colorado                                   │    5700000 │
│ Boulder County                             │     330000 │
│ Boulder                                    │     100000 │
│ China                                      │ 1500000000 │
│ Sichuan                                    │   83000000 │
│ Chengdu                                    │   20000000 │
│ America                                    │ 1000000000 │
│ North America                              │  600000000 │
│ Eurasia                                    │ 4294967295 │
│ Asia                                       │ 4294967295 │
└────────────────────────────────────────────┴────────────┘

regionIn

判断 lhs 区域是否属于 rhs 区域。如果属于，则返回值为 1 的 UInt8 数值；如果不属于，则返回 0。语法

regionIn(lhs, rhs\[, geobase\])

参数

lhs — geobase 中的 lhs 区域 ID。UInt32。
rhs — geobase 中的 rhs 区域 ID。UInt32。
geobase — 字典键。参见多个地理库。String。可选。

返回值

如果属于，则返回 1。UInt8。
如果不属于，则返回 0。

实现细节 这种关系是自反的——任何区域也属于它自身。示例

Query

SELECT regionToName(n1.number::UInt32, 'en') || (regionIn(n1.number::UInt32, n2.number::UInt32) ? ' is in ' : ' is not in ') || regionToName(n2.number::UInt32, 'en') FROM numbers(1,2) AS n1 CROSS JOIN numbers(1,5) AS n2;

Response

World is in World
World is not in USA
World is not in Colorado
World is not in Boulder County
World is not in Boulder
USA is in World
USA is in USA
USA is not in Colorado
USA is not in Boulder County
USA is not in Boulder    

regionHierarchy

接受一个 UInt32 数值，即 geobase 中的区域 ID。返回一个区域 ID 数组，其中包含传入的区域以及事件链中向上的所有父区域。语法

regionHierarchy(id\[, geobase\])

参数

id — geobase 中的区域 ID。UInt32。
geobase — 字典键。请参见多个地理库。String。可选。

返回值

返回包含传入区域及其沿层级链上的所有父区域的区域 ID 的数组。Array(UInt32)。

示例

Query

SELECT regionHierarchy(number::UInt32) AS arr, arrayMap(id -> regionToName(id, 'en'), arr) FROM numbers(5);

Response

┌─arr────────────┬─arrayMap(lambda(tuple(id), regionToName(id, 'en')), regionHierarchy(CAST(number, 'UInt32')))─┐
│ []             │ []                                                                                           │
│ [1]            │ ['World']                                                                                    │
│ [2,10,9,1]     │ ['USA','North America','America','World']                                                    │
│ [3,2,10,9,1]   │ ['Colorado','USA','North America','America','World']                                         │
│ [4,3,2,10,9,1] │ ['Boulder County','Colorado','USA','North America','America','World']                        │
└────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────┘

​多个地理库

​regionToName

​regionToCity

​regionToArea

​regionToDistrict

​regionToCountry

​regionToContinent

​regionToTopContinent

​regionToPopulation

​regionIn

​regionHierarchy

多个地理库

regionToName

regionToCity

regionToArea

regionToDistrict

regionToCountry

regionToContinent

regionToTopContinent

regionToPopulation

regionIn

regionHierarchy