カテゴリ：: クエリ構文

ASOF JOIN¶

ASOF JOIN 操作は、タイムスタンプ値が互いに続くか、先行するか、または完全に一致することに基づいて、2つのテーブルから行を結合します。最初の（または左側の）テーブルの各行に対して、結合は2番目の（または右側の）テーブルで最も近いタイムスタンプ値を持つ1つの行を検出します。右側の条件に該当する行はもっとも近い一致であり、指定された比較演算子に応じて、時間的に等しい、時間的に早い、または時間的に遅い可能性があります。

このトピックでは、 FROM 句で ASOF JOIN 構文を使用する方法について説明します。ASOF 結合の概念的な説明の詳細については、時系列データの分析をご参照ください。

内側や外側の結合など、他の標準的な結合型の構文を網羅する JOIN もご参照ください。

構文¶

以下の FROM 句の構文は ASOF JOIN に特有です。

FROM <left_table> ASOF JOIN <right_table>
  MATCH_CONDITION ( <left_table.timecol> <comparison_operator> <right_table.timecol> )
  [ ON <table.col> = <table.col> [ AND ... ] | USING ( <column_list> ) ]

Copy

パラメーター¶

FROM

FROM 句の最初（または左側）のテーブルには、2番目（または右側）のテーブルの記録に（時間的に）続くか、先行するか、正確に同期する記録が含まれていると仮定されます。左側のテーブルに一致する行がない場合、右側のテーブルの列はnullパディングされます。

通常のテーブルやビューに加えて、 ASOF JOIN では任意のオブジェクト参照を使用することができます。 FROM をご参照ください。

ASOF JOIN は、結合がサポートされているほとんどのコンテキストで使用できます。いくつかの制限事項については、使用上の注意をご参照ください。

MATCH_CONDITION ( left_table.timecol comparison_operator right_table.timecol )

この条件は、比較される各テーブルの特定のタイムスタンプ列を指定します。

条件において、テーブルの順序は重要です。左側のテーブルを最初に指定する必要があります。
括弧は必須です。
比較演算子は、 >=、 <=、 >、 < のいずれかにする必要があります。等号演算子（=）はサポートされていません。
DATE、 TIME、 DATETIME、 TIMESTAMP、 TIMESTAMP_LTZ、 TIMESTAMP_NTZ、 TIMESTAMP_TZ のデータ型すべてがサポートされています。
一致条件に NUMBER 列を使うこともできます。たとえば、 NUMBER 列に UNIX タイムスタンプ（1月1日1970からの経過秒数を定義）があるとします。
一致する2つの列のデータ型は完全に同じである必要はありませんが、互換性が必要です。

ON table.col = table.col [ AND ... ] | USING (column_list)

オプションの ON または USING 句は、クエリの結果を論理的にグループ化する目的で、2つのテーブルの列に1つ以上の等号条件を定義します。

ON と USING に関する一般的な情報については、 JOIN をご参照ください。USING で指定された結合は、中間結果セットにどちらか一方の結合列を投影し、両方は投影しないことに注意してください。ON 句で指定された結合は、両方の結合列を投影します。

次の注意事項は、 ASOF JOIN に固有のものです。

ON 句にある比較演算子は等号（=）にする必要があります。
ON 句に離接詞（OR で接続された条件）を含めることはできません。AND で接続された条件はサポートされます。
条件の各側は、結合内にある2つのテーブルのうち1つだけを参照する必要があります。ただし、テーブル参照の順序は関係ありません。
各条件は括弧で囲むことができますが、括弧は必須ではありません。

結合動作の詳細と ON 条件の代わりに USING 条件を指定もご参照ください。

使用上の注意¶

指定された行に一致するものが右側のテーブルにない場合、右側のテーブルから選択された列は結果でnullパディングされます。（ASOF 結合はこの点で左外側結合に類似しています。）
一致条件に TIME 列を使用する場合（タイムスタンプ型のいずれかではなく）は、 ASOF JOIN クエリ結果の表示時に比較される正確な TIME 値を確認するために、 TIME_OUTPUT_FORMAT パラメーターの設定が必要になる場合があります。デフォルトでは、 TIME 列の表示はミリ秒単位が切り捨てられます。一致条件の TIME 列をご参照ください。
各結合ですべての構文ルールに従う限り、同じクエリで複数の ASOF 結合を使用することができます。各結合では、その直後に MATCH_CONDITION を続ける必要があります。1つの MATCH_CONDITION を複数の ASOF 結合に適用することはできません。クエリ内における複数の ASOF 結合をご参照ください。
ASOF 結合は、 LATERAL テーブル関数または LATERAL インラインビューとの結合ではサポートされません。側方結合の詳細については、 LATERAL をご参照ください。
自己参照を含む ASOF 結合は、 RECURSIVE 共通テーブル式（CTE）では使用できません。CTEs については、 WITH をご参照ください。
ASOF JOIN クエリの EXPLAIN 出力は、 ON （または USING）条件と MATCH_CONDITION を識別します。たとえば、テキストや表形式の場合、プランのテーブルスキャンの上に次のようなテキストが表示されます。
```
->ASOF Join  joinKey: (S.LOCATION = R.LOCATION) AND (S.STATE = R.STATE),
  matchCondition: (S.OBSERVED >= R.OBSERVED)
```
クエリプロファイルは、プラン内の ASOF JOIN 操作も明確に識別します。この例では、テーブルスキャンが左側のテーブルから22M行を読み込んでいることがわかります。結合によりすべてが保たれています。プロファイルは、結合の一致条件も表示します。

テーブルスキャンがその上の ASOF JOIN 演算子に行を供給していることを示すクエリプロファイル。

結合動作の詳細¶

ASOF JOIN に対するオプションの ON （または USING）条件は、最終的に一致した行が必要な一致条件によって選択される前に、テーブル行をグループ化またはパーティション分割する方法を提供します。結合されたテーブルの行を、テーブルが共有する1つ以上のディメンション（株式記号、場所、都市、州、会社名など）でグループ化する場合は、 ON 条件を使用します。ON 条件を使用しない場合、左側のテーブルの各行は、最終結果セット内にある右側のテーブルから任意の行と(時間的に)適合される可能性があります。

以下の例では、テーブル left_table と right_table は、列 c1 に値 A、 B などを持ち、列 c2 に値 1、 2 などを持ちます。列 c3 は TIME 列、 c4 は数値（注目する列）です。

まず、テーブルを2つ作成してロードします。

CREATE OR REPLACE TABLE left_table (
  c1 VARCHAR(1),
  c2 TINYINT,
  c3 TIME,
  c4 NUMBER(3,2)
);

CREATE OR REPLACE TABLE right_table (
  c1 VARCHAR(1),
  c2 TINYINT,
  c3 TIME,
  c4 NUMBER(3,2)
);

INSERT INTO left_table VALUES
  ('A',1,'09:15:00',3.21),
  ('A',2,'09:16:00',3.22),
  ('B',1,'09:17:00',3.23),
  ('B',2,'09:18:00',4.23);

INSERT INTO right_table VALUES
  ('A',1,'09:14:00',3.19),
  ('B',1,'09:16:00',3.04);

Copy

SELECT * FROM left_table ORDER BY c1, c2;

Copy

+----+----+----------+------+
| C1 | C2 | C3       |   C4 |
|----+----+----------+------|
| A  |  1 | 09:15:00 | 3.21 |
| A  |  2 | 09:16:00 | 3.22 |
| B  |  1 | 09:17:00 | 3.23 |
| B  |  2 | 09:18:00 | 4.23 |
+----+----+----------+------+

SELECT * FROM right_table ORDER BY c1, c2;

Copy

+----+----+----------+------+
| C1 | C2 | C3       |   C4 |
|----+----+----------+------|
| A  |  1 | 09:14:00 | 3.19 |
| B  |  1 | 09:16:00 | 3.04 |
+----+----+----------+------+

c1 と c2 が共にクエリの ON 条件列であれば、 A と 1、 A と 2、 B と 1、または B と 2 が両方のテーブルにある時にのみ、左側のテーブルの行は右側のテーブルの行と一致します。そのような値に一致するものが見つからない場合、右側のテーブルの列はnullパディングされます。

SELECT *
  FROM left_table l ASOF JOIN right_table r
    MATCH_CONDITION(l.c3>=r.c3)
    ON(l.c1=r.c1 and l.c2=r.c2)
  ORDER BY l.c1, l.c2;

Copy

+----+----+----------+------+------+------+----------+------+
| C1 | C2 | C3       |   C4 | C1   | C2   | C3       |   C4 |
|----+----+----------+------+------+------+----------+------|
| A  |  1 | 09:15:00 | 3.21 | A    |  1   | 09:14:00 | 3.19 |
| A  |  2 | 09:16:00 | 3.22 | NULL | NULL | NULL     | NULL |
| B  |  1 | 09:17:00 | 3.23 | B    |  1   | 09:16:00 | 3.04 |
| B  |  2 | 09:18:00 | 4.23 | NULL | NULL | NULL     | NULL |
+----+----+----------+------+------+------+----------+------+

ON 条件が削除されると、 c1 と c2 にある値の任意の組み合わせが最終結果で一致する可能性があります。結果を決定するのは、一致条件のみです。

SELECT *
  FROM left_table l ASOF JOIN right_table r
    MATCH_CONDITION(l.c3>=r.c3)
  ORDER BY l.c1, l.c2;

Copy

+----+----+----------+------+----+----+----------+------+
| C1 | C2 | C3       |   C4 | C1 | C2 | C3       |   C4 |
|----+----+----------+------+----+----+----------+------|
| A  |  1 | 09:15:00 | 3.21 | A  |  1 | 09:14:00 | 3.19 |
| A  |  2 | 09:16:00 | 3.22 | B  |  1 | 09:16:00 | 3.04 |
| B  |  1 | 09:17:00 | 3.23 | B  |  1 | 09:16:00 | 3.04 |
| B  |  2 | 09:18:00 | 4.23 | B  |  1 | 09:16:00 | 3.04 |
+----+----+----------+------+----+----+----------+------+

右側のテーブルに「結び付き」が存在する場合の期待される動作¶

ASOF JOIN クエリは常に、左側のテーブルの単一行と右側のテーブルの単一行の一致を試みます。この動作は、右側のテーブルにある2つ（またはそれ以上）の行が同一で、結合の対象となる場合でも当てはまります。このような結び付きがある場合は、同じ結合クエリを複数回実行すると、異なる結果になる可能性があります。結果は非決定的です。なぜなら、同列の任意の行が返されるからです。ASOF JOIN クエリの結果に確信が持てない場合は、右側のテーブルにある行のタイムスタンプ値が完全に一致するかどうかを確認します。

たとえば、前のセクションの例と同じテーブルを使って、 right_table に right_id 列を追加し、以下の行を挿入します。

CREATE OR REPLACE TABLE right_table
  (c1 VARCHAR(1),
  c2 TINYINT,
  c3 TIME,
  c4 NUMBER(3,2),
  right_id VARCHAR(2));

INSERT INTO right_table VALUES
  ('A',1,'09:14:00',3.19,'A1'),
  ('A',1,'09:14:00',3.19,'A2'),
  ('B',1,'09:16:00',3.04,'B1');

SELECT * FROM right_table ORDER BY 1, 2;

Copy

+----+----+----------+------+----------+
| C1 | C2 | C3       |   C4 | RIGHT_ID |
|----+----+----------+------+----------|
| A  |  1 | 09:14:00 | 3.19 | A1       |
| A  |  1 | 09:14:00 | 3.19 | A2       |
| B  |  1 | 09:16:00 | 3.04 | B1       |
+----+----+----------+------+----------+

right_id の値以外、2つの行は同じです。次の ASOF JOIN クエリを実行します。

SELECT *
  FROM left_table l ASOF JOIN right_table r
    MATCH_CONDITION(l.c3>=r.c3)
  ORDER BY l.c1, l.c2;

Copy

+----+----+----------+------+----+----+----------+------+----------+
| C1 | C2 | C3       |   C4 | C1 | C2 | C3       |   C4 | RIGHT_ID |
|----+----+----------+------+----+----+----------+------+----------|
| A  |  1 | 09:15:00 | 3.21 | A  |  1 | 09:14:00 | 3.19 | A2       |
| A  |  2 | 09:16:00 | 3.22 | B  |  1 | 09:16:00 | 3.04 | B1       |
| B  |  1 | 09:17:00 | 3.23 | B  |  1 | 09:16:00 | 3.04 | B1       |
| B  |  2 | 09:18:00 | 4.23 | B  |  1 | 09:16:00 | 3.04 | B1       |
+----+----+----------+------+----+----+----------+------+----------+

right_table からの行 A1 と A2 は両方とも結合の対象ですが、 A2 のみが返されることに注意してください。その後同じクエリを実行すると、 A1 が代わりに返される可能性があります。

ASOF JOIN クエリを書き換えて、右側のテーブルのスキャンを削減します。¶

左側のテーブルにある ON または USING の結合列のカーディナリティが、右側のテーブルにある結合列のカーディナリティよりも小さい場合、オプティマイザーは右側のテーブルから一致していない行を削除しません。そのため、結合に必要な行数よりも多くの行が右側のテーブルからスキャンされます。この動作は通常、左側のテーブルにある非結合列に対する高度な選択フィルターがクエリに含まれており、そのフィルターによって結合列のカーディナリティが減少している場合に発生します。

この問題を回避するには、結合の対象となる行を手動で減らします。たとえば、元のクエリには以下のパターンがあり、 t1.c1 には t2.c1 よりも小さいカーディナリティがあります。

SELECT ...
  FROM t1
    ASOF JOIN t2
      MATCH_CONDITION(...)
      ON t1.c1 = t2.c1
  WHERE t1 ...;

Copy

クエリを以下のように書き換えると、 t2.c1 の値が t1.c1 にある行を t2 から手動で選択することができます。

WITH t1 AS (SELECT * FROM t1 WHERE t1 ...)
SELECT ...
  FROM t1
    ASOF JOIN (SELECT * FROM t2 WHERE t2.c1 IN (SELECT t1.c1 FROM t1)) AS t2
      MATCH_CONDITION(...)
      ON t1.c1 = t2.c1;

Copy

オブジェクト名とエイリアスとしての ASOF および MATCH_CONDITION の使用¶

SELECT コマンド構文における ASOF および MATCH_CONDITION キーワードの使用は制限されています。

SELECT ステートメントがテーブル、ビュー、インラインビューの名前として ASOF または MATCH_CONDITION を使用する場合、次のように識別する必要があります。
- オブジェクトが二重引用符で囲まれた名前で作成された場合は、同じ二重引用符で囲まれた名前を使用する。
- オブジェクトが二重引用符なしの名前で作成された場合は、二重引用符と大文字を使用する。
例えば、次のようなステートメントは使用できなくなり、エラーが返されます。
```
SELECT * FROM asof;

WITH match_condition AS (SELECT * FROM T1) SELECT * FROM match_condition;
```
Copy
二重引用符でオブジェクトを作成した場合は、二重引用符を使用して問題を修正する。
```
SELECT * FROM "asof";

WITH "match_condition" AS (SELECT * FROM T1) SELECT * FROM "match_condition";
```
Copy
二重引用符なしでオブジェクトを作成した場合は、二重引用符と大文字を使用して問題を修正する。
```
SELECT * FROM "ASOF";

WITH "MATCH_CONDITION" AS (SELECT * FROM T1) SELECT * FROM "MATCH_CONDITION";
```
Copy
引用符で囲まれていない識別子もご参照ください。
SELECT ステートメントがエイリアスとして ASOF または MATCH_CONDITION を使用する場合、エイリアスの前に AS を使用するか、エイリアスを二重引用符で囲む必要があります。例えば、次のようなステートメントは使用できなくなり、エラーが返されます。
```
SELECT * FROM t1 asof;

SELECT * FROM t2 match_condition;
```
Copy
次のいずれかの方法で問題を解決します。
```
SELECT * FROM t1 AS asof;

SELECT * FROM t1 "asof";

SELECT * FROM t2 AS match_condition;

SELECT * FROM t2 "match_condition";
```
Copy

例¶

次の例は、 ASOF JOIN クエリの予期される動作を示しています。まず、 2つのテーブルを最も近い一致で結合（アライメント）でクエリを実行し、ここの例に進みます。

NULL パディングの結果¶

trades テーブルに trades と quotes の既存の行より1日早い日付の新しい行を挿入します。

INSERT INTO trades VALUES('SNOW','2023-09-30 12:02:55.000',3000);

Copy

+-------------------------+
| number of rows inserted |
|-------------------------|
|                       1 |
+-------------------------+

ここで、最初の例のクエリをもう一度実行します。このクエリは4行を返しますが、新しい行はnullパディングされていることに注意してください。一致条件に該当する quotes テーブルに行がありません。 trades の列が返され、 quotes の対応する列はnullパディングされます。

SELECT t.stock_symbol, t.trade_time, t.quantity, q.quote_time, q.price
  FROM trades t ASOF JOIN quotes q
    MATCH_CONDITION(t.trade_time >= quote_time)
    ON t.stock_symbol=q.stock_symbol
  ORDER BY t.stock_symbol;

Copy

+--------------+-------------------------+----------+-------------------------+--------------+
| STOCK_SYMBOL | TRADE_TIME              | QUANTITY | QUOTE_TIME              |        PRICE |
|--------------+-------------------------+----------+-------------------------+--------------|
| AAPL         | 2023-10-01 09:00:05.000 |     2000 | 2023-10-01 09:00:03.000 | 139.00000000 |
| SNOW         | 2023-09-30 12:02:55.000 |     3000 | NULL                    |         NULL |
| SNOW         | 2023-10-01 09:00:05.000 |     1000 | 2023-10-01 09:00:02.000 | 163.00000000 |
| SNOW         | 2023-10-01 09:00:10.000 |     1500 | 2023-10-01 09:00:08.000 | 165.00000000 |
+--------------+-------------------------+----------+-------------------------+--------------+

一致条件内で異なる比較演算子を使用¶

前の例に続いて、一致条件の比較演算子が変更されると、クエリの結果が再び変わります。次のクエリは <= 演算子（>= の代わりに）を指定します。

SELECT t.stock_symbol, t.trade_time, t.quantity, q.quote_time, q.price
  FROM trades t ASOF JOIN quotes q
    MATCH_CONDITION(t.trade_time <= quote_time)
    ON t.stock_symbol=q.stock_symbol
  ORDER BY t.stock_symbol;

Copy

+--------------+-------------------------+----------+-------------------------+--------------+
| STOCK_SYMBOL | TRADE_TIME              | QUANTITY | QUOTE_TIME              |        PRICE |
|--------------+-------------------------+----------+-------------------------+--------------|
| AAPL         | 2023-10-01 09:00:05.000 |     2000 | 2023-10-01 09:00:07.000 | 142.00000000 |
| SNOW         | 2023-10-01 09:00:10.000 |     1500 | NULL                    |         NULL |
| SNOW         | 2023-10-01 09:00:05.000 |     1000 | 2023-10-01 09:00:07.000 | 166.00000000 |
| SNOW         | 2023-09-30 12:02:55.000 |     3000 | 2023-10-01 09:00:01.000 | 166.00000000 |
+--------------+-------------------------+----------+-------------------------+--------------+

小なり演算子および大なり比較演算子もご参照ください。

ON 条件の代わりに USING 条件を指定¶

ASOF JOIN クエリでは、 ON 条件または USING 条件を使用できます。次のクエリは前述のクエリと同じですが、 ON を USING に置き換えています。構文 USING(stock_symbol) は、条件 t.stock_symbol=q.stock_symbol を意味します。

SELECT t.stock_symbol, t.trade_time, t.quantity, q.quote_time, q.price
  FROM trades t ASOF JOIN quotes q
    MATCH_CONDITION(t.trade_time <= quote_time)
    USING(stock_symbol)
  ORDER BY t.stock_symbol;

Copy

3番目のテーブルへの内部結合¶

以下の例では、各株式シンボルの会社名を選択するために、結合に3番目の companies テーブルを追加しています。通常の INNER JOIN に ON 条件（または他の標準的な結合構文）を使用して、3番目のテーブルを追加することができます。しかし、 USING(stock_symbol) は stock_symbol への参照が曖昧になるため、ここでは機能しないことに注意してください。

CREATE OR REPLACE TABLE companies(
  stock_symbol VARCHAR(4),
  company_name VARCHAR(100)
);

 INSERT INTO companies VALUES
  ('NVDA','NVIDIA Corp'),
  ('TSLA','Tesla Inc'),
  ('SNOW','Snowflake Inc'),
  ('AAPL','Apple Inc')
;

Copy

SELECT t.stock_symbol, c.company_name, t.trade_time, t.quantity, q.quote_time, q.price
  FROM trades t ASOF JOIN quotes q
    MATCH_CONDITION(t.trade_time >= quote_time)
    ON t.stock_symbol=q.stock_symbol
    INNER JOIN companies c ON c.stock_symbol=t.stock_symbol
  ORDER BY t.stock_symbol;

Copy

+--------------+---------------+-------------------------+----------+-------------------------+--------------+
| STOCK_SYMBOL | COMPANY_NAME  | TRADE_TIME              | QUANTITY | QUOTE_TIME              |        PRICE |
|--------------+---------------+-------------------------+----------+-------------------------+--------------|
| AAPL         | Apple Inc     | 2023-10-01 09:00:05.000 |     2000 | 2023-10-01 09:00:03.000 | 139.00000000 |
| SNOW         | Snowflake Inc | 2023-09-30 12:02:55.000 |     3000 | NULL                    |         NULL |
| SNOW         | Snowflake Inc | 2023-10-01 09:00:05.000 |     1000 | 2023-10-01 09:00:02.000 | 163.00000000 |
| SNOW         | Snowflake Inc | 2023-10-01 09:00:10.000 |     1500 | 2023-10-01 09:00:08.000 | 165.00000000 |
+--------------+---------------+-------------------------+----------+-------------------------+--------------+

タイムスタンプとしての数値¶

次の例は、一致条件が数値を比較できることを示しています。この場合、テーブルには UNIX のタイムスタンプ値が NUMBER(38,0)列に格納されています。 1696150805 は 2023-10-30 10:20:05.000 と等価です（1696150802 より3秒遅い）。

SELECT * FROM trades_unixtime;

Copy

+--------------+------------+----------+--------------+
| STOCK_SYMBOL | TRADE_TIME | QUANTITY |        PRICE |
|--------------+------------+----------+--------------|
| SNOW         | 1696150805 |      100 | 165.33300000 |
+--------------+------------+----------+--------------+

SELECT * FROM quotes_unixtime;

Copy

+--------------+------------+----------+--------------+--------------+
| STOCK_SYMBOL | QUOTE_TIME | QUANTITY |          BID |          ASK |
|--------------+------------+----------+--------------+--------------|
| SNOW         | 1696150802 |      100 | 166.00000000 | 165.00000000 |
+--------------+------------+----------+--------------+--------------+

SELECT *
  FROM trades_unixtime tu
    ASOF JOIN quotes_unixtime qu
    MATCH_CONDITION(tu.trade_time>=qu.quote_time);

Copy

+--------------+------------+----------+--------------+--------------+------------+----------+--------------+--------------+
| STOCK_SYMBOL | TRADE_TIME | QUANTITY |        PRICE | STOCK_SYMBOL | QUOTE_TIME | QUANTITY |          BID |          ASK |
|--------------+------------+----------+--------------+--------------+------------+----------+--------------+--------------|
| SNOW         | 1696150805 |      100 | 165.33300000 | SNOW         | 1696150802 |      100 | 166.00000000 | 165.00000000 |
+--------------+------------+----------+--------------+--------------+------------+----------+--------------+--------------+

一致条件内の TIME 列¶

次の例では、気象観測を含んでいるテーブルを結合します。これらのテーブルの観測は、 TIME 列に記録されています。テーブルの作成とロードは次のとおりです。

CREATE OR REPLACE TABLE raintime(
  observed TIME(9),
  location VARCHAR(40),
  state VARCHAR(2),
  observation NUMBER(5,2)
);

INSERT INTO raintime VALUES
  ('14:42:59.230', 'Ahwahnee', 'CA', 0.90),
  ('14:42:59.001', 'Oakhurst', 'CA', 0.50),
  ('14:42:44.435', 'Reno', 'NV', 0.00)
;

CREATE OR REPLACE TABLE preciptime(
  observed TIME(9),
  location VARCHAR(40),
  state VARCHAR(2),
  observation NUMBER(5,2)
);

INSERT INTO preciptime VALUES
  ('14:42:59.230', 'Ahwahnee', 'CA', 0.91),
  ('14:42:59.001', 'Oakhurst', 'CA', 0.51),
  ('14:41:44.435', 'Las Vegas', 'NV', 0.01),
  ('14:42:44.435', 'Reno', 'NV', 0.01),
  ('14:40:34.000', 'Bozeman', 'MT', 1.11)
;

CREATE OR REPLACE TABLE snowtime(
  observed TIME(9),
  location VARCHAR(40),
  state VARCHAR(2),
  observation NUMBER(5,2)
);

INSERT INTO snowtime VALUES
  ('14:42:59.199', 'Fish Camp', 'CA', 3.20),
  ('14:42:44.435', 'Reno', 'NV', 3.00),
  ('14:43:01.000', 'Lake Tahoe', 'CA', 4.20),
  ('14:42:45.000', 'Bozeman', 'MT', 1.80)
;

Copy

最初のクエリを実行すると、結果セット（14:42:59、 14:42:44）で、 TIME 値の一部がまったく同じに表示されます。

SELECT * FROM preciptime p ASOF JOIN snowtime s MATCH_CONDITION(p.observed>=s.observed)
  ORDER BY p.observed;

Copy

+----------+-----------+-------+-------------+----------+-----------+-------+-------------+
| OBSERVED | LOCATION  | STATE | OBSERVATION | OBSERVED | LOCATION  | STATE | OBSERVATION |
|----------+-----------+-------+-------------+----------+-----------+-------+-------------|
| 14:40:34 | Bozeman   | MT    |        1.11 | NULL     | NULL      | NULL  |        NULL |
| 14:41:44 | Las Vegas | NV    |        0.01 | NULL     | NULL      | NULL  |        NULL |
| 14:42:44 | Reno      | NV    |        0.01 | 14:42:44 | Reno      | NV    |        3.00 |
| 14:42:59 | Oakhurst  | CA    |        0.51 | 14:42:45 | Bozeman   | MT    |        1.80 |
| 14:42:59 | Ahwahnee  | CA    |        0.91 | 14:42:59 | Fish Camp | CA    |        3.20 |
+----------+-----------+-------+-------------+----------+-----------+-------+-------------+

ミリ秒を含めて TIME 値をより正確に表示するには、以下の ALTER SESSION コマンドを実行し、 ASOF JOIN クエリを再度実行します。

ALTER SESSION SET TIME_OUTPUT_FORMAT = 'HH24:MI:SS.FF3';

Copy

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

SELECT * FROM preciptime p ASOF JOIN snowtime s MATCH_CONDITION(p.observed>=s.observed)
  ORDER BY p.observed;

Copy

+--------------+-----------+-------+-------------+--------------+-----------+-------+-------------+
| OBSERVED     | LOCATION  | STATE | OBSERVATION | OBSERVED     | LOCATION  | STATE | OBSERVATION |
|--------------+-----------+-------+-------------+--------------+-----------+-------+-------------|
| 14:40:34.000 | Bozeman   | MT    |        1.11 | NULL         | NULL      | NULL  |        NULL |
| 14:41:44.435 | Las Vegas | NV    |        0.01 | NULL         | NULL      | NULL  |        NULL |
| 14:42:44.435 | Reno      | NV    |        0.01 | 14:42:44.435 | Reno      | NV    |        3.00 |
| 14:42:59.001 | Oakhurst  | CA    |        0.51 | 14:42:45.000 | Bozeman   | MT    |        1.80 |
| 14:42:59.230 | Ahwahnee  | CA    |        0.91 | 14:42:59.199 | Fish Camp | CA    |        3.20 |
+--------------+-----------+-------+-------------+--------------+-----------+-------+-------------+

クエリ内の複数の ASOF 結合¶

次の例は、2つ以上の ASOF 結合を単一のクエリブロックで接続する方法を示しています。3つのテーブル（snowtime、 raintime、 preciptime）はすべて、特定の時刻に特定の場所で記録された気象観測を含んでいます。注目する列は observation 列です。行は州ごとに論理的にグループ化されています。

ALTER SESSION SET TIME_OUTPUT_FORMAT = 'HH24:MI:SS.FF3';

SELECT *
  FROM snowtime s
    ASOF JOIN raintime r
      MATCH_CONDITION(s.observed>=r.observed)
      ON s.state=r.state
    ASOF JOIN preciptime p
      MATCH_CONDITION(s.observed>=p.observed)
      ON s.state=p.state
  ORDER BY s.observed;

Copy

+--------------+------------+-------+-------------+--------------+----------+-------+-------------+--------------+----------+-------+-------------+
| OBSERVED     | LOCATION   | STATE | OBSERVATION | OBSERVED     | LOCATION | STATE | OBSERVATION | OBSERVED     | LOCATION | STATE | OBSERVATION |
|--------------+------------+-------+-------------+--------------+----------+-------+-------------+--------------+----------+-------+-------------|
| 14:42:44.435 | Reno       | NV    |        3.00 | 14:42:44.435 | Reno     | NV    |        0.00 | 14:42:44.435 | Reno     | NV    |        0.01 |
| 14:42:45.000 | Bozeman    | MT    |        1.80 | NULL         | NULL     | NULL  |        NULL | 14:40:34.000 | Bozeman  | MT    |        1.11 |
| 14:42:59.199 | Fish Camp  | CA    |        3.20 | 14:42:59.001 | Oakhurst | CA    |        0.50 | 14:42:59.001 | Oakhurst | CA    |        0.51 |
| 14:43:01.000 | Lake Tahoe | CA    |        4.20 | 14:42:59.230 | Ahwahnee | CA    |        0.90 | 14:42:59.230 | Ahwahnee | CA    |        0.91 |
+--------------+------------+-------+-------------+--------------+----------+-------+-------------+--------------+----------+-------+-------------+

小なり比較演算子および大なり比較演算子¶

前の例に続いて、2つの ASOF 結合が指定されていますが、今回は最初の一致条件で > 演算子が使用され、2番目の一致条件で < 演算子が使用されています。その結果、3つのテーブルすべてからデータを返す行が1つ、2つのテーブルからデータを返す行が3つになります。結果セットの列の多くはnullパディングされています。

論理的には、このクエリは snowtime テーブルの観測時刻が raintime テーブルの観測時刻より遅く、 preciptime テーブルの観測時刻より早い行を1行のみ見つけます。

SELECT *
  FROM snowtime s
    ASOF JOIN raintime r
      MATCH_CONDITION(s.observed>r.observed)
      ON s.state=r.state
    ASOF JOIN preciptime p
      MATCH_CONDITION(s.observed<p.observed)
      ON s.state=p.state
  ORDER BY s.observed;

Copy

+--------------+------------+-------+-------------+--------------+-----------+-------+-------------+--------------+----------+-------+-------------+
| OBSERVED     | LOCATION   | STATE | OBSERVATION | OBSERVED     | LOCATION  | STATE | OBSERVATION | OBSERVED     | LOCATION | STATE | OBSERVATION |
|--------------+------------+-------+-------------+--------------+-----------+-------+-------------+--------------+----------+-------+-------------|
| 14:42:44.435 | Reno       | NV    |        3.00 | 14:41:44.435 | Las Vegas | NV    |        0.00 | NULL         | NULL     | NULL  |        NULL |
| 14:42:45.000 | Bozeman    | MT    |        1.80 | NULL         | NULL      | NULL  |        NULL | NULL         | NULL     | NULL  |        NULL |
| 14:42:59.199 | Fish Camp  | CA    |        3.20 | 14:42:59.001 | Oakhurst  | CA    |        0.50 | 14:42:59.230 | Ahwahnee | CA    |        0.91 |
| 14:43:01.000 | Lake Tahoe | CA    |        4.20 | 14:42:59.230 | Ahwahnee  | CA    |        0.90 | NULL         | NULL     | NULL  |        NULL |
+--------------+------------+-------+-------------+--------------+-----------+-------+-------------+--------------+----------+-------+-------------+

予期されるエラーケースの例¶

以下の例は、予期される構文エラーを返すクエリを示しています。

snowtime s が左側のテーブルであると宣言しているため、右側のテーブル preciptime p を参照して一致条件を開始することはできません。

SELECT * FROM snowtime s ASOF JOIN preciptime p MATCH_CONDITION(p.observed>=s.observed);

Copy

010002 (42601): SQL compilation error:
MATCH_CONDITION clause is invalid: The left side allows only column references from the left side table, and the right side allows only column references from the right side table.

一致条件では、 >=、 <=、 >、 < 演算子のみを使用できます。

SELECT * FROM preciptime p ASOF JOIN snowtime s MATCH_CONDITION(p.observed=s.observed);

Copy

010001 (42601): SQL compilation error:
MATCH_CONDITION clause is invalid: Only comparison operators '>=', '>', '<=' and '<' are allowed. Keywords such as AND and OR are not allowed.

ASOF JOIN の ON 句には等号条件を含める必要があります。

SELECT *
  FROM preciptime p ASOF JOIN snowtime s
  MATCH_CONDITION(p.observed>=s.observed)
  ON s.state>=p.state;

Copy

010010 (42601): SQL compilation error:
ON clause for ASOF JOIN must contain conjunctions of equality conditions only. Disjunctions are not allowed. Each side of an equality condition must only refer to either the left table or the right table. S.STATE >= P.STATE is invalid.

ON 句の等号条件には、結合解除を含めることはできません。

SELECT *
  FROM preciptime p ASOF JOIN snowtime s
  MATCH_CONDITION(p.observed>=s.observed)
  ON s.state=p.state OR s.location=p.location;

Copy

010010 (42601): SQL compilation error:
ON clause for ASOF JOIN must contain conjunctions of equality conditions only. Disjunctions are not allowed. Each side of an equality condition must only refer to either the left table or the right table. (S.STATE = P.STATE) OR (S.LOCATION = P.LOCATION) is invalid.

ASOF 結合は、 LATERAL インラインビューでは使用できません。

SELECT t1.a "t1a", t2.a "t2a"
  FROM t1 ASOF JOIN
    LATERAL(SELECT a FROM t2 WHERE t1.b = t2.b) t2
    MATCH_CONDITION(t1.a >= t2.a)
  ORDER BY 1,2;

Copy

010004 (42601): SQL compilation error:
ASOF JOIN is not supported for joins with LATERAL table functions or LATERAL views.