ClickHouse DateTime：高效处理时间数据的秘密武器
Mark Needham约 9 分钟本文字数：768；估计阅读时间：19 分钟
作者：Mark Needham
Meetup活动
ClickHouse 上海第3届 Meetup 火热报名中，详见文末海报！
在本文中，我们将探讨 ClickHouse 中一些最有用的日期和日期时间查询与过滤函数，包括按小时或15分钟窗口进行时间取整、按一天中的特定时间过滤，以及计算两个时间戳之间的时间差。
如果你正在处理需要首先转换的原始日期字符串或时间戳，请查阅我撰写的另一篇关于在 ClickHouse 中解析日期和日期时间的文章（https://clickhouse.com/blog/parsing-dates-datetimes）。本文将在此基础上，重点介绍当数据已存储在 DateTime 列中时如何进行操作。
我们选用的数据集是纽约市出租车数据集，接下来我们将在 ClickHouse 中进行设置。首先，我们创建一个数据库：
CREATE DATABASE nyc_taxi;
然后创建一个表：
CREATE TABLE nyc_taxi.trips_small (
trip_id UInt32,
pickup_datetime DateTime,
dropoff_datetime DateTime,
pickup_longitude Nullable(Float64),
pickup_latitude Nullable(Float64),
dropoff_longitude Nullable(Float64),
dropoff_latitude Nullable(Float64),
passenger_count UInt8,
trip_distance Float32,
fare_amount Float32,
extra Float32,
tip_amount Float32,
tolls_amount Float32,
total_amount Float32,
payment_type Enum('CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4, 'UNK' = 5),
pickup_ntaname LowCardinality(String),
dropoff_ntaname LowCardinality(String)
)
ENGINE = MergeTree
PRIMARY KEY (pickup_datetime, dropoff_datetime);
接着，我们可以运行以下命令导入数据：
INSERT INTO nyc_taxi.trips_small
SELECT
trip_id,
pickup_datetime,
dropoff_datetime,
pickup_longitude,
pickup_latitude,
dropoff_longitude,
dropoff_latitude,
passenger_count,
trip_distance,
fare_amount,
extra,
tip_amount,
tolls_amount,
total_amount,
payment_type,
pickup_ntaname,
dropoff_ntaname
FROM s3(
'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{0..2}.gz',
'TabSeparatedWithNames'
);
此查询导入了超过300万条记录，从下面的输出中可以看出：
3000317 rows in set. Elapsed: 32.077 sec. Processed 3.00 million rows, 256.38 MB (93.53 thousand rows/s., 7.99 MB/s.)
Peak memory usage: 536.51 MiB.
如果需要导入更多数据，我们可以调整 URI 中的 {0..2} 以包含更多文件。
数据加载完成后，现在可以开始编写查询了。我们将主要处理 pickup_datetime 和 dropoff_datetime 这两列。
让我们首先探索2015年7月1日的出租车行程数据。我们将使用 toStartOfHour 将日期时间向下取整到最近的小时，使用 toDate 按指定日期过滤，并使用 toHour 将时间限定在上午时段：
SELECT
toStartOfHour(pickup_datetime) as hour,
count() as trips,
round(avg(passenger_count), 1) as avg_passengers
FROM nyc_taxi.trips_small
WHERE toDate(pickup_datetime) = '2015-07-01'
AND toHour(pickup_datetime) < 13
GROUP BY hour
ORDER BY hour;
┌────────────────hour─┬─trips─┬─avg_passengers─┐
│ 2015-07-01 00:00:00 │ 663 │ 1.7 │
│ 2015-07-01 01:00:00 │ 381 │ 1.6 │
│ 2015-07-01 02:00:00 │ 249 │ 1.8 │
│ 2015-07-01 03:00:00 │ 155 │ 1.6 │
│ 2015-07-01 04:00:00 │ 159 │ 1.5 │
│ 2015-07-01 05:00:00 │ 197 │ 1.5 │
│ 2015-07-01 06:00:00 │ 530 │ 1.6 │
│ 2015-07-01 07:00:00 │ 849 │ 1.6 │
│ 2015-07-01 08:00:00 │ 1034 │ 1.6 │
│ 2015-07-01 09:00:00 │ 1033 │ 1.7 │
│ 2015-07-01 10:00:00 │ 898 │ 1.7 │
│ 2015-07-01 11:00:00 │ 900 │ 1.6 │
│ 2015-07-01 12:00:00 │ 961 │ 1.7 │
└─────────────────────┴───────┴────────────────┘
夜间较为平静，行程量从早上6点左右开始上升，并在上午8-9点达到峰值。但7月1日的数据是否具有代表性呢？让我们移除日期过滤条件，观察所有日期的数据。为此，我们利用 ::Time 将 toStartOfHour 强制转换为 Time 类型，这会去除日期信息，仅保留时间，从而将所有日期的数据按时间进行聚合：
SELECT
toStartOfHour(pickup_datetime)::Time as hour,
count() as trips,
round(avg(passenger_count), 1) as avg_passengers
FROM nyc_taxi.trips_small
WHERE toHour(pickup_datetime) < 13
GROUP BY hour
ORDER BY hour;
┌─────hour─┬──trips─┬─avg_passengers─┐
│ 00:00:00 │ 118268 │ 1.7 │
│ 01:00:00 │ 86495 │ 1.7 │
│ 02:00:00 │ 65246 │ 1.7 │
│ 03:00:00 │ 47377 │ 1.7 │
│ 04:00:00 │ 34840 │ 1.7 │
│ 05:00:00 │ 32328 │ 1.6 │
│ 06:00:00 │ 68644 │ 1.6 │
│ 07:00:00 │ 107494 │ 1.6 │
│ 08:00:00 │ 132596 │ 1.6 │
│ 09:00:00 │ 136228 │ 1.6 │
│ 10:00:00 │ 134286 │ 1.7 │
│ 11:00:00 │ 137561 │ 1.7 │
│ 12:00:00 │ 145282 │ 1.7 │
└──────────┴────────┴────────────────┘
观察所有日期的数据，我们依然发现夜间行程量呈下降趋势，但行程量的增长提前了几个小时，从早上6点到7点。此后四小时内，行程数量保持相对稳定。
早上高峰期究竟从何时开始？让我们使用 toStartOfFifteenMinutes 函数将数据按 15 分钟间隔进行分桶，并使用 formatDateTime 函数使输出结果更具可读性。在 WHERE 子句中，我们将 pickup_datetime 转换为 Time 类型，以便仅按一天中的时间进行过滤，无需考虑日期：
SELECT
formatDateTime(toStartOfFifteenMinutes(pickup_datetime), '%r') AS timeWindow,
count() as trips,
round(avg(trip_distance), 2) as avgDistance
FROM nyc_taxi.trips_small
WHERE pickup_datetime::Time BETWEEN '06:00:00'::Time AND '09:59:59'::Time
AND trip_distance > 0
GROUP BY timeWindow
ORDER BY timeWindow;
┌─timeWindow─┬─trips─┬─avgDistance─┐
│ 06:00 AM │ 11601 │ 4.47 │
│ 06:15 AM │ 14645 │ 3.97 │
│ 06:30 AM │ 19033 │ 3.67 │
│ 06:45 AM │ 22795 │ 3.2 │
│ 07:00 AM │ 23179 │ 3.27 │
│ 07:15 AM │ 25465 │ 3.12 │
│ 07:30 AM │ 28350 │ 3.04 │
│ 07:45 AM │ 29914 │ 2.89 │
│ 08:00 AM │ 30444 │ 3 │
│ 08:15 AM │ 32063 │ 2.91 │
│ 08:30 AM │ 34293 │ 2.8 │
│ 08:45 AM │ 35116 │ 2.63 │
│ 09:00 AM │ 33776 │ 2.74 │
│ 09:15 AM │ 33800 │ 2.72 │
│ 09:30 AM │ 33694 │ 2.73 │
│ 09:45 AM │ 34235 │ 2.63 │
└────────────┴───────┴─────────────┘
高峰从 6:30 开始出现，到 7:30 达到加速阶段，并在 8:45 达到峰值。
我们已经知道了高峰期何时开始，但如果你乘坐出租车，高峰期会是怎样的体验呢？我们可以使用 dateDiff 函数计算行程持续时间（分钟），这使我们能够计算平均速度。我们还在 WHERE 子句中使用 dateDiff 来过滤掉持续时间为零的行程（错误数据）：
WITH buckets AS (
SELECT
formatDateTime(toStartOfFifteenMinutes(pickup_datetime), '%r') AS timeWindow,
count() as trips,
round(avg(trip_distance), 2) as avgDist,
round(avg(dateDiff('minute', pickup_datetime, dropoff_datetime)), 1) AS avgDuration,
round(avg(
trip_distance /
(dateDiff('minute', pickup_datetime, dropoff_datetime) / 60)
), 1) AS avgSpeed
FROM nyc_taxi.trips_small
WHERE pickup_datetime::Time BETWEEN '06:00:00'::Time AND '09:59:59'::Time
AND trip_distance > 0
AND dateDiff('minute', pickup_datetime, dropoff_datetime) > 0
GROUP BY timeWindow
ORDER BY timeWindow
)
SELECT timeWindow, trips, avgDuration, avgDist, avgSpeed,
bar(avgSpeed, 0, (SELECT max(avgSpeed) FROM buckets), 20) AS speedBar
FROM buckets
ORDER BY timeWindow ASC;
┌─timeWindow─┬─trips─┬─avgDuration─┬─avgDist─┬─avgSpeed─┬─speedBar─────────────┐
│ 06:00 AM │ 11562 │ 13.2 │ 4.48 │ 19.3 │ ████████████████████ │
│ 06:15 AM │ 14609 │ 13.1 │ 3.98 │ 18.2 │ ██████████████████▊ │
│ 06:30 AM │ 18993 │ 12.5 │ 3.67 │ 17.3 │ █████████████████▉ │
│ 06:45 AM │ 22754 │ 11.5 │ 3.21 │ 16.1 │ ████████████████▋ │
│ 07:00 AM │ 23139 │ 12.3 │ 3.27 │ 15.4 │ ███████████████▉ │
│ 07:15 AM │ 25428 │ 12.7 │ 3.12 │ 14.3 │ ██████████████▊ │
│ 07:30 AM │ 28313 │ 13.9 │ 3.04 │ 13.5 │ █████████████▉ │
│ 07:45 AM │ 29873 │ 13.8 │ 2.89 │ 12.7 │ █████████████▏ │
│ 08:00 AM │ 30411 │ 14.2 │ 3 │ 12 │ ████████████▍ │
│ 08:15 AM │ 32017 │ 15.2 │ 2.91 │ 11.5 │ ███████████▉ │
│ 08:30 AM │ 34258 │ 15.4 │ 2.8 │ 11 │ ███████████▍ │
│ 08:45 AM │ 35071 │ 14.9 │ 2.64 │ 10.8 │ ███████████▏ │
│ 09:00 AM │ 33718 │ 15.3 │ 2.74 │ 10.9 │ ███████████▎ │
│ 09:15 AM │ 33754 │ 15.3 │ 2.72 │ 10.8 │ ███████████▏ │
│ 09:30 AM │ 33657 │ 15.4 │ 2.73 │ 10.8 │ ███████████▏ │
│ 09:45 AM │ 34188 │ 14.8 │ 2.63 │ 10.9 │ ███████████▎ │
└────────────┴───────┴─────────────┴─────────┴──────────┴──────────────────────┘
bar 函数用于绘制根据最大值进行缩放的 ASCII 柱状图，这是一种方便地以内联方式可视化相对值的方法。
早上 6 点，出租车行驶速度略高于 19 英里/小时。到早上 8 点，速度降至约 12 英里/小时——虽然很慢，但对于大城市高峰期来说相当普遍。随着上午时间的推移，速度会持续下降。
我们已经证实了高峰期的存在，但周末也会出现高峰期吗？我们可以使用 countIf 结合 toDayOfWeek 函数来区分工作日和周末的行程：其中，1-5 代表工作日，6-7 代表周末。接着，我们使用 lag 窗口函数计算每 15 分钟窗口之间行程数量的百分比变化：
WITH trips AS (
SELECT
formatDateTime(toStartOfFifteenMinutes(pickup_datetime), '%r') AS timeWindow,
countIf(toDayOfWeek(pickup_datetime) <= 5) as wdTrips,
countIf(toDayOfWeek(pickup_datetime) > 5) as weTrips
FROM nyc_taxi.trips_small
WHERE trip_distance > 0
AND pickup_datetime::Time BETWEEN '06:00:00'::Time AND '09:59:59'::Time
GROUP BY timeWindow
ORDER BY timeWindow
)
SELECT timeWindow, wdTrips,
round((
(wdTrips - lag(wdTrips) OVER (ORDER BY timeWindow)) /
lag(wdTrips) OVER (ORDER BY timeWindow)) * 100,
1) as wdPctChange,
weTrips,
round(
((weTrips - lag(weTrips) OVER (ORDER BY timeWindow)) /
lag(weTrips) OVER (ORDER BY timeWindow)) * 100,
1) as wePctChange
FROM trips
ORDER BY timeWindow;
┌─timeWindow─┬─wdTrips─┬─wdPctChange─┬─weTrips─┬─wePctChange─┐
│ 06:00 AM │ 9398 │ inf │ 2203 │ inf │
│ 06:15 AM │ 12254 │ 30.4 │ 2391 │ 8.5 │
│ 06:30 AM │ 16106 │ 31.4 │ 2927 │ 22.4 │
│ 06:45 AM │ 19727 │ 22.5 │ 3068 │ 4.8 │
│ 07:00 AM │ 20285 │ 2.8 │ 2894 │ -5.7 │
│ 07:15 AM │ 22129 │ 9.1 │ 3336 │ 15.3 │
│ 07:30 AM │ 24494 │ 10.7 │ 3856 │ 15.6 │
│ 07:45 AM │ 25681 │ 4.8 │ 4233 │ 9.8 │
│ 08:00 AM │ 26259 │ 2.3 │ 4185 │ -1.1 │
│ 08:15 AM │ 27509 │ 4.8 │ 4554 │ 8.8 │
│ 08:30 AM │ 28891 │ 5 │ 5402 │ 18.6 │
│ 08:45 AM │ 29154 │ 0.9 │ 5962 │ 10.4 │
│ 09:00 AM │ 27872 │ -4.4 │ 5904 │ -1 │
│ 09:15 AM │ 27268 │ -2.2 │ 6532 │ 10.6 │
│ 09:30 AM │ 26426 │ -3.1 │ 7268 │ 11.3 │
│ 09:45 AM │ 26070 │ -1.3 │ 8165 │ 12.3 │
└────────────┴─────────┴─────────────┴─────────┴─────────────┘
工作日显示在 6:15–6:30 有一个急剧增长，在 7:15–7:30 左右有第二次（较小）增长，并在 8:45 达到峰值后趋于平稳。周末则有所不同：6:30 的增长更为平缓，且行程数量在上午持续稳步增长。
好消息：ClickHouse Shanghai User Group第 3 届 Meetup 火热报名中，将于2026年5月16日在上海市浦东新区世纪大道1568号中建大厦33层 Optiver上海举行，扫码免费报名
/END/
试用阿里云 ClickHouse企业版
轻松节省30%云资源成本？阿里云数据库ClickHouse 云原生架构全新升级，首次购买ClickHouse企业版计算和存储资源组合，首月消费不超过99.58元（包含最大16CCU+450G OSS用量）了解详情：https://t.aliyun.com/Kz5Z0q9G
征稿启示
面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：[email protected]