Applying ROW_NUMBER using Windowing Functions

#1

Let us see how we can assign row numbers using row_number function.

  • If we have to get ranks globally, we just need to specify ORDER BY
  • If we have to get ranks with in a key then we need to specify PARTITION BY and then ORDER BY.
  • By default ORDER BY will sort the data in ascending order. We can change the order by passing DESC after order by.

Here is an example to assign row numbers using daily_product_revenue with in each day based on revenue.

USE training_retail;

SELECT t.*,
  row_number() OVER (
    PARTITION BY order_date
    ORDER BY revenue DESC
  ) AS rn
FROM daily_product_revenue t
ORDER BY order_date, revenue DESC
LIMIT 100;

Here is another example to assign row numbers using employees data set with in each department.

USE training_hr;

SELECT
  employee_id,
  department_id,
  salary,
  row_number() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
  ) rn
FROM employees
ORDER BY department_id, salary DESC;

Practice hive on state of the art Big Data cluster - https://labs.itversity.com
You can sign up for our courses on Udemy using $10 coupons - Udemy Coupons - Big Data Courses


0 Likes