主页 / dba / 问题 / 336146
Accepted
James Madison
Asked: 2024-02-23 01:04:29 +0800 CST

Snowflake 不允许重命名分析函数

  • 772

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

2 个回答

  1. Best Answer

    SQL Server 有另一种语法用于从另一个选择创建表。

    此外，您还需要执行所有名为

    --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) 
  over (partition by a1 order by a2) as new_col_name
  from a
;

-- But if I can't name the column, I can't CTAS.

select a1
     , a2
     , lag(a2)
  over (partition by a1 order by a2) as new_column

  INTO b
  from a
;
    a1 a2 （无列名）
    A 1 无效的
    A 3 1
    A 5 3
    A 9 5
    乙 1 无效的
    乙 3 1
    乙 4 3
    C 3 无效的
    C 4 3
    C 5 4
    a1 a2 新列名
    A 1 无效的
    A 3 1
    A 5 3
    A 9 5
    乙 1 无效的
    乙 3 1
    乙 4 3
    C 3 无效的
    C 4 3
    C 5 4 
    SELECT * FROM b
    a1 a2 新列
    A 1 无效的
    A 3 1
    A 5 3
    A 9 5
    乙 1 无效的
    乙 3 1
    乙 4 3
    C 3 无效的
    C 4 3
    C 5 4

    小提琴

    • 1

  2. lag(...) over(...)是一个完整的表达式，不能随意分割。正确的语法是

    select a1
     , a2
     , lag(a2) over (partition by a1 order by a2) new_col_name
from a
    • 1

相关问题