hadoop - HIVE QUERY SELECT * FROM bookfreq其中freq IN(SELECT max(freq)FROM bookfreq);

我正在编写配置单元查询，因为获取记录具有最大频率值。

table name bookfreq, having two column year & freq

year freq

1999  2

2000  4

1989  4

1990  5

查询:

SELECT * FROM bookfreq where freq IN (SELECT Max(freq) FROM bookfreq);

我收到类似的异常

FAILED: ParseException line 1:38 cannot recognize input near 'SELECT' 'Max' '(' in expression specification

最佳答案

如果您具有Hive 0.13或更高版本(如here所述)，则这种类型的子查询应该是可能的。但是，列名称仍必须完全限定。因此，要做我想在Hive 0.13或更高版本中要做的事情是

SELECT * FROM bookfreq a
WHERE a.freq IN (SELECT max(b.freq) FROM bookfreq b);

如果您使用的是较旧版本的Hive，则可以尝试使用以下符号:

SELECT a.*
FROM bookfreq a JOIN (SELECT max(freq) as max_freq FROM bookfreq) b
  ON a.freq = b.max_freq;

如果仍然不起作用(这可能意味着您的Hive版本已经过时)，则可能必须首先创建包含max(freq)的表作为具体对象:

CREATE TABLE b AS SELECT max(freq) AS max_freq FROM bookfreq;

然后使用纯b运行上述查询。就像是:

SELECT bookfreq.*
FROM bookfreq JOIN b ON bookfreq.freq = b.max_freq;