snowflake-cloud-data-platform - 使用横向展平和连接会导致语法错误

Question

如果我尝试在同一个查询中加入和横向展平，我会遇到语法错误。我创建了以下示例：

设置

    CREATE TABLE "DEMO_DB"."PUBLIC"."MAIN_TABLE" (id number, department_id number, stuff variant);
    insert into "DEMO_DB"."PUBLIC"."MAIN_TABLE" (id, department_id, stuff) SELECT 1, 1, PARSE_JSON('[{ "json_id": 1, "name": "Thing 1-One" }, { "json_id": 2, "name": "Thing 1-Two" }]');
    insert into "DEMO_DB"."PUBLIC"."MAIN_TABLE" (id, department_id, stuff) SELECT 2, 1, PARSE_JSON('[{ "json_id": 1, "name": "Thing 2-One" }, { "json_id": 2, "name": "Thing 2-Two" }]');
    insert into "DEMO_DB"."PUBLIC"."MAIN_TABLE" (id, department_id, stuff) SELECT 3, 2, PARSE_JSON('[{ "json_id": 1, "name": "Thing 3-One" }, { "json_id": 3, "name": "Thing 3-Three" }]');

并设置一个表加入：

    CREATE TABLE "DEMO_DB"."PUBLIC"."DEPARTMENTS" (id number, name string);
    insert into "DEMO_DB"."PUBLIC"."DEPARTMENTS" (id, name) values (1, 'First Department');
    insert into "DEMO_DB"."PUBLIC"."DEPARTMENTS" (id, name) values (2, 'Second Department');

例子

基本横向展平工作：

    SELECT
        MAIN_TABLE.id,
        MAIN_TABLE.department_id,
        my_stuff.value:json_id::number stuff_id,
        my_stuff.value:name::string stuff_name
    FROM
        "DEMO_DB"."PUBLIC"."MAIN_TABLE",
        lateral flatten ( input => MAIN_TABLE.stuff ) my_stuff
    ;

问题

“SQL 编译错误：位置 59 处的错误行 9 无效标识符 'MAIN_TABLE.DEPARTMENT_ID'”

    SELECT
        MAIN_TABLE.id,
        MAIN_TABLE.department_id,
        DEPARTMENTS.name department_name,
        my_stuff.value:json_id::number stuff_id,
        my_stuff.value:name::string stuff_name
    FROM
        "DEMO_DB"."PUBLIC"."MAIN_TABLE",
        lateral flatten ( input => MAIN_TABLE.stuff ) my_stuff
    JOIN "DEMO_DB"."PUBLIC"."DEPARTMENTS" on (DEPARTMENTS.id = MAIN_TABLE.department_id);

更有效

-- 没有横向展平的基本连接

SELECT
    MAIN_TABLE.id,
    MAIN_TABLE.department_id,
    DEPARTMENTS.name department_name -- ,
--    my_stuff.value:json_id::number stuff_id,
--    my_stuff.value:name::string stuff_name
FROM
    "DEMO_DB"."PUBLIC"."MAIN_TABLE" -- ,
--    lateral flatten ( input => MAIN_TABLE.stuff ) my_stuff
JOIN "DEMO_DB"."PUBLIC"."DEPARTMENTS" on (DEPARTMENTS.id = MAIN_TABLE.department_id);

-- Using a table in the from clause with a where
SELECT
    MAIN_TABLE.id,
    MAIN_TABLE.department_id,
    DEPARTMENTS.name department_name,
    my_stuff.value:json_id::number stuff_id,
    my_stuff.value:name::string stuff_name
FROM
    "DEMO_DB"."PUBLIC"."MAIN_TABLE",
    lateral flatten ( input => MAIN_TABLE.stuff ) my_stuff,
    "DEMO_DB"."PUBLIC"."DEPARTMENTS"
 WHERE (DEPARTMENTS.id = MAIN_TABLE.department_id);



-- Using an inline view to pull in the join data
SELECT
    MAIN_TABLE.id,
    MAIN_TABLE.department_id,
    MAIN_TABLE.department_name,
    my_stuff.value:json_id::number stuff_id,
    my_stuff.value:name::string stuff_name
FROM
    (
      SELECT
        MAIN_TABLE.*,
        DEPARTMENTS.name department_name
      FROM
        "DEMO_DB"."PUBLIC"."MAIN_TABLE"
      JOIN "DEMO_DB"."PUBLIC"."DEPARTMENTS" on (DEPARTMENTS.id = MAIN_TABLE.department_id)
    ) MAIN_TABLE,
    lateral flatten ( input => MAIN_TABLE.stuff ) my_stuff;

标签： snowflake-cloud-data-platformlateral-join

Answer 1

这有效：

    SELECT
    MAIN_TABLE.id,
    MAIN_TABLE.department_id,
    DEPARTMENTS.name department_name,
    my_stuff.value:json_id::number stuff_id,
    my_stuff.value:name::string stuff_name
FROM
     "DEMO_DB"."PUBLIC"."MAIN_TABLE"
JOIN "DEMO_DB"."PUBLIC"."DEPARTMENTS" on (DEPARTMENTS.id = MAIN_TABLE.department_id),
     lateral flatten ( input => MAIN_TABLE.stuff ) my_stuff ;

横向连接的顺序很重要。这是一个讨论它的用法说明：

https://docs.snowflake.com/en/sql-reference/constructs/join-lateral.html#usage-notes