Spark中替代EXISTS和IN的方法

Question

Spark中替代EXISTS和IN的方法

13

我正在尝试运行一个使用EXIST子句的查询:

select <...>    
  from A, B, C
where
  A.FK_1 = B.PK and
  A.FK_2 = C.PK and
  exists (select A.ID from <subquery 1>) or 
  exists (select A.ID from <subquery 2>)

很遗憾，这似乎不被支持。我也尝试用IN子句替换EXISTS子句：

select <...>    
  from A, B, C
where
  A.FK_1 = B.PK and
  A.FK_2 = C.PK and
  A.ID in (select ID from ...) or
  A.ID in (select ID from ...)

不幸的是，IN子句似乎也不受支持。

有什么想法可以编写SQL查询以实现所需的结果吗？原则上我可以将WHERE子句建模为另一个JOIN，并将第二个OR子句建模为UNION，但这似乎非常笨拙。

编辑：列出若干个可能的解决方案。

解决方案1

select <...>    
  from A, B, C
       (select ID from ...) as exist_clause_1,
       (select ID from ...) as exist_clause_2,
where
  A.FK_1 = B.PK and
  A.FK_2 = C.PK and
  A.ID = exist_clause_1.ID or
  A.ID = exist_clause_2.ID

解决方案 2

select <...>    
  from A, B, C
       ( (select ID from ...) UNION
         (select ID from ...)
        ) as exist_clause,
where
  A.FK_1 = B.PK and
  A.FK_2 = C.PK and
  A.ID = exist_clause.ID

- Radu

你的第一个查询模板不应该包含形如 exists (select E.ID from <subquery> E where E.ID = A.ID)) 的 EXISTS 调用吗？ - philipxy

2个回答

0

Spark现在支持exists。这里是Sparks3.3.2文档中相关部分的链接。https://spark.apache.org/docs/3.3.2/sql-ref-null-semantics.html#existsnot-exists-subquery-

-- Even if subquery produces rows with `NULL` values, the `EXISTS` expression
-- evaluates to `TRUE` as the subquery produces 1 row.
SELECT * FROM person WHERE EXISTS (SELECT null);
+--------+----+
|    name| age|
+--------+----+
|  Albert|null|
|Michelle|  30|
|    Fred|  50|
|    Mike|  18|
|     Dan|  50|
|   Marry|null|
|     Joe|  30|
+--------+----+

-- `NOT EXISTS` expression returns `FALSE`. It returns `TRUE` only when
-- subquery produces no rows. In this case, it returns 1 row.
SELECT * FROM person WHERE NOT EXISTS (SELECT null);
+----+---+
|name|age|
+----+---+
+----+---+

-- `NOT EXISTS` expression returns `TRUE`.
SELECT * FROM person WHERE NOT EXISTS (SELECT 1 WHERE 1 = 0);
+--------+----+
|    name| age|
+--------+----+
|  Albert|null|
|Michelle|  30|
|    Fred|  50|
|    Mike|  18|
|     Dan|  50|
|   Marry|null|
|     Joe|  30|
+--------+----+

- samredai

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- philipxy · Accepted Answer

SparkSQL目前不支持EXISTS和IN。 {{link1：“（最新）Spark SQL / DataFrames和Datasets Guide /支持的Hive功能”}}

可以通过使用JOIN或LEFT SEMI JOIN来重写EXISTS和IN。{{link2：“虽然Apache Spark SQL当前不支持IN或EXISTS子查询，但可以通过重写查询以使用LEFT SEMI JOIN来有效地实现语义。”}}OR可以使用UNION进行重写。AND NOT可以使用EXCEPT进行重写。

一个表保存了使某个谓词（由列名参数化的语句）为真的行：

DBA为每个基本表T（具有列T.C，...）提供谓词：T（T.C，...）。
JOIN包含使其参数的谓词AND成立的行；对于UNION，OR；对于EXCEPT，是AND NOT。
SELECT DISTINCT保留的列FROMT包含存在丢弃的列 [T的谓词]的行。
TLEFT SEMI JOINU包含满足U-only列 [T的谓词 AND U的谓词]的行。
TWHERE条件包含满足T的谓词和条件的行。

通常来说，查询请参见 this answer。因此，通过记住与SQL相对应的谓词表达式，您可以使用简单的逻辑重写规则来组合和/或重新组织查询。例如，在这里使用UNION不必在可读性或执行方面显得“笨拙”。您最初的问题表明您已经了解可以使用UNION，并且您已经编辑了变量并将其从原始查询中删除了EXISTS和IN。以下是另一种变体，也删掉了OR。

    select <...>    
    from A, B, C, (select ID from ...) as e
    where
      A.FK_1 = B.PK and
      A.FK_2 = C.PK and
      A.ID = e.id
union
    select <...>    
    from A, B, C, (select ID from ...) as e
    where
      A.FK_1 = B.PK and
      A.FK_2 = C.PK and
      A.ID = e.ID

您的解决方案1并不像您想的那样工作。如果exists_clause表中有一个为空，即使在另一个表中有ID匹配项可用，表的FROM交叉积也为空，并且不返回任何行。（“ SQL语义的一个令人困惑的结果”：第6章数据库语言SQL侧边栏页面264，来自《数据库系统：完整书籍第二版》） FROM不仅为表的行引入名称，而且在此之后进行CROSS JOIN和/或OUTER JOIN，然后ON（对于INNER JOIN）和WHERE过滤一些行。

通常情况下，返回相同行的不同表达式的性能是不同的。这取决于DBMS优化。许多细节，DBMS和/或程序员可能知道，如果知道，则可能知道或不知道，并且可能会影响查询的最佳评估方式以及编写查询的最佳方式。但是，在WHERE子句中每行执行两个ORed子查询（如原始查询和晚期解决方案2中一样）不一定比运行两个SELECT的UNION更好（如我的查询中所示）。