如何在Snowflake中结合Lateral Flatten与Join进行查询？

问题原因 #

你遇到的Error: invalid identifier 'R.STOCK_NUMBER'错误，是因为混合使用隐式逗号分隔的Lateral Flatten和显式JOIN时，Snowflake的查询解析顺序导致r.stock_number的作用域无法被正确识别。用逗号将table2 as r和lateral flatten连写后直接跟join table1，解析器会优先处理JOIN逻辑，此时r的关联关系还未被正确绑定。

解决方案 #

以下两种写法均可解决问题，推荐用子查询/CTE的方式，逻辑更清晰：

方案1：使用CTE预处理table2 #

先将table2经过Lateral Flatten过滤后的数据封装成CTE，再与table1关联：

WITH processed_table2 AS (
    SELECT DISTINCT r.stock_number
    FROM table2 as r
    LATERAL FLATTEN(input => r.line_items) as value
    WHERE r.created_date_time::date > TO_DATE('2023-12-04')
      AND value:reconciliationStatus::string != 'Matched'
      AND ARRAY_SIZE(r.payment_items) > 0
)
SELECT DISTINCT c.stocknumber
FROM table1 as c
JOIN processed_table2 as r ON r.stock_number = c.stocknumber
WHERE c.currentstatus = 'Sold'

方案2：使用子查询替代CTE #

直接在JOIN中嵌套子查询处理table2的Flatten逻辑：

SELECT DISTINCT c.stocknumber
FROM table1 as c
JOIN (
    SELECT DISTINCT r.stock_number
    FROM table2 as r
    LATERAL FLATTEN(input => r.line_items) as value
    WHERE r.created_date_time::date > TO_DATE('2023-12-04')
      AND value:reconciliationStatus::string != 'Matched'
      AND ARRAY_SIZE(r.payment_items) > 0
) as r ON r.stock_number = c.stocknumber
WHERE c.currentstatus = 'Sold'

补充写法：调整JOIN顺序（可选） #

如果想保留隐式Lateral Flatten的写法，可以先将table1与table2关联，再应用Lateral Flatten（注意过滤条件的位置）：

SELECT DISTINCT c.stocknumber
FROM table1 as c
JOIN table2 as r ON r.stock_number = c.stocknumber
LATERAL FLATTEN(input => r.line_items) as value
WHERE c.currentstatus = 'Sold'
  AND r.created_date_time::date > TO_DATE('2023-12-04')
  AND value:reconciliationStatus::string != 'Matched'
  AND ARRAY_SIZE(r.payment_items) > 0

内容的提问来源于stack exchange，提问作者GThree