Spark sql top n per group

You can use the window function feature that was added in Spark 1.4 Suppose that we have a productRevenue table as shown below. enter image description here

the answer to What are the best-selling and the second best-selling products in every category is as follows

SELECT product,category,revenue FROM 
   (SELECT product,category,revenue,dense_rank() 
         OVER (PARTITION BY category ORDER BY revenue DESC) as rank 
    FROM productRevenue) tmp 
WHERE rank <= 2

Tis will give you the desired result

Spark sql top n per group

Related

Recent Posts