如何在BigQuery中创建层级结构表并实现数据查询？

1. 创建层级表结构 #

根据需求，通过CREATE TABLE语句定义包含层级字段和字符串数组字段的表：

CREATE TABLE `your-project-id.your-dataset.your-hierarchy-table` (
  tier_1 STRING,
  tier_2 STRING,
  tier_3 STRING,
  cities ARRAY<STRING>
);

替换your-project-id、your-dataset和your-hierarchy-table为你实际的项目ID、数据集名称和表名

2. 插入示例层级数据 #

按照层级关系插入测试数据，示例如下：

INSERT INTO `your-project-id.your-dataset.your-hierarchy-table`
VALUES
  ('Asia', 'East Asia', 'China', ['Beijing', 'Shanghai', 'Guangzhou']),
  ('Asia', 'East Asia', 'Japan', ['Tokyo', 'Osaka', 'Kyoto']),
  ('Asia', 'Southeast Asia', 'Thailand', ['Bangkok', 'Chiang Mai']),
  ('Europe', 'Western Europe', 'France', ['Paris', 'Lyon', 'Marseille']),
  ('Europe', 'Eastern Europe', 'Poland', ['Warsaw', 'Krakow']);

3. 常用查询操作 #

3.1 查询完整层级与城市列表 #

直接查询所有字段，查看完整的层级结构：

SELECT tier_1, tier_2, tier_3, cities
FROM `your-project-id.your-dataset.your-hierarchy-table`;

3.2 展开数组，将城市拆分为单独行 #

使用UNNEST函数将数组类型的cities字段展开，实现一行数据拆分为多行：

SELECT tier_1, tier_2, tier_3, city
FROM `your-project-id.your-dataset.your-hierarchy-table`,
UNNEST(cities) AS city;

3.3 按层级条件过滤数据 #

根据指定的层级维度筛选结果，比如查询亚洲下的东亚区域数据：

SELECT tier_1, tier_2, tier_3, cities
FROM `your-project-id.your-dataset.your-hierarchy-table`
WHERE tier_1 = 'Asia' AND tier_2 = 'East Asia';

3.4 统计各层级下的城市数量 #

使用ARRAY_LENGTH函数计算每个层级条目对应的城市数量：

SELECT 
  tier_1, 
  tier_2, 
  tier_3, 
  ARRAY_LENGTH(cities) AS total_cities
FROM `your-project-id.your-dataset.your-hierarchy-table`
ORDER BY tier_1, tier_2, tier_3;

内容的提问来源于stack exchange，提问作者Thivagar Moorthy