Kategorien:: Abfragesyntax

ASOF JOIN¶

Unter diesem Thema wird die Verwendung des ASOF JOIN-Konstrukts in der FROM-Klausel beschrieben. Eine ASOF JOIN-Operation kombiniert Zeilen aus zwei Tabellen auf der Grundlage von Zeitstempelwerten, die eng aufeinander folgen, einander vorausgehen oder genau übereinstimmen. Eine Erläuterung des Konzepts von ASOF-Joins finden Sie unter Verknüpfen 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:

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

Copy

SELECT ...

Mit der SELECT-Liste können beliebige und alle Spalten aus beiden Tabellen ausgewählt werden. Wenn es keine Übereinstimmung für eine Zeile in der linken Tabelle gibt, werden die Spalten der rechten Tabelle mit NULL aufgefüllt.

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.

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 Joins 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 in jeder Tabelle, die 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 ... ]

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

Der Vergleichsoperator 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 Tabellenreferenzen spielt jedoch keine Rolle.
Jede ON-Bedingung kann in Klammern eingeschlossen werden, muss es aber nicht.

Weitere Informationen dazu finden Sie unter Weitere Details zum Join-Verhalten.

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.
Mehrere ASOF-Joins können in derselben Abfrage verwendet werden, 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 Seitliche Verknüpfung (Lateral Join).
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-Bedingungen und die MATCH_CONDITION-Bedingung. Beispielsweise wird im Text- oder Tabellenformat diese Art der Ausgabe im Ausführungsplan über den Tabellenscans 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.

Weitere Details zum Join-Verhalten¶

Die optionalen ON-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 der linken Tabelle mit jeder Zeile der rechten Tabelle im endgültigen Resultset (zeitlich) abgeglichen werden.

Hier ist ein abstraktes Beispiel. Die Tabellen left_table und right_table haben 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).

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

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;

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

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;

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

Verwenden von ASOF und MATCH_CONDITION als Objektnamen und Aliasnamen¶

Mit diesem Feature werden zwei neue Schlüsselwörter eingeführt: ASOF und MATCH_CONDITION. Die Verwendung dieser Schlüsselwörter 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;
```
Copy
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";
```
Copy
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";
```
Copy
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;
```
Copy
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";
```
Copy

Beispiele¶

Die folgenden Beispiele zeigen das erwartete Verhalten von ASOF JOIN-Abfragen.

Join mit Match- und ON-Bedingungen¶

Das folgende Beispiel wird unter Konzeptuelles Beispiel für eine ASOF JOIN-Abfrage beschrieben.

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

Die ON-Bedingung gruppiert die übereinstimmenden Zeilen nach ihren Aktiensymbolen.

Mit NULL aufgefüllte Ergebnisse¶

In Weiterführung des vorherigen Beispiels 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);

Copy

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

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

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;

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

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

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

Im folgenden Beispiel wird dem Join eine dritte Firmentabelle 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.

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

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;

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

Im folgenden Beispiel werden zwei Tabellen verknüpft, die Wetterbeobachtungen enthalten. Die Beobachtungen in beiden Tabellen werden in TIME-Spalten aufgezeichnet. Alle TIME-Werte scheinen in diesem Resultset genau gleich zu sein (14:42:59).

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

Copy

+----------+----------+-------+-------------+----------+-----------+-------+-------------+
| OBSERVED | LOCATION | STATE | OBSERVATION | OBSERVED | LOCATION  | STATE | OBSERVATION |
|----------+----------+-------+-------------+----------+-----------+-------+-------------|
| 14:42:59 | Oakhurst | CA    |        0.50 | NULL     | NULL      | NULL  |        NULL |
| 14:42:59 | Ahwahnee | CA    |        0.90 | 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 Join-Abfrage erneut aus:

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

Copy

+--------------+----------+-------+-------------+--------------+-----------+-------+-------------+
| OBSERVED     | LOCATION | STATE | OBSERVATION | OBSERVED     | LOCATION  | STATE | OBSERVATION |
|--------------+----------+-------+-------------+--------------+-----------+-------+-------------|
| 14:42:59.001 | Oakhurst | CA    |        0.50 | NULL         | NULL      | NULL  |        NULL |
| 14:42:59.230 | Ahwahnee | CA    |        0.90 | 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;

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

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;

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

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

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.

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

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.

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;

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.

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;

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

Copy

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