How to find longest duration in HIVE

Question

I have small issue in hive logic. I need to find longest customer in my data set.My dataset contains values as below

customer       time
 cust 1        8:01
 cust 1        8:02
 cust 2        8:02
 cust 2        8:03
 cust 2        8:04
 cust 2        8:05
 cust 3        8:02
 cust 3        8:03

Here in my example, cust 2 has the longest customer based on the total time he/she has engaged. cust 1 total count is 2, cust 2 count is 4 and cust 3 count is 2. My issue between 8:01 to 8:05, the longest duration belongs to cust 2 . Hence cust 2 should come as result. How can i implement this logic.

Please help

Note: need solution without limit operation

score 1 · Answer 1 · answered Dec 24 '18 at 21:00

1

Something like this:

select customer
from t
where time >= '8:01' and time <= '8:05'
group by customer
order by count(*) desc
limit 1;

answered Dec 24 '18 at 21:00

Gordon Linoff

1,242,037
58
646
786

I dont have option to do limit operation – BigD Dec 24 '18 at 21:07
Can u help on this without applying limit – BigD Dec 25 '18 at 20:41

Himanshu · Answer 2 · 2018-12-24T21:27:03.283

0

I think this will give the desired

    Select distinct customer from table where 
     (distinct time) = (select max(distinct time) from table)

or if you want desired as per group reduction

         Select  customer from( 
           (Select customer,(max(time)-
         min(time)) as time_diff
          from table group by customer
         order by time_diff desc LIMIT 1)
          ;

edited Dec 24 '18 at 21:27

answered Dec 24 '18 at 21:09

Himanshu

3,830
2
10
29

Can u elaborate ? – BigD Dec 24 '18 at 21:15
the first solution will mean the customer who has the largest duration (max) of all in the list the next solution be the longest not the largest – Himanshu Dec 24 '18 at 21:21
How come rownum logic came? Where is the field romnum? – BigD Dec 24 '18 at 21:25
then it must be limit 1 as you specified sql in the tag as well first it came from that – Himanshu Dec 24 '18 at 21:27
https://stackoverflow.com/questions/30441744/select-top-2-rows-in-hive take a look at this refer this – Himanshu Dec 24 '18 at 21:29

How to find longest duration in HIVE

2 Answers2