首页 / 技术问答 / Sql问答列表 / 问答详情

SQL到Snowflake转换嵌套的SELECT(值

有人知道这种类型的SQL查询的Snowflake Equiv吗？try 了snowflake中的Corr，但没有奏效.

select ROW_NUMBER() OVER ( PARTITION BY x.LMID
                               ORDER BY (   SELECT MIN(val)
                                            FROM (   VALUES ( ABS(DATEDIFF(DD, rh.OpenDT, x.Booked)))
                                                            , ( ABS(DATEDIFF(DD, rh.CloseDT, x.Booked)))) AS value (
                                            val ) )
                                        , v.LastVersion DESC ) AS RowNum
           , (   SELECT MIN(val)
                 FROM (   VALUES ( ABS(DATEDIFF(DD, rh.OpenDT, x.Booked)))
                                 , ( ABS(DATEDIFF(DD, rh.CloseDT, x.Booked)))) AS value ( val ) )
    FROM (   SELECT lm.LMID
                    , lm.ID
                    , lm.Booked
                    , req.RequestId AS RequestID
             FROM #Pop lm
                 INNER JOIN dbo.request req ON req.ServicerLoanNumber = lm.ID
                 INNER JOIN dbo.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 dbo.Request v ON v.RequestId = x.RequestID
        INNER JOIN dbo.RequestHistory rh ON rh.RequestId = v.RequestId
                                                       AND rh.Version = v.LastVersion;

推荐答案

所以如果我修改你的SQL，我可以看到它的层:

Select 
    ROW_NUMBER() OVER ( 
        PARTITION BY x.LMID 
        ORDER BY (
            SELECT 
                MIN(val)
            FROM (   
                VALUES 
                    ( ABS(DATEDIFF(DD, rh.OpenDT, x.Booked))),
                    ( ABS(DATEDIFF(DD, rh.CloseDT, x.Booked)))
            ) AS value(val) 
        ), v.LastVersion DESC 
    ) AS RowNum
    ,(   
        SELECT 
            MIN(val)
        FROM (   
            VALUES 
                ( ABS(DATEDIFF(DD, rh.OpenDT, x.Booked))),
                ( ABS(DATEDIFF(DD, rh.CloseDT, x.Booked)))
        ) AS value(val)
    )
FROM (   
    SELECT 
        lm.LMID
        ,lm.ID
        ,lm.Booked
        ,req.RequestId AS RequestID
    FROM #Pop as lm
    INNER JOIN dbo.request as req 
        ON req.ServicerLoanNumber = lm.ID
    INNER JOIN dbo.RequestHistory as 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
) as x
INNER JOIN dbo.Request as v 
    ON v.RequestId = x.RequestID
INNER JOIN dbo.RequestHistory as rh 
    ON rh.RequestId = v.RequestId
        AND rh.Version = v.LastVersion;

因此，您有一个显示在T-SQL中的临时表和两个dbo表.

因此，我们可以制作一些伪表作为CTE来对此进行一些测试:

with pop(lmid, id, booked, AddDate) as (
    select * from values
        (1,10,'2023-11-14'::date, '2023-11-14'::date)
), request(ServicerLoanNumber, RequestId, LastVersion) as (
    select * from values 
        (10,100, 1000)
),RequestHistory(RequestId, version, FinalDispositionTypeId, RequestStatusTypeId, opendt, closedt) as (
    select * from values 
        (100, 1000, 1, 0, '2023-11-14'::date, '2023-11-14'::date)
)
Select 
    ROW_NUMBER() OVER ( 
        PARTITION BY x.LMID 
        ORDER BY (
            SELECT 
                MIN(val)
            FROM (   
                VALUES 
                    ( ABS(DATEDIFF(DD, rh.OpenDT, x.Booked))),
                    ( ABS(DATEDIFF(DD, rh.CloseDT, x.Booked)))
            ) AS value(val) 
        ), v.LastVersion DESC 
    ) AS RowNum
    ,(   
        SELECT 
            MIN(val)
        FROM (   
            VALUES 
                ( ABS(DATEDIFF(DD, rh.OpenDT, x.Booked))),
                ( ABS(DATEDIFF(DD, rh.CloseDT, x.Booked)))
        ) AS value(val)
    )
FROM (   
    SELECT 
        lm.LMID
        ,lm.ID
        ,lm.Booked
        ,req.RequestId
    FROM Pop as lm
    INNER JOIN request as req 
        ON req.ServicerLoanNumber = lm.ID
    INNER JOIN RequestHistory as 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
) as x
INNER JOIN Request as v 
    ON v.RequestId = x.RequestID
INNER JOIN RequestHistory as rh 
    ON rh.RequestId = v.RequestId
        AND rh.Version = v.LastVersion;

给出错误:

VALUES子句中的表达式[ABS(DATE_DETATEINDAYS("2023 -11- 14"，"2023-11- 14"))]无效

这是有意义的，因为Snowflake只接受值中的常量表达式.

因此，如果我们开始移动代码以便于查看代码链，则可以将子 Select x移动到CTE中:

with pop(lmid, id, booked, AddDate) as (
    select * from values
        (1,10,'2023-11-14'::date, '2023-11-14'::date)
), request(ServicerLoanNumber, RequestId, LastVersion) as (
    select * from values 
        (10,100, 1000)
),RequestHistory(RequestId, version, FinalDispositionTypeId, RequestStatusTypeId, opendt, closedt) as (
    select * from values 
        (100, 1000, 1, 0, '2023-11-14'::date, '2023-11-14'::date)
), cte_x as (
    SELECT 
        lm.LMID
        ,lm.ID
        ,lm.Booked
        ,req.RequestId
    FROM Pop as lm
    INNER JOIN request as req 
        ON req.ServicerLoanNumber = lm.ID
    INNER JOIN RequestHistory as 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
)

Select 
    ROW_NUMBER() OVER ( 
        PARTITION BY x.LMID 
        ORDER BY (
            SELECT 
                MIN(val)
            FROM (   
                VALUES 
                    ( ABS(DATEDIFF(DD, rh.OpenDT, x.Booked))),
                    ( ABS(DATEDIFF(DD, rh.CloseDT, x.Booked)))
            ) AS value(val) 
        ), v.LastVersion DESC 
    ) AS RowNum
    ,(   
        SELECT 
            MIN(val)
        FROM (   
            VALUES 
                ( ABS(DATEDIFF(DD, rh.OpenDT, x.Booked))),
                ( ABS(DATEDIFF(DD, rh.CloseDT, x.Booked)))
        ) AS value(val)
    )
FROM cte_x as x
INNER JOIN Request as v 
    ON v.RequestId = x.RequestID
INNER JOIN RequestHistory as rh 
    ON rh.RequestId = v.RequestId
        AND rh.Version = v.LastVersion;

现在这个块重复两次，所以这可以做一次(这也是问题所在)

SELECT 
    MIN(val)
FROM (   
    VALUES 
        ( ABS(DATEDIFF(DD, rh.OpenDT, x.Booked))),
        ( ABS(DATEDIFF(DD, rh.CloseDT, x.Booked)))
) AS value(val)

无论如何，如果这是固定的，我们可以交换到最小值而不是最小值:

LEAST( ABS(DATEDIFF(DD, rh.OpenDT, x.Booked)),
       ABS(DATEDIFF(DD, rh.CloseDT, x.Booked)))

如果任何输入为空，则Lest表现不佳，因此，只要所有三个值(Booked、opendt、Closedt)都不为空，这是安全的，

因此，它可以写成:

with pop(lmid, id, booked, AddDate) as (
    select * from values
        (1,10,'2023-11-14'::date, '2023-11-14'::date)
), request(ServicerLoanNumber, RequestId, LastVersion) as (
    select * from values 
        (10,100, 1000)
),RequestHistory(RequestId, version, FinalDispositionTypeId, RequestStatusTypeId, opendt, closedt) as (
    select * from values 
        (100, 1000, 1, 0, '2023-11-14'::date, '2023-11-14'::date)
), cte_x as (
    SELECT 
        lm.LMID
        ,lm.ID
        ,lm.Booked
        ,req.RequestId
    FROM Pop as lm
    INNER JOIN request as req 
        ON req.ServicerLoanNumber = lm.ID
    INNER JOIN RequestHistory as 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
)
Select 
    ROW_NUMBER() OVER ( 
        PARTITION BY x.LMID 
        ORDER BY LEAST( ABS(DATEDIFF(DD, rh.OpenDT, x.Booked)),
                   ABS(DATEDIFF(DD, rh.CloseDT, x.Booked))), v.LastVersion DESC 
    ) AS RowNum
    ,LEAST( ABS(DATEDIFF(DD, rh.OpenDT, x.Booked)),
                   ABS(DATEDIFF(DD, rh.CloseDT, x.Booked))) as min_days
FROM cte_x as x
INNER JOIN Request as v 
    ON v.RequestId = x.RequestID
INNER JOIN RequestHistory as rh 
    ON rh.RequestId = v.RequestId
        AND rh.Version = v.LastVersion;

这提供了:

enter image description here

这就是我所期望的，我不喜欢做最少的两次，我倾向于这样做是CTE，但我假设还需要更复杂的逻辑，它已经被剥离，所以就不说了.

Sql相关问答推荐

将有界时间周期作为阶跃函数，其中周期开始返回1，周期结束返回0

Group By子句返回太多行

BigQuery`喜欢ANY‘和`不喜欢ANY’

SQL基于多个值 Select 单行

在Oracle SQL中将列值转换为行

UPDATE查询中的乐观锁

对列进行排序后，基于两列删除重复行

如何在SQL中从多个查询进行分组

如何在连接中使用三个不同的列，从而在PostgreSQL中只获得两个列？

使用递归CTE在BigQuery中获取文件路径

如何使用jsonn_populate_record()插入到包含IDENTITY列的表中

如何向 mariadb 添加外键？

使用临时表判断记录是否存在 - 如果存在则执行相同的操作

如何在sparksql查询中使用日期值？

使用其他表 SUM 的交换价格转换价格并获得 AVG

如何筛选 GROUP BY 结果？ HAVING 没有产生预期的结果

Athena：从字符串birth_dt列计算年龄

将单行中的多个行值转换为列

Select 随机行，使得列子组的组合是唯一的

在 AWS athena 的视图之上创建视图时，如何消除此错误：列别名列表有 1 个条目但t有 4 列可用？

实用课程推荐
相关教程推荐
休闲君评测 |  TOOLFK工具网 |  古诗文网 |  良许Linux教程网 |  Python之禅 |  红色石头 |  入党申请书 | 

© 2020 Hi LearnFk 意见反馈 免责声明 关于我们 桂ICP备11002319号-3 友链&广告位+QQ: 1963612630