Snowflake won't allow an analytic function to be renamed

Question

In the code below, I can do the LAG and PARTITION BY with no problem as a query. But I need it as a table. If I do CTAS, I need an alias on the column. But when I try to put an alias on it, it fails. I think this is valid SQL. But I'm using Snowflake, so I'm asking it on Super User as I fear this is a vendor issue.

How can I create a table with the output of that query?

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
;

Output is:

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

score 3 · Accepted Answer · answered Feb 22 '24 at 18:06

3

lag(...) over(...) is an entire expression, you cannot split it at will. The correct syntax is

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

answered Feb 22 '24 at 18:06

mustaccio

28,207
24
60
76

score 2 · Answer 2 · edited Feb 22 '24 at 20:33

SQL server has another syntax for creating tables from another select.

Also you need to do that all columns named

--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	(No column name)
a	1	null
a	3	1
a	5	3
a	9	5
b	1	null
b	3	1
b	4	3
c	3	null
c	4	3
c	5	4

a1	a2	new_col_name
a	1	null
a	3	1
a	5	3
a	9	5
b	1	null
b	3	1
b	4	3
c	3	null
c	4	3
c	5	4

SELECT * FROM b

a1	a2	new_column
a	1	null
a	3	1
a	5	3
a	9	5
b	1	null
b	3	1
b	4	3
c	3	null
c	4	3
c	5	4

fiddle

Snowflake won't allow an analytic function to be renamed

2 Answers2