Hive查询获得最大数量 [英] Hive query to get max of count

问题描述

我的输入文件是这样的：

  id，phnName，price，model 
 1，iphone，2000 ，abc 
 2，iphone，3000，abc1 
 3，诺基亚，4000，abc2 
 4，sony，5000，abc3 
 5，诺基亚6000，abc4 
 6，iphone，7000，abc5 
 7，诺基亚，8500，abc6 
  

我想编写配置单元查询以获取特定手机的最大数量。

输出：

  iphone 3 
 nokia 3 
  

到现在为止，我尝试了以下query：

  select d.phnName，count（*）from phnDetails d group by d.phnName 
  

并得到如下输出：

  iphone 3 
 nokia 3 
 sony 1 
  

帮助我检索最大值。

解决方案

我有查询正在工作中

输入

  1，iphone，2000，abc 
 2，iphone，3000，abc1 
 3，诺基亚， 4000，abc2 
 4，sony，5000，a bc3 
 5，诺基亚6000，abc4 
 6，iphone，7000，abc5 
 7，诺基亚，8500，abc6 
  
  select c.phnName，c.counter 
从
（选择d.phnName作为phnName，将count（*）作为计数器从phnDetails d group by d.phnName）c 
 join 
（选择max（f.counter）作为countmax from 
（从cnt.phnName中选择cnt.phnName作为phnName，count（*）作为phnDetails cnt组的计数器）f）g 
其中c.counter = g.countmax; 
  
不确定是否进行了优化，但它的运行情况符合预期
 
 
 
输出如下，
  
 
 
 
使用以下步骤解决，如果它回答您的问题，请将其标记为已回答 
 
 
  CREATE TABLE phnDetails（id INT，phnName STRING，price INT，details STRING）
 ROW FORMAT DELIMITED FIELDS TERMINATED BY'，'STORED as TEXTFILE; 
 
 LOAD DATA LOCAL INPATH'/home/training/Phone/phones.txt'覆盖表格phnDetails; 
 
 select * from phnDetails; 
 
 
选择d.phnName，通过d.phnName从phnDetails d组中计数（*）; 
 
 
从
中选择c.phnName，c.counter（选择d.phnName作为phnName，count（*）作为phnDetails d中的计数器d.phnName）c; 
 
从
选择max（f.counter）作为countmax（选择cnt.phnName作为phnName，count（*）作为phnDetails cnt组的cnt.phnName计数器）f; 
 
从
选择max（f.counter）作为countmax（选择cnt.phnName作为phnName，count（*）作为phnDetails cnt组的cnt.phnName计数器）f; 
 
从
中选择c.phnName，c.counter 
（选择d.phnName作为phnName，将count（*）作为计数器从phnDetails d group by d.phnName）c 
加入
（从cb.phnName中选择max（f.counter）作为countmax从
（选择cnt.phnName作为phnName，count（*）作为phnDetails cnt组的计数器）f）g 
其中c.counter = g.countmax; 
  
 
My input file is like this:
id,phnName,price,model
1,iphone,2000,abc
2,iphone,3000,abc1
3,nokia,4000,abc2
4,sony,5000,abc3
5,nokia,6000,abc4
6,iphone,7000,abc5
7,nokia,8500,abc6
I want to write a hive query to get the max count of a particular phone.

output:
iphone 3
nokia 3
till now I've tried the following query:
select d.phnName,count(*) from phnDetails d group by d.phnName
and got output like this:
iphone 3
nokia 3
sony 1
Help me to retrieve only the max value.
解决方案
I have the query working 

Input 
1,iphone,2000,abc
2,iphone,3000,abc1
3,nokia,4000,abc2
4,sony,5000,abc3
5,nokia,6000,abc4
6,iphone,7000,abc5
7,nokia,8500,abc6
Working Query 
select c.phnName, c.counter 
from 
(select d.phnName as phnName, count(*) as counter from phnDetails d group by d.phnName ) c 
join 
(select max(f.counter) as countmax from
(select cnt.phnName as phnName, count(*) as counter from phnDetails cnt group by cnt.phnName ) f) g 
where c.counter = g.countmax;
Not sure if it is optimized however it is working as expected

Output as below,


Solved using the steps as below, If it answers your question please mark it as answered
CREATE TABLE phnDetails ( id INT, phnName STRING, price INT, details STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH '/home/training/Phone/phones.txt' OVERWRITE INTO TABLE phnDetails;

select * from phnDetails;


select d.phnName, count(*) from phnDetails d group by d.phnName;


select c.phnName, c.counter from 
(select d.phnName as phnName, count(*) as counter from phnDetails d group by d.phnName ) c ;

select max(f.counter) as countmax from
(select cnt.phnName as phnName, count(*) as counter from phnDetails cnt group by cnt.phnName ) f ;

select max(f.counter) as countmax from
(select cnt.phnName as phnName, count(*) as counter from phnDetails cnt group by cnt.phnName ) f ;

select c.phnName, c.counter 
from 
(select d.phnName as phnName, count(*) as counter from phnDetails d group by d.phnName ) c 
join 
(select max(f.counter) as countmax from
(select cnt.phnName as phnName, count(*) as counter from phnDetails cnt group by cnt.phnName ) f) g 
where c.counter = g.countmax;


                        
这篇关于Hive查询获得最大数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！