寻求该嵌套SELECT查询的Snowflake等效写法，CORR函数尝试无效

以下是你提供的SQL对应的Snowflake等效写法，同时优化了原查询中取两个日期差绝对值最小值的逻辑（用Snowflake原生支持的LEAST函数替代子查询写法，更高效且避免语法兼容问题）：

SELECT 
    ROW_NUMBER() OVER (
        PARTITION BY x.LMID
        ORDER BY 
            LEAST(
                ABS(DATEDIFF(DAY, rh.OpenDT, x.Booked)),
                ABS(DATEDIFF(DAY, rh.CloseDT, x.Booked))
            ),
            v.LastVersion DESC
    ) AS RowNum,
    LEAST(
        ABS(DATEDIFF(DAY, rh.OpenDT, x.Booked)),
        ABS(DATEDIFF(DAY, rh.CloseDT, x.Booked))
    ) AS MinAbsDateDiff
FROM (
    SELECT 
        lm.LMID,
        lm.ID,
        lm.Booked,
        req.RequestId AS RequestID
    FROM #Pop lm
    INNER JOIN PUBLIC.request req ON req.ServicerLoanNumber = lm.ID
    INNER JOIN PUBLIC.RequestHistory rh 
        ON rh.RequestId = req.RequestId
        AND req.LastVersion = rh.Version
        AND (rh.FinalDispositionTypeId = 1 OR rh.RequestStatusTypeId = 20)
    WHERE 
        lm.Booked >= lm.AddDate
        AND YEAR(lm.Booked) >= 2013
) x
INNER JOIN PUBLIC.Request v ON v.RequestId = x.RequestID
INNER JOIN PUBLIC.RequestHistory rh 
    ON rh.RequestId = v.RequestId
    AND rh.Version = v.LastVersion;

关键转换说明 #

• 日期差函数：将SQL Server的DATEDIFF(DD, 开始日期, 结束日期)替换为Snowflake兼容的DATEDIFF(DAY, 开始日期, 结束日期)（Snowflake也支持DD作为DAY的别名，可按需选择）
• 最小值逻辑：用LEAST函数直接取两个绝对值的最小值，替代原查询中嵌套VALUES子查询的写法，简化语法同时提升查询性能
• Schema前缀：Snowflake默认无dbo系统schema，需将dbo.替换为你实际使用的schema名称（示例中用默认的PUBLIC.）
• 临时表：Snowflake支持#开头的会话级临时表，#Pop可直接使用；若为本地临时表，需提前用CREATE TEMPORARY TABLE定义

内容的提问来源于stack exchange，提问作者Matt