不支持的子查询表达式“fashion”：子查询表达式仅引用外部查询表达式

sq1bmfud 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(520)

我正在使用以下查询：

select UserId, Category from customer_data 
where (Category in ('Fashion','Electronics'))
and (Action in ('Click','AddToCart','Purchase'))
and customer_data.UserId not in (select ustomer_data.UserId from customer_data where customer_data.Category='Fashion' and customer_data.Category='Electronics')  ;

获取以下错误：

hive> Unsupported SubQuery Expression ''Fashion'': SubQuery expression refers to Outer query expressions only.

我不确定这个错误，我是否需要在外部查询中为每个cloumn（如customer\u data.category）使用表名？你能帮忙吗？
样本数据：

UserId,ProductId,Category,Action
1,111,Electronics,Browse
2,112,Fashion,Click
3,113,Kids,AddtoCart
4,114,Food,Purchase
5,115,Books,Logout
6,114,Food,Click
7,113,Kids,AddtoCart
8,115,Books,Purchase
9,111,Electronics,Click
10,112,Fashion,Purchase
3,112,Fashion,Click
12,113,Kids,AddtoCart

期望输出：

Output File
•   userID
•   category

mysql Hive apache-spark-sql hadoop2

来源：https://stackoverflow.com/questions/52189856/unsupported-subquery-expression-fashion-subquery-expression-refers-to-outer

1条答案

按热度按时间

ljsrvy3e1#

使用分析函数计算每个用户标识的标志：

select UserId, Category
from
( --calculate User level flags
select UserId, Category,
       max(fashion_flag)     over (partition by UserId) as user_fashion_flag,
       max(electronics_flag) over (partition by UserId) as user_electronics_flag
from
(--maybe you do not need this subquery, if case will work inside max() over
    select UserId, Category,
           case when Category='Fashion'     then 1 else 0 end fashion_flag,
           case when Category='Electronics' then 1 else 0 end electronics_flag
      from customer_data 
     where (Category in ('Fashion','Electronics'))
       and (Action in ('Click','AddToCart','Purchase'))
) s

) s
where user_fashion_flag+user_electronics_flag=1 --not allow two flags at a time
;

赞(0）回复(0）举报 2021-06-20

我来回答

不支持的子查询表达式“fashion”：子查询表达式仅引用外部查询表达式

1条答案

相关问题

热门标签

最新问答