《雪花》中无限与楠的处理

Question

我正在将数据仓库前端从Hadoop后端转换为雪花后端，我需要将其每个SQL函数转换为雪花等效函数。我遇到的一个麻烦是处理产生无穷大/-无穷大或NaN（不是一个数字）的公式。

在Hadoop中，这是一个使用IS_INF和is_nan检查公式的简单问题。在SQL Server（我们测试的另一个后端）中，这可以使用SET ARITHABORT off/SET ANSI_WARNINGS off来完成。Hadoop方法向SQL添加了额外的逻辑检查，SQL Server方法直接忽略错误并返回null，而不是抛出错误。我更喜欢后一种方法，因为它不会减慢查询速度，但我对任何在雪花中真正有效的方法都持开放态度，因为到目前为止我还找不到一种方法。

我应该注意到，有一些雪花函数可以帮助避免非常特殊的错误，比如被零除（即DIV0函数或NULLIF)，但这对我没有帮助，原因有二。首先，它只处理某些问题（div by 0）。我需要一些东西，将处理产生INF/NAN的任何公式。第二，有问题的公式往往是终端用户在前端写的，这意味着我不知道公式的哪一部分是被除数，哪一部分是除数。这个公式可能非常复杂（包括CASE、WHEN或其他函数或任何数量的东西），所以解析出除数（这样我可以使用NULLIF)，或者解析出被除数和除数（这样我可以使用DIV0)都不是一个选项。

我注意到Snowflake的文档，其中说FLOAT数据类型可以处理像Infinity和NaN这样的特殊值，但我看不到任何方法在公式中实际生成这些值而不引发错误。

Answer 1

（据我所知）对于无限的NaN值没有构建方便函数。尤其不是你想要的那种程度。在我自己的工作中，我没有处理过这样的情况，即数值无限成为一个问题，但是我们可以通过创建自己的函数来实现至少一些功能。在这种情况下，我们可能只使用SQL-UDF，而JavaScript有更多的灵活性。
下面我为此定义了4个函数(is_inf、is_na、if_na和if_inf)。请注意，这些函数接受变量输入并返回变量输出。这使得我不必为所有不同的数据类型重载函数。实际上，这只对varchar和派生类型(date、timestamp)，以及可能的地理空间数据有问题，这些数据必须强制转换为variant。

CREATE FUNCTION IS_INF(x VARIANT)
RETURNS boolean
AS 
$$
    iff(try_cast(x::varchar as float) IN ('Inf', -'Inf'), TRUE, FALSE)
$$;
CREATE FUNCTION IS_NA(x VARIANT)
RETURNS boolean
AS 
$$
    iff(try_cast(x::varchar as float) = 'NaN', TRUE, FALSE)
$$;
CREATE FUNCTION IF_NA(x VARIANT, y VARIANT, z VARIANT)
RETURNS VARIANT 
AS 
$$
    iff(try_cast(x::varchar as float) = 'NaN', y, z)
$$;
CREATE FUNCTION IF_INF(x VARIANT, y VARIANT, z VARIANT)
RETURNS VARIANT 
AS 
$$
    iff(try_cast(x::varchar as float) IN ('Inf', -'Inf'), y, z)
$$;

请注意，这要求它们存储在特定的模式中，因此如果希望在另一个模式或数据库中使用它，则需要选择特定的模式。为此目的拥有一个数据库/模式通常是有帮助的。另外，snowflake确实允许is_nan，但是我后来不能使用这个函数。

select is_inf(3245); -- false
SELECT is_na('NaN'); -- true
SELECT IF_inf(3245, 'NaN'::float, 4); -- 4
SELECT if_na(3245, TRUE, FALSE); -- false