hadoop - HIVE QUERY SELECT * FROM bookfreq where freq IN (SELECT Max(freq) FROM bookfreq);

Question

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

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

Answer 1

如果你有 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;