ClickHouse で DateTime をクエリする方法

本記事では、日付や日時のクエリおよびフィルタリングに役立つ ClickHouse の主要な関数を取り上げます。最も近い時間や 15 分単位のウィンドウへの丸め、時間帯によるフィルタリング、2 つのタイムスタンプ間の所要時間の計算などを紹介します。

未加工の日付文字列やタイムスタンプの事前変換が必要な場合は、以前執筆した ClickHouse での日付と日時のパースをご覧ください。本記事はその続きとして、データがすでに DateTime カラムに格納されている状態を前提に解説します。

ニューヨーク市タクシーデータセットのインポート

ここではニューヨーク市タクシーデータセットを使用します。まずは ClickHouse 側で準備しましょう。はじめにデータベースを作成します。

1CREATE DATABASE nyc_taxi;

次にテーブルを作成します。

1CREATE TABLE nyc_taxi.trips_small (
2    trip_id             UInt32,
3    pickup_datetime     DateTime,
4    dropoff_datetime    DateTime,
5    pickup_longitude    Nullable(Float64),
6    pickup_latitude     Nullable(Float64),
7    dropoff_longitude   Nullable(Float64),
8    dropoff_latitude    Nullable(Float64),
9    passenger_count     UInt8,
10    trip_distance       Float32,
11    fare_amount         Float32,
12    extra               Float32,
13    tip_amount          Float32,
14    tolls_amount        Float32,
15    total_amount        Float32,
16    payment_type        Enum('CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4, 'UNK' = 5),
17    pickup_ntaname      LowCardinality(String),
18    dropoff_ntaname     LowCardinality(String)
19)
20ENGINE = MergeTree
21PRIMARY KEY (pickup_datetime, dropoff_datetime);

続いて、以下のコマンドを実行してデータをインポートします。

1INSERT INTO nyc_taxi.trips_small
2SELECT
3    trip_id,
4    pickup_datetime,
5    dropoff_datetime,
6    pickup_longitude,
7    pickup_latitude,
8    dropoff_longitude,
9    dropoff_latitude,
10    passenger_count,
11    trip_distance,
12    fare_amount,
13    extra,
14    tip_amount,
15    tolls_amount,
16    total_amount,
17    payment_type,
18    pickup_ntaname,
19    dropoff_ntaname
20FROM s3(
21    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{0..2}.gz',
22    'TabSeparatedWithNames'
23);

以下の出力のとおり、このクエリによって 300 万件強のレコードがインポートされます。

13000317 rows in set. Elapsed: 32.077 sec. Processed 3.00 million rows, 256.38 MB (93.53 thousand rows/s., 7.99 MB/s.)
2Peak memory usage: 536.51 MiB.

さらに多くのデータをインポートしたい場合は、URI 内の {0..2} を調整して対象ファイル数を増やせます。

データのロードが完了したので、クエリを書いていきましょう。今回は pickup_datetime カラムと dropoff_datetime カラムを使用します。

時間別の移動回数

まずは 2015年7月1日のタクシーの運行状況を調べてみましょう。toStartOfHour で日時を 1 時間単位へ切り捨て、toDate で特定の日付に絞り込み、toHour で午前の時間帯に限定します。

1SELECT
2  toStartOfHour(pickup_datetime) as hour,
3  count() as trips,
4  round(avg(passenger_count), 1) as avg_passengers
5FROM nyc_taxi.trips_small
6WHERE toDate(pickup_datetime) = '2015-07-01'
7AND toHour(pickup_datetime) < 13
8GROUP BY hour
9ORDER BY hour;

1┌────────────────hour─┬─trips─┬─avg_passengers─┐
2│ 2015-07-01 00:00:00 │   663 │            1.7 │
3│ 2015-07-01 01:00:00 │   381 │            1.6 │
4│ 2015-07-01 02:00:00 │   249 │            1.8 │
5│ 2015-07-01 03:00:00 │   155 │            1.6 │
6│ 2015-07-01 04:00:00 │   159 │            1.5 │
7│ 2015-07-01 05:00:00 │   197 │            1.5 │
8│ 2015-07-01 06:00:00 │   530 │            1.6 │
9│ 2015-07-01 07:00:00 │   849 │            1.6 │
10│ 2015-07-01 08:00:00 │  1034 │            1.6 │
11│ 2015-07-01 09:00:00 │  1033 │            1.7 │
12│ 2015-07-01 10:00:00 │   898 │            1.7 │
13│ 2015-07-01 11:00:00 │   900 │            1.6 │
14│ 2015-07-01 12:00:00 │   961 │            1.7 │
15└─────────────────────┴───────┴────────────────┘

夜間は静かで、朝 6 時頃から移動が増え始め、8〜9 時頃にピークを迎えています。しかし、7月1日の傾向は典型的なものだったのでしょうか。日付のフィルタを外し、全日程を対象に見てみましょう。そのためには、::Time を使って toStartOfHour の結果を Time 型にキャストします。これにより日付部分が除去されて時刻のみが残るため、全日程のデータをまとめてグループ化できます。

1SELECT
2  toStartOfHour(pickup_datetime)::Time as hour,
3  count() as trips,
4  round(avg(passenger_count), 1) as avg_passengers
5FROM nyc_taxi.trips_small
6WHERE toHour(pickup_datetime) < 13
7GROUP BY hour
8ORDER BY hour;

1┌─────hour─┬──trips─┬─avg_passengers─┐
2│ 00:00:00 │ 118268 │            1.7 │
3│ 01:00:00 │  86495 │            1.7 │
4│ 02:00:00 │  65246 │            1.7 │
5│ 03:00:00 │  47377 │            1.7 │
6│ 04:00:00 │  34840 │            1.7 │
7│ 05:00:00 │  32328 │            1.6 │
8│ 06:00:00 │  68644 │            1.6 │
9│ 07:00:00 │ 107494 │            1.6 │
10│ 08:00:00 │ 132596 │            1.6 │
11│ 09:00:00 │ 136228 │            1.6 │
12│ 10:00:00 │ 134286 │            1.7 │
13│ 11:00:00 │ 137561 │            1.7 │
14│ 12:00:00 │ 145282 │            1.7 │
15└──────────┴────────┴────────────────┘

全日程を見ても夜間の移動減少は変わりませんが、増加が始まるタイミングは 1〜2 時間早く、午前 6 時から 7 時にかけてとなっています。その後 4 時間ほどは、移動回数がほぼ横ばいで推移します。

15 分単位で見るラッシュアワー

朝のラッシュは正確にはいつ始まるのでしょうか。toStartOfFifteenMinutes で 15 分刻みのバケットにまとめ、formatDateTime で出力を読みやすく整形して詳しく確認してみましょう。WHERE 句では pickup_datetime を Time 型にキャストして時間帯のみでフィルタリングしているため、日付を指定する必要はありません。

1SELECT
2  formatDateTime(toStartOfFifteenMinutes(pickup_datetime), '%r') AS timeWindow,
3  count() as trips,
4  round(avg(trip_distance), 2) as avgDistance
5FROM nyc_taxi.trips_small
6WHERE pickup_datetime::Time BETWEEN '06:00:00'::Time AND '09:59:59'::Time
7AND trip_distance > 0
8GROUP BY timeWindow
9ORDER BY timeWindow;

1┌─timeWindow─┬─trips─┬─avgDistance─┐
2│ 06:00 AM   │ 11601 │        4.47 │
3│ 06:15 AM   │ 14645 │        3.97 │
4│ 06:30 AM   │ 19033 │        3.67 │
5│ 06:45 AM   │ 22795 │         3.2 │
6│ 07:00 AM   │ 23179 │        3.27 │
7│ 07:15 AM   │ 25465 │        3.12 │
8│ 07:30 AM   │ 28350 │        3.04 │
9│ 07:45 AM   │ 29914 │        2.89 │
10│ 08:00 AM   │ 30444 │           3 │
11│ 08:15 AM   │ 32063 │        2.91 │
12│ 08:30 AM   │ 34293 │         2.8 │
13│ 08:45 AM   │ 35116 │        2.63 │
14│ 09:00 AM   │ 33776 │        2.74 │
15│ 09:15 AM   │ 33800 │        2.72 │
16│ 09:30 AM   │ 33694 │        2.73 │
17│ 09:45 AM   │ 34235 │        2.63 │
18└────────────┴───────┴─────────────┘

急増は 6:30 に始まり、7:30 まで加速を続け、8:45 にピークに達します。

ラッシュアワーの混雑の実態

ラッシュアワーの開始時刻は分かりました。では、タクシーに乗っている側はどのように感じるのでしょうか。dateDiff を使って乗車時間 (分単位) を算出し、そこから平均速度を計算できます。また、WHERE 句でも dateDiff を使用して所要時間 0 分の移動 (異常データ) を除外しています。

1WITH buckets AS (
2  SELECT
3    formatDateTime(toStartOfFifteenMinutes(pickup_datetime), '%r') AS timeWindow,
4    count() as trips,
5    round(avg(trip_distance), 2) as avgDist,
6    round(avg(dateDiff('minute', pickup_datetime, dropoff_datetime)), 1) AS avgDuration,
7    round(avg(
8      trip_distance /
9      (dateDiff('minute', pickup_datetime, dropoff_datetime) / 60)
10    ), 1) AS avgSpeed
11  FROM nyc_taxi.trips_small
12  WHERE pickup_datetime::Time BETWEEN '06:00:00'::Time AND '09:59:59'::Time
13  AND trip_distance > 0
14  AND dateDiff('minute', pickup_datetime, dropoff_datetime) > 0
15  GROUP BY timeWindow
16  ORDER BY timeWindow
17)
18SELECT timeWindow, trips, avgDuration, avgDist, avgSpeed,
19       bar(avgSpeed, 0, (SELECT max(avgSpeed) FROM buckets), 20) AS speedBar
20FROM buckets
21ORDER BY timeWindow ASC;

1┌─timeWindow─┬─trips─┬─avgDuration─┬─avgDist─┬─avgSpeed─┬─speedBar─────────────┐
2│ 06:00 AM   │ 11562 │        13.2 │    4.48 │     19.3 │ ████████████████████ │
3│ 06:15 AM   │ 14609 │        13.1 │    3.98 │     18.2 │ ██████████████████▊  │
4│ 06:30 AM   │ 18993 │        12.5 │    3.67 │     17.3 │ █████████████████▉   │
5│ 06:45 AM   │ 22754 │        11.5 │    3.21 │     16.1 │ ████████████████▋    │
6│ 07:00 AM   │ 23139 │        12.3 │    3.27 │     15.4 │ ███████████████▉     │
7│ 07:15 AM   │ 25428 │        12.7 │    3.12 │     14.3 │ ██████████████▊      │
8│ 07:30 AM   │ 28313 │        13.9 │    3.04 │     13.5 │ █████████████▉       │
9│ 07:45 AM   │ 29873 │        13.8 │    2.89 │     12.7 │ █████████████▏       │
10│ 08:00 AM   │ 30411 │        14.2 │       3 │       12 │ ████████████▍        │
11│ 08:15 AM   │ 32017 │        15.2 │    2.91 │     11.5 │ ███████████▉         │
12│ 08:30 AM   │ 34258 │        15.4 │     2.8 │       11 │ ███████████▍         │
13│ 08:45 AM   │ 35071 │        14.9 │    2.64 │     10.8 │ ███████████▏         │
14│ 09:00 AM   │ 33718 │        15.3 │    2.74 │     10.9 │ ███████████▎         │
15│ 09:15 AM   │ 33754 │        15.3 │    2.72 │     10.8 │ ███████████▏         │
16│ 09:30 AM   │ 33657 │        15.4 │    2.73 │     10.8 │ ███████████▏         │
17│ 09:45 AM   │ 34188 │        14.8 │    2.63 │     10.9 │ ███████████▎         │
18└────────────┴───────┴─────────────┴─────────┴──────────┴──────────────────────┘

bar 関数は最大値に合わせてスケールした ASCII バーチャートを描画するため、相対的な数値をインラインで手軽に可視化できます。

午前 6 時のタクシーの速度は時速 19 マイル強です。8 時までには時速約 12 マイルまで低下します。遅いものの、大都市のラッシュアワーとしてはごく一般的な速度です。朝が進むにつれて速度はさらに低下していきます。

平日と週末の比較

ラッシュアワーが存在することは確認できました。では、週末にも発生するのでしょうか。toDayOfWeek と countIf を組み合わせて、平日と週末の移動を分割できます。値の 1〜5 が平日、6〜7 が週末です。さらに、ウィンドウ関数の lag を使って 15 分間隔ごとの移動回数の変化率を計算します。

1WITH trips AS (
2  SELECT
3    formatDateTime(toStartOfFifteenMinutes(pickup_datetime), '%r') AS timeWindow,
4    countIf(toDayOfWeek(pickup_datetime) <= 5) as wdTrips,
5    countIf(toDayOfWeek(pickup_datetime) > 5) as weTrips
6  FROM nyc_taxi.trips_small
7  WHERE trip_distance > 0
8  AND pickup_datetime::Time BETWEEN '06:00:00'::Time AND '09:59:59'::Time
9  GROUP BY timeWindow
10  ORDER BY timeWindow
11)
12SELECT timeWindow, wdTrips,
13  round((
14    (wdTrips - lag(wdTrips) OVER (ORDER BY timeWindow)) /
15    lag(wdTrips) OVER (ORDER BY timeWindow)) * 100,
16  1) as wdPctChange,
17  weTrips,
18  round(
19    ((weTrips - lag(weTrips) OVER (ORDER BY timeWindow)) /
20    lag(weTrips) OVER (ORDER BY timeWindow)) * 100,
21  1) as wePctChange
22FROM trips
23ORDER BY timeWindow;

1┌─timeWindow─┬─wdTrips─┬─wdPctChange─┬─weTrips─┬─wePctChange─┐
2│ 06:00 AM   │    9398 │         inf │    2203 │         inf │
3│ 06:15 AM   │   12254 │        30.4 │    2391 │         8.5 │
4│ 06:30 AM   │   16106 │        31.4 │    2927 │        22.4 │
5│ 06:45 AM   │   19727 │        22.5 │    3068 │         4.8 │
6│ 07:00 AM   │   20285 │         2.8 │    2894 │        -5.7 │
7│ 07:15 AM   │   22129 │         9.1 │    3336 │        15.3 │
8│ 07:30 AM   │   24494 │        10.7 │    3856 │        15.6 │
9│ 07:45 AM   │   25681 │         4.8 │    4233 │         9.8 │
10│ 08:00 AM   │   26259 │         2.3 │    4185 │        -1.1 │
11│ 08:15 AM   │   27509 │         4.8 │    4554 │         8.8 │
12│ 08:30 AM   │   28891 │           5 │    5402 │        18.6 │
13│ 08:45 AM   │   29154 │         0.9 │    5962 │        10.4 │
14│ 09:00 AM   │   27872 │        -4.4 │    5904 │          -1 │
15│ 09:15 AM   │   27268 │        -2.2 │    6532 │        10.6 │
16│ 09:30 AM   │   26426 │        -3.1 │    7268 │        11.3 │
17│ 09:45 AM   │   26070 │        -1.3 │    8165 │        12.3 │
18└────────────┴─────────┴─────────────┴─────────┴─────────────┘

平日は 6:15〜6:30 に急増し、7:15〜7:30 頃に 2 回目の (小さめの) 増加が見られ、8:45 にピークを迎えた後は落ち着きます。週末は様相が異なり、6:30 の増加は緩やかで、午前中にかけて移動回数が着実に増え続けます。

今すぐ始める

自社のデータで ClickHouse がどのように機能するか試してみませんか？数分で ClickHouse Cloud を使い始めることができ、$300 分の無料クレジットも獲得できます。

サインアップ

ClickHouse で DateTime をクエリする方法

ニューヨーク市タクシーデータセットのインポート

時間別の移動回数

15 分単位で見るラッシュアワー

ラッシュアワーの混雑の実態

平日と週末の比較

今すぐ始める

Subscribe to our newsletter

最近の記事

ClickHouse リリース 26.9

FountainがClickHouse Cloud上でデータプレーンを再構築し、現場向け超知能「Cue」を強化した方法

NVMe 上の Postgres: 性能、そしてトランザクションとアナリティクスの統合

Apache Airflow 向けの公式 ClickHouse プロバイダーが利用可能に

Follow us

ClickHouse で DateTime をクエリする方法

ニューヨーク市タクシーデータセットのインポート #

時間別の移動回数 #

15 分単位で見るラッシュアワー #

ラッシュアワーの混雑の実態 #

平日と週末の比較 #

今すぐ始める

Subscribe to our newsletter

最近の記事

ClickHouse リリース 26.9

FountainがClickHouse Cloud上でデータプレーンを再構築し、現場向け超知能「Cue」を強化した方法

NVMe 上の Postgres: 性能、そしてトランザクションとアナリティクスの統合

Apache Airflow 向けの公式 ClickHouse プロバイダーが利用可能に

Follow us

ニューヨーク市タクシーデータセットのインポート

時間別の移動回数

15 分単位で見るラッシュアワー

ラッシュアワーの混雑の実態

平日と週末の比較