ROWS and RANGE: Windowing clause defaults – learning by example in MySQL.

The more I dive into Window Functions, at least 2 things are very apparent to me: 1) They are incredibly powerful, 2) I have a long ways to go with wrapping my head around them. I recently wrote, The PARTITION BY clause of a Window Function – with an example in MySQL where I built upon a base Window Function query with the PARTITION BY clause. However, in this post, I want to return to and explore, some defaults going on behind the scenes in that query in regards to the windowing portion.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

OS and DB used:

• Xubuntu Linux 16.04.5 LTS (Xenial Xerus)
• MySQL 8.0.15

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

To be perfectly honest, the credit is not all mine alone. In fact, none of it is. I happened upon this fantastic SQL Server video on YouTube, Difference between rows and range, that really put into perspective some default behavior in the windowing portion of the OVER() clause. I’m merely passing on what I learned from that resource.

In the OVER() clause, you can specify 1 to 3 different optional clauses. They are:

Let’s revisit this query and result set from the blog post referenced in the opener:

You can see that the ‘amt_per_trail’ column is a nice running total (think SUM()) of the ‘burned_calories’ values from the current row, in addition to, those previous rows above.

For better understanding, let’s see the comparison between the original query without the explicit windowing RANGE specification and one with it. Note: I removed the ‘day_walked’ column for better readability on-screen.

As seen above, both versions of the Window Function return the exact same results.

In this particular query, the RANGE clause, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means: Execute whatever operation we are doing, (in this case, totaling with SUM()) on the current row, as well as all preceding rows (i.e., above, before, prior, etc…).

Yet, RANGE is not the only option for we can specify ROWS instead. Notice the similarities – or differences, if any – between them in the below results:

So they are the same right? Both columns’ results match up and all.

So why are there 2 different options that provide seemingly identical functionality? Are you sure they produce the exact same results?

Astute readers will have noticed these particular rows:

Maybe you are wondering, why does ROWS do that?

In fact, it’s not ROWS at all. It is in how RANGE treats those duplicate values of 378.3 in the ‘burned_calories’ column. RANGE treats duplicates as a single entity where ROWS does not.

Here is a break down: In the RANGE column, the value in the row above the duplicates, 6017.4, is added to 378.3 twice (E.g., 6017.4 + 378.3 + 378.3) and that equals 6774.0 which is displayed in both following rows.

Whereas for ROW, those duplicate values are added to the previous row, individually per row. Therefore, displaying a true running total throughout the result set.

I hope this blog post has provided you with an overview of default behavior, as well as underlying nuances with both the RANGE and ROW clauses. I know I sure have learned a great deal in writing it.

Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!

Explore the official MySQL 8.0 Online Manual for more information.

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.