SQL(Snowflake)创建月份表

Question

我想创建一个月度汇总表，对日期列位于另一个现有表的另外两个不同日期内的值进行汇总。

我尝试过使用以下SQL代码，但Snowflake返回此错误：SQL compilation error: error line 9 at position 10 invalid identifier 'DATE'。

select 
dateadd(month, seq, dt::date) as DATE,
year(DATE) as Y,
month(DATE) as M,
(select 
sum(items.total_price_disc_conv)
from BI.MODELS.CONTR_LINES_ACC as items
where DATE between items.subscription_start_date and items.subscription_end_date
) as ARR
from (
select 
seq4() as seq,  
dateadd(month, 1, '2019-12-01'::date) as dt 
from table(generator(rowcount => (12 * 5))
)
);

Answer 1

我认为您遇到这个错误是因为您试图在WHERE筛选器中使用ALIAS(DATE)。你能试试这个吗？

select 
dateadd(month, seq, dt::date) as DATE,
year(DATE) as Y,
month(DATE) as M,
(select 
sum(items.total_price_disc_conv)
from BI.MODELS.CONTR_LINES_ACC as items
where dateadd(month, seq, dt::date) between items.subscription_start_date and items.subscription_end_date
) as ARR
from (
select 
seq4() as seq,  
dateadd(month, 1, '2019-12-01'::date) as dt 
from table(generator(rowcount => (12 * 5))
)
);

当然，最好使用MY_DATE而不是DATE。