snowflake-cloud-data-platform - Snowflake (LEFT JOIN) LATERAL:无法评估不受支持的子查询类型
问题描述
在 FROM 子句中,LATERAL 关键字允许内联视图引用该内联视图之前的表表达式中的列。
横向连接的行为更像是相关子查询,而不是大多数 JOIN。
让我们稍微调整一下文档中提供的代码:
CREATE TABLE departments (department_id INTEGER, name VARCHAR);
CREATE TABLE employees (employee_ID INTEGER, last_name VARCHAR,
department_ID INTEGER, project_names ARRAY);
INSERT INTO departments (department_ID, name) VALUES
(1, 'Engineering'),
(2, 'Support'),
(3, 'HR'); -- adding new row
INSERT INTO employees (employee_ID, last_name, department_ID) VALUES
(101, 'Richards', 1),
(102, 'Paulson', 1),
(103, 'Johnson', 2);
询问:
SELECT *
FROM departments AS d,
LATERAL (SELECT * FROM employees AS e
WHERE e.department_ID = d.department_ID
ORDER BY employee_id DESC LIMIT 1) AS iv2 -- adding ORDER BY ... LIMIT ...
ORDER BY employee_ID;
SQL 编译错误:无法评估不支持的子查询类型
是的,我知道我可以用ROW_NUMBER()
或其他方式重写此查询。
1)为什么TOP/LIMIT
在这种特定情况下无法使用?
2)是否有语法可以实现LEFT JOIN LATERAL/OUTER APPLY
?
即使 LATERAL 子查询没有为它们生成任何行,我也希望能够获取结果集中的所有源行。要获得最终结果:
┌────────────────┬──────────────┬──────────────┬────────────┬────────────────┬───────────────┐
│ department_id │ name │ employee_id │ last_name │ department_id │ project_names │
├────────────────┼──────────────┼──────────────┼────────────┼────────────────┼───────────────┤
│ 1 │ Engineering │ 102 │ Paulson │ 1 │ null │
│ 2 │ Support │ 103 │ Johnson │ 2 │ null │
│ 3 │ HR │ null │ null │ null │ null │
└────────────────┴──────────────┴──────────────┴────────────┴────────────────┴───────────────┘
解决方案
所以即使我们之前讨论过你知道你可以重写它,这里是重写
WITH departments AS (
SELECT * FROM VALUES
(1, 'Engineering'),
(2, 'Support'),
(3, 'HR')
v(department_ID, name)
), employees AS (
SELECT * FROM VALUES
(101, 'Richards', 1),
(102, 'Paulson', 1),
(103, 'Johnson', 2)
v(employee_ID, last_name, department_ID)
), dep_emp AS (
SELECT *
FROM employees
QUALIFY ROW_NUMBER() OVER (PARTITION BY department_ID ORDER BY employee_id) = 1
)
SELECT *
FROM departments AS d
LEFT JOIN dep_emp AS e ON d.department_ID = e.department_ID
ORDER BY employee_ID;
如您所愿:
DEPARTMENT_ID NAME EMPLOYEE_ID LAST_NAME DEPARTMENT_ID
1 Engineering 101 Richards 1
2 Support 103 Johnson 2
3 HR null null null
通过从 LATERAL 移动到具有 QUALIFY 的 CTE 以实现 LIMIT/TOP,然后使用 LEFT JOIN 来获取空匹配,您就有了您想要的步骤。
对于为什么会这样的未决问题。Snowflake 并不是真正的每行数据库,它更像是一个 Map/Reduce/MergeJoin 进程,它可以将简单的相关子查询重写为多步骤(也称为 CTE/joins),但它不能重写复杂的东西。他们一直在改进它。但是,如果您知道您的数据并且您知道您的模型,那么在批量操作中表达事物并让 MergeJoin 的强大功能为您带来胜利才是最有意义的。
是否有实现 LEFT JOIN LATERAL/OUTER APPLY 的语法?这是通过FLATTEN命令
, OUTER => TRUE
中的参数完成的
推荐阅读
- sql - 等同于 Keep in Snowflake
- r - 如何在R中将坐标从十进制度分转换为十进制度?
- git - github:确定提交是否完成
- javascript - 如何访问对象 React JS 中的对象
- c - esp32 在等待 XQueueRecieve 监视 uart 中断时崩溃
- arrays - 在 Rust 中将图像转换为 Rulinalg 矩阵
- javascript - MapBox GL JS:从通过代码设置的字符串地址获取经纬度坐标
- c# - SaveChanges 的 EF Core 全局查询过滤器(更新、删除)
- java - JIT 编译器是否利用长期记忆来跟踪优化的方法?
- c# - .net Web API 中的匿名类型语法