主页 / user-35334

James Madison's questions

Martin Hope
James Madison
Asked: 2024-02-23 01:04:29 +0800 CST
  • 7

在下面的代码中，我可以毫无问题地执行 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
query