LATERAL¶

何时使用 LATERAL¶

LATERAL 是适用于以下用例的宝贵工具：

• 在嵌套数据上链接表函数：当需要在数组中展平数组或在多个级别的嵌套 JSON 中导航时，每个后续表函数调用都必须引用前一个表函数的输出。横向联接使这成为可能。

• 使用特定于行的实参调用表函数：当表函数（例如 UDTF) 需要从左侧表中接收每行的不同输入值。

对于展平单级数组等简单情况，不使用横向连接，TABLE(FLATTEN(...)) 也能产生相同的结果。仅当内联视图必须引用 FROM 子句中前一个表达式中才能获得的列时，才需要使用横向联接。

语法¶

SELECT ...
  FROM <left_hand_table_expression>, LATERAL ( <inline_view> )
...

参数¶

left_hand_table_expression

这是行的源，例如：

• 表。

• 视图。

• 子查询。

• 表函数。

• 先前联接的结果。

inline_view

inline_view 可以是：

• 内联视图：在语句中定义的视图，仅在语句的持续时间内有效。

• 子查询。

• 表函数：内置表函数（如 FLATTEN）或用户定义的表函数 (UDTF)。

inline_view 不能是纯表引用。它必须是一个可以根据左侧表表达式中的值处理或筛选行的表达式，例如带有 WHERE 子句或表函数调用。

使用说明¶

• 关键字 LATERAL 后面的内联视图只能引用内联视图本身以及 FROM 子句内联视图左侧的表中的列。

  SELECT *
    FROM table_reference_me, LATERAL (...), table_do_not_reference_me ...
  

  Copy

• 尽管内联视图通常引用 left_hand_table_expression 中的一个或多个列，但并不要求这样做。

• 正如 INNER JOIN 语法可以使用逗号或关键字 INNER JOIN 一样，横向联接也可以使用逗号或关键字 INNER JOIN。例如：

  FROM departments AS d INNER JOIN LATERAL (...)
  

  Copy

• 不能指定 ON、USING 或 NATURAL JOIN 子句：

  • 横向表函数（SQL UDTF 除外)

  • 与表函数的外部横向联接（ SQL UDTF 除外)

  有关详细信息，请参阅 JOIN 主题中的使用说明。

示例¶

另请参阅 示例：使用带 FLATTEN 表函数的横向联接 和 使用 FLATTEN 筛选 WHERE 子句中的结果。

以下示例使用这些表：

CREATE TABLE departments (department_id INTEGER, name VARCHAR);
CREATE TABLE employees (employee_ID INTEGER, last_name VARCHAR,
  department_ID INTEGER, project_names ARRAY);

INSERT INTO departments (department_ID, name) VALUES
  (1, 'Engineering'),
  (2, 'Support');
INSERT INTO employees (employee_ID, last_name, department_ID) VALUES
  (101, 'Richards', 1),
  (102, 'Paulson',  1),
  (103, 'Johnson',  2);

下面的查询是一个带有子查询的横向联接。

SELECT *
  FROM departments AS d,
    LATERAL (SELECT * FROM employees AS e WHERE e.department_ID = d.department_ID) AS iv2
  ORDER BY employee_ID;

+---------------+-------------+-------------+-----------+---------------+---------------+
| DEPARTMENT_ID | NAME        | EMPLOYEE_ID | LAST_NAME | DEPARTMENT_ID | PROJECT_NAMES |
|---------------+-------------+-------------+-----------+---------------+---------------|
|             1 | Engineering |         101 | Richards  |             1 | NULL          |
|             1 | Engineering |         102 | Paulson   |             1 | NULL          |
|             2 | Support     |         103 | Johnson   |             2 | NULL          |
+---------------+-------------+-------------+-----------+---------------+---------------+

下面的 SQL 语句是等效的，并产生相同的输出。它使用关键字 INNER JOIN 而不是 FROM 子句中的逗号。

SELECT *
  FROM departments AS d INNER JOIN
    LATERAL (SELECT * FROM employees AS e WHERE e.department_ID = d.department_ID) AS iv2
  ORDER BY employee_ID;

CREATE OR REPLACE TABLE persons AS
  SELECT column1 AS id, PARSE_JSON(column2) AS c
    FROM VALUES
      (12712555,
       '{ "name": { "first": "John", "last": "Smith" },
          "contact": [{ "business": [
            { "type": "phone", "content": "555-1234" },
            { "type": "email", "content": "j.smith@example.com" }
          ]}]}'),
      (98127771,
       '{ "name": { "first": "Jane", "last": "Doe" },
          "contact": [{ "business": [
            { "type": "phone", "content": "555-1236" },
            { "type": "email", "content": "j.doe@example.com" }
          ]}]}');

SELECT id,
    f1.value:type::STRING AS contact_type,
    f1.value:content::STRING AS contact_details
  FROM persons p,
    LATERAL FLATTEN(INPUT => p.c, PATH => 'contact') f,
    LATERAL FLATTEN(INPUT => f.value:business) f1;

+----------+--------------+---------------------+
|       ID | CONTACT_TYPE | CONTACT_DETAILS     |
|----------+--------------+---------------------|
| 12712555 | phone        | 555-1234            |
| 12712555 | email        | j.smith@example.com |
| 98127771 | phone        | 555-1236            |
| 98127771 | email        | j.doe@example.com   |
+----------+--------------+---------------------+

LATERAL 与其他方法的比较¶

下表总结了与其他方法相比时，何时使用 LATERAL：