荷兰铁路交通分析

Gabor Szarnyas

2024-05-31 · 11 分钟

介绍

荷兰是 DuckDB 的诞生地，面积约 42,000 平方公里，人口约 1800 万。该国的高人口密度是其广泛的铁路网络的关键因素，该网络由 3,223 公里的轨道和 397 个车站组成。

有关该网络车站和服务的信息以开放数据集的形式提供。这些高质量的数据集由 Rijden de Treinen （火车在运行吗？）应用程序背后的团队维护。

在这篇文章中，我们将演示 DuckDB 在荷兰铁路网络数据集上的一些分析功能。与我们大多数其他博客文章不同，本文不介绍新功能或新版本：相反，它在一个领域中演示了几个现有功能。本博客文章中解释的一些查询以简化形式显示在 DuckDB 的主页上。

加载数据

对于我们的初始查询，我们将使用 2023 年的铁路服务数据集。要获取此数据集，请下载 services-2023.csv.gz 文件 (330 MB) 并将其加载到 DuckDB 中。

首先，在持久数据库上启动 DuckDB 命令行客户端

duckdb railway.db

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

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

尽管这个查询看起来很简单，但其中包含了相当多的内容。让我们来解析一下这个查询

• 首先，无需为我们的 services 表明确定义模式，也无需使用 COPY ... FROM 语句。DuckDB 自动检测到 'services-2023.csv.gz' 指的是一个 gzip 压缩的 CSV 文件，因此它调用 read_csv 函数，该函数解压缩文件并使用 CSV 嗅探器从其内容中推断出其模式。

• 其次，该查询利用了 DuckDB 的FROM 优先语法，它允许用户省略 SELECT * 子句。因此，SQL 语句 FROM 'services-2023.csv.gz'; 是 SELECT * FROM 'services-2023.csv.gz'; 的简写。

• 第三，该查询创建一个名为 services 的表，并使用 CSV 读取器的结果填充它。这是通过使用 CREATE TABLE ... AS 语句实现的。

使用 DuckDB v0.10.3，在 M2 MacBook Pro 上加载数据集大约需要 5 秒。要检查已加载的数据量，我们可以运行以下查询，它会美观地打印 services 表中的行数

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

我们可以看到，2023 年荷兰有超过 2100 万趟列车服务。

查找每月最繁忙的车站

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

首先，对于每个月，让我们计算通过每个车站的服务数量。为此，我们使用 month 函数从服务日期中提取月份，然后使用 count(*) 执行分组聚合

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

请注意，此查询展示了 SQL 中的常见冗余：我们在 SELECT 和 GROUP BY 子句中都列出了非聚合列的名称。使用 DuckDB 的 GROUP BY ALL 功能，我们可以消除这种冗余。同时，我们还可以使用 CREATE TABLE ... AS 语句将此结果转换为一个名为 services_per_month 的中间表

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

为了回答这个问题，我们可以使用 arg_max(arg, val) 聚合函数，它返回具有最大值 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;

也许令人惊讶的是，在大多数月份，最繁忙的火车站不在阿姆斯特丹，而是在该国第四大城市乌得勒支，这归功于其优越的地理位置。

查找每个夏季月份排名前三的最繁忙车站

让我们把问题改为：每个夏季月份排名前三的最繁忙车站是哪些？ arg_max() 函数只能帮助我们找到第一名，但不足以找到前 k 名的结果。

使用窗口函数 (OVER)

DuckDB 对 SQL 功能提供广泛支持，包括窗口函数，我们可以使用 rank() 函数来查找前 k 个值。此外，我们使用 make_date 来重建日期，使用 strftime 将其转换为月份名称，并使用 array_agg

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;

这给出了以下结果

我们可以看到，前三名由四个车站共享：乌得勒支中央车站、阿姆斯特丹中央车站、史基浦机场和阿姆斯特丹斯劳特代克车站。

使用 max_by(arg, val, n) 函数

从 DuckDB 1.1.0 版本开始，您可以使用 max_by 函数的一个变体，它接受第三个参数 n，用于指定行数。生成的代码比使用窗口函数的代码更简洁、更快速。

SELECT
    month,
    strftime(make_date(2023, month, 1), '%B') AS month_name,
    max_by(station, num_services, 3) AS stations,
FROM services_per_month
WHERE month BETWEEN 6 AND 8
GROUP BY ALL
ORDER BY month;

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

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

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

它返回以下结果

使用远程 Parquet 文件，回答每个夏季月份排名前三的最繁忙车站是哪些？的查询可以直接在远程 Parquet 文件上运行，而无需创建任何本地表。为此，我们可以将 services_per_month 表定义为 WITH 子句中的公共表表达式。查询的其余部分保持不变

WITH services_per_month AS (
    SELECT
        month("Service:Date") AS month,
        "Stop: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，但它只使用大约 20 MB 的网络流量，约占文件总大小的 6%。

网络流量的减少是由于数据列和行的部分读取。首先，Parquet 的列式布局允许读取器只访问所需的列。其次，Parquet 文件元数据中可用的区域图允许下推过滤优化（例如，读取器只获取包含夏季月份日期的行组）。这两个优化都通过HTTP 范围请求实现，在对远程 Parquet 文件运行查询时，节省了大量流量和时间。

荷兰火车站之间的最大距离

让我们回答以下问题：荷兰哪两个火车站之间的铁路旅行距离最远？为此，我们将使用两个数据集。第一个是stations-2022-01.csv，它包含有关火车站的信息（车站名称、国家等）。我们可以简单地加载和查询此数据集，如下所示

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，包含车站距离。这些距离被定义为铁路网络上的最短路线，并用于计算票价。让我们查看这个文件

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。正如数据集描述中所解释的，这个字符串意味着这两个站是同一个站。如果我们将值加载为 XXX，CSV 读取器将假定所有列的类型都是 VARCHAR 而不是数值类型。虽然这可以在以后清理，但完全避免这个问题要容易得多。为此，我们使用 read_csv 函数并将 nullstr 参数设置为 XXX

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

为了使 NULL 值在命令行输出中可见，我们将 .nullvalue 点命令设置为 NULL

.nullvalue NULL

然后，使用 DESCRIBE 语句，我们可以确认 DuckDB 已正确推断出该列为 BIGINT 类型

FROM (DESCRIBE distances)
LIMIT 5;

要显示前 9 列，我们可以运行以下查询，在 SELECT 语句中使用 #1、#2 等列索引

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

我们可以看到数据已正确加载，但宽表格式对于进一步处理来说有点笨拙：要查询车站对，我们需要首先使用 UNPIVOT 语句将其转换为长表。天真地讲，我们会写出类似以下的代码

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

然而，我们有将近 400 个车站，所以逐一列出它们的名称会非常繁琐。幸运的是，DuckDB 有一个技巧可以帮助解决这个问题：COLUMNS(*) 表达式列出所有列，其可选的 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 表根据起始站和结束站进行连接，然后筛选出位于荷兰的车站。我们引入对称性破坏 (station < other_station) 以确保同一对车站只在输出中出现一次。最后，我们选择前三名结果

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 的一些关键功能，包括基于文件名自动检测格式、自动推断 CSV 文件模式、直接 Parquet 查询、远程查询、窗口函数、UNPIVOT 以及一些友好的 SQL 功能（例如 FROM 优先、GROUP BY ALL 和 COLUMNS(*)）等等。这些功能的组合允许使用不同的文件格式（CSV、Parquet）、数据源（本地、HTTPS、S3）和 SQL 功能来构建查询。这有助于用户快速高效地回答查询。

在下一篇文章中，我们将使用 AsOf 连接来查看时间数据，并使用 DuckDB 的 spatial 扩展来查看地理空间数据。