SQL

Understanding the DENSE_RANK() Function in SQL

by

Many relational database management systems (RDBMSs) provide a DENSE_RANK() function that we can use in our SQL queries. The SQL DENSE_RANK() function is a window function that returns the rank of the current row within its partition, without gaps.

The “without gaps” part is what distinguishes the DENSE_RANK() function from the RANK() function.

DENSE_RANK() returns contiguous rank numbers whenever there are ties, whereas RANK() will leave a gap between the tie and the next rank, resulting in noncontiguous rank numbers.

Read more

Understanding the RANK() Function in SQL

by

In SQL databases, the RANK() function is a window function that returns the rank of the current row within its partition, with gaps.

By “with gaps” I mean that if there are any ties for a given rank, there will be a gap between that rank value and the next rank value.

If you don’t want such gaps, use the DENSE_RANK() function instead, as it returns the rank without gaps.

Read more

Create a Window Function in SQL

by

Most of the major RDBMSs allow us to create window functions in SQL queries.

A window function is a special aggregate-like function that enables partitioning and ordering of data within a result set. Unlike regular aggregate functions, which collapse multiple rows into a single result, window functions retain individual rows in the output, augmenting them with additional computed values.

The following example demonstrates how to create a window function in SQL.

Read more

Get the Most Populous City in Each District When Using a Window Function in SQL

by

While it’s true that we can use the SQL MAX() function to get the maximum value in a column, what if we want to return the value from another column instead of the actual maximum value itself? And what if we want it partitioned by category, so that it’s based on the maximum value from each category? And what if we want all values listed out, including those that aren’t the most populous?

In the following example, we use a window function to solve this problem.

Read more

Overview of the WINDOW Clause in SQL

by

The WINDOW clause is an optional clause that we can use in our SQL queries to create a named window. The named window can then be used as part of a window function.

When creating a window function, a SQL developer will often define it directly in the OVER clause. But that’s not the only way to do it. We can alternatively use the WINDOW clause to define it in a named window, and then refer to that named window in the OVER clause.

Read more

Compute a Moving Average in SQL

by

SQL provides us with an easy way to automatically compute the moving average of a given column.

The moving average (also known as the rolling average, running average, moving mean (MM), or rolling mean) is a series of averages of different selections of the full data set. The moving average changes as we move through the data set. We can add the moving average to our SQL query result sets to see how it changes across the result set.

Read more

Create a Running Total in SQL

by

We can use SQL to create a running total of a given column. By this I mean, we can create a column that calculates the cumulative sum of a given column.

The running total/cumulative sum is the sum of all rows up until the current row. The result is that the sum increases (or decreases in the case of negative values) with each row returned in the result set. Each row’s value is added to the cumulative amount from all prior rows, so for any given row, we get the total sum for all rows up to that point – the “running total”.

Read more