从union表将2个计数合并到配置单元sql中的一个和中

tp5buhyn 于 2021-06-26 发布在 Hive

关注(0)|答案(3)|浏览(490)

我很难将计数之和作为配置单元sql中联合表的结果进行组合

SELECT pulocation AS locID,count(pulocation) AS puCount FROM task1 
  WHERE  distance > 0.5 AND distance < 1  
  GROUP BY pulocation 
UNION
SELECT dolocation,count(dolocation) AS doCount FROM task1 
  WHERE  distance > 0.5 AND distance < 1
  GROUP BY dolocation

会给我这张table的结果吗

_u2.locid   _u2.pucount
1           18
1           24  
3           3
3           4
4           4693

我试图把这个表创建一个新的组合计数表，但没有成功。

SELECT _u2.locid, SUM(_u2.pucount)
FROM (
SELECT pulocation AS locID,count(pulocation) AS puCount FROM task1 
  WHERE  distance > 0.5 AND distance < 1  
  GROUP BY pulocation 
UNION
SELECT dolocation,count(dolocation) AS doCount FROM task1 
  WHERE  distance > 0.5 AND distance < 1
  GROUP BY dolocation)
GROUP BY u2.locid

我尝试使用'u2'或'u2'。但导致了这个错误

org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 14:0 Failed to recognize predicate 'GROUP'. Failed rule: 'identifier' in subquery source

我最想要的就是这张table

_u2.locid   _u2.pucount
1           42
3           7       
4           4693

Hive hiveql

来源：https://stackoverflow.com/questions/52304954/combine-2-counts-into-a-sum-in-hive-sql-from-union-table

3条答案

按热度按时间

ubby3x7f1#

这就是你需要的 SELECT locID, sum(totCount) as totCount FROM ( SELECT pulocation AS locID,count(pulocation) AS totCount FROM task1 WHERE distance > 0.5 AND distance < 1 GROUP BY pulocation UNION ALL SELECT dolocation AS locID,count(dolocation) AS totCount FROM task1 WHERE distance > 0.5 AND distance < 1 GROUP BY dolocation ) t1 GROUP BY locID

赞(0）回复(0）举报 2021-06-26

5ssjco0h2#

在前面答案的帮助下，

SELECT t.locid, SUM(t.pucount) AS count
FROM (
  SELECT pulocation AS locID,COUNT(pulocation) as pucount
  FROM task1 
    WHERE  distance > 0.5 AND distance < 1  
    GROUP BY pulocation 
  UNION
  SELECT dolocation,count(dolocation) as doCount
  FROM task1 
    WHERE  distance > 0.5 AND distance < 1
    GROUP BY dolocation) AS t
GROUP BY t.locid 
ORDER BY count DESC
LIMIT 10

赞(0）回复(0）举报 2021-06-26

sshcrbum3#

尝试以下方式：

SELECT t.locid, SUM(t.pucount)
FROM ((SELECT pulocation AS locID, count(pulocation) AS puCount
       FROM task1 
       WHERE  distance > 0.5 AND distance < 1  
       GROUP BY pulocation
      )
      UNION ALL
      (SELECT dolocation, count(dolocation) AS doCount
       FROM task1 
       WHERE  distance > 0.5 AND distance < 1
       GROUP BY dolocation
      )
     ) t
GROUP BY t.locid

赞(0）回复(0）举报 2021-06-26

我来回答

从union表将2个计数合并到配置单元sql中的一个和中

3条答案

相关问题

热门标签

最新问答