雪花(左连接)横向：不支持的子查询类型无法求值 [英] Snowflake (LEFT JOIN) LATERAL: Unsupported subquery type cannot be evaluated

问题描述

Lateral Join

在FROM子句中，SLATAL关键字允许内联视图引用该内联视图前面的表表达式中的列。

横向联接的行为更像相关子查询，而不是大多数联接。

让我们稍微调整一下文档中提供的代码：

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的语法？

我也希望能够获取结果集中的所有源行，即使横向子查询没有为它们生成任何行。要获得最终结果，请执行以下操作：

┌────────────────┬──────────────┬──────────────┬────────────┬────────────────┬───────────────┐
│ 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          │
└────────────────┴──────────────┴──────────────┴────────────┴────────────────┴───────────────┘

db<>fiddle demo

推荐答案

因此，尽管我们在前面已经讨论过，您知道可以重写它，但这里还是重写它

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

通过从具有实现限制/顶部的资格的横向CTE移动到CTE，然后使用左联接获取空匹配，您就拥有了所需的步骤。

对于为什么会是这样这个未被问到的问题。Snowflake不是真正的每行数据库，它更像是一个Map/Reduce/MergeJoin进程，它可以将简单的相关子查询重写为多步骤(也就是类似于CTE/JOINS)，但不能重写复杂的内容。他们一直在改进它。但是，如果您了解您的数据和模型，那么最有意义的做法就是以批操作的方式表达事物，并让MergeJoin的强大功能为您带来胜利。

是否有实现左联接横向/外部应用的语法？ 这是通过FLATTEN命令中的参数, OUTER => TRUE

完成的

这篇关于雪花(左连接)横向：不支持的子查询类型无法求值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！