hive sql inner join - how to get sum & row_num in same query

0
votes

i've 2 tables product & psales, data in the tables is as shown below

select * from psales;
+-------------+---------------+--+
| psales.pid  | psales.sales  |
+-------------+---------------+--+
| 1           | 100           |
| 1           | 150           |
| 1           | 200           |
| 2           | 75            |
| 2           | 45            |
| 2           | 145           |
| 3           | 176           |
| 3           | 99            |
| 1           | 27            |
| 4           | 51            |
+-------------+---------------+--+

select * from product;
+--------------+----------------+--+
| product.pid  | product.pname  |
+--------------+----------------+--+
| 1            | p1             |
| 2            | p2             |
| 3            | p3             |
| 4            | p4             |
+--------------+----------------+--+

Objective is to get the product with 2nd highest combined sales.

Here is the query i'm currently using to get the product with highest combined sales (working fine)

select p1.pname, p1.total_sales
from (select p.pid as pid, p.pname as pname, s.sales as sales,  
      sum(s.sales) over (partition by p.pid order by p.pid) as total_sales 
      from product p
      inner join psales s on (p.pid = s.pid) 
      order by total_sales desc) p1 
limit 1;

How do i get the product with the 2nd highest combined sales ?

When i try to get the row_num in the inner query, it gives me error shown below :

select p1.pname as pname, p1.total_sales as total_sales, row_num() over (partition by pname order by pname) as rownum 
from (select p.pid as pid, p.pname as pname, s.sales as sales,  
      sum(s.sales) over (partition by p.pid order by p.pid) as total_sales, 
      row_num() over (partition by p.pid) as rownum 
      from product p 
      inner join psales s on (p.pid = s.pid) 
      order by total_sales desc) p1 
where rownum =2;

Error: Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies. Underlying error: Invalid function row_num (state=42000,code=40000)

Thanks for help in advance.

2
hivehiveql
Please format code readably. – philipxy
ORDER BY without LIMIT in a subquery does nothing--tables have no row order, query result sets [sic] are ordered. PS If you get a syntax error then chop your code down until you don't. – philipxy
Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on minimal reproducible example. – philipxy
Possible duplicate of Retrieve 3rd MAX salary in Hive – philipxy

2 Answers

0
votes

You can use dense_rank to rank the sum per pid,name combination. 

select p1.pname,p1.pid,p1.total_sales
from (select p.pid, p.pname,sum(s.sales) as total_sales,
      dense_rank() over(order by sum(s.sales) desc) as rnk 
      from product p 
      join psales s on p.pid = s.pid
      group by p.pid,p.pname
     ) p1
where rnk=2
0
votes

Use row_number() function in the upper subquery. And it seems you do not need analytic sum(), simple group by will do:

select p1.pname, p1.pid, p1.total_sales
  from
(
select p1.pname, p1.pid, p1.total_sales, 
       row_number() over (order by total_sales  desc) rn
  from 
     (select p.pid, p.pname, sum(s.sales) as total_sales 
        from product p 
             inner join psales s on p.pid = s.pid
        group by p.pid, p.pname
     )p1
)s
where rn=2
;

Use dense_rank() instead of row_number() if you want to select all products with the same sales.