Skip to main content

A new detail that helps you step up your ‘window’ game in SQL!

 






I have a love-hate relationship with the window functions in SQL.

I love how it allows one access to data from rows beyond the current row as it simplifies the tasks like creating rolling averages or ranking values within a group.

I hate it when I have to write a complicated query involving the usage of window functions along with joins, subqueries, CTEs, etc.

But I learned a new detail recently about window functions that made me gasp and admire it.

To understand it, let us consider the task of creating a 3-day rolling average on a numeric column.

This task requires data from the current row and the preceding two rows. Hence, it is a no-brainer to use window functions here.

The usual syntax of the window function for averaging is as below:

AVG() OVER ()

This window function considers the value of the current row and the value of all the preceding rows to calculate and display the average. (Let us overlook the functionalities of PARTITION BY and ORDER BY clauses for a while.)

But, to calculate the 3-day rolling average, we need this function to access only two preceding rows along with the current one.

This can be done by a simple addition as below:

AVG() OVER(ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

“ROWS BETWEEN” allows one to specify a range for the aggregation performed by window functions.

Its syntax is as below:

ROWS BETWEEN preceding_limit AND following_limit

If we specify these limits as “UNBOUNDED PRECEDING” or “UNBOUNDED FOLLOWING,” all the preceding and following rows are considered accordingly.

If we want to restrict these limits to a few rows, we could use below:

n PRECEDING (restricts to ’n’ preceding rows)
n FOLLOWING (restricts to ’n’ following rows)

If we want the aggregation to start from or end at the current row, simply specifying the corresponding limit as “CURRENT ROW” would suffice.

Default mode when we omit ‘ROWS BETWEEN’ from our queries is UNBOUNDED PRECEDING to CURRENT ROW, which explains the usual functionality of window functions as we know.

I wonder why I was in the dark about this functionality.

Labels:

Comments

Post a Comment

Popular posts from this blog

Solving Customer Churn with a hammer!

Learning when data should take a back seat and give way to domain knowledge is a valuable skill. Suppose you built a machine learning model on the data of your customers to predict churn risk. Now that you have a risk score for each customer, what do you do next? Do you filter the top n% based on the risk and send them a coupon with a discount in the hopes that it will prevent churn? But what if price is not the factor driving churn in many of these customers? Customers might have been treated poorly by customer service, which drove them away from your company's product.  Or there might have been an indirect competitor's product or service that removes the need for your company's product altogether (this happened to companies like Blockbuster and Kodak in the past!) There could be a myriad of factors, but you get the point! Dashboards and models cannot guide any company's strategic actions directly. If companies try to use them without additional context, more often tha
Post a Comment
Read more

What is SUTVA for A/B testing?

Imagine if person B’s blood pressure reading depends on whether person A receives the blood pressure medicine in a randomized controlled trial. This will be violating Stable Unit Treatment Value Assumption (SUTVA) SUTVA states that the treatment received by an individual should not influence the outcome we see for another individual during the experiment. I know the initial example sounded absurd, so let me try again. Consider LinkedIn A/B testing a new ‘dislike’ reaction for its users, and the gods of fate chose you to be part of the initial treatment group that received this update. Excited after seeing this new update, you use this dislike reaction on my post and send a screenshot to a few of your connections to do the same, who are coincidentally in the control group that did not receive the update. Your connections log in and engage with my posts to use this dislike reaction, but later get disappointed as this new update is not yet available to them. The offices of LinkedIn are tr
Post a Comment
Read more