2023年5月10日 22:12:39go评论97阅读模式

英文:

Error while converting Presto SQL to Hive SQL containing subquery

问题

我试图将以下Presto SQL转换为Hive SQL。在Hive中，WHERE子查询不起作用，并且引发错误。

select distinct id 
from account
where load_date = (select max(load_date) from account)
and account_status='current'
and account_type='crnt'

请指导我如何修复这个问题，使其在Hive中工作。

英文:

I am trying to convert the following Presto SQL to Hive SQL. The sub query in WHERE clause isn't working in Hive and is throwing an error.

select distinct id 
from account
where load_date = (select max(load_date) from account)
and account_status=&#39;current&#39;
and account_type=&#39;crnt&#39;

Please advise how I can fix this and make it work in Hive.

答案1

得分: 2

Hive不支持在WHERE子句中使用子查询。可以尝试以下两种方式：

第一种方式：

SELECT DISTINCT a.id 
FROM account a
JOIN (
  SELECT max(load_date) AS max_load_date 
  FROM account
) b
ON a.load_date = b.max_load_date
WHERE a.account_status='current'
AND a.account_type='crnt';

第二种方式：

WITH max_load_date AS (
  SELECT max(load_date) AS max_date 
  FROM account
)
    
SELECT DISTINCT a.id 
FROM account a
JOIN max_load_date m
ON a.load_date = m.max_date
WHERE a.account_status='current'
AND a.account_type='crnt';

英文:

Hive doesn't support subqueries in the WHERE clause. Try this

SELECT DISTINCT a.id 
FROM account a
JOIN (
  SELECT max(load_date) AS max_load_date 
  FROM account
) b
ON a.load_date = b.max_load_date
WHERE a.account_status=&#39;current&#39;
AND a.account_type=&#39;crnt&#39;;

or this

WITH max_load_date AS (
  SELECT max(load_date) AS max_date 
  FROM account
)
    SELECT DISTINCT a.id 
    FROM account a
    JOIN max_load_date m
    ON a.load_date = m.max_date
    WHERE a.account_status=&#39;current&#39;
    AND a.account_type=&#39;crnt&#39;;

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

“Presto SQL 转换为包含子查询的 Hive SQL 时出错”

问题

答案1

Not all variables bound in PL/SQL function.

SQL语句中order by子句中使用的子查询的含义是什么？

Oracle SQL命令用于HAVING COUNT。

PostgreSQL查找每个实体的缺失属性。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。