为什么不能识别选择部分中命名的别名？ [英] Why can't hive recognize alias named in select part?

问题描述

以下是这种情况：当我如下调用hql时，它告诉我它找不到u1的别名。

  hive> ;选择用户为u1，url为rank2中的u2，其中u1！=; 
 FAILED：SemanticException [错误10004]：行1:50无效的表别名或列引用'u1':(可能的列名是：user，url）
  

这个问题与我尝试使用 count（*）作为cnt 相同。任何人都可以给我一些关于如何在where子句中使用别名的提示吗？非常感谢！

  hive>选择用户，从rank_test将count（*）计数为cnt，其中cnt> = 2按用户分组; 
 FAILED：ParseException行1:58在'where'附近'user'缺少EOF 
  

其中子句在选择子句之前被计算，这就是为什么您无法在您的where子句中引用选择别名。

然而，您可以引用派生表中的别名。

  select * from（
选择user为u1，url为rank2中的u2）$ t1 $ u1< ; 
 
 select * from（
 select user，count（*）as cnt from rank_test group by user 
）t1 where cnt> = 2; 
  

注意：编写最后一个查询的更有效方法是

 选择用户，count_（*）作为rank_test组中的cnt用户
包含count（*）> = 2 
  

如果我没有记错的话，可以参考中含有的别名，即有cnt> = 2

Here's the scenario: When I invoke hql as follows, it tells me that it cannot find alias for u1.

hive> select user as u1, url as u2 from rank_test where u1 != "";
FAILED: SemanticException [Error 10004]: Line 1:50 Invalid table alias or column reference 'u1': (possible column names are: user, url)

This problem is the same as when I try to use count(*) as cnt. Could anyone give me some hint on how to use alias in where clause? Thanks a lot!

hive> select user, count(*) as cnt from rank_test where cnt >= 2 group by user;
FAILED: ParseException line 1:58 missing EOF at 'where' near 'user'

解决方案

The where clause is evaluated before the select clause, which is why you can't refer to select aliases in your where clause.

You can however refer to aliases from a derived table.

select * from (
  select user as u1, url as u2 from rank_test
) t1 where u1 <> "";

select * from (
  select user, count(*) as cnt from rank_test group by user
) t1 where cnt >= 2;

Side note: a more efficient way to write the last query would be

select user, count(*) as cnt from rank_test group by user
having count(*) >= 2

If I remember correctly, you can refer to the alias in having i.e. having cnt >= 2

这篇关于为什么不能识别选择部分中命名的别名？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！