sql - 雪花子查询

Question

我有两张 table 。 交易(ID、TERMINALID)和终端(ID、TERMINALID、EXPORT_DATE)。目标是为 Transaction 表中的每一行获取最新的 Terminal 表记录。 Snowflake 用作后端。

我有这个 SQL 查询:

SELECT tr.ID,
       (SELECT te.ID
        FROM "Terminal" te
        WHERE te.TERMINALID = tr.TERMINALID
        ORDER BY te.EXPORT_DATE DESC
        LIMIT 1)
FROM "Transaction" tr;

但是我得到这个错误:

SQL compilation error: Unsupported subquery type cannot be evaluated

如果我将 tr.TERMINALID 替换为特定值，错误就会消失。所以我不能从嵌套的 SELECT 中引用父表。为什么这是不可能的？查询在 MySQL 中有效。

Answer 1

恐怕 Snowflake 不支持这种相关的子查询。

您可以通过使用 FIRST_VALUE 来计算最佳的每个终端 ID 来实现您想要的:

-- First compute per-terminalid best id
with sub1 as (
  select 
    terminalid, 
    first_value(id) over (partition by terminalid order by d desc) id
  from terminal 
),
-- Now, make sure there's only one per terminalid id
sub2 as (
  select 
    terminalid, 
    any_value(id) id
  from sub1
  group by terminalid
)
-- Now use that result
select tr.ID, sub2.id
FROM "Transaction" tr
JOIN sub2 ON tr.terminalid = sub2.terminalid

您可以先运行子查询以查看它们的作用。

我们正在努力改进对子查询的支持，并且可能会进行更简单的重写，但我希望它能有所帮助。