メインコンテンツまでスキップ
に最終更新

時系列の最近傍マッチングに使用する ASOF JOIN

時系列データを分析するとき、あるイベントに対して、別のテーブルから「時刻が最も近い」レコードを探す必要がよくあります。たとえば、次のような場合です。

  • 各株式取引が発生した時点での最新の気配値を探す。
  • 各注文を、発注時点で有効だった価格や在庫のスナップショットに対応付ける。
  • 各イベントログのエントリを、その直前または直後の状態変更に対応付ける。

通常の JOIN でこのようなクエリを実装するには、一般にサブクエリやウィンドウ関数（ROW_NUMBER() など）が必要になり、SQL が複雑になって実行コストも高くなります。ASOF JOIN は、このような「時系列の最近傍マッチング」のために Doris が提供する専用の JOIN です。クエリを簡潔な単一の文で記述できます。

ASOF JOIN は日時カラムに基づいてマッチングします。通常の等価 JOIN と異なり、値が完全に一致する必要はありません。左テーブルの各行について、MATCH_CONDITION で指定した方向にある条件を満たす右テーブルの行のうち、最も近い行を選びます。

ここでいう「最も近い」は、時刻の差の絶対値が最小であることを意味しません。MATCH_CONDITION で指定した方向の条件を満たす行のうち、最も近い行を意味します。

ASOF JOIN には次の種類があります。

種類説明一致しない行の扱い
ASOF JOIN / ASOF LEFT JOIN左外部 ASOF JOIN右側のカラムに NULL が入る
ASOF INNER JOIN内部 ASOF JOIN結果からその行を除外する

構文​

SELECT <select_list>
FROM <left_table>
ASOF [LEFT | INNER] JOIN <right_table>
    MATCH_CONDITION(<left_datetime_expr> <comparison_operator> <right_datetime_expr>)
    { ON <left_table.col> = <right_table.col> [AND ...]
    | USING (<column_name> [, ...]) }

主なポイントは次のとおりです。

  • ASOF JOIN は ASOF LEFT JOIN と同じです。一致する行がない左テーブルの行も残り、右側のカラムには NULL が入ります。
  • ASOF INNER JOIN は、一致する行がない左テーブルの行を除外します。
  • <comparison_operator> には >=、>、<=、< のいずれかを指定する必要があります。

パラメータ​

パラメータ必須説明
left_tableはい左テーブル（プローブテーブル）。このテーブルの各行を評価します。
right_tableはい右テーブル（ビルドテーブル）。最も近い一致行を探す対象です。
MATCH_CONDITIONはい最近傍マッチングのルールを定義します。両辺はそれぞれ対応するテーブルのカラムを参照する必要があり、両カラムの型は DATEV2、DATETIMEV2、TIMESTAMP_NS、TIMESTAMPTZ のいずれかでなければなりません。式も使用できます。使用可能な演算子は >=、>、<=、< です。
ON / USING 句はいグループ化キーとして使う等価キーを定義します。同じグループ内でのみマッチングします。ON では、等価（=）条件を複数指定でき、式（SUBSTRING(l.code, 1, 3) = r.prefix など）も使用できます。USING では、同じ名前のカラムを複数指定できます。

マッチングのルール​

マッチングの方向は、MATCH_CONDITION の比較演算子によって決まります。

演算子マッチングの動作代表的な用途
>=左テーブルの各行について、左の値以下である右テーブルの値のうち最大のものを探すイベント時刻以前で最新のスナップショットや気配値を探す
>左テーブルの各行について、左の値未満である右テーブルの値のうち最大のものを探すイベント時刻より前で最新のスナップショットや気配値を探す
<=左テーブルの各行について、左の値以上である右テーブルの値のうち最小のものを探す現在の時刻以降で次のイベントやスナップショットを探す
<左テーブルの各行について、左の値より大きい右テーブルの値のうち最小のものを探す現在の時刻より後で次のイベントやスナップショットを探す

次のルールに特に注意してください。

  1. MATCH_CONDITION のカラムの型は、DATEV2、DATETIMEV2、TIMESTAMP_NS、TIMESTAMPTZ のいずれかでなければなりません。
  2. MATCH_CONDITION 内では式を使用できます。たとえば、MATCH_CONDITION(l.ts >= r.ts + INTERVAL 1 HOUR) や MATCH_CONDITION(l.ts >= DATE_ADD(r.ts, INTERVAL 3 HOUR)) を指定できます。
  3. 等価キー句は ON または USING で記述できます。ON を使う場合は、等価（=）条件を AND で結んだものだけが許可されます。不等号条件（> など）、OR、リテラルとの比較（l.grp = 1 など）は ON 句では使用できません。
  4. マッチングに使うカラムや等価キーカラムに NULL がある場合は一致しません。左テーブルの行のマッチングカラムが NULL の場合、または同じグループに条件を満たす右テーブルの行がない場合、右側のカラムには NULL が入る（LEFT JOIN）か、その行が除外されます（INNER JOIN）。
  5. 右テーブルに、グループ化キーとマッチングカラムの値が同じ行が複数あり、すべてがマッチング条件を満たす場合、そのうちのどれかが返されます（結果は非決定的です）。

4.1.5 以降、ASOF JOIN の ON 句の等価キーには通常の = のみを使用でき、NULL セーフな等価演算子 <=> は使用できません。この制約は、通常の JOIN における <=> の動作には影響しません。

使用例​

データの準備​

以下の例では、trades テーブルと quotes テーブルを使用します。symbol でグループ化し、時刻の近さに基づいてマッチングします。

CREATE TABLE trades (
    trade_id INT,
    symbol VARCHAR(10),
    trade_time DATETIME,
    price DECIMAL(10, 2),
    quantity INT
) DISTRIBUTED BY HASH(trade_id) BUCKETS 3
PROPERTIES("replication_num" = "1");

CREATE TABLE quotes (
    quote_id INT,
    symbol VARCHAR(10),
    quote_time DATETIME,
    bid_price DECIMAL(10, 2),
    ask_price DECIMAL(10, 2)
) DISTRIBUTED BY HASH(quote_id) BUCKETS 3
PROPERTIES("replication_num" = "1");

INSERT INTO trades VALUES
(1, 'AAPL', '2024-01-01 10:00:05', 150.50, 100),
(2, 'AAPL', '2024-01-01 10:00:15', 151.00, 200),
(3, 'AAPL', '2024-01-01 10:00:25', 150.75, 150),
(4, 'GOOG', '2024-01-01 10:00:10', 2800.00, 50),
(5, 'GOOG', '2024-01-01 10:00:20', 2805.00, 75),
(6, 'MSFT', '2024-01-01 10:00:08', 380.00, 120);

INSERT INTO quotes VALUES
(1, 'AAPL', '2024-01-01 10:00:00', 150.00, 150.10),
(2, 'AAPL', '2024-01-01 10:00:10', 150.40, 150.60),
(3, 'AAPL', '2024-01-01 10:00:20', 150.90, 151.10),
(4, 'GOOG', '2024-01-01 10:00:05', 2795.00, 2800.00),
(5, 'GOOG', '2024-01-01 10:00:15', 2802.00, 2808.00),
(6, 'MSFT', '2024-01-01 10:00:00', 378.00, 380.00),
(7, 'MSFT', '2024-01-01 10:00:10', 379.50, 381.00);

例 1: 各取引の直前の気配値を探す（>=）​

シナリオ: 各取引について、同じ symbol の気配値のうち、quote_time が trade_time 以下で最新のものを探します。

SELECT t.trade_id, t.symbol, t.trade_time, t.price,
       q.quote_id, q.quote_time, q.bid_price, q.ask_price
FROM trades t
ASOF LEFT JOIN quotes q
    MATCH_CONDITION(t.trade_time >= q.quote_time)
    ON t.symbol = q.symbol
ORDER BY t.trade_id;
+----------+--------+---------------------+--------+----------+---------------------+-----------+-----------+
| trade_id | symbol | trade_time          | price  | quote_id | quote_time          | bid_price | ask_price |
+----------+--------+---------------------+--------+----------+---------------------+-----------+-----------+
|        1 | AAPL   | 2024-01-01 10:00:05 | 150.50 |        1 | 2024-01-01 10:00:00 |    150.00 |    150.10 |
|        2 | AAPL   | 2024-01-01 10:00:15 | 151.00 |        2 | 2024-01-01 10:00:10 |    150.40 |    150.60 |
|        3 | AAPL   | 2024-01-01 10:00:25 | 150.75 |        3 | 2024-01-01 10:00:20 |    150.90 |    151.10 |
|        4 | GOOG   | 2024-01-01 10:00:10 | 2800.00 |        4 | 2024-01-01 10:00:05 |   2795.00 |   2800.00 |
|        5 | GOOG   | 2024-01-01 10:00:20 | 2805.00 |        5 | 2024-01-01 10:00:15 |   2802.00 |   2808.00 |
|        6 | MSFT   | 2024-01-01 10:00:08 | 380.00 |        6 | 2024-01-01 10:00:00 |    378.00 |    380.00 |
+----------+--------+---------------------+--------+----------+---------------------+-----------+-----------+

たとえば、取引 #1（AAPL、10:00:05）は気配値 #1（AAPL、10:00:00）にマッチします。同じ symbol で、取引時刻以前の最新の気配値だからです。

例 2: 各取引の次の気配値を探す（<=）​

シナリオ: マッチングの方向を逆にして、各取引の後に発生する次の気配値を探します。

SELECT t.trade_id, t.symbol, t.trade_time, t.price,
       q.quote_id, q.quote_time, q.bid_price
FROM trades t
ASOF LEFT JOIN quotes q
    MATCH_CONDITION(t.trade_time <= q.quote_time)
    ON t.symbol = q.symbol
ORDER BY t.trade_id;
+----------+--------+---------------------+--------+----------+---------------------+-----------+
| trade_id | symbol | trade_time          | price  | quote_id | quote_time          | bid_price |
+----------+--------+---------------------+--------+----------+---------------------+-----------+
|        1 | AAPL   | 2024-01-01 10:00:05 | 150.50 |        2 | 2024-01-01 10:00:10 |    150.40 |
|        2 | AAPL   | 2024-01-01 10:00:15 | 151.00 |        3 | 2024-01-01 10:00:20 |    150.90 |
|        3 | AAPL   | 2024-01-01 10:00:25 | 150.75 |     NULL | NULL                |      NULL |
|        4 | GOOG   | 2024-01-01 10:00:10 | 2800.00 |        5 | 2024-01-01 10:00:15 |   2802.00 |
|        5 | GOOG   | 2024-01-01 10:00:20 | 2805.00 |     NULL | NULL                |      NULL |
|        6 | MSFT   | 2024-01-01 10:00:08 | 380.00 |        7 | 2024-01-01 10:00:10 |    379.50 |
+----------+--------+---------------------+--------+----------+---------------------+-----------+

取引 #3（AAPL、10:00:25）の後には気配値データがないため、右側には NULL が返されます。

例 3: INNER JOIN で一致しない行を除外する​

シナリオ: 一致する取引だけが必要で、NULL を含む行を後続の処理に渡したくない場合です。

SELECT t.trade_id, t.symbol, t.trade_time, t.price,
       q.quote_id, q.quote_time, q.bid_price
FROM trades t
ASOF INNER JOIN quotes q
    MATCH_CONDITION(t.trade_time >= q.quote_time)
    ON t.symbol = q.symbol
ORDER BY t.trade_id;
+----------+--------+---------------------+--------+----------+---------------------+-----------+
| trade_id | symbol | trade_time          | price  | quote_id | quote_time          | bid_price |
+----------+--------+---------------------+--------+----------+---------------------+-----------+
|        1 | AAPL   | 2024-01-01 10:00:05 | 150.50 |        1 | 2024-01-01 10:00:00 |    150.00 |
|        2 | AAPL   | 2024-01-01 10:00:15 | 151.00 |        2 | 2024-01-01 10:00:10 |    150.40 |
|        3 | AAPL   | 2024-01-01 10:00:25 | 150.75 |        3 | 2024-01-01 10:00:20 |    150.90 |
|        4 | GOOG   | 2024-01-01 10:00:10 | 2800.00 |        4 | 2024-01-01 10:00:05 |   2795.00 |
|        5 | GOOG   | 2024-01-01 10:00:20 | 2805.00 |        5 | 2024-01-01 10:00:15 |   2802.00 |
|        6 | MSFT   | 2024-01-01 10:00:08 | 380.00 |        6 | 2024-01-01 10:00:00 |    378.00 |
+----------+--------+---------------------+--------+----------+---------------------+-----------+

このデータセットでは、すべての取引に一致する気配値があるため、結果は例 1 と同じです。一致する気配値がない取引があれば、その行は除外されます。

例 4: 複数の等価条件でグループ化する​

シナリオ: product_id と region の両方でマッチングをグループ化し、各注文について同じ商品と地域で有効な最新の価格を探します。

SELECT o.order_id, o.product_id, o.region, o.order_time,
       p.price, p.effective_time
FROM orders o
ASOF LEFT JOIN prices p
    MATCH_CONDITION(o.order_time >= p.effective_time)
    ON o.product_id = p.product_id AND o.region = p.region
ORDER BY o.order_id;

例 5: MATCH_CONDITION で式を使用する​

シナリオ: 右側のタイムスタンプが左側より少なくとも 1 時間前である場合にのみマッチングを許可します。

SELECT l.id, l.ts, r.id AS rid, r.ts AS rts, r.data
FROM left_table l
ASOF LEFT JOIN right_table r
    MATCH_CONDITION(l.ts >= r.ts + INTERVAL 1 HOUR)
    ON l.grp = r.grp
ORDER BY l.id;

日時関数も使用できます。

MATCH_CONDITION(l.ts >= DATE_ADD(r.ts, INTERVAL 3 HOUR))
MATCH_CONDITION(DATE_SUB(l.ts, INTERVAL 1 HOUR) >= r.ts)

例 6: 多段階の ASOF JOIN​

ASOF JOIN は、ほかの ASOF JOIN や通常の JOIN と連結できます。

各注文に、有効な価格と在庫スナップショットの両方を対応付けます。

SELECT o.order_id, o.order_time,
       p.price, p.effective_time AS price_time,
       i.stock_level, i.snapshot_time AS inv_time
FROM orders o
ASOF LEFT JOIN prices p
    MATCH_CONDITION(o.order_time >= p.effective_time)
    ON o.product_id = p.product_id AND o.region = p.region
ASOF LEFT JOIN inventory i
    MATCH_CONDITION(o.order_time >= i.snapshot_time)
    ON o.product_id = i.product_id AND o.region = i.region
ORDER BY o.order_id;

ASOF JOIN は通常の JOIN と組み合わせることもできます。

SELECT o.order_id, prod.product_name,
       o.order_time, p.price
FROM orders o
INNER JOIN products prod ON o.product_id = prod.product_id
ASOF LEFT JOIN prices p
    MATCH_CONDITION(o.order_time >= p.effective_time)
    ON o.product_id = p.product_id AND o.region = p.region
ORDER BY o.order_id;

例 7: ASOF JOIN と集計​

シナリオ: symbol ごとの取引件数と、マッチした気配値の買い気配値の平均を計算します。

SELECT t.symbol,
       COUNT(*) AS trade_count,
       AVG(q.bid_price) AS avg_bid
FROM trades t
ASOF LEFT JOIN quotes q
    MATCH_CONDITION(t.trade_time >= q.quote_time)
    ON t.symbol = q.symbol
GROUP BY t.symbol
ORDER BY t.symbol;

例 8: 双方向のマッチングで前後のレコードを探す​

シナリオ: 各注文について、直前と直後の両方の有効な価格を探します。

SELECT o.order_id, o.order_time,
       p_before.price AS price_before,
       p_before.effective_time AS time_before,
       p_after.price AS price_after,
       p_after.effective_time AS time_after
FROM orders o
ASOF LEFT JOIN prices p_before
    MATCH_CONDITION(o.order_time >= p_before.effective_time)
    ON o.product_id = p_before.product_id AND o.region = p_before.region
ASOF LEFT JOIN prices p_after
    MATCH_CONDITION(o.order_time <= p_after.effective_time)
    ON o.product_id = p_after.product_id AND o.region = p_after.region
ORDER BY o.order_id;

例 9: 絶対的な近さではなく方向に基づくマッチング​

ASOF JOIN は、MATCH_CONDITION で指定した方向だけを検索します。左側と右側のレコード間の時刻差の絶対値は比較しません。

「前」を検索します。

WITH left_events AS (
    SELECT 1 AS event_id, 'AAPL' AS symbol, CAST('2024-01-01 10:00:06' AS DATETIME) AS event_time
),
right_events AS (
    SELECT 1 AS right_id, 'AAPL' AS symbol, CAST('2024-01-01 10:00:00' AS DATETIME) AS ref_time
    UNION ALL
    SELECT 2 AS right_id, 'AAPL' AS symbol, CAST('2024-01-01 10:00:08' AS DATETIME) AS ref_time
)
SELECT l.event_id, l.event_time, r.right_id, r.ref_time
FROM left_events l
ASOF LEFT JOIN right_events r
    MATCH_CONDITION(l.event_time >= r.ref_time)
    ON l.symbol = r.symbol;
+----------+---------------------+----------+---------------------+
| event_id | event_time          | right_id | ref_time            |
+----------+---------------------+----------+---------------------+
|        1 | 2024-01-01 10:00:06 |        1 | 2024-01-01 10:00:00 |
+----------+---------------------+----------+---------------------+

10:00:08 は左側の時刻からわずか 2 秒後で、10:00:00 は 6 秒前ですが、MATCH_CONDITION(l.event_time >= r.ref_time) がマッチングを許可するのは左側のタイムスタンプ以前の右テーブルのレコードだけです。そのため、結果は 10:00:00 になります。

「後」を検索する場合は逆になります。

WITH left_events AS (
    SELECT 1 AS event_id, 'AAPL' AS symbol, CAST('2024-01-01 10:00:06' AS DATETIME) AS event_time
),
right_events AS (
    SELECT 1 AS right_id, 'AAPL' AS symbol, CAST('2024-01-01 10:00:00' AS DATETIME) AS ref_time
    UNION ALL
    SELECT 2 AS right_id, 'AAPL' AS symbol, CAST('2024-01-01 10:00:08' AS DATETIME) AS ref_time
)
SELECT l.event_id, l.event_time, r.right_id, r.ref_time
FROM left_events l
ASOF LEFT JOIN right_events r
    MATCH_CONDITION(l.event_time <= r.ref_time)
    ON l.symbol = r.symbol;
+----------+---------------------+----------+---------------------+
| event_id | event_time          | right_id | ref_time            |
+----------+---------------------+----------+---------------------+
|        1 | 2024-01-01 10:00:06 |        2 | 2024-01-01 10:00:08 |
+----------+---------------------+----------+---------------------+

例 10: マッチング値が重複すると結果は非決定的になる​

右テーブルにグループ化キーとマッチング値が同じ行が複数ある場合、ASOF JOIN はそのうちのどれかを返す可能性があります。これは TIMESTAMPTZ 型にも当てはまります。

WITH left_events AS (
    SELECT 1 AS event_id, 'AAPL' AS symbol,
           CAST('2024-01-01 10:00:05 +00:00' AS TIMESTAMPTZ) AS event_time
),
right_events AS (
    SELECT 1 AS right_id, 'AAPL' AS symbol,
           CAST('2024-01-01 10:00:00 +00:00' AS TIMESTAMPTZ) AS ref_time, 'snapshot_a' AS tag
    UNION ALL
    SELECT 2 AS right_id, 'AAPL' AS symbol,
           CAST('2024-01-01 10:00:00 +00:00' AS TIMESTAMPTZ) AS ref_time, 'snapshot_b' AS tag
)
SELECT l.event_id, r.right_id, r.ref_time, r.tag
FROM left_events l
ASOF LEFT JOIN right_events r
    MATCH_CONDITION(l.event_time >= r.ref_time)
    ON l.symbol = r.symbol;
+----------+----------+---------------------------+------------+
| event_id | right_id | ref_time                  | tag        |
+----------+----------+---------------------------+------------+
|        1 |        1 | 2024-01-01 10:00:00+00:00 | snapshot_a |
+----------+----------+---------------------------+------------+

このクエリは、tag = snapshot_b の right_id = 2 を返す場合もあります。ビジネスロジックで決定的な結果が必要なら、ASOF JOIN を実行する前に右テーブルの重複を排除するか、事前に集計してください。

ウィンドウ関数を使用した等価な書き換え​

ASOF JOIN は、以下の LEFT JOIN と ROW_NUMBER() を組み合わせたパターンと意味的に等価ですが、実行性能は大幅に優れています。

-- Equivalent to: ASOF LEFT JOIN ... MATCH_CONDITION(l.ts >= r.ts)
SELECT id, rid, val FROM (
    SELECT l.id, r.id AS rid, r.val,
           ROW_NUMBER() OVER (PARTITION BY l.id ORDER BY r.ts DESC) AS rn
    FROM left_table l
    LEFT JOIN right_table r
        ON l.grp = r.grp AND l.ts >= r.ts
) t
WHERE rn = 1;

ベストプラクティス​

  • 時系列データの特定時点のクエリには ASOF JOIN を優先します。 ファクトテーブルの各行に対し、参照テーブルから最新または最も近いレコードを探す場合、ASOF JOIN が最も自然で効率的な方法です。
  • グループ化に適切な等価キーを追加します。 ON または USING 句にグループ化キーを含めます。グループを細かく限定するほど検索範囲が狭くなり、性能が向上します。
  • 適切な比較演算子を選びます。 同じタイムスタンプも一致に含める場合は >=、厳密に除外する場合は > を使います。逆方向でも同様です。
  • 一致しない行が不要なら ASOF INNER JOIN を優先します。 NULL を含む行の生成を避け、後続の処理を簡単にできます。
  • 決定的な結果が必要なら右テーブルの重複を排除します。 右テーブルにグループ化キーとマッチングカラムの値が同じ行が複数あると、ASOF JOIN はそのうちのどれかを返す可能性があります。
  • 時間差を設けたマッチングには式を使います。 たとえば、MATCH_CONDITION(l.ts >= r.ts + INTERVAL 1 HOUR) は少なくとも 1 時間の間隔を必要とします。
Report issue