荷兰铁路交通数据分析

原文：https://duckdb.org/2024/05/31/analyzing-railway-traffic-in-the-netherlands.html

作者：Gabor Szarnyas

简介

DuckDB 的诞生地荷兰，国土面积约 42,000 平方公里，人口约 1800 万。如此高的人口密度，使得荷兰拥有 非常密集的铁路网络 ^[1]。该网络包含 3,223 公里的铁轨和 397 个车站。

应用程序 Rijden de Treinen _(火车在运行吗？) ^[2] 的开发团队维护着一套 开放数据集 ^[3]，其中包含了该网络的站点和服务信息，这些数据集质量很高。

本文将以荷兰铁路网络数据集为例，展示 DuckDB 的数据分析能力。与我们之前的大多数文章不同，本文不会介绍新功能或新版本：而是用一个实际案例来演示 DuckDB 的一些现有功能。DuckDB 首页上以简化形式展示了本文中介绍的一些查询案例。

加载数据

首先，我们将使用 2023 年的 铁路服务数据集 ^[4] 进行一些初始查询。要获取此数据集，请下载 services-2023.csv.gz
 文件 ^[5] (330 MB)，并将其加载到 DuckDB 中。

首先，启动 DuckDB 命令行客户端：

duckdb railway.db

然后，将 services-2023.csv.gz
 文件加载到 services
 表中：

CREATE TABLE services AS
    FROM 'services-2023.csv.gz';

虽然这个查询语句看起来很简单，但背后其实做了很多工作。让我们来拆解一下这个查询：

• • 首先，我们不需要为 services
   表显式定义模式，也不需要使用 COPY ... FROM
   语句 ^[6]。DuckDB 会自动识别出 'services-2023.csv.gz'
   是一个 gzip 压缩的 CSV 文件，并调用 read_csv
   函数 ^[7]， 该函数会先解压文件，然后使用 CSV 嗅探器 ^[8] 根据文件内容推断出其模式。

• • 其次，该查询使用了 DuckDB 的 FROM
  -first 语法 ^[9]，这种语法允许用户省略 SELECT *
   子句。因此，SQL 语句 FROM 'services-2023.csv.gz';
   等同于 SELECT * FROM 'services-2023.csv.gz';
  。

• • 第三，该查询创建了一个名为 services
   的表，并将 CSV 阅读器的结果填充到该表中。这是通过 CREATE TABLE ... AS
   语句 ^[10] 实现的。

在 M2 MacBook Pro 上使用 DuckDB v0.10.3 ^[11]，加载该数据集大约需要 5 秒钟。要查看加载的数据量，我们可以运行以下查询，该查询将 以千位分隔符格式打印 ^[12] services
 表中的行数：

SELECT format('{:,}', count(*)) AS num_services
FROM services;

我们可以看到，2023 年在荷兰运行的列车服务超过了 2100 万列。

查找每月最繁忙的车站

让我们先来回答一个简单的问题：2023 年前 6 个月，荷兰最繁忙的火车站是哪些？

首先，我们需要计算每个月经过每个车站的列车服务数量。为此，我们使用 month
 函数 ^[13] 提取服务日期的月份， 然后使用 count(*)
 进行分组聚合：

SELECT
    month("Service:Date") AS month,
    "Stransform: translateY(station name" AS station,
    count(*) AS num_services
FROM services
GROUP BY month, station
LIMIT 5;

需要注意的是，该查询存在 SQL 中常见的冗余：我们在 SELECT
 和 GROUP BY
 子句中都列出了非聚合列的名称。使用 DuckDB 的 GROUP BY ALL
 功能 ^[14] 可以消除这种冗余。同时，我们还可以使用 CREATE TABLE ... AS
 语句将结果保存到一个名为 services_per_month
 的中间表中：

CREATE TABLE services_per_month AS
    SELECT
        month("Service:Date") AS month,
        "Stransform: translateY(station name" AS station,
        count(*) AS num_services
    FROM services
    GROUP BY ALL;

为了回答这个问题，我们可以使用 arg_max(arg, val)
 聚合函数 ^[15]， 该函数返回具有最大值 val
 的行中的 arg
 列。我们过滤月份并返回结果：

SELECT
    month,
    arg_max(station, num_services) AS station,
    max(num_services) AS num_services
FROM services_per_month
WHERE month <= 6
GROUP BY ALL;

令人惊讶的是，在大多数月份，最繁忙的火车站不在阿姆斯特丹，而是在荷兰第四大城市 乌得勒支 ^[16]，这得益于乌得勒支的中心地理位置。

查找夏季每月客流量排名前三的车站

让我们把问题改成：夏季每个月客流量排名前三的车站是哪些？ arg_max()
 函数只能帮我们找到排名第一的值，但无法找到前 k 名的结果。幸运的是，DuckDB 对 SQL 功能提供了广泛的支持，包括 窗口函数 ^[17]，我们可以使用 rank()
 函数 ^[18] 来查找前 k 名的值。此外，我们还可以使用 make_date
 ^[19] 函数来重建日期，使用 strftime
 ^[20] 函数将日期转换为月份名称，并使用 array_agg
 ^[21] 函数进行聚合：

SELECT month, month_name, array_agg(station) AS top3_stations
FROM (
    SELECT
        month,
        strftime(make_date(2023, month, 1), '%B') AS month_name,
        rank() OVER
            (PARTITION BY month ORDER BY num_services DESC) AS rank,
        station,
        num_services
    FROM services_per_month
    WHERE month BETWEEN 6 AND 8
)
WHERE rank <= 3
GROUP BY ALL
ORDER BY month;

这将返回以下结果：

我们可以看到，排名前三的车站分别是乌得勒支中央车站、阿姆斯特丹中央车站、史基浦机场和阿姆斯特丹斯劳特迪克车站。

通过 HTTPS 或 S3 直接查询 Parquet 文件

DuckDB 支持通过 HTTP(S) 协议和 S3 API ^[22] 查询远程文件，包括 CSV 和 Parquet 文件。例如，我们可以运行以下查询：

SELECT "Service:Date", "Stransform: translateY(station name"
FROM 'https://blobs.duckdb.org/nl-railway/services-2023.parquet'
LIMIT 3;

这将返回以下结果：

使用远程 Parquet 文件，我们可以在不创建任何本地表的情况下，直接在远程 Parquet 文件上运行查询以回答 夏季每个月客流量排名前三的车站是哪些？ 这个问题。为此，我们可以使用 WITH
 子句 ^[23] 将 services_per_month
 表定义为一个公用表表达式。查询的其余部分保持不变：

WITH services_per_month AS (
    SELECT
        month("Service:Date") AS month,
        "Stransform: translateY(station name" AS station,
        count(*) AS num_services
    FROM 'https://blobs.duckdb.org/nl-railway/services-2023.parquet'
    GROUP BY ALL
)
SELECT month, month_name, array_agg(station) AS top3_stations
FROM (
    SELECT
        month,
        strftime(make_date(2023, month, 1), '%B') AS month_name,
        rank() OVER
            (PARTITION BY month ORDER BY num_services DESC) AS rank,
        station,
        num_services
    FROM services_per_month
    WHERE month BETWEEN 6 AND 8
)
WHERE rank <= 3
GROUP BY ALL
ORDER BY month;

该查询将返回与上述查询相同的结果，并且只需大约 1-2 秒即可完成（具体时间取决于网络速度）。之所以速度这么快，是因为 DuckDB 不需要下载整个 Parquet 文件就可以执行查询：虽然文件大小为 309 MB，但 DuckDB 只使用了大约 20 MB 的网络流量，大约是文件总大小的 6%。

网络流量之所以能够减少，是因为 DuckDB 实现了 部分读取 ^[24] 功能，该功能允许 DuckDB 只读取数据文件中必要的部分。首先，Parquet 的列式存储格式允许 DuckDB 只读取所需的列。其次，Parquet 文件元数据中包含的 区图 ^[25] 允许 DuckDB 只读取满足查询条件的 行组 ^[26]（例如，只读取夏季月份中包含日期的行组）。这两项优化都是通过 HTTP 范围请求 ^[27] 实现的， 在查询远程 Parquet 文件时，可以节省大量的流量和时间。

荷兰相距最远的两个火车站

接下来，让我们来回答这个问题：乘坐火车旅行时，荷兰哪两个火车站之间的距离最远？ 为了回答这个问题，我们需要用到两组数据集。第一组数据集是 stations-2022-01.csv
 ^[28]，其中包含 火车站 ^[29] 的信息（例如，车站名称和所属国家/地区）。我们可以直接加载并查询该数据集，如下所示：

CREATE TABLE stations AS
    FROM 'https://blobs.duckdb.org/data/stations-2022-01.csv';

SELECT
    id,
    name_short,
    name_long,
    country,
    printf('%.2f', geo_lat) AS latitude,
    printf('%.2f', geo_lng) AS longitude
FROM stations
LIMIT 5;

第二组数据集是 tariff-distances-2022-01.csv
 ^[30]，其中包含 车站距离 ^[31] 的信息。车站距离是指铁路网络上的最短路线长度，用于计算火车票价。让我们先来看一下这个文件的内容：

head -n 9 tariff-distances-2022-01.csv | cut -d, -f1-9

Station,AC,AH,AHP,AHPR,AHZ,AKL,AKM,ALM
AC,XXX,82,83,85,90,71,188,32
AH,82,XXX,1,3,8,77,153,98
AHP,83,1,XXX,2,9,78,152,99
AHPR,85,3,2,XXX,11,80,150,101
AHZ,90,8,9,11,XXX,69,161,106
AKL,71,77,78,80,69,XXX,211,96
AKM,188,153,152,150,161,211,XXX,158
ALM,32,98,99,101,106,96,158,XXX

我们可以看到，车站距离信息以矩阵的形式存储，对角线上的元素为 XXX
。根据 数据集描述 ^[32]，XXX
 表示两个车站是同一个车站。如果我们直接将 XXX
 加载为字符串，CSV 阅读器会将所有列的类型都推断为 VARCHAR
，而不是数值类型。虽然我们可以在加载数据之后再进行数据清洗，但最好在一开始就避免这个问题。为此，我们可以使用 read_csv
 函数，并将 nullstr
 参数 ^[33] 设置为 XXX
：

CREATE TABLE distances AS
    FROM read_csv(
        'https://blobs.duckdb.org/data/tariff-distances-2022-01.csv',
        nullstr = 'XXX'
    );

为了在命令行输出中显示 NULL
 值，我们可以使用 .nullvalue
 点命令 ^[34] 将其设置为 NULL
：

.nullvalue NULL

然后，我们可以使用 DESCRIBE
 语句 ^[35] 来确认 DuckDB 是否已将列的类型正确地推断为 BIGINT
：

FROM (DESCRIBE distances)
LIMIT 5;

要显示前 9 列，我们可以使用 SELECT
 语句 ^[36] 中的 #1
、#2
 等列索引：

SELECT #1, #2, #3, #4, #5, #6, #7, #8, #9
FROM distances
LIMIT 8;

我们可以看到数据已经正确加载，但这种宽表格式不利于我们进行下一步的处理：为了查询车站对之间的距离，我们需要首先使用 UNPIVOT
 ^[37] 语句将宽表转换为长表。如果我们直接写出所有列名，代码会非常冗长：

CREATE TABLE distances_long AS
    UNPIVOT distances
    ON AC, AH, AHP, ...

因为我们有将近 400 个车站，手动写出所有车站名的工作量非常大。幸运的是，DuckDB 提供了一个技巧来解决这个问题： COLUMNS(*)
 表达式 ^[38] 可以列出所有列名， 并且其可选的 EXCLUDE
 子句可以从列表中排除指定的列名。因此，我们可以使用 COLUMNS(* EXCLUDE station)
 表达式来列出除 station
 之外的所有列名，这正是我们执行 UNPIVOT
 命令所需要的：

CREATE TABLE distances_long AS
    UNPIVOT distances
    ON COLUMNS (* EXCLUDE station)
    INTO NAME other_station VALUE distance;

这将生成以下表格：

SELECT station, other_station, distance
FROM distances_long
LIMIT 3;

现在，我们可以将 distances_long
 表与 stations
 表连接起来，连接条件是 distances_long
 表中的车站名与 stations
 表中的车站代码相匹配。然后，我们筛选出位于荷兰境内的车站。为了避免重复统计相同车站对之间的距离，我们添加了 station < other_station
 条件。最后，我们按照距离降序排列，并返回前 3 名的结果：

SELECT
    s1.name_long AS station1,
    s2.name_long AS station2,
    distances_long.distance
FROM distances_long
JOIN stations s1 ON distances_long.station = s1.code
JOIN stations s2 ON distances_long.other_station = s2.code
WHERE s1.country = 'NL'
  AND s2.country = 'NL'
  AND station < other_station
ORDER BY distance DESC
LIMIT 3;

结果表明，荷兰境内相距最远的两座火车站之间的距离至少为 425 公里，对于这样一个面积不大的国家来说，这个距离已经相当远了。

结论

在本文中，我们演示了 DuckDB 的一些关键特性， 包括 根据文件名自动检测文件格式 ^[39]、 自动推断 CSV 文件的模式 ^[40]、 直接查询 Parquet 文件 ^[41]、 查询远程数据 ^[42]、 使用窗口函数 ^[43]、 使用 UNPIVOT 语句 ^[44]、 使用一些人性化的 SQL 特性 ^[45]（例如，FROM
-first 语法、GROUP BY ALL
 语句和 COLUMNS(*)
 表达式）等等。这些特性的结合使得用户能够使用不同的文件格式（例如，CSV 和 Parquet）、数据源（例如，本地文件、HTTPS 和 S3）和 SQL 功能来快速高效地查询数据。

在下一篇文章中，我们将介绍如何使用 AsOf 连接 ^[46] 查询时间数据， 以及如何使用 DuckDB 的 spatial
 扩展 ^[47] 查询地理空间数据。

引用链接

[1]
 非常密集的铁路网络 : https://en.wikipedia.org/wiki/Rail_transport_in_the_Netherlands
[2]
 Rijden de Treinen _(火车在运行吗？) : https://www.rijdendetreinen.nl/en/about
[3]
 开放数据集 : https://www.rijdendetreinen.nl/en/open-data/
[4]
 铁路服务数据集 : https://www.rijdendetreinen.nl/en/open-data/train-archive
[5]
 services-2023.csv.gz
 文件 : https://blobs.duckdb.org/nl-railway/services-2023.csv.gz
[6]
 COPY ... FROM
 语句 : https://duckdb.org/docs/sql/statements/copy#copy--from
[7]
 read_csv
 函数 : https://duckdb.org/docs/data/csv/overview#csv-functions
[8]
 CSV 嗅探器 : https://duckdb.org/docs/data/csv/auto_detection
[9]
 FROM
-first 语法 : https://duckdb.org/docs/sql/query_syntax/from#from-first-syntax
[10]
 CREATE TABLE ... AS
 语句 : https://duckdb.org/docs/sql/statements/create_table#create-table--as-select-ctas
[11]
 DuckDB v0.10.3 : https://duckdb.org/docs/installation
[12]
 以千位分隔符格式打印 : https://duckdb.org/docs/sql/functions/char#print-numbers-with-thousand-separators
[13]
 month
 函数 : https://duckdb.org/docs/sql/functions/datepart#monthdate
[14]
 GROUP BY ALL
 功能 : https://duckdb.org/docs/sql/query_syntax/groupby#group-by-all
[15]
 arg_max(arg, val)
 聚合函数 : https://duckdb.org/docs/sql/aggregates#arg_maxarg-val
[16]
 乌得勒支 : https://en.wikipedia.org/wiki/Utrecht
[17]
 窗口函数 : https://duckdb.org/docs/sql/window_functions
[18]
 rank()
 函数 : https://duckdb.org/docs/sql/window_functions#rank
[19]
 make_date
 : https://duckdb.org/docs/sql/functions/date#make_dateyear-month-day
[20]
 strftime
 : https://duckdb.org/docs/sql/functions/timestamptz#strftimetimestamptz-format
[21]
 array_agg
 : https://duckdb.org/docs/sql/aggregates#array_aggarg
[22]
 HTTP(S) 协议和 S3 API : https://duckdb.org/docs/extensions/httpfs
[23]
 WITH
 子句 : https://duckdb.org/docs/sql/query_syntax/with
[24]
 部分读取 : https://duckdb.org/docs/data/parquet/overview#partial-reading
[25]
 区图 : https://duckdb.org/docs/guides/performance/indexing#zonemaps
[26]
 行组 : https://duckdb.org/docs/internals/storage#row-groups
[27]
 HTTP 范围请求 : https://developer.mozilla.org/en-US/docs/Web/HTTP/Range_requests
[28]
 stations-2022-01.csv
 : https://blobs.duckdb.org/data/stations-2022-01.csv
[29]
 火车站 : https://www.rijdendetreinen.nl/en/open-data/stations
[30]
 tariff-distances-2022-01.csv
 : https://blobs.duckdb.org/data/tariff-distances-2022-01.csv
[31]
 车站距离 : https://www.rijdendetreinen.nl/en/open-data/station-distances
[32]
 数据集描述 : https://www.rijdendetreinen.nl/en/open-data/station-distances#description
[33]
 nullstr
 参数 : https://duckdb.org/docs/data/csv/overview#parameters
[34]
 .nullvalue
 点命令 : https://duckdb.org/docs/api/cli/dot_commands
[35]
 DESCRIBE
 语句 : https://duckdb.org/docs/guides/meta/describe
[36]
 SELECT
 语句 : https://duckdb.org/docs/sql/statements/select
[37]
 UNPIVOT
 : https://duckdb.org/docs/sql/statements/unpivot
[38]
 COLUMNS(*)
 表达式 : https://duckdb.org/docs/sql/expressions/star#columns-expression
[39]
 根据文件名自动检测文件格式 : https://duckdb.org/docs/data/overview
[40]
 自动推断 CSV 文件的模式 : https://duckdb.org/2023/10/27/csv-sniffer
[41]
 直接查询 Parquet 文件 : https://duckdb.org/2021/06/25/querying-parquet
[42]
 查询远程数据 : https://duckdb.org/docs/extensions/httpfs/overview
[43]
 使用窗口函数 : https://duckdb.org/2021/10/13/windowing
[44]
 使用 UNPIVOT 语句 : https://duckdb.org/docs/sql/statements/unpivot
[45]
 使用一些人性化的 SQL 特性 : https://duckdb.org/docs/guides/sql_features/friendly_sql
[46]
 AsOf 连接 : https://duckdb.org/2023/09/15/asof-joins-fuzzy-temporal-lookups
[47]
 spatial
 扩展 : https://duckdb.org/2023/04/28/spatial