类别：: 查询语法

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

SELECT ...

SELECT 列表可以从两个表中选择任意列和所有列。当左表中的某行没有匹配项时，右表中的列将用 null 值填充。

FROM

FROM 子句中的第一个（或左）表假定包含某些记录，这些记录（在时间上）可能会在第二个（或右）表的记录之前、之后，或与第二个（或右）表的记录完全同步。

除了常规表和视图，在 ASOF JOIN 中还可以使用任何对象引用。请参阅 FROM。

ASOF JOIN 可用在支持联接的大多数上下文中。请参阅使用说明，以了解某些限制。

MATCH_CONDITION ( left_table.timecol comparison_operator right_table.timecol )

此条件为将要比较的每个表中的特定时间戳列指定名称。

表的顺序在条件中很重要。必须先指定左表名称。
圆括号是必需的。
比较运算符必须为以下运算符之一：>=、<=, >, <. The equals operator (=) 不受支持。
支持以下所有数据类型：DATE、TIME、DATETIME、TIMESTAMP、TIMESTAMP_LTZ、TIMESTAMP_NTZ、TIMESTAMP_TZ。
您还可以在匹配条件中使用 NUMBER 列。例如，您可能有包含 UNIX 时间戳的 NUMBER 列，这些列定义自 1970 年 1 月 1 日以来经过的秒数。
两个匹配列的数据类型不必完全相同，但它们必须兼容。

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

可选的 ON 或 USING 子句在两个表中的列上定义一个或多个相等条件，以便对查询结果进行逻辑分组。

有关 ON 和 USING 的一般信息，请参阅 JOIN。请注意，用 USING 指定的联接将投影其中间结果集中的联接列之一，而不是同时投影二者。用 ON 子句指定的联接会同时投影两个联接列。

以下说明特定于 ASOF JOIN：

ON 子句中的比较运算符必须是等号 (=)。
ON 子句不能包含分离性条件，即用 OR 连接的条件。支持用 AND 连接的条件。
条件的每侧只能引用联接中的两个表之一。但是，表引用的顺序并不重要。
每个条件都可以放在圆括号内，但这不是必需的。

另请参阅有关联接行为的更多详细信息和指定 USING 条件而不是 ON 条件。

使用说明¶

如果在右表中未找到给定行的匹配项，则结果是右表中的所选列将用 null 值填充。（ASOF 联接在这方面类似于左外部联接。）
如果在匹配条件中使用 TIME 列（而不是时间戳类型之一），则可能需要设置 TIME_OUTPUT_FORMAT 参数，以便在查看 ASOF JOIN 查询结果时看到正在比较的确切 TIME 值。默认情况下，TIME 列的显示会截断毫秒。请参阅匹配条件中的 TIME 列。
您可以在同一个查询中使用多个 ASOF 联接，前提是每个联接都遵循所有语法规则。每个联接之后都必须紧接自己的 MATCH_CONDITION。不能将单个 MATCH_CONDITION 应用于多个 ASOF 联接。请参阅查询中的多个 ASOF 联接。
含有 LATERAL 表函数或 LATERAL 内联视图的联接不支持 ASOF 联接。有关横向联接的更多信息，请参阅横向联接。
在 RECURSIVE 公用表表达式 (CTE) 中，不允许使用含有自引用的 ASOF 联接。有关 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 操作。在本例中，您可以看到表扫描从左表中读取了 2200 万行，这些行全部通过联接保留下来。配置文件还会显示联接的匹配条件。

有关联接行为的更多详细信息¶

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 表中插入一个新行，其日期要比 trades 和 quotes 中现有的行早一天：

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

Copy

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

现在，再次运行第一个示例查询。请注意，该查询返回四行，但新行以 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 |
+--------------+-------------------------+----------+-------------------------+--------------+

在匹配条件中使用不同的比较运算符¶

继续前一个示例，当匹配条件中的比较运算符改变时，查询结果会再次改变。以下查询指定 <= operator (instead of >=）：

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

另请参阅小于和大于比较运算符。

指定 USING 条件而不是 ON 条件¶

可以将 ON 条件或 USING 条件用于 ASOF JOIN 查询。以下查询与先前的查询等效，但它将 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 （或其他标准联接语法）来添加第三个表。但是，请注意 USING(stock_symbol) 在此处无效，原因是对 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.

在匹配条件中，只允许使用 >=、<=, >, and < 运算符：

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.