SNOWFLAKE累计窗口框架不支持SUM函数

Question

我正在使用雪花数据库。在使用snowflakeSNOWFLAKE_SAMPLE_DATA数据库尝试一些windows操作符时，遇到了累积函数的情况。

不工作的SQL如下:

SELECT 
SUM( C_BIRTH_DAY )
OVER (PARTITION BY
C_BIRTH_MONTH,
C_BIRTH_YEAR 
ORDER BY
NVL(C_BIRTH_COUNTRY,'COSTA RICA')
RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS MAX1,
MIN( C_BIRTH_DAY )
OVER ( PARTITION BY C_BIRTH_MONTH ,
C_BIRTH_YEAR 
ORDER BY
NVL(C_BIRTH_COUNTRY,'COSTA RICA')
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS MIN1
FROM  CUSTOMER;

所提供的SQL将失败并返回错误消息:SNOWFLAKE Cumulative window frame unsupported for function SUM.

预期的结果是应该执行查询。

我用几个操作符AVG,MAX,SUM尝试了这个查询，所有我得到了相同的结果。

查看文档，语法看起来很好:

对于累积窗口

cumulativeFrame ::=
{
{ ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
| { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
}

对于滑动窗:

slidingFrame ::=
{
ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND <N> { PRECEDING | FOLLOWING }
| ROWS BETWEEN UNBOUNDED PRECEDING AND <N> { PRECEDING | FOLLOWING }
| ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
}

所以我目前找不到这个错误的解释。

Answer 1

在尝试了各种各样的东西之后，我发现如果我切换窗口语法，而不是使用RANGE，我使用这样的行:

SELECT 
SUM( C_BIRTH_DAY )
OVER (PARTITION BY
C_BIRTH_MONTH,
C_BIRTH_YEAR 
ORDER BY
NVL(C_BIRTH_COUNTRY,'CR')
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS MAX1,
MIN( C_BIRTH_DAY )
OVER ( PARTITION BY C_BIRTH_MONTH ,
C_BIRTH_YEAR 
ORDER BY
NVL(C_BIRTH_COUNTRY,'CR')
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS MIN1
FROM  CUSTOMER;

它! !