HIVE试图加入一个字段并从另一个选择语句字段中减去一个字段

我正在尝试将这两个选择语句加入id和playerID以及year和yearID（下面的模式）。还减去别名HAB-EG

  SELECT playerID, yearID, (E/ G) AS EG FROM fielding
  WHERE yearID > 2005 AND yearID < 2009 AND G > 20;


  SELECT id, year, (hits/ab) AS HAB FROM batting
  WHERE year > 2005 AND year < 2009 AND ab > 40;

希玛

CREATE EXTERNAL TABLE IF NOT EXISTS fielding
(playerID STRING ,yearID INT ,teamID STRING ,lgID STRING ,
POS STRING ,G INT ,GS INT , InnOuts INT , PO INT,A INT, E INT,  
DP INT , PB INT , WP INT ,SB INT ,CS INT , ZR INT ) ROW
FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION      '/home/hduser/hivetest/fielding';

只是模式

 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 '/home/hduser/hivetest/batting';