카테고리:: 쿼리 구문

ASOF JOIN¶

ASOF JOIN 작업은 서로 따르거나, 서로 앞에 있거나, 정확히 일치하는 타임스탬프 값을 기반으로 두 테이블의 행을 결합합니다. 첫 번째(또는 왼쪽) 테이블의 각 행에 대해 조인은 가장 가까운 타임스탬프 값을 갖는 두 번째(또는 오른쪽) 테이블의 단일 행을 찾습니다. 오른쪽에 있는 적격 행은 가장 가까운 일치 항목이며, 지정된 비교 연산자에 따라 시간상 동일하거나 이전 또는 이후일 수 있습니다.

이 항목에서는 FROM 절에서 ASOF JOIN 구문을 사용하는 방법에 대해 설명합니다. ASOF 조인에 대한 자세한 개념 설명은 시계열 데이터 분석하기 섹션을 참조하십시오.

내부 및 외부 조인과 같은 다른 표준 조인 유형에 대한 구문을 다루는 JOIN 도 참조하십시오.

구문¶

다음 FROM 절 구문은 ASOF JOIN에만 적용됩니다.

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

Copy

매개 변수¶

FROM

FROM 절의 첫 번째(또는 왼쪽) 테이블은 두 번째(또는 오른쪽) 테이블의 레코드를 따르거나(시간상) 앞서거나 해당 레코드와 정확하게 동기화되는 레코드를 포함하는 것으로 가정됩니다. 왼쪽 테이블의 행과 일치하는 항목이 없으면 오른쪽 테이블의 열이 null로 채워집니다.

일반 테이블 및 뷰 외에도 모든 오브젝트 참조를 ASOF JOIN에서 사용할 수 있습니다. FROM 섹션을 참조하십시오.

조인이 지원되는 대부분의 상황에서 ASOF JOIN을 사용할 수 있습니다. 일부 제한 사항에 대한 자세한 내용은 사용법 노트 를 참조하십시오.

MATCH_CONDITION ( left_table.timecol comparison_operator right_table.timecol )

이 조건에서는 각 테이블에서 비교할 특정 타임스탬프 열 이름이 지정됩니다.

이 조건에서는 테이블의 순서가 중요합니다. 왼쪽 테이블의 이름을 먼저 지정해야 합니다.
괄호가 꼭 필요합니다.
비교 연산자는 >=, <=, >, < 중 하나여야 합니다. 같음 연산자(=)는 지원되지 않습니다.
지원되는 모든 데이터 타입은 DATE, TIME, DATETIME, TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ입니다.
일치 조건에서 NUMBER 열을 사용할 수도 있습니다. 예를 들어 (1970년 1월 1일 이후 경과된 초 수를 정의하는) UNIX 타임스탬프를 포함하는 NUMBER 열이 있을 수 있습니다.
일치하는 두 열의 데이터 타입이 완전히 동일할 필요는 없지만 호환 가능 해야 합니다.

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

선택적 ON 또는 USING 절은 쿼리 결과를 논리적으로 그룹화하기 위해 두 테이블의 열에 대해 하나 이상의 등식 조건을 정의합니다.

ON 및 USING에 대한 일반 정보는 JOIN 섹션을 참조하십시오. USING으로 지정된 조인은 중간 결과 세트의 조인 열 중 하나를 프로젝션하며, 두 열 모두를 프로젝션하지 않습니다. ON 절로 지정된 조인은 두 조인 열을 모두 프로젝션합니다.

다음 참고 사항은 ASOF JOIN에만 해당됩니다.

ON 절의 비교 연산자는 등호(=)여야 합니다.
ON 절에는 분리(OR와 연결된 조건)를 포함할 수 없습니다. AND와 연결된 조건이 지원됩니다.
조건의 각 측면은 조인의 두 테이블 중 하나만 참조해야 합니다. 그러나 테이블 참조의 순서는 중요하지 않습니다.
각 조건은 괄호로 묶을 수 있지만 필수 사항은 아닙니다.

조인 동작에 대한 자세한 내용 및 ON 조건 대신 USING 조건 지정하기 섹션도 참조하십시오.

사용법 노트¶

지정된 행에 대해 오른쪽 테이블에 일치하는 항목이 없으면 오른쪽 테이블에서 선택한 열에 대해 결과가 null로 채워집니다. (ASOF 조인은 이 점에서 왼쪽 외부 조인과 유사합니다.)
일치 조건에서 TIME 열을 사용하는 경우(타임스탬프 유형 중 하나와 반대) ASOF JOIN 쿼리 결과를 볼 때 비교되는 정확한 TIME 값을 보려면 TIME_OUTPUT_FORMAT 매개 변수를 설정해야 할 수도 있습니다. 기본적으로 TIME 열 표시에서는 밀리초가 잘립니다. 일치 조건의 TIME 열 을 참조하십시오.
각 조인에 대해 모든 구문 규칙을 준수하는 한 동일한 쿼리에서 두 개 이상의 ASOF 조인을 사용할 수 있습니다. 각 조인 바로 뒤에 자체 MATCH_CONDITION이 와야 합니다. 단일 MATCH_CONDITION을 여러 ASOF 조인에 적용할 수 없습니다. 쿼리의 여러 ASOF 조인 을 참조하십시오.
LATERAL 테이블 함수 또는 LATERAL 인라인 뷰를 사용한 조인에는 ASOF 조인이 지원되지 않습니다. 측면 조인에 대한 자세한 내용은 LATERAL 섹션을 참조하십시오.
자체 참조가 포함된 ASOF 조인은 RECURSIVE 공통 테이블 식(CTE)에서 허용되지 않습니다. CTEs 에 대한 정보는 WITH 섹션을 참조하십시오.
ASOF JOIN 쿼리의 EXPLAIN 출력은 ON(또는 USING) 조건과 MATCH_CONDITION을 식별합니다. 예를 들어, 텍스트 또는 테이블 형식으로 다음 텍스트와 유사한 출력이 계획의 테이블 스캔 위에 표시됩니다.
```
->ASOF Join  joinKey: (S.LOCATION = R.LOCATION) AND (S.STATE = R.STATE),
  matchCondition: (S.OBSERVED >= R.OBSERVED)
```
쿼리 프로필 은 계획의 ASOF JOIN 작업도 명확하게 식별합니다. 이 예에서는 테이블 스캔이 모두 조인으로 보존되는 왼쪽 테이블에서 2,200만 개의 행을 읽는 것을 확인할 수 있습니다. 프로필에는 조인에 대한 일치 조건도 표시됩니다.

테이블 스캔이 그 위의 ASOF JOIN 연산자에 행을 공급하는 모습을 보여주는 쿼리 프로필.

조인 동작에 대한 자세한 내용¶

ASOF JOIN에 대한 선택적 ON(또는 USING) 조건은 필수적인 일치 조건으로 최종 일치 행이 선정되기 전에 테이블 행을 그룹화하거나 분할하는 방법을 제공합니다. 조인된 테이블의 행을 테이블이 공유하는 하나 이상의 차원(티커 심벌, 위치, 구/군/시, 시/도, 회사 이름 등)에서 그룹화하려면 ON 조건을 사용하십시오. ON 조건을 사용하지 않는 경우 왼쪽 테이블의 각 행은 최종 결과 세트에서 오른쪽 테이블의 모든 행과 (시간 기준으로) 일치할 수 있습니다.

다음 예제에서, 테이블 left_table 및 right_table 의 c1 열에는 A, B 등의 값이 있고 c2 열에는 1, 2 등의 값이 있습니다. c3 열은 TIME 열이고, c4 는 숫자 값(관심 있는 열)입니다.

먼저 테이블 두 개를 만들어 로드합니다.

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

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

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

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

Copy

SELECT * FROM left_table ORDER BY c1, c2;

Copy

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

SELECT * FROM right_table ORDER BY c1, c2;

Copy

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

c1 및 c2 가 모두 쿼리의 ON 조건 열인 경우 A 와 1, A 와 2, B 와 1, 또는 B 와 2 가 두 테이블에 모두에서 발견될 때만 왼쪽 테이블의 행이 오른쪽 테이블의 행과 일치합니다. 그와 같은 값과 일치하는 항목이 없으면 오른쪽 테이블 열이 null로 채워집니다.

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

Copy

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

ON 조건이 제거되면 c1 및 c2 의 값 조합이 최종 결과에서 일치할 수 있습니다. 일치 조건에 따라서만 결과가 결정됩니다.

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

Copy

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

오른쪽 테이블에 “동점”이 있을 때 예상되는 동작¶

ASOF JOIN 쿼리는 항상 왼쪽 테이블의 단일 행을 오른쪽 테이블의 단일 행과 일치시키려고 시도합니다. 이 동작은 오른쪽 테이블의 두 개(또는 그 이상)의 행이 동일하고 조인에 적합한 경우에도 적용됩니다. 이러한 동점이 존재하고 동일한 조인 쿼리를 여러 번 실행하면 다른 결과가 나올 수 있습니다. 동점 행 중 하나가 반환될 수 있으므로 결과는 결정적이지 않습니다. ASOF JOIN 쿼리 결과가 확실하지 않은 경우 오른쪽 테이블의 행에 대한 타임스탬프 값에서 정확히 일치하는지 확인합니다.

예를 들어, 이전 섹션의 예제와 동일한 테이블을 사용하여 right_table 에 right_id 열을 추가하고 다음 행을 삽입합니다.

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

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

SELECT * FROM right_table ORDER BY 1, 2;

Copy

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

두 행은 right_id 값을 제외하고는 동일합니다. 이제 다음 ASOF JOIN 쿼리를 실행합니다.

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

Copy

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

right_table 의 A1 및 A2 행은 모두 조인 자격이 있지만 A2 만 반환된다는 점에 유의하십시오. 이후 동일한 쿼리를 실행하면 A1 이 대신 반환될 수 있습니다.

오른쪽 테이블에서 스캔을 줄이기 위해 ASOF JOIN 쿼리 재작성하기¶

왼쪽 테이블의 ON 또는 USING 조인 열의 카디널리티가 오른쪽 테이블의 조인 열의 카디널리티보다 낮은 경우 최적화 프로그램은 오른쪽 테이블에서 일치하지 않는 행을 정리 하지 않습니다. 따라서 조인에 필요한 행보다 더 많은 행이 오른쪽 테이블에서 스캔됩니다. 이러한 동작은 일반적으로 쿼리에 왼쪽 테이블의 조인되지 않은 열에 대한 매우 선택적인 필터가 포함되어 있고, 필터가 조인 열의 카디널리티를 줄이는 경우 발생합니다.

조인에 적합한 행을 수동으로 줄이면 이 문제를 해결할 수 있습니다. 예를 들어, 원래 쿼리의 패턴은 다음과 같으며 t1.c1 는 t2.c1 보다 카디널리티가 낮습니다.

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

Copy

다음과 같이 쿼리를 재작성하여 t2 에서 t2.c1 값이 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;

Copy

ASOF 및 MATCH_CONDITION을 오브젝트 이름과 별칭으로 사용하기¶

SELECT 명령 구문에서는 ASOF 및 MATCH_CONDITION 키워드의 사용이 제한됩니다.

SELECT 문이 ASOF 또는 MATCH_CONDITION을 테이블, 뷰 또는 인라인 뷰의 이름으로 사용하는 경우 다음과 같이 식별해야 합니다.
- 이름에 큰따옴표를 포함하여 오브젝트를 생성한 경우 동일한 큰따옴표를 포함한 이름을 사용합니다.
- 이름에 큰따옴표 없이 오브젝트를 생성한 경우 큰따옴표와 대문자를 사용합니다.
예를 들어 다음 문은 더 이상 허용되지 않으며 오류를 반환합니다.
```
SELECT * FROM asof;

WITH match_condition AS (SELECT * FROM T1) SELECT * FROM match_condition;
```
Copy
큰따옴표를 사용하여 오브젝트를 생성한 경우 큰따옴표를 사용하여 문제를 해결하십시오.
```
SELECT * FROM "asof";

WITH "match_condition" AS (SELECT * FROM T1) SELECT * FROM "match_condition";
```
Copy
큰따옴표 없이 오브젝트를 생성한 경우 큰따옴표와 대문자를 사용하여 문제를 해결하십시오.
```
SELECT * FROM "ASOF";

WITH "MATCH_CONDITION" AS (SELECT * FROM T1) SELECT * FROM "MATCH_CONDITION";
```
Copy
따옴표가 없는 식별자 도 참조하십시오.
SELECT 문이 ASOF 또는 MATCH_CONDITION을 별칭으로 사용하는 경우 별칭 앞에 AS를 사용하거나 별칭을 큰따옴표로 묶어야 합니다. 예를 들어 다음 문은 더 이상 허용되지 않으며 오류를 반환합니다.
```
SELECT * FROM t1 asof;

SELECT * FROM t2 match_condition;
```
Copy
다음 방법 중 하나로 문제를 해결하십시오.
```
SELECT * FROM t1 AS asof;

SELECT * FROM t1 "asof";

SELECT * FROM t2 AS match_condition;

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

예¶

다음 예에서는 ASOF JOIN 쿼리의 예상 동작을 보여줍니다. 가장 가까운 일치 항목에서 두 테이블 결합하기(정렬) 에서 쿼리를 실행하여 시작하고 여기에 있는 예제를 진행합니다.

NULL로 채워진 결과¶

trades 및 quotes 모두의 기존 행보다 하루 빠른 날짜를 사용하여 trades 테이블에 새 행을 삽입합니다.

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

Copy

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

이제 첫 번째 예제 쿼리를 다시 실행하십시오. 쿼리는 4개의 행을 반환하지만 새 행은 null로 채워집니다. quotes 테이블에 일치 조건에 적합한 행이 없습니다. trades 의 열이 반환되고 quotes 의 해당 열은 null로 채워집니다.

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

Copy

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

일치 조건에서 다른 비교 연산자 사용하기¶

이전 예에 이어 일치 조건의 비교 연산자가 변경되면 쿼리 결과가 다시 변경됩니다. 다음 쿼리는 >= 대신 <= 연산자를 지정합니다.

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

Copy

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

보다 작음 및 보다 큼 비교 연산자 도 참조하십시오.

ON 조건 대신 USING 조건 지정하기¶

ASOF JOIN 쿼리와 함께 ON 조건 또는 USING 조건을 사용할 수 있습니다. 다음 쿼리는 이전 쿼리와 동일하지만, ON을 USING으로 바꿉니다. 구문 USING(stock_symbol) 은 조건 t.stock_symbol=q.stock_symbol 을 의미합니다.

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

Copy

세 번째 테이블에 대한 내부 조인¶

다음 예제에서는 각 티커 심벌에 대한 회사 이름을 선택하기 위해 조인에 세 번째 companies 테이블을 추가합니다. ON 조건(또는 다른 표준 조인 구문)과 함께 일반 INNER JOIN을 사용하여 세 번째 테이블을 추가할 수 있습니다. 그러나 stock_symbol 에 대한 참조가 모호하므로 USING(stock_symbol) 은 여기서 작동하지 않습니다.

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

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

Copy

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

Copy

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

타임스탬프로서의 숫자¶

다음 예에서는 일치 조건이 숫자 값을 비교할 수 있음을 보여줍니다. 이 경우 테이블에는 NUMBER(38,0) 열에 저장된 UNIX 타임스탬프 값이 있습니다. 1696150805 는 2023-10-30 10:20:05.000 (1696150802 보다 3초 늦음)과 같습니다.

SELECT * FROM trades_unixtime;

Copy

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

SELECT * FROM quotes_unixtime;

Copy

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

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

Copy

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

일치 조건의 TIME 열¶

다음 예제에서는 날씨 관측값이 포함된 테이블을 조인합니다. 이러한 테이블의 관측치는 TIME 열에 기록됩니다. 다음과 같이 테이블을 만들고 로드할 수 있습니다.

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

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

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

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

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

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

Copy

첫 번째 쿼리를 실행하면 일부 TIME 값이 결과 세트(14:42:59, 14:42:44)에서 정확히 동일한 값으로 나타납니다.

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

Copy

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

밀리초를 포함하여 TIME 값의 보다 정확한 표시를 반환하려면 다음 ALTER SESSION 명령을 실행한 후 ASOF JOIN 쿼리를 다시 실행합니다.

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

Copy

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

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

Copy

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

한 쿼리에 여러 개의 ASOF 조인¶

다음 예에서는 단일 쿼리 블록에서 두 개 이상의 ASOF 조인 시퀀스를 연결하는 방법을 보여줍니다. 세 개의 테이블(snowtime, raintime, preciptime)에 모두 특정 시간에 특정 위치에서 기록된 기상 관측치가 포함됩니다. 관심 있는 열은 observation 열입니다. 행은 상태별로 논리적으로 그룹화됩니다.

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

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

Copy

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

보다 작음 및 보다 큼 비교 연산자¶

이전 예에 이어 두 개의 ASOF 조인이 지정되지만 이번에는 첫 번째 일치 조건에서 > 연산자를 사용하고 두 번째 일치 조건에서 < 연산자를 사용합니다. 결과는 세 테이블 모두에서 데이터를 반환하는 단일 행과 이들 테이블 중 두 개에서 데이터를 반환하는 세 개의 행입니다. 결과 세트의 열 중 다수가 null로 채워집니다.

논리적으로, 이 쿼리는 snowtime 테이블의 관측 시간이 raintime 테이블의 관측 시간보다 늦었지만 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;

Copy

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

예상되는 오류 사례의 예¶

다음 예에서는 예상되는 구문 오류를 반환하는 쿼리를 보여줍니다.

snowtime s 가 왼쪽 테이블이라고 선언했으므로 오른쪽 테이블 preciptime p 에 대한 참조를 사용하여 일치 조건을 시작할 수 없습니다.

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

Copy

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

일치 조건에는 >=, <=, > 및 < 연산자만 허용됩니다.

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

Copy

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

ASOF JOIN의 ON 절에는 등식 조건이 포함되어야 합니다.

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

Copy

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

ON 절 등식 조건에는 분리가 포함될 수 없습니다.

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

Copy

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

ASOF 조인은 LATERAL 인라인 뷰와 함께 사용할 수 없습니다.

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

Copy

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