首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >具有分组依据的配置组子查询

问具有分组依据的配置组子查询
EN

Stack Overflow用户
提问于 2020-10-24 21:08:44
回答 2查看 37关注 0票数 0

我是Hive的新手,但我认为自己是SQL的中级。我得到了如下所示的错误。我已经尝试过更改、添加、删除括号和它们的位置,以及添加和删除子查询别名。似乎没有任何东西可以清除这个错误。感谢您的帮助!

代码语言:javascript
复制
org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: 
FAILED: ParseException line 16:11 missing ) at 'testing' near ')' in expression specification
代码语言:javascript
复制
DROP 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
;
EN

回答 2

Stack Overflow用户

发布于 2020-10-25 16:30:04

子查询中缺少SELECT和extra ()。请参阅代码中标记为-- 1st, 2nd and 3rd的注释:

代码语言:javascript
复制
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 ;
票数 0
EN

Stack Overflow用户

发布于 2020-10-25 23:28:02

Had在Group By语句之前删除了别名,并修复了bstate字段名称中的类型。感谢所有提出的解决方案!

代码语言:javascript
复制
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 ;
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/64513636

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档