sql—尝试在配置单元中查找select语句的最大值

gkl3eglg 于 2021-05-29 发布在 Hadoop

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

我试图在下面的脚本中给出一个按体重计算的最佳人选。我有一个工作版本的方式下面返回马特假日250作为重量，现在这是所有我想要的球员与最大重量，他只是没有其他人

SELECT DISTINCT n.fname, n.lname, MAX(n.weight) FROM master n 
JOIN (SELECT b.id as id, b.year as year, b.triples as triples FROM batting b 
WHERE year == 2005 AND triples > 5) x
ON x.id = n.id
ORDER BY n.weight DESC;

现在出现了这样一个错误

Failed: Semantic Exception [Error 10128]: Line 4:34 Not yet supported place for UDAF 'MAX'

但是这个脚本返回了我所期望的结果，输出如下

SELECT DISTINCT n.fname, n.lname, n.weight FROM master n 
JOIN (SELECT b.id as id, b.year as year, b.triples as triples FROM batting b 
WHERE year == 2005 AND triples > 5) x
ON x.id = n.id
ORDER BY n.weight DESC;

输出

Matt Holiday 250
Bill Dickey 205
Bob Feller 200
Tom Glavine 190

sql hadoop apache-hive

来源：https://stackoverflow.com/questions/36700564/trying-to-find-the-max-of-select-statement-in-hive

1条答案

按热度按时间

628mspwn1#

您有一个聚合函数，为了得到想要的结果，您需要使用groupby

SELECT n.fname, n.lname, MAX(n.weight) FROM master n 
JOIN (SELECT b.id as id, b.year as year, b.triples as triples FROM batting b 
WHERE year == 2005 AND triples > 5) x
ON x.id = n.id
GROUP BY n.fname,n.lname
ORDER BY n.weight DESC
LIMIT 1;

参数或参数

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

表达式1，表达式2。。。表达式\n未封装在聚合函数中且必须包含在sql语句末尾的group by子句中的表达式http://www.techonthenet.com/sql/group_by.php
这可能是因为hiveql中也存在相同的规则

赞(0）回复(0）举报 2021-05-29

我来回答

sql—尝试在配置单元中查找select语句的最大值

1条答案

相关问题

热门标签

最新问答