Categorias:

Sintaxe de consulta

ASOF JOIN

Uma operação ASOF JOIN combina linhas de duas tabelas com base em valores de carimbo de data/hora que seguem um ao outro, precedem um ao outro ou correspondem exatamente. Para cada linha na primeira tabela (ou esquerda), a junção encontra uma única linha na segunda tabela (ou direita) que tenha o valor de carimbo de data/hora mais próximo. A linha de qualificação no lado direito é a correspondência mais próxima, que pode ser igual no tempo, mais cedo ou mais tarde, dependendo do operador de comparação especificado.

Este tópico descreve como usar o constructo ASOF JOIN na cláusula FROM. Para uma explicação conceitual mais detalhada das junções ASOF, consulte Análise de dados de séries temporais.

Consulte também JOIN, que aborda a sintaxe para outros tipos de junção padrão, como junções internas e externas.

Sintaxe

A seguinte sintaxe da cláusula FROM é específica para 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> ) ]

Parâmetros

FROM

Presume-se que a primeira tabela (ou à esquerda) na cláusula FROM contenha registros que seguem (no tempo), precedem ou são exatamente sincronizados com os registros na segunda tabela (ou à direita). Quando não há correspondência para uma linha na tabela esquerda, as colunas da tabela direita são preenchidas com nulos.

Além de tabelas e exibições regulares, qualquer referência de objeto pode ser usada em um ASOF JOIN. Consulte FROM.

ASOF JOIN pode ser usado na maioria dos contextos onde junções são permitidas. Para obter informações sobre algumas restrições, consulte as Notas de uso.

MATCH_CONDITION ( left_table.timecol comparison_operator right_table.timecol )

Esta condição nomeia as colunas de carimbo de data/hora específicas a serem comparadas em cada tabela.

  • A ordem das tabelas é importante na condição. A tabela esquerda deve ser nomeada primeiro.

  • Os parênteses são obrigatórios.

  • O operador de comparação deve ser uma das seguintes opções: >=, <=, >, <. O operador igual (=) não é suportado.

  • Todos os tipos de dados a seguir são compatíveis: DATE, TIME, DATETIME, TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ.

  • Você também pode usar colunas NUMBER na condição de correspondência. Por exemplo, você pode ter colunas NUMBER que contenham carimbos de data/hora UNIX (que definem o número de segundos decorridos desde 1 de janeiro de 1970).

  • Os tipos de dados das duas colunas correspondentes não precisam ser exatamente os mesmos, mas devem ser compatíveis.

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

A cláusula opcional ON ou USING define uma ou mais condições de igualdade nas colunas das duas tabelas, com a finalidade de agrupar logicamente os resultados da consulta.

Para obter informações gerais sobre ON e USING, consulte JOIN. Observe que uma junção especificada com USING projeta uma das colunas de junção em seu conjunto de resultados intermediário, não ambas. Uma junção especificada com uma cláusula ON projeta ambas as colunas de junção.

As seguintes notas são específicas para ASOF JOIN:

  • O operador de comparação na cláusula ON deve ser o sinal de igual (=).

  • A cláusula ON não pode conter disjuntos (condições conectadas com OR). As condições conectadas a AND são suportadas.

  • Cada lado de uma condição deve referir-se apenas a uma das duas tabelas da junção. Entretanto, a ordem das referências da tabela não importa.

  • Cada condição pode ser colocada entre parênteses, mas não é obrigatório.

Consulte também Mais detalhes sobre o comportamento de junção e Especificação de uma condição USING em vez de uma condição ON.

Notas de uso

  • Se nenhuma correspondência for encontrada na tabela certa para uma determinada linha, o resultado será preenchido com nulos para as colunas selecionadas na tabela certa. (As junções ASOF são semelhantes às junções externas esquerdas nesse aspecto.)

  • Se você usar colunas TIME na condição de correspondência (em oposição a um dos tipos de carimbo de data/hora), talvez seja necessário definir o parâmetro TIME_OUTPUT_FORMAT para ver os valores TIME exatos que estão sendo comparados ao analisar os resultados de consulta ASOF JOIN. Por padrão, a exibição de uma coluna TIME trunca milissegundos. Consulte Colunas TIME na condição de correspondência.

  • Você pode usar mais de uma junção ASOF na mesma consulta, desde que todas as regras de sintaxe sejam seguidas para cada junção. Cada junção deve ser imediatamente seguida por seu próprio MATCH_CONDITION. Você não pode aplicar um único MATCH_CONDITION a várias junções de ASOF. Consulte Múltiplas junções ASOF em uma consulta.

  • As junções ASOF não são suportadas para junções com funções de tabela LATERAL ou exibições inline LATERAL. Para obter mais informações sobre junções laterais, consulte LATERAL.

  • Uma junção ASOF com autorreferência não é permitida em uma expressão de tabela comum RECURSIVE (CTE). Para obter mais informações sobre CTEs, consulte WITH.

  • A saída EXPLAIN para consultas ASOF JOIN identifica as condições ON (ou USING) e MATCH_CONDITION. Por exemplo, no formato de texto ou tabular, uma saída semelhante ao texto a seguir aparece acima das varreduras de tabela no plano:

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

  • Os perfis de consulta também identificam claramente a operação ASOF JOIN no plano. Neste exemplo, você pode ver que a varredura da tabela lê 22 milhões de linhas da tabela esquerda, todas preservadas pela junção. O perfil também mostra a condição de correspondência da junção.

Perfil de consulta que mostra varreduras de tabela alimentando linhas para o operador ASOF JOIN acima dela.

Mais detalhes sobre o comportamento de junção

As condições opcionais ON (ou USING) para ASOF JOIN fornecem uma maneira de agrupar ou particionar linhas de tabela antes que as linhas correspondentes finais sejam selecionadas pela condição de correspondência necessária. Se você deseja que as linhas das tabelas unidas sejam agrupadas em uma ou mais dimensões compartilhadas pelas tabelas (símbolo de ação, local, cidade, estado, nome da empresa etc.), use uma condição ON. Se você não usar uma condição ON, cada linha da tabela da esquerda poderá ser correspondida (por tempo) com qualquer linha da tabela da direita no conjunto de resultados final.

No exemplo a seguir, as tabelas left_table e right_table possuem valores A, B etc. na coluna c1 e valores 1, 2 etc. na coluna c2. A coluna c3 é uma coluna TIME e c4 é um valor numérico (coluna de interesse).

Primeiro, crie e carregue as duas tabelas:

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

Se c1 e c2 forem colunas de condição ON na consulta, uma linha na tabela esquerda só corresponderá a uma linha na tabela direita quando A e 1, A e 2, B e 1 ou B e 2 forem encontrados em ambas as tabelas. Se nenhuma correspondência for encontrada para esses valores, as colunas da tabela direita serão preenchidas com nulos.

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

Se as condições ON forem removidas, qualquer combinação de valores em c1 e c2 poderá corresponder ao resultado final. Somente a condição de correspondência determina os resultados.

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

Comportamento esperado quando existem “empates” na tabela certa

As consultas ASOF JOIN sempre tentam corresponder uma única linha na tabela da esquerda com uma única linha na tabela da direita. Esse comportamento é verdadeiro mesmo se duas (ou mais) linhas na tabela da direita forem idênticas e se qualificarem para a junção. Quando tais empates existem e você executa a mesma consulta de junção várias vezes, você pode obter resultados diferentes. Os resultados não são determinísticos porque qualquer uma das linhas de vinculação pode ser retornada. Se não tiver certeza sobre os resultados das consultas ASOF JOIN, verifique se há correspondências exatas nos valores de carimbo de data/hora das linhas na tabela da direita.

Por exemplo, usando as mesmas tabelas dos exemplos da seção anterior, adicione uma coluna right_id para right_table e insira as seguintes linhas:

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

Duas das linhas são idênticas, exceto por seus valores right_id. Agora execute a seguinte consulta ASOF JOIN:

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

Observe que as linhas A1 e A2 de right_table se qualificam para a junção, mas apenas A2 é retornado. Em uma execução subsequente da mesma consulta, A1 poderia ser devolvido em vez disso.

Como reescrever as consultas ASOF JOIN para reduzir varreduras na tabela certa

Quando a cardinalidade da coluna de junção ON ou USING na tabela da esquerda for menor que a cardinalidade da coluna de junção na tabela da direita, o otimizador não remove as linhas não correspondentes da tabela da direita. Portanto, mais linhas do que o necessário para a junção serão escaneadas da tabela da direita. Esse comportamento geralmente ocorre quando a consulta inclui um filtro altamente seletivo em uma coluna sem junção da tabela da esquerda, e o filtro reduz a cardinalidade da coluna de junção.

Você pode contornar esse problema reduzindo manualmente as linhas que se qualificam para a junção. Por exemplo, a consulta original tem o seguinte padrão e t1.c1 tem cardinalidade menor que t2.c1:

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

Você pode reescrever a consulta da seguinte forma para selecionar manualmente as linhas de t2 onde os valores t2.c1 são encontrados em 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;

Uso de ASOF e MATCH_CONDITION como nomes de objetos e aliases

Uso de palavras-chave ASOF e MATCH_CONDITION na sintaxe do comando SELECT é restrito:

  • Se uma instrução SELECT usar ASOF ou MATCH_CONDITION como nome de uma tabela, exibição ou exibição inline, você deverá identificá-la da seguinte forma:

    • Se o objeto foi criado com aspas duplas no nome, use o mesmo nome entre aspas duplas.

    • Se o objeto foi criado sem aspas duplas no nome, use aspas duplas e letras maiúsculas.

    Por exemplo, as seguintes instruções não são mais permitidas e retornam erros:

    SELECT * FROM asof;

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

    Se você criou os objetos com aspas duplas, corrija o problema usando aspas duplas:

    SELECT * FROM "asof";

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

    Se você criou os objetos sem aspas duplas, corrija o problema usando aspas duplas e letras maiúsculas:

    SELECT * FROM "ASOF";

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

    Consulte também Identificadores não delimitados por aspas.

  • Se uma instrução SELECT usar ASOF ou MATCH_CONDITION como alias, você deverá usar AS antes do alias ou colocar aspas duplas no alias. Por exemplo, as seguintes instruções não são mais permitidas e retornam erros:

    SELECT * FROM t1 asof;

SELECT * FROM t2 match_condition;

    Corrija o problema de uma das seguintes maneiras:

    SELECT * FROM t1 AS asof;

SELECT * FROM t1 "asof";

SELECT * FROM t2 AS match_condition;

SELECT * FROM t2 "match_condition";

Exemplos

Os exemplos a seguir demonstram o comportamento esperado de consultas ASOF JOIN. Comece executando a consulta em União de duas tabelas na correspondência mais próxima (alinhamento), então prossiga com os exemplos aqui.

Resultados preenchidos com NULL

Insira uma nova linha na tabela trades com uma data que seja um dia anterior às linhas existentes em trades e quotes:

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

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

Agora execute a primeira consulta de exemplo novamente. Observe que a consulta retorna quatro linhas, mas a nova linha é preenchida com nulos. Não há nenhuma linha na tabela quotes que se qualifique para a condição de correspondência. As colunas de trades são retornadas e as colunas correspondentes de quotes são preenchidas com nulos.

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

Uso de um operador de comparação diferente na condição de correspondência

Seguindo o exemplo anterior, os resultados da consulta mudam novamente quando o operador de comparação na condição de correspondência é alterado. A consulta a seguir especifica o operador <= (em vez de >=):

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

Consulte também Operadores de comparação menor e maior que.

Especificação de uma condição USING em vez de uma condição ON

Você pode usar uma condição ON ou uma condição USING com consultas ASOF JOIN. A consulta a seguir é equivalente à consulta anterior, mas substitui ON por USING. A sintaxe USING(stock_symbol) implica a condição 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;

Junção interna a uma terceira tabela

O exemplo a seguir adiciona uma terceira tabela companies à junção para escolher o nome da empresa para cada símbolo de ação. Você pode usar um INNER JOIN regular com uma condição ON (ou alguma outra sintaxe de junção padrão) para adicionar a terceira tabela. No entanto, observe que USING(stock_symbol) não funcionaria aqui porque a referência a stock_symbol seria ambígua.

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

Números como carimbos de data/hora

O exemplo a seguir demonstra que a condição de correspondência pode comparar valores numéricos. Nesse caso, as tabelas possuem valores UNIX de carimbo de data/hora armazenados em colunas NUMBER(38,0). 1696150805 é equivalente a 2023-10-30 10:20:05.000 (três segundos depois de 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 |
+--------------+------------+----------+--------------+--------------+------------+----------+--------------+--------------+

Colunas TIME na condição de correspondência

Os exemplos a seguir unem tabelas que contêm observações meteorológicas. As observações nestas tabelas são registadas em colunas TIME. Você pode criar e carregar as tabelas da seguinte maneira:

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

Quando você executa a primeira consulta, alguns dos valores TIME parecem ser exatamente os mesmos no conjunto de resultados (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 |
+----------+-----------+-------+-------------+----------+-----------+-------+-------------+

Para retornar uma exibição mais precisa de valores TIME, incluindo milissegundos, execute o seguinte comando ALTER SESSION e, em seguida, execute a consulta ASOF JOIN novamente:

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

Várias junções ASOF em uma consulta

O exemplo a seguir mostra como conectar uma sequência de duas ou mais junções ASOF em um único bloco de consulta. As três tabelas (snowtime, raintime, preciptime) contêm observações meteorológicas que foram registradas em locais específicos em horários específicos. A coluna de interesse é a coluna observation. As linhas são agrupadas logicamente por estado.

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

Operadores de comparação menor e maior que

Seguindo o exemplo anterior, duas junções ASOF são especificadas, mas desta vez a primeira condição de correspondência usa o operador > e a segunda usa o operador <. O resultado é uma única linha que retorna dados de todas as três tabelas e três linhas que retornam dados de duas das tabelas. Muitas das colunas no conjunto de resultados são preenchidas com nulos.

Logicamente, a consulta encontra apenas uma linha em que o horário observado na tabela snowtime foi posterior ao horário observado na tabela raintime, mas anterior ao horário observado na tabela preciptime.

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

Exemplos de casos de erro esperados

Os exemplos a seguir mostram consultas que retornam erros de sintaxe esperados.

Tendo declarado que snowtime s é a tabela da esquerda, você não pode iniciar a condição de correspondência com uma referência à tabela da direita, preciptime p:

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.

Somente os operadores >=, <=, > e < são permitidos em condições de correspondência:

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.

A cláusula ON para ASOF JOIN deve conter condições de igualdade:

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.

Uma condição de igualdade da cláusula ON não pode conter disjunções:

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.

As junções ASOF não podem ser usadas com exibições inline 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;

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