迁移 SQL Server 外部应用查询到雪花查询

Question

正在从SQL Server迁移到Snowflake。卡在下面的查询。在雪花中找不到任何等效的东西。 对于外部查询中的每一行，我们进入内部查询以获取小于外部查询中的日期键的顶级日期键。

select a.datekey , a.userid, a.transactionid
,cast( cast(b.datekey as varchar(8)) as date) priorone
from tableA a
outer apply (select top 1 b.datekey
from tableA b where a.userid = b.userid
and b.transactionid < a.transactionid and b.datekey < a.datekey
order by b.transactionid desc) as b

尝试了如下建议的答案：

create or replace table tableA
(datekey date , userid int ,transactionid int)
insert into tableA
values('2020-06-01',1,101),('2020-06-02',1,102),('2020-06-02',1,103),('2020-06-01',2,104),('2020-06-02',2,105)
select 
a.datekey, 
a.userid, 
a.transactionid
,(
select b.datekey
from tableA b 
where 
a.userid = b.userid
and b.transactionid < a.transactionid 
and b.datekey < a.datekey
order by b.transactionid desc
limit 1
) priorone
from tableA a

Answer 1

超前/滞后不能替代 slq 服务器外部应用

外部应用可以让你做一个前 1 名，这是我使用它的唯一原因 - 超级方便。 您不能在 CTE 中排名前 1，因为 CTE 在加入之前会先进行评估。 因此，外部应用是我能看到的唯一解决方案。 太糟糕了，它不在雪花中

Answer 2

我想你要找的是Snowflake中的LEAD((函数。 它将为您保存子查询或完全连接：

select 
datekey, 
userid, 
transactionid,
lead(datekey) over (partition by userid order by datekey desc) as priorone
from tableA;

这将根据日期键的降序获取用户 id 的下一条记录。

你也可以使用 LAG(( 并以相反的方式进行排序：

select 
datekey, 
userid, 
transactionid,
lag(datekey) over (partition by userid order by datekey asc) as priorone
from tableA;

Answer 3

您只从外部连接中获取一列，因此您可以将代码改写为直接相关的子查询。

雪花不支持top，但它具有与limit相同的功能。

最后，您似乎要删除datekey的时间药水：您可以使用date()。

select 
a.datekey, 
a.userid, 
a.transactionid
(
select date(b.datekey)
from tableA b 
where 
a.userid = b.userid
and b.transactionid < a.transactionid 
and b.datekey < a.datekey
order by b.transactionid desc
limit 1
) priorone
from tableA a