Snowflake使用CTE创建表格

Question

Snowflake是否允许您创建一系列CTE，然后在最后将它们连接在一起以创建一个表？

例如：

with CTE1 as ( SELECT * FROM TABLE1)
,CTE2 AS (SELECT * FROM TABLE2)
,CTE3 AS (SELECT * FROM TABLE3)
CREATE TABLE TABLE_NAME_HERE AS 
SELECT * FROM CTE1 AS 1
LEFT JOIN CTE2 AS 2 ON 1.KEY = 2.KEY
LEFT JOIN CTE3 AS 3 ON 1.KEY = 3.KEY

我收到一个unexpected 'CREATE'.错误

Answer 1

这是一个有趣的表单，因为CTAS(CREATE TABLE AS(表单是

CREATE TABLE <name> AS <select>

SELECT形式可以有CTE，CTE形式是

WITH <name> AS <select>

CTE也可以处于子选择中；它只是一个选择"；，因此

SELECT <columns> 
FROM (
WITH cte_1 AS (
SELECT <columns>
FROM table
)
SELECT <columns>
FROM cte_1
)

这说明了为什么Lukasz显示的形式是正确的，因为如果我们添加更多的parens

CREATE TABLE name AS (
WITH cte_1 AS (
SELECT <columns>
FROM table
)
SELECT <columns>
FROM cte_1
)

Answer 2

是的，有可能：

CREATE TABLE TABLE_NAME_HERE AS 
WITH CTE1 as ( SELECT * FROM TABLE1)
,CTE2 AS (SELECT * FROM TABLE2)
,CTE3 AS (SELECT * FROM TABLE3)
SELECT *     -- here should be explicit column list to avoid name duplication error
FROM CTE1 AS 1
LEFT JOIN CTE2 AS 2 ON 1.KEY = 2.KEY
LEFT JOIN CTE3 AS 3 ON 1.KEY = 3.KEY;