Tag: window functions

Understanding the PARTITION BY Clause in SQL

Posted on by Ian

Sometimes when we run SQL queries we might want to group the results by a given column. A common way to do this is with the GROUP BY clause.

But sometimes the GROUP BY clause isn’t enough.

Sometimes we want the results presented in a way that the GROUP BY clause doesn’t cater for. The GROUP BY is perfect if we want to collapse groups of rows into a single row, so that we can get an aggregate value for each set of collapsed rows. But sometimes we don’t want this. Sometimes we want to see all of the rows, as well as the aggregate values.

This is where the PARTITION BY clause comes in.

Continue reading →

Understanding the WINDOW Clause in MySQL

Posted on by Ian

In MySQL, the WINDOW clause is an optional clause that we can use to create a named window. The named window can then be referred to from a window function.

Many SQL developers define their window functions directly in the OVER clause. But that’s not the only way to do it. We can also define them in a WINDOW clause, and then refer to them in the OVER clause.

When we define the window function in a WINDOW clause, we name it. When we do this, we can refer to that name from the OVER clause. This eliminates the need to include the definition directly inside the OVER clause.

Continue reading →

Introduction to the OVER Clause in SQL

Posted on by Ian

When running SQL database queries, sometimes we need to use a window function in order to get the results we’re looking for. A window function is an aggregate-like function that enables partitioning and ordering of data within a result set.

The OVER clause is what enables us to create a window function.

The examples below demonstrate how we can incorporate the OVER clause in our SQL queries.

Continue reading →

Understanding Window Functions in SQL

Posted on by Ian

Window functions can be a useful tool when writing SQL queries. They allow us to include aggregate data across multiple rows without getting those pesky errors that sometimes occur when we try to use an aggregate function in the wrong way.

In this article, I aim to provide a simple overview of window functions and how they can be used to provide a more useful result set when running SQL queries.

Continue reading →

Understanding the VAR_SAMP() Function in MySQL

Posted on by Ian

In MySQL, the VAR_SAMP() function returns the sample variance of an expression. The denominator is the number of rows minus one.

If there are no matching rows, or if the expression is NULL, VAR_SAMP() returns NULL.

VAR_SAMP() is an aggregate function, and so it can be used with the GROUP BY clause.

The VAR_SAMP() function can also be used as a window function.

Continue reading →

How VARIANCE() Works in MySQL

Posted on by Ian

In MySQL, the VARIANCE() function returns the population standard variance of an expression.

If there are no matching rows, or if the expression is NULL, the function returns NULL.

The VARIANCE() function is a synonym for the standard SQL VAR_POP() function. In other words, they both do the same thing, but VARIANCE() is not standard SQL. Therefore, if you need to use standard SQL, use VAR_POP() instead.

Continue reading →

Understanding the VAR_POP() Function in MySQL

Posted on by Ian

In MySQL, the VAR_POP() function returns the population standard variance of an expression.

If there are no matching rows, or if the expression is NULL, VAR_POP() it returns NULL.

VAR_POP() is an aggregate function, and so it can be used with the GROUP BY clause.

The VAR_POP() function is standard SQL. There’s also a VARIANCE() function that does the same thing, but is not standard SQL.

Continue reading →

Understanding the STDDEV_SAMP() Function in MySQL

Posted on by Ian

In MySQL, the STDDEV_SAMP() function returns the sample standard deviation of a given expression. This is the square root of VAR_SAMP().

STDDEV_SAMP() is an aggregate function, and so it can be used with the GROUP BY clause.

If there are no matching rows, or if the expression is NULL, STDDEV_SAMP() returns NULL.

Continue reading →