用 DuckDB 分析荷兰铁路交通

本文以真实铁路数据集展示 DuckDB 的多项能力，包括查询不同文件格式、连接远程数据源，以及使用高级 SQL 功能。

简介

荷兰是 DuckDB 的诞生地，面积约42,000平方公里，人口约1,800万。人口密度高是其铁路网络发达的重要原因：网络包含3,223公里轨道和397座车站。以上数字采用原文背景数据。

车站和列车服务信息以开放数据集 提供，由 Rijden de Treinen（“火车在运行吗？”）应用团队维护。

本文以荷兰铁路数据展示 DuckDB 的分析能力，不介绍新功能或版本，而是在一个领域内演示多项已有功能。其中部分查询的简化版也出现在 DuckDB 首页。

加载数据

先使用2023年的铁路服务数据集。下载 \u00000\u0000（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 检测到文件是 gzip 压缩的 CSV，调用 read_csv 解压，再用 CSV sniffer 根据内容推断结构。

• 查询使用 DuckDB 的 FROM 优先语法，可省略 SELECT *。因此 FROM 'services-2023.csv.gz'; 是 SELECT * FROM 'services-2023.csv.gz'; 的简写。

• CREATE TABLE ... AS 创建 services 表，并将 CSV 读取器的结果填入表中。

原作者使用 DuckDB v0.10.3，在 M2 MacBook Pro 上加载约需5秒。可通过以下查询，将 services 行数按带千位分隔符的格式输出：

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

num_services

---

21,239,393

结果表明，数据集记录了2023年荷兰超过2,100万条列车服务记录。

找出每月最繁忙的车站

先问一个简单问题：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;

得到以下结果：

前三名由四座车站占据：Utrecht Centraal、Amsterdam Centraal、Schiphol Airport 和 Amsterdam Sloterdijk。

使用 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 文件时，“夏季每月最繁忙的三个车站”可以直接在远程文件上查询，无须创建本地表。将 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 的列式布局使读取器只读取所需列；元数据中的 zonemap 则支持过滤下推，例如只获取日期属于夏季的行组。

两项优化都通过 HTTP Range 请求实现，显著减少远程 Parquet 查询的流量和耗时。

荷兰铁路车站之间的最大距离

下一个问题是：沿铁路旅行时，荷兰哪两站距离最远？这里使用两个数据集。第一个 \u00000\u0000 包含站名、国家等信息，可以直接加载和查询：

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;

第二个 \u00000\u0000 包含站间距离，定义为铁路网络中的最短路线，用于计算票价。先看文件开头：

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'
    );

然后用 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) 恰好给出 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公里——对这样一个小国来说距离相当可观。

结论

本文展示了按文件名自动识别格式、自动推断 CSV 结构、直接查询 Parquet、远程查询、窗口函数、逆透视，以及 FROM 优先、GROUP BY ALL、COLUMNS(*) 等便捷 SQL 功能。

组合这些能力，可以跨文件格式（CSV、Parquet）、数据来源（本地、HTTPS、S3）和 SQL 功能编写查询，快速高效地回答问题。

下一篇将讨论使用 AsOf 连接处理时序数据，以及用 DuckDB 的 spatial 扩展处理地理空间数据。

---

来源：Analyzing Railway Traffic in the Netherlands，Gábor Szárnyas，2024-05-31。性能数字均为原作者报告。文档仓库 © 2018–2025 Stichting DuckDB Foundation，采用 MIT 许可证。数据集遵循其来源条款。

MIT 许可声明

Copyright 2018-2025 Stichting DuckDB Foundation

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.