Aprende Window Functions: ROW_NUMBER, RANK, DENSE_RANK

Sección 1. Capítulo 2

single

Desliza para mostrar el menú

Window functions are a powerful feature in SQL that allow you to perform calculations across sets of rows related to the current row, without collapsing the result set. Among the most commonly used window functions for analytical reporting are ROW_NUMBER, RANK, and DENSE_RANK. These functions are essential when you need to assign a unique position or ranking to each row within a partition of your data, such as ranking products by sales within each category or identifying the top performers within each department.

The general syntax for these ranking window functions is as follows:

SELECT
  ...,
  ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) AS row_num,
  RANK()       OVER (PARTITION BY ... ORDER BY ...) AS rank,
  DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...) AS dense_rank
FROM
  your_table;

The PARTITION BY clause divides the data into groups (partitions) for the ranking calculation;
The ORDER BY clause specifies how to order the rows within each partition for ranking purposes.

Use cases for these functions include:

Finding the top N items per group;
Detecting duplicate records;
Assigning sequential numbers within groups;
Comparing rankings with and without ties.

Definition

ROW_NUMBER assigns a unique sequential number to each row within a partition, with no gaps or duplicates, even if rows are tied on the ordering column. Use this when you want a strict ordering with no ties.

RANK assigns the same rank to tied rows, but leaves gaps in the ranking sequence after ties. Use RANK when it is important to reflect the presence of ties in your data.

DENSE_RANK also assigns the same rank to tied rows, but does not leave gaps in the ranking sequence after ties. Use DENSE_RANK when you want to show ties but keep the ranking numbers contiguous.


              12345678910111213141516171819
            
SELECT
  category,
  product_name,
  SUM(quantity) AS total_quantity,
  ROW_NUMBER() OVER (
    PARTITION BY category
    ORDER BY SUM(quantity) DESC
  ) AS row_num,
  RANK() OVER (
    PARTITION BY category
    ORDER BY SUM(quantity) DESC
  ) AS rank,
  DENSE_RANK() OVER (
    PARTITION BY category
    ORDER BY SUM(quantity) DESC
  ) AS dense_rank
FROM product_sales
GROUP BY category, product_name
ORDER BY category, row_num;

Suppose the e-commerce analytics team needs to identify the top 3 selling products in each category for the past month. Using window functions, you can efficiently rank products by their sales within each category and filter for the top performers. This approach is especially useful for generating leaderboard-style reports or for driving recommendations in dashboards.


              1234567891011121314151617181920
            
SELECT
  category,
  product_name,
  total_quantity
FROM (
  SELECT
    category,
    product_name,
    SUM(quantity) AS total_quantity,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY SUM(quantity) DESC
    ) AS row_num
  FROM product_sales
  WHERE sale_date >= '2022-03-01'
    AND sale_date < '2023-04-01'
  GROUP BY category, product_name
) ranked
WHERE row_num <= 3
ORDER BY category, row_num;

Tarea

Desliza para comenzar a programar

Write a query to assign a RANK to employees based on their total sales within each department.

Join the hr_employees and sales tables using the employee_id column.
For each employee, calculate the sum of their total_amount from the sales table.
Partition the ranking by department_id and order by total sales in descending order.
Assign the RANK using the RANK() window function.
Output the department_id, employee_id, first_name, last_name, total sales, and the computed rank.

Solución

Cambia al escritorio para practicar en el mundo realContinúe desde donde se encuentra utilizando una de las siguientes opciones

¿Todo estuvo claro?

¡Gracias por tus comentarios!

Sección 1. Capítulo 2

single

Pregunte a AI

Pregunte lo que quiera o pruebe una de las preguntas sugeridas para comenzar nuestra charla