Kategorien:

Abfragesyntax

ASOF JOIN

Eine ASOF JOIN-Operation kombiniert Zeilen aus zwei Tabellen auf der Grundlage von Zeitstempelwerten, die aufeinander folgen, einander vorausgehen oder genau übereinstimmen. Für jede Zeile in der ersten (oder linken) Tabelle findet die Join-Verknüpfung eine einzelne Zeile in der zweiten (oder rechten) Tabelle, die den nächstgelegenen Zeitstempelwert hat. Die passende Zeile auf der rechten Seite ist die mit der besten Übereinstimmung, deren Zeitpunkt gleich, früher oder später liegen kann, je nach dem angegebenen Vergleichsoperator.

Unter diesem Thema wird die Verwendung des ASOF JOIN-Konstrukts in der FROM-Klausel beschrieben. Eine detaillierte Erläuterung des Konzepts von ASOF-Joins finden Sie unter Analysieren von Zeitreihendaten.

Weitere Informationen finden Sie auch unter JOIN, wo die Syntax für andere Standard-Join-Typen wie innere und äußere Joins beschrieben wird.

Syntax

Die folgende Syntax der FROM-Klausel ist spezifisch für 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> ) ]

Parameter

FROM

Es wird davon ausgegangen, dass die erste (oder linke) Tabelle in der FROM-Klausel Datensätze enthält, die entweder auf die Datensätze in der zweiten (oder rechten) Tabelle (zeitlich) folgen, ihnen vorausgehen oder genau mit ihnen synchronisiert sind. Wenn es keine Übereinstimmung für eine Zeile in der linken Tabelle gibt, werden die Spalten der rechten Tabelle mit NULL aufgefüllt.

Zusätzlich zu regulären Tabellen und Ansichten kann jede Objektreferenz in einem ASOF JOIN verwendet werden. Siehe FROM.

ASOF JOIN kann in den meisten Kontexten verwendet werden, in denen Verknüpfungen unterstützt werden. Informationen zu einigen Einschränkungen finden Sie unter Nutzungshinweise.

MATCH_CONDITION ( left_table.timecol comparison_operator right_table.timecol )

Diese Bedingung nennt die spezifischen Zeitstempelspalten, die in jeder Tabelle verglichen werden sollen.

  • Die Reihenfolge der Tabellen ist für die Bedingung wichtig. Der Name der linken Tabelle muss zuerst genannt werden.

  • Die Klammern sind erforderlich.

  • Der Vergleichsoperator muss einer der folgenden sein: >=, <=, >, <. Der Gleichheitsoperator (=) wird nicht unterstützt.

  • Die folgenden Datentypen werden unterstützt: DATE, TIME, DATETIME, TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ.

  • Sie können auch NUMBER-Spalten in der Übereinstimmungsbedingung verwenden. Sie könnten zum Beispiel NUMBER-Spalten haben, die UNIX-Zeitstempel enthalten (die die Anzahl der Sekunden definieren, die seit dem 1. Januar 1970 vergangen sind).

  • Die Datentypen der beiden übereinstimmenden Spalten müssen nicht genau gleich sein, aber sie müssen kompatibel sein.

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

Die optionale ON- oder USING-Klausel definiert eine oder mehrere Gleichheitsbedingungen für Spalten in den beiden Tabellen, um die Ergebnisse der Abfrage logisch zu gruppieren.

Allgemeine Informationen zu ON und USING finden Sie unter JOIN. Beachten Sie, dass bei einer mit USING angegebenen Join-Verknüpfung nur eine der verknüpften Spalten im Resultset mit Zwischenergebnissen projiziert wird, nicht beide. Eine Join-Verknüpfung, die mit einer ON-Klausel angegeben wird, projiziert beide verknüpften Spalten.

Die folgenden Hinweise beziehen sich speziell auf ASOF JOIN:

  • Der Vergleichsoperator in der ON-Klausel muss das Gleichheitszeichen (=) sein.

  • Die ON-Klausel kann keine Disjunkte (mit OR verbundene Bedingungen) enthalten. Mit AND verbundene Bedingungen werden unterstützt.

  • Jede Seite einer Bedingung darf sich nur auf eine der beiden Tabellen im Join beziehen. Die Reihenfolge der Tabellenverweise spielt jedoch keine Rolle.

  • Jede Bedingung kann in Klammern eingeschlossen werden, aber sie sind nicht erforderlich.

Weitere Informationen dazu finden Sie unter Weitere Details zum Join-Verhalten und Angeben einer USING-Bedingung anstelle einer ON-Bedingung.

Nutzungshinweise

  • Wenn in der rechten Tabelle für eine bestimmte Zeile keine Übereinstimmung gefunden wird, wird das Ergebnis für die ausgewählten Spalten der rechten Tabelle mit NULL aufgefüllt. (ASOF-Joins sind in dieser Hinsicht ähnlich wie linke äußere Verknüpfungen (Left Outer Joins)).

  • Wenn Sie in der Übereinstimmungsbedingung TIME-Spalten verwenden (im Gegensatz zu einem der Zeitstempeltypen), müssen Sie möglicherweise den Parameter TIME_OUTPUT_FORMAT festlegen, damit Sie beim Anzeigen der ASOF JOIN-Abfrageergebnisse die genauen TIME-Werte sehen, die verglichen werden. Bei der Anzeige einer TIME-Spalte werden standardmäßig Millisekunden abgeschnitten. Siehe TIME-Spalten in der Übereinstimmungsbedingung.

  • Sie können mehrere ASOF-Joins in derselben Abfrage verwenden, solange alle Syntaxregeln für jeden Join befolgt werden. Auf jeden Join muss unmittelbar eine eigene MATCH_CONDITION-Bedingung folgen. Sie können eine einzelne MATCH_CONDITION-Bedingung nicht auf mehrere ASOF-Joins anwenden. Siehe Mehrere ASOF-Joins in einer Abfrage.

  • ASOF-Joins werden für Joins mit LATERAL-Tabellenfunktionen oder LATERAL-Inline-Ansichten nicht unterstützt. Weitere Informationen zu seitlichen Verknüpfungen (Lateral Joins) finden Sie unter LATERAL.

  • Ein ASOF-Join mit einer Selbstreferenz ist in einem RECURSIVE-CTE (Common Table Expression) nicht zulässig. Weitere Informationen zu CTEs finden Sie unter WITH.

  • Die EXPLAIN-Ausgabe für ASOF JOIN-Abfragen identifiziert die ON- (oder USING-)Bedingungen und die MATCH_CONDITION-Bedingung. Im Text- oder Tabellenformat wird zum Beispiel über den Tabellenscans im Ausführungsplan eine Ausgabe ähnlich dem folgenden Text angezeigt:

    ->ASOF Join  joinKey: (S.LOCATION = R.LOCATION) AND (S.STATE = R.STATE),
  matchCondition: (S.OBSERVED >= R.OBSERVED)

  • In den Query Profiles ist auch die ASOF JOIN-Operation im Ausführungsplan eindeutig gekennzeichnet. In diesem Beispiel können Sie sehen, dass der Tabellenscan 22M Zeilen aus der linken Tabelle liest, die alle durch die Verknüpfung erhalten bleiben. Das Profil zeigt auch die Übereinstimmungsbedingung der Verknüpfung an.

Query Profile mit Tabellenscans, die dem darüber liegenden ASOF JOIN-Operator Zeilen zuführen.

Weitere Details zum Join-Verhalten

Die optionalen ON- (oder USING-)Bedingungen für ASOF JOIN bieten eine Möglichkeit, Tabellenzeilen zu gruppieren oder zu partitionieren, bevor die endgültigen übereinstimmenden Zeilen durch die erforderliche Übereinstimmungsbedingung herausgefiltert werden. Wenn Sie möchten, dass die Zeilen aus den verknüpften Tabellen nach einer oder mehreren Dimensionen gruppiert werden, die die Tabellen gemeinsam haben (Aktiensymbol, Standort, Stadt, Bundesland, Firmenname usw.), verwenden Sie eine ON-Bedingung. Wenn Sie keine ON-Bedingung verwenden, kann jede Zeile aus der linken Tabelle mit jeder Zeile aus der rechten Tabelle im endgültigen Resultset (zeitlich) abgeglichen werden.

Im folgenden Beispiel haben die Tabellen left_table und right_table die Werte A, B usw. in Spalte c1 und die Werte 1, 2 usw. in Spalte c2. Spalte c3 ist eine TIME-Spalte, und c4 ist ein numerischer Wert (diese Spalte ist von Interesse).

Erstellen und laden Sie zuerst die beiden Tabellen:

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

SELECT * FROM left_table ORDER BY c1, c2;

+----+----+----------+------+
| 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;

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

Wenn c1 und c2 Spalten einer ON-Bedingung in der Abfrage sind, stimmt eine Zeile in der linken Tabelle nur mit einer Zeile in der rechten Tabelle überein, wenn A und 1, A und 2, B und 1 oder B und 2 in beiden Tabellen gefunden werden. Wenn für solche Werte keine Übereinstimmung gefunden wird, werden die rechten Tabellenspalten mit NULL aufgefüllt.

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;

+----+----+----------+------+------+------+----------+------+
| 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 |
+----+----+----------+------+------+------+----------+------+

Wenn die ON-Bedingungen entfernt werden, kann jede Kombination von Werten in c1 und c2 im Endergebnis übereinstimmen. Nur die Übereinstimmungsbedingung bestimmt die Ergebnisse.

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

+----+----+----------+------+----+----+----------+------+
| 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 |
+----+----+----------+------+----+----+----------+------+

Erwartetes Verhalten, wenn in der rechten Tabelle „Verbindungen“ vorliegen

ASOF JOIN-Abfragen versuchen immer, für eine einzelne Zeile in der linken Tabelle eine Übereinstimmung mit einer einzelnen Zeile in der rechten Tabelle zu finden. Dieses Verhalten gilt auch dann, wenn zwei (oder mehr) Zeilen in der rechten Tabelle identisch sind und für die Verknüpfung in Frage kommen. Wenn solche Verbindungen bestehen und Sie dieselbe Join-Abfrage mehrfach ausführen, erhalten Sie möglicherweise unterschiedliche Ergebnisse. Die Ergebnisse sind nicht deterministisch, da jede der zusammenfassenden Zeilen zurückgegeben werden kann. Wenn Sie sich bei den Ergebnissen von ASOF JOIN-Abfragen nicht sicher sind, dann suchen Sie für Zeilen in der rechten Tabelle nach exakten Übereinstimmungen in den Zeitstempelwerten.

Fügen Sie beispielsweise unter Verwendung derselben Tabellen aus den Beispielen im vorherigen Abschnitt eine Spalte right_id zu right_table hinzu, und fügen Sie die folgenden Zeilen ein:

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;

+----+----+----------+------+----------+
| 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       |
+----+----+----------+------+----------+

Zwei der Zeilen sind bis auf ihre right_id-Werte identisch. Führen Sie nun die folgende ASOF JOIN-Abfrage aus:

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

+----+----+----------+------+----+----+----------+------+----------+
| 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       |
+----+----+----------+------+----+----+----------+------+----------+

Beachten Sie, dass die Zeilen A1 und A2 aus right_table beide für die Verknüpfung in Frage kommen, aber nur A2 zurückgegeben wird. Bei einer späteren Ausführung derselben Abfrage könnte stattdessen A1 zurückgegeben werden.

Umschreiben von ASOF JOIN-Abfragen, um Scans der rechten Tabelle zu reduzieren

Wenn die Kardinalität der ON- oder USING-Join-Spalte in der linken Tabelle kleiner ist als die Kardinalität der Join-Spalte in der rechten Tabelle, führt der Optimierer kein Pruning der nicht übereinstimmenden Zeilen aus der rechten Tabelle durch. Daher werden mehr Zeilen aus der rechten Tabelle gescannt, als für die Verknüpfung benötigt werden. Dieses Verhalten tritt typischerweise auf, wenn die Abfrage einen hochselektiven Filter auf eine Nicht-Join-Spalte der linken Tabelle enthält und der Filter die Kardinalität der Join-Spalte reduziert.

Sie können dieses Problem umgehen, indem Sie die Zeilen, die für die Verknüpfung in Frage kommen, manuell reduzieren. Beispielsweise hat die ursprüngliche Abfrage das folgende Muster, und t1.c1 hat eine geringere Kardinalität als t2.c1:

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

Sie können die Abfrage wie folgt umschreiben, um manuell die Zeilen aus t2 auszuwählen, in denen t2.c1-Werte in t1.c1 gefunden werden:

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;

Verwenden von ASOF und MATCH_CONDITION als Objekt- und Aliasnamen

Die Verwendung der Schlüsselwörter ASOF und MATCH_CONDITION in der SELECT-Befehlssyntax ist eingeschränkt:

  • Wenn eine SELECT-Anweisung ASOF oder MATCH_CONDITION als Name einer Tabelle, einer Ansicht oder einer Inline-Ansicht verwendet, ist folgende Identifizierung erforderlich:

    • Wenn das Objekt mit doppelten Anführungszeichen im Namen erstellt wurde, verwenden Sie denselben Namen in doppelten Anführungszeichen.

    • Wenn das Objekt ohne Anführungszeichen im Namen erstellt wurde, verwenden Sie doppelte Anführungszeichen und Großbuchstaben.

    Die folgenden Anweisungen sind zum Beispiel nicht mehr zulässig und geben Fehler zurück:

    SELECT * FROM asof;

WITH match_condition AS (SELECT * FROM T1) SELECT * FROM match_condition;

    Wenn Sie die Objekte mit doppelten Anführungszeichen erstellt haben, beheben Sie das Problem, indem Sie doppelte Anführungszeichen verwenden:

    SELECT * FROM "asof";

WITH "match_condition" AS (SELECT * FROM T1) SELECT * FROM "match_condition";

    Wenn Sie die Objekte ohne doppelte Anführungszeichen erstellt haben, beheben Sie das Problem, indem Sie doppelte Anführungszeichen und Großbuchstaben verwenden:

    SELECT * FROM "ASOF";

WITH "MATCH_CONDITION" AS (SELECT * FROM T1) SELECT * FROM "MATCH_CONDITION";

    Siehe auch Bezeichner ohne Anführungszeichen.

  • Wenn eine SELECT-Anweisung ASOF oder MATCH_CONDITION als Alias verwendet, müssen Sie AS vor dem Alias verwenden oder den Alias in doppelte Anführungszeichen setzen. Die folgenden Anweisungen sind zum Beispiel nicht mehr zulässig und geben Fehler zurück:

    SELECT * FROM t1 asof;

SELECT * FROM t2 match_condition;

    Zur Behebung des Problems gibt es folgende Optionen:

    SELECT * FROM t1 AS asof;

SELECT * FROM t1 "asof";

SELECT * FROM t2 AS match_condition;

SELECT * FROM t2 "match_condition";

Beispiele

Die folgenden Beispiele zeigen das erwartete Verhalten von ASOF JOIN-Abfragen. Führen Sie zunächst die Abfrage unter Verknüpfen zweier Tabellen anhand der engsten Übereinstimmung (Alignment) aus, und fahren Sie dann mit den Beispielen hier fort.

Mit NULL aufgefüllte Ergebnisse

Fügen Sie eine neue Zeile in die Tabelle trades mit einem Datum ein, das einen Tag vor dem Datum der vorhandenen Zeilen in trades und quotes liegt:

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

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

Führen Sie nun die erste Beispielabfrage erneut aus. Beachten Sie, dass die Abfrage vier Zeilen zurückgibt, aber die neue Zeile mit NULL aufgefüllt ist. Es gibt keine Zeile in der Tabelle quotes, auf die die Übereinstimmungsbedingung zutrifft. Die Spalten von trades werden zurückgegeben, und die entsprechenden Spalten von quotes werden mit NULL aufgefüllt.

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;

+--------------+-------------------------+----------+-------------------------+--------------+
| 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 |
+--------------+-------------------------+----------+-------------------------+--------------+

Verwenden eines anderen Vergleichsoperators in der Übereinstimmungsbedingung

In Weiterführung des vorherigen Beispiels ändern sich die Ergebnisse der Abfrage erneut, wenn der Vergleichsoperator in der Übereinstimmungsbedingung geändert wird. In der folgenden Abfrage wird der Operator <= (anstelle von >=) angegeben:

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;

+--------------+-------------------------+----------+-------------------------+--------------+
| 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 |
+--------------+-------------------------+----------+-------------------------+--------------+

Siehe auch Vergleichsoperatoren „Kleiner als“ und „Größer als“.

Angeben einer USING-Bedingung anstelle einer ON-Bedingung

Sie können in ASOF JOIN-Abfragen eine ON-Bedingung oder eine USING-Bedingung verwenden. Die folgende Abfrage entspricht der vorherigen Abfrage, nur dass ON durch USING ersetzt wird. Die Syntax USING(stock_symbol) impliziert die Bedingung 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;

Innere Verknüpfung (Inner Join) mit einer dritten Tabelle

Im folgenden Beispiel wird dem Join eine dritte Tabelle companies hinzugefügt, um den Firmennamen für jedes Aktiensymbol auszuwählen. Sie können einen reguläre INNER JOIN-Ausdruck mit einer ON-Bedingung (oder eine andere Standard-Join-Syntax) verwenden, um die dritte Tabelle hinzuzufügen. Beachten Sie jedoch, dass USING(stock_symbol) hier nicht funktionieren würde, da der Verweis auf stock_symbol mehrdeutig wäre.

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

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;

+--------------+---------------+-------------------------+----------+-------------------------+--------------+
| 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 |
+--------------+---------------+-------------------------+----------+-------------------------+--------------+

Zahlen als Zeitstempel

Das folgende Beispiel zeigt, dass die Übereinstimmungsbedingung numerische Werte vergleichen kann. In diesem Fall weisen die Tabellen NUMBER(38,0)-Spalten auf, in denen UNIX-Zeitstempelwerte gespeichert sind. 1696150805 ist äquivalent zu 2023-10-30 10:20:05.000 (drei Sekunden später als 1696150802).

SELECT * FROM trades_unixtime;

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

SELECT * FROM quotes_unixtime;

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

+--------------+------------+----------+--------------+--------------+------------+----------+--------------+--------------+
| 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-Spalten in der Übereinstimmungsbedingung

In den folgenden Beispielen werden zwei Tabellen verknüpft, die Wetterbeobachtungen enthalten. Die Beobachtungen in diesen Tabellen werden in TIME-Spalten erfasst. Sie können die Tabellen wie folgt erstellen und laden:

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

Wenn Sie die erste Abfrage ausführen, scheinen einige der TIME-Werte im Resultset genau gleich zu sein (14:42:59, 14:42:44).

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

+----------+-----------+-------+-------------+----------+-----------+-------+-------------+
| 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 |
+----------+-----------+-------+-------------+----------+-----------+-------+-------------+

Um eine genauere Anzeige der TIME-Werte, einschließlich Millisekunden, zu erhalten, führen Sie den folgenden ALTER SESSION-Befehl aus, und führen Sie dann die ASOF JOIN-Abfrage erneut aus:

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

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

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

+--------------+-----------+-------+-------------+--------------+-----------+-------+-------------+
| 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 |
+--------------+-----------+-------+-------------+--------------+-----------+-------+-------------+

Mehrere ASOF-Joins in einer Abfrage

Das folgende Beispiel zeigt, wie eine Sequenz von zwei oder mehr ASOF-Joins in einem einzigen Abfrageblock verbunden wird. Die drei Tabellen (snowtime, raintime, preciptime) enthalten alle Wetterbeobachtungen, die an bestimmten Orten zu bestimmten Zeiten aufgezeichnet wurden. Die Spalte von Interesse ist die Spalte observation. Die Zeilen sind logisch nach Bundesstaat gruppiert.

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;

+--------------+------------+-------+-------------+--------------+----------+-------+-------------+--------------+----------+-------+-------------+
| 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 |
+--------------+------------+-------+-------------+--------------+----------+-------+-------------+--------------+----------+-------+-------------+

Vergleichsoperatoren „Kleiner als“ und „Größer als“

In Anlehnung an das vorherige Beispiel werden zwei ASOF-Joins angegeben, aber diesmal wird für die erste Übereinstimmungsbedingung der Operator > und für die zweite der Operator < verwendet. Das Ergebnis besteht aus genau einer Zeile, die Daten aus allen drei Tabellen liefert, und drei Zeilen, die Daten aus zwei der Tabellen liefern. Viele der Spalten im Resultset sind mit NULL aufgefüllt.

Logischerweise findet die Abfrage nur eine Zeile, in der die beobachtete Zeit aus der Tabelle snowtime nach der beobachteten Zeit aus der Tabelle raintime, aber vor der beobachteten Zeit aus der Tabelle preciptime lag.

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;

+--------------+------------+-------+-------------+--------------+-----------+-------+-------------+--------------+----------+-------+-------------+
| 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 |
+--------------+------------+-------+-------------+--------------+-----------+-------+-------------+--------------+----------+-------+-------------+

Beispiele für erwartete Fehlerfälle

Die folgenden Beispiele zeigen Abfragen, die erwartete Syntaxfehler zurückgeben.

Wenn Sie deklariert haben, dass snowtime s die linke Tabelle ist, können Sie die Übereinstimmungsbedingung nicht mit einem Verweis auf die rechte Tabelle preciptime p beginnen:

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

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.

In Übereinstimmungsbedingungen sind nur die Operatoren >=, <=, > und < erlaubt:

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

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.

Die ON-Klausel für ASOF JOIN muss Gleichheitsbedingungen enthalten:

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

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.

Eine Gleichheitsbedingung einer ON-Klausel kann keine Disjunktionen enthalten:

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

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-Joins können nicht mit LATERAL-Inline-Ansichten verwendet werden:

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;

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