20 Powerful Analytic Functions in SQL

Photo by Google DeepMind on Unsplash

Analytic functions in SQL are used for performing calculations across a set of table rows related to the current row. These functions can be powerful tools for tasks such as calculating running totals, moving averages, and ranking results. The availability of these functions can vary between different database systems.

Here’s a list of some commonly used analytic functions:

1. ROW_NUMBER():

• Assigns a unique number to each row within a partition of a result set, starting at 1 for the first row.

SELECT column1, column2, ROW_NUMBER() OVER (ORDER BY column1) AS row_num
FROM table_name;

2. RANK():

• Assigns a rank to each row within a partition of a result set based on the values of specified columns.

SELECT column1, column2, RANK() OVER (ORDER BY column1 DESC) AS rank
FROM table_name;

3. DENSE_RANK():

• Similar to RANK(), but without gaps in rank values for tied rows.

SELECT column1, column2, DENSE_RANK() OVER (ORDER BY column1 DESC) AS dense_rank
FROM table_name;

4. NTILE(n):

• Divides the result set into approximately n parts or “tiles.”