雪花SQL UDF: SELECT TOP N，LIMIT，ROW_NUMBER()和RANK()在子查询中不起作用？

Question

我一直在试验Snowflake SQL UDF解决方案，以便在时间戳中添加所需的工作日数。我一直在尝试定义一个函数，该函数将时间戳和所需的工作天数作为参数添加，并返回日期。该函数使用日期维度表。当我将单个日期作为参数传递给它时，该函数可以工作，但每当我试图给它一个完整的日期列时，它就会抛出错误“不支持的子查询类型无法求值”。似乎每当我试图在子查询中使用SELECT TOP N、LIMIT、ROW_NUMBER()或RANK()时，都会发生这种情况。

下面是我尝试过的一种方法的示例：

CREATE OR REPLACE FUNCTION "ADDWORKINGDAYSTOWORKINGDAY"(STARTDATE TIMESTAMP_NTZ, DAYS NUMBER)
RETURNS DATE
LANGUAGE SQL
AS '
    WITH CTE AS ( 
SELECT PAIVA 
    FROM EDW_DEV.REPORTING_SCHEMA."D_PAIVA"
    WHERE ARKIPAIVA = 1 AND ARKIPYHA_FI = FALSE 
    AND 1 = CASE WHEN DAYS < 0 AND P.PAIVA < TO_DATE(STARTDATE) THEN 1
                 WHEN DAYS < 0 AND P.PAIVA >= TO_DATE(STARTDATE) THEN 0
                 WHEN DAYS >= 0 AND P.PAIVA > TO_DATE(STARTDATE) THEN 1
                 ELSE 0
            END),
CTE2 AS (           
SELECT
    PAIVA
    ,CASE WHEN DAYS >= 0 THEN RANK() OVER
        (ORDER BY PAIVA)
        ELSE RANK() OVER 
        (ORDER BY PAIVA DESC)
    END AS RANK
FROM CTE
ORDER BY RANK)

SELECT TOP 1
ANY_VALUE (CASE WHEN DAYS IS NULL OR TO_DATE(STARTDATE) IS NULL THEN NULL
     WHEN DAYS = 0 THEN TO_DATE(STARTDATE)
     ELSE PAIVA
     END) AS PAIVA
FROM CTE2 
WHERE CASE  WHEN DAYS IS NULL OR TO_DATE(STARTDATE) IS NULL THEN 1 = 1
            WHEN DAYS > 0 THEN RANK = DAYS
            WHEN DAYS = 0 THEN 1 = 1
            ELSE RANK = -DAYS
      END
  ';

Answer 1

UDF是标量的。它们将只返回一个指定类型的值，在本例中为date。如果要为列返回一组值，则可能需要研究UDTF，即用户定义的表函数，它们返回表。

https://docs.snowflake.net/manuals/sql-reference/udf-table-functions.html

只需对UDF稍作修改，即可将其转换为UDTF。您可以将列而不是标量值传递给它。然后，可以将从UDTF得到的表与基表连接起来，以获得工作日加法值。