ASOF JOIN¶

Syntaxe¶

La syntaxe suivante de la clause FROM est spécifique à 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> ) ]

Paramètres¶

FROM

La première table (ou table de gauche) de la clause FROM est supposée contenir des enregistrements qui suivent (dans le temps), précèdent ou sont exactement synchronisés avec les enregistrements de la deuxième table (ou table de droite). Lorsqu’il n’y a pas de correspondance pour une ligne dans la table de gauche, les colonnes de la table de droite sont complétées par des valeurs nulles.

Outre les tables et les vues ordinaires, toute référence d’objet peut être utilisée dans un ASOF JOIN. Voir FROM.

ASOF JOIN peut être utilisé dans la plupart des contextes où les jointures sont prises en charge. Pour plus d’informations sur certaines restrictions, voir Notes sur l’utilisation.

MATCH_CONDITION ( left_table.timecol comparison_operator right_table.timecol )

Cette condition désigne les colonnes d’horodatage spécifiques à comparer dans chaque table.

• L’ordre des tables est important dans la condition. La table de gauche doit être nommée en premier.

• Les parenthèses sont obligatoires.

• L’opérateur de comparaison doit être l’un des suivants : >=, <=, >, <. L’opérateur égal (=) n’est pas pris en charge.

• Les types de données suivants sont pris en charge : DATE, TIME, DATETIME, TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ.

• Vous pouvez également utiliser des colonnes NUMBER dans la condition de correspondance. Par exemple, vous pouvez avoir NUMBER colonnes qui contiennent des horodatages UNIX (qui définissent le nombre de secondes qui se sont écoulées depuis le 1er janvier, 1970).

• Les types de données des deux colonnes correspondantes ne doivent pas être exactement les mêmes, mais ils doivent être compatibles.

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

La clause ON ou USING facultative définit une ou plusieurs conditions d’égalité sur les colonnes des deux tables, afin de regrouper logiquement les résultats de la requête.

Pour des informations générales sur ON et USING, voir JOIN. Notez qu’une jointure spécifiée avec USING projette l’une des colonnes de jointure dans son jeu de résultats intermédiaire, et non les deux. Une jointure spécifiée avec une clause ON projette les deux colonnes de jointure.

Les remarques suivantes sont spécifiques à ASOF JOIN :

• L’opérateur de comparaison de la clause ON doit être le signe égal (=).

• La clause ON ne peut pas contenir de disjonctions (conditions liées à OR). Les conditions liées à AND sont prises en charge.

• Chaque côté d’une condition doit faire référence à une seule des deux tables de la jointure. Toutefois, l’ordre des références de la table n’a pas d’importance.

• Chaque condition peut être placée entre parenthèses, mais elles ne sont pas obligatoires.

Voir également Plus de détails sur le comportement de jointure et Spécification d’une condition USING au lieu d’une condition ON.

Notes sur l’utilisation¶

• Si aucune correspondance n’est trouvée dans la table de droite pour une ligne donnée, le résultat est complété par des valeurs nulles pour les colonnes sélectionnées dans la table de droite. (Les jointuresASOF sont similaires aux jointures externes gauches à cet égard).

• Si vous utilisez des colonnes TIME dans la condition de correspondance (par opposition à l’un des types d’horodatage), vous devrez peut-être définir le paramètre TIME_OUTPUT_FORMAT afin de voir les valeurs TIME exactes qui sont comparées lorsque vous regardez les résultats de la requête ASOF JOIN. Par défaut, l’affichage d’une colonne TIME tronque les millisecondes. Voir Colonnes TIME dans la condition de correspondance.

• Vous pouvez utiliser plusieurs jointures ASOF dans la même requête, à condition que toutes les règles syntaxiques soient respectées pour chaque jointure. Chaque jointure doit être immédiatement suivie de son propre MATCH_CONDITION. Vous ne pouvez pas appliquer une MATCH_CONDITION seule à plusieurs jointures ASOF. Voir Jointures ASOF multiples dans une requête.

• Les jointures ASOF ne sont pas prises en charge pour les jointures avec des fonctions de table LATERAL ou des vues en ligne LATERAL. Pour plus d’informations sur les jonctions latérales, voir LATERAL.

• Une jointure ASOF avec une autoréférence n’est pas autorisée dans une expression de table commune RECURSIVE (CTE). Pour plus d’informations sur CTEs, voir WITH.

• La sortie EXPLAIN des requêtes ASOF JOIN identifie les conditions ON (ou USING) et la MATCH_CONDITION. Par exemple, dans un format texte ou tabulaire, une sortie similaire au texte suivant apparaît au-dessus des analyses de table dans le plan :

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

• Les profils de requête identifient également clairement l’opération ASOF JOIN dans le plan. Dans cet exemple, vous pouvez voir que l’analyse de la table lit 22 millions de lignes de la table de gauche, qui sont toutes préservées par la jointure. Le profil indique également la condition de correspondance pour la jointure.

Plus de détails sur le comportement de jointure¶

Les conditions ON (ou USING) facultatives pour ASOF JOIN permettent de regrouper ou de partitionner les lignes de la table avant que les lignes correspondantes finales ne soient distinguées par la condition de correspondance requise. Si vous souhaitez que les lignes des tables jointes soient regroupées en fonction d’une ou plusieurs dimensions communes aux tables (symbole boursier, lieu, ville, état, nom de la société, etc.), utilisez une condition ON. Si vous n’utilisez pas de condition ON, chaque ligne de la table de gauche peut être mise en correspondance (en fonction du temps) avec n’importe quelle ligne de la table de droite dans le jeu de résultats final.

Dans l’exemple suivant, les tables left_table et right_table ont les valeurs A, B, etc. dans la colonne c1, et les valeurs 1, 2, etc. dans la colonne c2. La colonne c3 est une colonne TIME et c4 est une valeur numérique (colonne d’intérêt).

Tout d’abord, créez et chargez les deux tables :

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

Si c1 et c2 sont toutes deux des colonnes de condition ON dans la requête, une ligne de la table de gauche ne correspond à une ligne de la table de droite que si A et 1, A et 2, B et 1, ou B et 2 se trouvent dans les deux tables. Si aucune correspondance n’est trouvée pour ces valeurs, les colonnes de la table de droite sont complétées par des valeurs nulles.

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

Si les conditions ON sont supprimées, toute combinaison de valeurs dans c1 et c2 peut être prise en compte dans le résultat final. Seule la condition de correspondance détermine les résultats.

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

Comportement prévu lorsqu’il existe des « liens » dans la table droite¶

Les requêtes ASOF JOIN tentent toujours de mettre en correspondance une seule ligne de la table gauche avec une seule ligne de la table droite. Ce comportement est vrai, même si deux lignes (ou plus) de la table droite sont identiques et remplissent les conditions requises pour la jointure. Lorsqu’il existe de tels liens et que vous exécutez plusieurs fois la même requête de jointure, vous pouvez obtenir des résultats différents. Les résultats ne sont pas déterministes, car n’importe laquelle des lignes liantes peut être renvoyée. Si vous n’êtes pas sûr des résultats des requêtes ASOF JOIN, vérifiez les correspondances exactes dans les valeurs d’horodatage des lignes de la table droite.

Par exemple, en utilisant les mêmes tables que celles des exemples de la section précédente, ajoutez une colonne right_id à right_table et insérez les lignes suivantes :

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

Deux des lignes sont identiques, à l’exception de leurs valeurs right_id. À présent, exécutez la requête ASOF JOIN suivante :

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

Notez que les lignes A1 et A2 de right_table remplissent toutes les deux les conditions requises pour la jointure, mais que seule la ligne A2 est renvoyée. Lors d’une exécution ultérieure de la même requête, il se peut que la ligne A1 soit renvoyée à la place.

Réécriture des requêtes ASOF JOIN pour réduire les analyses sur la table droite¶

Lorsque la cardinalité de la colonne de jointure ON ou USING de la table gauche est inférieure à la cardinalité de la colonne de jointure de la table droite, l’optimiseur ne supprime pas les lignes non correspondantes de la table droite. Par conséquent, un plus grand nombre de lignes que le nombre nécessaire pour la jointure sera analysé dans la table droite. Ce comportement se produit généralement lorsque la requête inclut un filtre très sélectif sur une colonne hors jointure de la table gauche et que le filtre réduit la cardinalité de la colonne de jointure.

Vous pouvez contourner ce problème en réduisant manuellement le nombre de lignes remplissant les conditions requises pour la jointure. Par exemple, la requête d’origine présente le modèle suivant, et t1.c1 a une cardinalité inférieure à celle de t2.c1 :

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

Vous pouvez réécrire la requête comme suit pour sélectionner manuellement les lignes de t2 lorsque des valeurs de t2.c1 sont trouvées dans t1.c1 :

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;

Utilisation de ASOF et de MATCH_CONDITION comme noms et alias d’objet¶

L’utilisation des mots-clés ASOF et MATCH_CONDITION dans la syntaxe de la commande SELECT est limitée :

• Si une instruction SELECT utilise ASOF ou MATCH_CONDITION comme nom d’une table, d’une vue ou d’une vue en ligne, vous devez l’identifier comme suit :

  • Si l’objet a été créé avec des guillemets doubles dans le nom, utilisez le même nom entre guillemets doubles.

  • Si l’objet a été créé sans guillemets doubles dans le nom, utilisez des guillemets doubles et des lettres majuscules.

  Par exemple, les instructions suivantes ne sont plus autorisées et renvoient des erreurs :

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

  Copy

  Si vous avez créé les objets avec des guillemets doubles, corrigez le problème en utilisant des guillemets doubles :

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

  Copy

  Si vous avez créé les objets sans guillemets doubles, corrigez le problème en utilisant des guillemets doubles et des lettres majuscules :

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

  Copy

  Voir aussi Identificateurs sans guillemets.

• Si une instruction SELECT utilise ASOF ou MATCH_CONDITION comme alias, vous devez utiliser AS avant l’alias ou mettre l’alias entre guillemets doubles. Par exemple, les instructions suivantes ne sont plus autorisées et renvoient des erreurs :

  SELECT * FROM t1 asof;
  
  SELECT * FROM t2 match_condition;
  

  Copy

  Réglez le problème de l’une des manières suivantes :

  SELECT * FROM t1 AS asof;
  
  SELECT * FROM t1 "asof";
  
  SELECT * FROM t2 AS match_condition;
  
  SELECT * FROM t2 "match_condition";
  

  Copy

Exemples¶

Les exemples suivants illustrent le comportement attendu des requêtes ASOF JOIN. Commencez par exécuter la requête sous Jointure de deux tables sur la correspondance la plus proche (alignement), puis suivez les exemples présentés ici.

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

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

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

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

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;

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

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

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

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

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

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

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

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.

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.

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.

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.

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.