SQL Hive子查询错误 [英] SQL Hive subquery error

问题描述

我在下面查询

set hive.cli.print.header=true;
set hive.query.max.partition=1000;
set hive.mapred.mode=unstrict;

SELECT

dim_lookup("accounts",name,"account_id") = '28016' as company,
dim_lookup("campaigns",name,"campaign_id") in (117649,112311,112319,112313,107799,110743,112559,112557,105191,105231,107377,108675,106587,107325,110671,107329,107181,106565,105123,106569,106579,110835,105127,105243,107185,105211,105215) as campaign_name,
case when is_click_through=0 then "PV" else "PC" end as conv_type,
(SELECT COUNT(1) FROM impressions WHERE ad_info[2] in (117649,112311,112319,112313,107799,110743,112559,112557,105191,105231,107377,108675,106587,107325,110671,107329,107181,106565,105123,106569,106579,110835,105127,105243,107185,105211,105215)) AS impressions

FROM actions

WHERE
data_date>='20170101'
AND data_date<='20171231'
AND conversion_action_id in (20769223,20769214,20769219,20764929,20764932,20764935,20769215,20769216,20764919,20769218,20769217,20769220,20769222)
GROUP BY conv_type

执行它时出现错误

 ERROR ql.Driver: FAILED: ParseException line 8:1 cannot recognize input near 'SELECT' 'COUNT' '(' in expression specification

我正在尝试获取指定的conversion_action_id的每次展示计数.我的查询中可能有什么错误?感谢您的帮助.

I am trying to fetch each count of impression for a specified conversion_action_id. What could be the error in my query? Thanks for the help.

仅供参考:ad_info [2]和campaign_id相同.

FYI: ad_info[2] and campaign_id are the same.

推荐答案

问题很明显，您在SELECT中有一个子查询.

The problem is quite clear, you have a subquery inside your SELECT.

这不是它的工作原理.

不幸的是，确切的解决方案还不清楚，因为我不确定您想要什么，但是这里有一些一般性建议:

Unfortunately the exact solution is not that clear, as it I am not completely sure what you want, but here is some general advice:

1. 编写您的子查询，对其进行测试并确保其正常
2. 与其将其放置在SELECT部分​​中，不将其放置在FROM部分中，并且(一如既往)将其放置在FROM部分中.

只需将您的子查询输出视为可以在from语句中使用的另一个表，并且需要将其与from语句中的其他表组合(JOIN，UNION?).

Just think of your subquery output as an other table that can be used in the from statement, and which needs to be combined (JOIN, UNION?) with other tables in the from statement.

这篇关于SQL Hive子查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！