我是Hive的新手,但我认为自己是SQL的中级。我得到了如下所示的错误。我已经尝试过更改、添加、删除括号和它们的位置,以及添加和删除子查询别名。似乎没有任何东西可以清除这个错误。感谢您的帮助!
org.apache.hive.service.cli.HiveSQLException: Error while compiling statement:
FAILED: ParseException line 16:11 missing ) at 'testing' near ')' in expression specificationDROP TABLE IF EXISTS batting;
CREATE EXTERNAL TABLE IF NOT EXISTS batting(id STRING, year INT, team STRING, league STRING, games INT, ab INT, runs INT, hits INT, doubles INT, triples INT, homeruns INT, rbi INT, sb INT, cs INT, walks INT, strikeouts INT, ibb INT, hbp INT, sh INT, sf INT, gidp INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/user/maria_dev/hivetest/batting';
DROP TABLE IF EXISTS master;
CREATE EXTERNAL TABLE IF NOT EXISTS master(id STRING, byear INT, bmonth INT, bday INT, bcountry STRING, bstate STRING, bcity STRING, dyear INT, dmonth INT, dday INT, dcountry STRING, dstate STRING, dcity STRING, fname STRING, lname STRING, name STRING, weight INT, height INT, bats STRING, throws STRING, debut STRING, finalgame STRING, retro STRING, bbref STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/user/maria_dev/hivetest/master';
SELECT
bcity, bstate
FROM(
SELECT bcity, bstate, DENSE_RANK() OVER (ORDER BY total_double_triples DESC) as ranked
FROM
( SELECT bcity, bstate, SUM(doubles+triples) as total_double_triples
FROM
(
((SELECT id as b_id, doubles, triples
FROM batting) batting_data
JOIN
(SELECT id, bcity, bstate
FROM master
WHERE NOT ISNULL(bcity) AND NOT ISNULL(bstate)) master_join
ON master_join.id = batting_data.b_id)
) testing GROUP BY (bcity, bstat)
) total_group_by
) subquery
WHERE subquery.ranked <= 5
;发布于 2020-10-25 16:30:04
子查询中缺少SELECT和extra ()。请参阅代码中标记为-- 1st, 2nd and 3rd的注释:
SELECT bcity, bstate FROM(
SELECT bcity, bstate, DENSE_RANK() OVER (ORDER BY total_double_triples DESC) as ranked
FROM
( SELECT bcity, bstate, SUM(doubles+triples) as total_double_triples
FROM
(--There should be SELECT ... FROM ---------------------------------- 1st
( ----------Remove this line ------------------------------------ 2nd
(SELECT id as b_id, doubles, triples
FROM batting) batting_data
JOIN
(SELECT id, bcity, bstate
FROM master
WHERE NOT ISNULL(bcity) AND NOT ISNULL(bstate)
) master_join
ON master_join.id = batting_data.b_id ) --Remove extra)------3rd
) testing GROUP BY (bcity, bstat)
) total_group_by
) subquery WHERE subquery.ranked <= 5 ;发布于 2020-10-25 23:28:02
Had在Group By语句之前删除了别名,并修复了bstate字段名称中的类型。感谢所有提出的解决方案!
SELECT bcity, bstate FROM(
SELECT bcity, bstate, DENSE_RANK() OVER (ORDER BY total_double_triples DESC) as ranked FROM (
SELECT bcity, bstate, SUM(doubles+triples) as total_double_triples FROM (
(SELECT id as b_id, doubles, triples FROM batting) batting_data
JOIN
(SELECT id, bcity, bstate FROM master WHERE NOT ISNULL(bcity) AND NOT ISNULL(bstate)) master_join
ON master_join.id = batting_data.b_id
) GROUP BY bcity, bstate
) group_by_totals
) subquery WHERE subquery.ranked <= 5 ;https://stackoverflow.com/questions/64513636
复制相似问题