探索海量真实世界数据集：ClickHouse 中 100 多年的天气记录

将真实世界数据集加载到 ClickHouse 通常需要经历许多简单的步骤。本文将是该系列文章的第一部分，我们将探索此类数据集——在本例中，来自 NOAA 全球历史气候网络的 10 亿行气候数据——完成采样、准备、丰富和加载数据的典型过程，然后在针对特定查询优化我们的模式。我们认为数据集的探索是改进 ClickHouse 的关键组成部分，不仅可以发现边缘案例问题，还可以识别使我们的用户生活更轻松的功能：我们甚至在主存储库上使用特定的 GitHub 标签跟踪潜在的趣味机会。

这篇博文源于去年创建的 一个问题，旨在探索 NOAA 天气数据集。此数据的各种版本以不同的格式存在，并且质量参差不齐。这里我们使用 awslabs 下分发的版本，该版本是来自众多来源的气候记录的组合，经过合并，并经过一套通用的质量保证审查。我们专注于清理数据并将其加载到 ClickHouse 中，然后发出一些基本查询以确认 历史天气记录，以及用于插入补充数据集的更高级功能。

对于我们的客户端机器，我们使用具有 16 个内核和 32GB RAM 的 c5ad.4xlarge。ClickHouse 托管在具有 16 个内核和 64GB RAM 的 AWS m5d.4xlarge 实例上。我们还将此数据加载到 sql.clickhouse.com，供用户进行实验，并在权限允许的情况下链接示例查询。

随着 ClickHouse 最近添加了 对 1900 年起日期的支持，我们可以下载 1900 年至 2022 年的数据。如果使用旧版本的 ClickHouse，请将范围限制为 1925 年 - 随后的查询结果显然会有所不同。数据集以 csv 和压缩 gz 两种格式提供。由于 ClickHouse 可以原生读取 gz，因此我们更喜欢后者，因为它体积小得多（12GB 对比 100GB）。

for i in {1900..2022}; do wget https://noaa-ghcn-pds.s3.amazonaws.com/csv.gz/${i}.csv.gz; done

下载此数据集大约需要 10 分钟，具体取决于您的连接。

在上个世纪，人类大大增加了他们对天气的数据收集工作：代表 1900 年的文件有 460 万行，而 2022 年的文件有近 3600 万行。对数据进行抽样，我们可以看到它以每行一个测量值的格式分布，即

zcat 2021.csv.gz | head
AE000041196,20210101,TMAX,278,,,S,
AE000041196,20210101,PRCP,0,D,,S,
AE000041196,20210101,TAVG,214,H,,S,
AEM00041194,20210101,TMAX,266,,,S,
AEM00041194,20210101,TMIN,178,,,S,
AEM00041194,20210101,PRCP,0,,,S,
AEM00041194,20210101,TAVG,217,H,,S,
AEM00041217,20210101,TMAX,262,,,S,
AEM00041217,20210101,TMIN,155,,,S,
AEM00041217,20210101,TAVG,202,H,,S,

总结 格式文档和列的顺序

• 一个 11 个字符的站点识别码。它本身编码了一些 有用的信息
• YEAR/MONTH/DAY = YYYYMMDD 格式的 8 个字符的日期（例如 19860529 = 1986 年 5 月 29 日）
• ELEMENT = 4 个字符的元素类型指示符。实际上是测量类型。虽然有很多测量值 可用，但我们选择以下几种
  • PRCP - 降水量（十分之一毫米）
  • SNOW - 降雪量（毫米）
  • SNWD - 雪深（毫米）
  • TMAX - 最高温度（十分之一摄氏度）
  • TAVG - 平均温度（十分之一摄氏度）
  • TMIN - 最低温度（十分之一摄氏度）
  • PSUN - 每日可能的日照百分比（百分比）
  • AWND - 平均每日风速（十分之一米/秒）
  • WSFG - 峰值阵风风速（十分之一米/秒）
  • WT** = 天气类型，其中 ** 定义天气类型。天气类型的完整列表 此处。
• DATA VALUE = ELEMENT 的 5 个字符的数据值，即测量值。
• M-FLAG = 1 个字符的测量标志。它有 10 个可能的值。其中一些值表明数据准确性值得怀疑。我们接受设置为“P”的数据 - 标识为缺失，假定为零，因为这仅与 PRCP、SNOW 和 SNWD 测量值相关。
• Q-FLAG 是测量质量标志，具有 14 个可能的值。我们只对值为空的数据感兴趣，即它没有通过任何质量保证检查。
• S-FLAG 是观测的来源标志。对我们的分析没有用处，因此被忽略。
• OBS-TIME = 4 个字符的观测时间，格式为小时-分钟（即 0700 = 早上 7:00）。通常在较旧的数据中不存在。为了我们的目的，我们忽略这一点。

使用 clickhouse-local，这是一个允许处理本地文件的出色工具，我们可以过滤代表感兴趣的测量值且符合我们质量要求的行，同时避免安装和运行 ClickHouse 的需要。

clickhouse-local --query "SELECT count() 
FROM file('*.csv.gz', CSV, 'station_id String, date String, measurement String, value Int64, mFlag String, qFlag String, sFlag String, obsTime String') WHERE qFlag = '' AND (measurement IN ('PRCP', 'SNOW', 'SNWD', 'TMAX', 'TAVG', 'TMIN', 'PSUN', 'AWND', 'WSFG') OR startsWith(measurement, 'WT'))"

2679255970

由于超过 26 亿行，这不是一个快速查询，因为它涉及解析所有文件。在我们的客户端机器上，这大约需要 160 秒。

注意：完整数据集包含 2,956,750,089 行。我们仅通过排除那些具有质量挑战的行来删除 0.3% 的行。

虽然每行一个测量值的结构可以与 ClickHouse 一起使用，但它会不必要地使未来的查询复杂化。理想情况下，我们需要每站点 ID 和日期一行，其中每个测量类型和关联值都是一列，即

"station_id","date","tempAvg","tempMax","tempMin","precipitation","snowfall","snowDepth","percentDailySun","averageWindSpeed","maxWindSpeed","weatherType"
"AEM00041194","2022-07-30",347,0,308,0,0,0,0,0,0,0
"AEM00041194","2022-07-31",371,413,329,0,0,0,0,0,0,0
"AEM00041194","2022-08-01",384,427,357,0,0,0,0,0,0,0
"AEM00041194","2022-08-02",381,424,352,0,0,0,0,0,0,0

使用 clickhouse-local 和简单的 GROUP BY，我们可以将数据重新透视到这种结构。

clickhouse-local --query "SELECT station_id,
       toDate32(date)  as date,
       anyIf(value, measurement = 'TAVG') as tempAvg,
       anyIf(value, measurement = 'TMAX') as tempMax,
       anyIf(value, measurement = 'TMIN') as tempMin,
       anyIf(value, measurement = 'PRCP') as precipitation,
       anyIf(value, measurement = 'SNOW') as snowfall,
       anyIf(value, measurement = 'SNWD') as snowDepth,
       anyIf(value, measurement = 'PSUN') as percentDailySun,
       anyIf(value, measurement = 'AWND') as averageWindSpeed,
       anyIf(value, measurement = 'WSFG') as maxWindSpeed,
       toUInt8OrZero(replaceOne(anyIf(measurement, startsWith(measurement, 'WT') AND value = 1), 'WT', '')) as weatherType
FROM file('./raw/*.csv.gz', CSV, 'station_id String, date String, measurement String, value Int64, mFlag String, qFlag String, sFlag String, obsTime String')
WHERE qFlag = ''
GROUP BY station_id, date
ORDER BY station_id, date FORMAT CSVWithNames;" > noaa.csv

这是一个非常消耗内存的查询。为了在较小的机器上执行此工作，我们可以请求聚合溢出到磁盘，使用 max_bytes_before_external_group_by 设置，或者简单地一次计算一个文件

for i in {1900..2022}
do
clickhouse-local --query "SELECT station_id,
       toDate32(date) as date,
       anyIf(value, measurement = 'TAVG') as tempAvg,
       anyIf(value, measurement = 'TMAX') as tempMax,
       anyIf(value, measurement = 'TMIN') as tempMin,
       anyIf(value, measurement = 'PRCP') as precipitation,
       anyIf(value, measurement = 'SNOW') as snowfall,
       anyIf(value, measurement = 'SNWD') as snowDepth,
       anyIf(value, measurement = 'PSUN') as percentDailySun,
       anyIf(value, measurement = 'AWND') as averageWindSpeed,
       anyIf(value, measurement = 'WSFG') as maxWindSpeed,
       toUInt8OrZero(replaceOne(anyIf(measurement, startsWith(measurement, 'WT') AND value = 1), 'WT', '')) as weatherType
FROM file('$i.csv.gz', CSV, 'station_id String, date String, measurement String, value Int64, mFlag String, qFlag String, sFlag String, obsTime String')
WHERE qFlag = ''
GROUP BY station_id, date
ORDER BY station_id, date FORMAT TSV" >> "noaa.tsv";
done

此查询将花费一些时间。为了加速，我们有几个选项：要么通过处理多个文件来并行化，要么将数据加载到 ClickHouse 实例中，并利用 INSERT SELECT 来根据需要重新定向数据。我们在“最终丰富”中探讨后一种方法。

我们当前的数据除了站点 ID（包括前缀国家代码）外，没有位置指示。理想情况下，每个站点都应具有与其关联的纬度和经度。为了实现这一点，NOAA 方便地提供了每个站点的详细信息，作为一个单独的 ghcnd-stations.txt。此文件具有 多个列，其中五个对我们未来的分析很有用：id、纬度、经度、海拔和名称。

为了解析此文件，我们使用 Regexp 格式，并使用一个简单的正则表达式组捕获每一列。我们将其加入到我们新创建的 noaa.tsv 中。

wget http://noaa-ghcn-pds.s3.amazonaws.com/ghcnd-stations.txt

clickhouse-local --query "WITH stations AS (SELECT id, lat, lon, elevation, name FROM file('ghcnd-stations.txt', Regexp, 'id String, lat Float64, lon Float64, elevation Float32, name String'))
SELECT station_id,
       date,
       tempAvg,
       tempMax,
       tempMin,
       precipitation,
       snowfall,
       snowDepth,
       percentDailySun,
       averageWindSpeed,
       maxWindSpeed,
       weatherType,
       tuple(lon, lat) as location,
       elevation,
       name
FROM file('noaa.tsv', TSV,
          'station_id String, date Date32, tempAvg Int32, tempMax Int32, tempMin Int32, precipitation Int32, snowfall Int32, snowDepth Int32, percentDailySun Int8, averageWindSpeed Int32, maxWindSpeed Int32, weatherType UInt8') as noaa LEFT OUTER
         JOIN stations ON noaa.station_id = stations.id FORMAT TSV SETTINGS format_regexp='^(.{11})\s+(\-?\d{1,2}\.\d{4})\s+(\-?\d{1,3}\.\d{1,4})\s+(\-?\d*\.\d*)\s+(.*?)\s{2,}.*$'" > noaa_enriched.tsv

请注意我们如何将经度和纬度捕获为 Point，表示为元组位置。我们加入的数据大约为 83GB。

仅仅超过 10 亿行，这对于 ClickHouse 来说是一个相当小的数据集，可以由单个节点和 MergeTree 表管理。请注意使用 ClickHouse Cloud 的用户 - 下面的 DDL 语句将透明地创建一个复制的合并树（甚至可以省略 ENGINE）。我们将在未来的博文中优化下面的模式，目前，我们使用一个简单的定义。Enum 允许我们捕获不同类型的气象事件。

CREATE TABLE noaa
(
   `station_id` LowCardinality(String),
   `date` Date32,
   `tempAvg` Int32 COMMENT 'Average temperature (tenths of a degrees C)',
   `tempMax` Int32 COMMENT 'Maximum temperature (tenths of degrees C)',
   `tempMin` Int32 COMMENT 'Minimum temperature (tenths of degrees C)',
   `precipitation` UInt32 COMMENT 'Precipitation (tenths of mm)',
   `snowfall` UInt32 COMMENT 'Snowfall (mm)',
   `snowDepth` UInt32 COMMENT 'Snow depth (mm)',
   `percentDailySun` UInt8 COMMENT 'Daily percent of possible sunshine (percent)',
   `averageWindSpeed` UInt32 COMMENT 'Average daily wind speed (tenths of meters per second)',
   `maxWindSpeed` UInt32 COMMENT 'Peak gust wind speed (tenths of meters per second)',
   `weatherType` Enum8('Normal' = 0, 'Fog' = 1, 'Heavy Fog' = 2, 'Thunder' = 3, 'Small Hail' = 4, 'Hail' = 5, 'Glaze' = 6, 'Dust/Ash' = 7, 'Smoke/Haze' = 8, 'Blowing/Drifting Snow' = 9, 'Tornado' = 10, 'High Winds' = 11, 'Blowing Spray' = 12, 'Mist' = 13, 'Drizzle' = 14, 'Freezing Drizzle' = 15, 'Rain' = 16, 'Freezing Rain' = 17, 'Snow' = 18, 'Unknown Precipitation' = 19, 'Ground Fog' = 21, 'Freezing Fog' = 22),
   `location` Point,
   `elevation` Float32,
   `name` LowCardinality(String)
) ENGINE = MergeTree() ORDER BY (station_id, date);

之前透视和清理数据的努力确保了现在的加载非常简单。最简单的加载方法依赖于 INFILE 子句，该子句接受加载的本地文件路径。我们在 clickhouse-client 中执行此操作，以确保我们收到诸如执行时间和每秒行数之类的详细信息。

INSERT INTO noaa(station_id, date, tempAvg, tempMax, tempMin, precipitation, snowfall, snowDepth, percentDailySun, averageWindSpeed, maxWindSpeed, weatherType, location, elevation, name) FROM INFILE '/data/blog/noaa_enriched.tsv' FORMAT TSV;

1076229294 rows in set. Elapsed: 195.762 sec.

// split the file into roughly 16 equal parts
time split -l 67264331 noaa_enriched_2.tsv noaa_split

real    6m36.569s

// insert each file via clickhouse-client using a 16 separate process for each client 
time find . -type f -name 'noaa_split*' | xargs -P 16 -n 1 -I {} sh -c "clickhouse-client --query 'INSERT INTO noaa(station_id, date, tempAvg, tempMax, tempMin, precipitation, snowfall, snowDepth, percentDailySun, averageWindSpeed, maxWindSpeed, weatherType, location, elevation, name) FORMAT TSV' < '{}'"

real    2m15.047s

这个简单的更改，虽然可能不是必需的，但已将我们的加载时间缩短到 135 秒或大约 620 mb/秒。对于更技术性的读者，这与我们的客户端磁盘在顺序读取工作负载下通过 fio 测量的最大读取性能一致 - 基于 此处 的步骤。这里的任何增益都会被拆分文件所花费的时间抵消，因此只有当您的文件已经分成多个部分时，此方法才有意义。我们可以在我们的丰富步骤中生成多个文件，以便稍后利用此优化。

加载数据后，我们很想看看它与 有据可查的历史天气记录相比如何。尽管存在争议，“根据 世界气象组织 (WMO) 的说法，有史以来记录的最高温度是 56.7 °C（134.1 °F），发生在 1913 年 7 月 10 日，地点是美国加利福尼亚州 火炉溪（格陵兰牧场）”。

让我们用一个简单的查询来确认这一点 - 注意我们将第一个达到温度的地方记为第一名（数据集包含自 1913 年以来达到 56.7 的几个地方），并过滤到温度记录为高于 50C 的地方

SELECT
    tempMax / 10 AS maxTemp,
    location,
    name,
    date
FROM blogs.noaa
WHERE tempMax > 500
ORDER BY
    tempMax DESC,
    date ASC
LIMIT 5

┌─maxTemp─┬─location──────────┬─name─────────────┬───────date─┐
│    56.7 │ (-116.8667,36.45) │ CA GREENLAND RCH │ 1913-07-10 │
│    56.7 │ (-115.4667,32.55) │ MEXICALI (SMN)   │ 1949-08-20 │
│    56.7 │ (-115.4667,32.55) │ MEXICALI (SMN)   │ 1949-09-18 │
│    56.7 │ (-115.4667,32.55) │ MEXICALI (SMN)   │ 1952-07-17 │
│    56.7 │ (-115.4667,32.55) │ MEXICALI (SMN)   │ 1952-09-04 │
└─────────┴───────────────────┴──────────────────┴────────────┘

5 rows in set. Elapsed: 0.107 sec. Processed 1.08 billion rows, 4.35 GB (10.03 billion rows/s., 40.60 GB/s.)

 ✎

令人欣慰地与 记录的记录 在 火炉溪 保持一致。

任何未来更复杂的天气分析可能都需要识别特定地理区域的天气事件的能力。一个区域理论上可以由纬度/经度坐标的 多边形 甚至 多多边形（多边形数组）定义。pointinpolygon 查询可用于检查天气事件是否属于多边形。我们将在以后的博文中将此功能用于更有趣的多边形，但就目前而言，我们将考虑国家/地区。

Datahub.io 提供了许多有用的小型数据集，包括世界所有国家/地区的多边形列表，以 geojson 格式分发。这使我们能够演示如何使用 url 函数导入函数并在插入时执行数据转换。

这个单个 JSON 文件包含国家/地区的多边形，作为数组“features”的元素。为了获得每个国家/地区一行及其各自的多边形，我们求助于 arrayJoin 和 JSONExtractArrayRaw 函数。

SELECT arrayJoin(JSONExtractArrayRaw(json, 'features')) AS json
FROM url('https://datahub.io/core/geo-countries/r/0.geojson', JSONAsString)
LIMIT 1
FORMAT JSONEachRow

{"json":"{\"type\":\"Feature\",\"properties\":{\"ADMIN\":\"Aruba\",\"ISO_A3\":\"ABW\"},\"geometry\":{\"type\":\"Polygon\",\"coordinates\":[[[-69.99693762899992,12.577582098000036],[-69.93639075399994,12.53172435100005],[-69.92467200399994,12.519232489000046],[-69.91576087099992,12.497015692000076],[-69.88019771999984,12.453558661000045],[-69.87682044199994,12.427394924000097],[-69.88809160099993,12.417669989000046],[-69.90880286399994,12.417792059000107],[-69.93053137899989,12.425970770000035],[-69.94513912699992,12.44037506700009],[-69.92467200399994,12.44037506700009],[-69.92467200399994,12.447211005000014],[-69.95856686099992,12.463202216000099],[-70.02765865799992,12.522935289000088],[-70.04808508999989,12.53115469000008],[-70.05809485599988,12.537176825000088],[-70.06240800699987,12.546820380000057],[-70.06037350199995,12.556952216000113],[-70.0510961579999,12.574042059000064],[-70.04873613199993,12.583726304000024],[-70.05264238199993,12.600002346000053],[-70.05964107999992,12.614243882000054],[-70.06110592399997,12.625392971000068],[-70.04873613199993,12.632147528000104],[-70.00715084499987,12.5855166690001],[-69.99693762899992,12.577582098000036]]]}}"}

这仍然为我们留下了每个国家/地区一个 JSON blob。指示国家/地区及其各自 iso 代码的字段很明显：分别为 ADMIN 和 ISO_A3。当国家/地区是单个连续陆地面积时，它们可以是多边形，或者当它们具有复杂的陆地面积时，例如希腊及其岛屿，它们可以是多多边形。幸运的是，type 字段指示是哪种情况。为了确保未来的查询得到简化，我们将使用一些简单的 条件 逻辑将所有多边形转换为多多边形。我们在 INSERT SELECT 语句中使用此语句 - 这是我们在过去的帖子中使用过的一项功能，用于将国家/地区数据插入到表中。

CREATE TABLE countries
(
    `name` String,
    `coordinates` MultiPolygon
)
ENGINE = MergeTree
ORDER BY name

INSERT INTO countries SELECT
    name,
    coordinates
FROM
(
    SELECT
        JSONExtractString(JSONExtractString(json, 'properties'), 'ADMIN') AS name,
        JSONExtractString(JSONExtractRaw(json, 'geometry'), 'type') AS type,
        if(type = 'Polygon', [JSONExtract(JSONExtractRaw(JSONExtractRaw(json, 'geometry'), 'coordinates'), 'Polygon')], JSONExtract(JSONExtractRaw(JSONExtractRaw(json, 'geometry'), 'coordinates'), 'MultiPolygon')) AS coordinates
    FROM
    (
        SELECT arrayJoin(JSONExtractArrayRaw(json, 'features')) AS json
        FROM url('https://datahub.io/core/geo-countries/r/0.geojson', JSONAsString)
    )
)

有了这个补充数据集，我们可以使用 pointInPolygon 函数来识别葡萄牙国家/地区多边形内发生的天气事件。请注意我们如何使用 arrayExists 遍历多多边形字段的多边形。葡萄牙本身有 17 个多边形来捕获其边界 - 结果是每个天气事件都需要进行这么多比较才能找到有记录以来最热的一天。

WITH (
      SELECT coordinates
      FROM countries
      WHERE name = 'Portugal'
  ) AS pCoords
SELECT
    tempMax,
    station_id,
    date,
    location
FROM noaa
WHERE arrayExists(cord -> pointInPolygon(location, cord), pCoords)
ORDER BY tempMax DESC
LIMIT 5

┌─tempMax─┬─station_id──┬───────date─┬─location──────────┐
│     458 │ PO000008549 │ 1944-07-30 │ (-8.4167,40.2)    │
│     454 │ PO000008562 │ 2003-08-01 │ (-7.8667,38.0167) │
│     452 │ PO000008562 │ 1995-07-23 │ (-7.8667,38.0167) │
│     445 │ POM00008558 │ 2003-08-01 │ (-7.9,38.533)     │
│     442 │ POM00008558 │ 2022-07-13 │ (-7.9,38.533)     │
└─────────┴─────────────┴────────────┴───────────────────┘

10 rows in set. Elapsed: 3388.576 sec. Processed 1.06 billion rows, 46.83 GB (314.06 thousand rows/s., 13.82 MB/s.

虽然响应与 葡萄牙的历史记录 一致，但此查询速度非常慢，接近一小时。对于更复杂的国家/地区，例如加拿大，此查询将不可行。

SELECT
   name,
   length(coordinates) AS num_coordinates
FROM countries
ORDER BY num_coordinates DESC
LIMIT 5
 
┌─name─────────────────────┬─num_coordinates─┐
│ Canada                   │             410 │
│ United States of America │             346 │
│ Indonesia                │             264 │
│ Russia                   │             213 │
│ Antarctica               │             179 │
└──────────────────────────┴─────────────────┘

为了加速此查询，我们可以使用 Polygon 字典。这些字典允许用户有效地搜索包含指定点的多边形。我们主要使用默认值来定义我们的字典，使用 countries 表作为其来源。

CREATE DICTIONARY country_polygons
(
   `name` String,
   `coordinates` MultiPolygon
)
PRIMARY KEY coordinates
SOURCE(CLICKHOUSE(TABLE 'countries'))
LIFETIME(MIN 0 MAX 0)
LAYOUT(POLYGON(STORE_POLYGON_KEY_COLUMN 1))

SELECT
    tempMax / 10 AS maxTemp,
    station_id,
    date,
    location
FROM noaa
WHERE dictGet(country_polygons, 'name', location) = 'Portugal'
ORDER BY tempMax DESC
LIMIT 5
Query id: bfb88bc1-4c1d-4808-bd2d-3a2406d387c3
┌─maxTemp─┬─station_id──┬───────date─┬─location──────────┐
│    45.8 │ PO000008549 │ 1944-07-30 │ (-8.4167,40.2)    │
│    45.4 │ PO000008562 │ 2003-08-01 │ (-7.8667,38.0167) │
│    45.2 │ PO000008562 │ 1995-07-23 │ (-7.8667,38.0167) │
│    44.5 │ POM00008558 │ 2003-08-01 │ (-7.9,38.533)     │
│    44.2 │ POM00008558 │ 2022-07-13 │ (-7.9,38.533)     │
└─────────┴─────────────┴────────────┴───────────────────┘
5 rows in set. Elapsed: 14.498 sec. Processed 1.06 billion rows, 46.83 GB (73.40 million rows/s., 3.23 GB/s.)
 ✎

*注意第一次发出此查询时，字典将被加载到内存中 - 这大约需要 5 秒。后续查询应保持一致。要使用简单查询加载字典，我们可以发出一个简单的 dictGet。例如，SELECT dictGet(country_polygons, 'name', (-9.3704, 38.8027));

SELECT
   tempMax / 10 AS maxTemp,
   station_id,
   date,
   location
FROM noaa
WHERE dictGet(country_polygons, 'name', location) = 'Canada'
ORDER BY tempMax DESC
LIMIT 5
  
┌─maxTemp─┬─station_id──┬───────date─┬─location───────┐
│    47.3 │ CA001163780 │ 2021-06-30 │ (-120.45,50.7) │
│    46.6 │ CA001163780 │ 2021-07-01 │ (-120.45,50.7) │
│    46.2 │ CA001163842 │ 2021-06-30 │ (-120.45,50.7) │
│    45.3 │ CA001163842 │ 2021-07-01 │ (-120.45,50.7) │
│      45 │ CA004015160 │ 1937-07-05 │ (-103.4,49.4)  │
└─────────┴─────────────┴────────────┴────────────────┘
 
5 rows in set. Elapsed: 14.481 sec. Processed 1.06 billion rows, 46.83 GB (73.49 million rows/s., 3.23 GB/s.)
✎

相似的性能，并且与多边形复杂度无关！也与 加拿大的记录 一致 - 尽管位置和确切温度 略有偏差。

在实施此特定查询时，很明显 station_id 的前两个字母代表国家/地区 - 这是了解您的数据的一课。下面显示了上面查询的更简单（也更快）的等效查询。但是，Polygon 字典提供了一种更灵活的解决方案，因为它们允许我们潜在地匹配任意陆地面积。

SELECT
   tempMax / 10 AS maxTemp,
   station_id,
   date,
   location
FROM noaa
WHERE substring(station_id, 1, 2) = 'CA'
ORDER BY tempMax DESC
LIMIT 5
 
┌─maxTemp─┬─station_id──┬───────date─┬─location───────┐
│    47.3 │ CA001163780 │ 2021-06-30 │ (-120.45,50.7) │
│    46.6 │ CA001163780 │ 2021-07-01 │ (-120.45,50.7) │
│    46.2 │ CA001163842 │ 2021-06-30 │ (-120.45,50.7) │
│    45.3 │ CA001163842 │ 2021-07-01 │ (-120.45,50.7) │
│      45 │ CA004015160 │ 1937-07-05 │ (-103.4,49.4)  │
└─────────┴─────────────┴────────────┴────────────────┘
 
5 rows in set. Elapsed: 3.000 sec. Processed 1.06 billion rows, 22.71 GB (354.76 million rows/s., 7.57 GB/s.)
✎

使用 美国滑雪胜地列表及其各自的位置，我们将这些位置与过去 5 年中任何一个月中天气站最多的前 1000 个天气站进行连接。按 geoDistance 对此连接进行排序，并将结果限制为距离小于 20 公里的结果，我们选择每个度假村的顶部结果，并按总降雪量对其进行排序。请注意，我们还将度假村限制在海拔 1800 米以上的度假村，作为良好滑雪条件的大致指标。

SELECT
   resort_name,
   total_snow / 1000 AS total_snow_m,
   resort_location,
   month_year
FROM
(
   WITH resorts AS
       (
           SELECT
               resort_name,
               state,
               (lon, lat) AS resort_location,
               'US' AS code
           FROM url('https://gist.githubusercontent.com/Ewiseman/b251e5eaf70ca52a4b9b10dce9e635a4/raw/9f0100fe14169a058c451380edca3bda24d3f673/ski_resort_stats.csv', CSVWithNames)
       )
   SELECT
       resort_name,
       highest_snow.station_id,
       geoDistance(resort_location.1, resort_location.2, station_location.1, station_location.2) / 1000 AS distance_km,
       highest_snow.total_snow,
       resort_location,
       station_location,
       month_year
   FROM
   (
       SELECT
           sum(snowfall) AS total_snow,
           station_id,
           any(location) AS station_location,
           month_year,
           substring(station_id, 1, 2) AS code
       FROM noaa
       WHERE (date > '2017-01-01') AND (code = 'US') AND (elevation > 1800)
       GROUP BY
           station_id,
           toYYYYMM(date) AS month_year
       ORDER BY total_snow DESC
       LIMIT 1000
   ) AS highest_snow
   INNER JOIN resorts ON highest_snow.code = resorts.code
   WHERE distance_km < 20
   ORDER BY
       resort_name ASC,
       total_snow DESC
   LIMIT 1 BY
       resort_name,
       station_id
)
ORDER BY total_snow DESC
LIMIT 5
  
┌─resort_name──────────┬─total_snow_m─┬─resort_location─┬─month_year─┐
│ Sugar Bowl, CA       │        7.799 │ (-120.3,39.27)  │     201902 │
│ Donner Ski Ranch, CA │        7.799 │ (-120.34,39.31) │     201902 │
│ Boreal, CA           │        7.799 │ (-120.35,39.33) │     201902 │
│ Homewood, CA         │        4.926 │ (-120.17,39.08) │     201902 │
│ Alpine Meadows, CA   │        4.926 │ (-120.22,39.17) │     201902 │
└──────────────────────┴──────────────┴─────────────────┴────────────┘
 
5 rows in set. Elapsed: 2.119 sec. Processed 989.55 million rows, 24.99 GB (467.07 million rows/s., 11.80 GB/s.)

在本系列的下一篇文章中，我们将更详细地深入研究此数据集，并尝试回答一些更有趣的问题，并探索一些地理数据可视化技术。

我们要感谢全球历史气候网络为准备、清理和分发此数据所做的努力。我们感谢您的努力。

Menne, M.J., I. Durre, B. Korzeniewski, S. McNeal, K. Thomas, X. Yin, S. Anthony, R. Ray, R.S. Vose, B.E.Gleason, 和 T.G. Houston, 2012: 全球历史气候网络 - 每日 (GHCN-Daily)，版本 3。[在小数点后注明使用的子集，例如版本 3.25]。NOAA 国家环境信息中心。 http://doi.org/10.7289/V5D21VHZ [2020 年 8 月 17 日]