在下面的代码中，我可以毫无问题地执行 LAG 和 PARTITION BY 作为查询。但我需要它作为一张桌子。如果我执行 CTAS，我需要在列上使用别名。但是当我尝试为其添加别名时，它失败了。我认为这是有效的 SQL。但我正在使用Snowflake，所以我向超级用户询问，因为我担心这是供应商问题。
如何使用该查询的输出创建一个表？
drop table a;
create table a (
a1 varchar,
a2 varchar
);
insert into a values ( 'a', 1 );
insert into a values ( 'a', 3 );
insert into a values ( 'a', 5 );
insert into a values ( 'a', 9 );
insert into a values ( 'b', 1 );
insert into a values ( 'b', 3 );
insert into a values ( 'b', 4 );
insert into a values ( 'c', 3 );
insert into a values ( 'c', 4 );
insert into a values ( 'c', 5 );
-- This works fine.
select a1
, a2
, lag(a2)
over (partition by a1 order by a2)
from a
;
-- This fails.
select a1
, a2
, lag(a2) new_col_name
over (partition by a1 order by a2)
from a
;
-- But if I can't name the column, I can't CTAS.
create table b as
select a1
, a2
, lag(a2)
over (partition by a1 order by a2)
from a
;
输出是：
status
A successfully dropped.
status
Table A successfully created.
number of rows inserted
1
number of rows inserted
1
number of rows inserted
1
number of rows inserted
1
number of rows inserted
1
number of rows inserted
1
number of rows inserted
1
number of rows inserted
1
number of rows inserted
1
number of rows inserted
1
A1 A2 LiteralRedacted0
b 1 NULL
b 3 1
b 4 3
c 3 NULL
c 4 3
c 5 4
a 1 NULL
a 3 1
a 5 3
a 9 5
001003 (42000): SQL compilation error:
syntax error line 4 at position 2 unexpected 'over'.
002022 (42601): SQL compilation error:
Missing column specification
