title: Hive Grouping and calculating average by calculating distinct
body: <p>Folks we have one wired requirement in HIVE and we are not able to write query for the same</p>
  
  <p>Basically we have following data.</p>
  
  <pre><code>CUSTOMER_NAME PRODUCT_NAME PRICE OCCURANCE ID
  customer1,    product1,    20,       1
  customer1,    product2,    30,       2
  customer1,    product1,    25,       3
  customer1,    product1,    20,       1
  customer1,    product2,    20,       2
  </code></pre>
  
  <p>Basically what we have to do is list the average price for (customer_name,product_name) for single  occurance.</p>
  
  <p>e.g. for combination (customer1,product1) price for product1 is 
  25+20/2(no of distinct occurences for customer(1 and 3)) = 22.5. But as we want to group by PRODUCT_NAME also we donot know how to calculate the distinct occurance. I have marked the query with [] bracket where we feel we need to do some change.</p>
  
  <p>Other aspect is the inner query here we want to select customers where their average price will fall in to top 5 for distinct occurrencs. ( This works properly as group by clause is having only one attribute CUSTOMER_NAME)</p>
  
  <pre><code>select customer_name,product_name,[sum(price)/count(distinct(occurance_id))]
  from customer_prd cprd
  Join (select customer_name,sum(price)/count(distinct(occurance id))
  order by sum group  
  by customer_name limit 5)
  cprdd
  where cprd.customer_name = cprdd.customer_name group by cprd.customer_name,cprd.product_name
  </code></pre>
  
  <p>output expected.</p>
  
  <pre><code> customer1,product1, 20 (avg for occurance ID 1) + 25(average for occurance ID 2)/2 = 22.5
   customer1,product2, 30 + 20/2 = 25
  </code></pre>