blog
A Review of the New Analytic Window Functions in MySQL 8.0
Data is captured and stored for a variety of reasons. Hours beyond count (and even more budget) invested in collecting, ingesting, structuring, validating, and ultimately storing of data; to say that it is a valuable asset is to drive home a moot point. This day in age it may, in fact, be our most precious commodity.
Some data is used strictly as an archive. Perhaps to record or track events that happened in the past. But the other side of that coin is that historical data has value in basing decisions for the future and future endeavors.
- What day to have our sale on? (Planning for future sales based on how we did in the past.)
- Which salesperson performed the best in quarter one? (Looking back, who can we reward for their efforts.)
- Which restaurant is frequented the most in the middle of July? (The travel season is upon us… Who can we sell our foodstuffs and goods to?)
You get the picture. Using data on hand is integral for any organization.
Many companies build, base, and provide services with data. They depend on it.
Several months back, depending on when you are reading this, I began walking for exercise, in earnest, to lose weight, get a handle on my health, and to seek a daily bit of solitude from this busy world we live in.
I used a mobile pedometer app to track my hikes, even considering which shoes I wore, as I have a tendency to be ultra-picky when it comes to footwear.
While this data is not nearly as important as that mentioned in those scenarios above, for me, a key element in learning anything, is using something I am interested in, can relate to, and understand.
Window Functions have been on my radar to explore for a long while now. So, I thought to try my hand at a couple of them in this post. Having recently been supported in MySQL 8 (Visit this Severalnines blog I wrote about MySQL 8 upgrades and new additions where I mention them briefly) that ecosystem is the one I will use here. Be forewarned, I am not a window analytical function guru.
What is a MySQL Window Function?
The MySQL documentation defines them as such: “A window function performs an aggregate-like operation on a set of query rows. However, whereas an aggregate operation groups query rows into a single result row, a window function produces a result for each query row:”
Data Set and Setup for This Post
I store the captured data from my walks in this table:
mysql> DESC hiking_stats;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| day_walked | date | YES | | NULL | |
| burned_calories | decimal(4,1) | YES | | NULL | |
| distance_walked | decimal(4,2) | YES | | NULL | |
| time_walking | time | YES | | NULL | |
| pace | decimal(2,1) | YES | | NULL | |
| shoes_worn | text | YES | | NULL | |
| trail_hiked | text | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
7 rows in set (0.01 sec)There is close to 90 days worth of data here:
mysql> SELECT COUNT(*) FROM hiking_stats;
+----------+
| COUNT(*) |
+----------+
| 84 |
+----------+
1 row in set (0.00 sec)I’ll admit, I am finicky about my footwear so let’s determine which pair of shoes I favored most:
mysql> SELECT DISTINCT shoes_worn, COUNT(*)
-> FROM hiking_stats
-> GROUP BY shoes_worn;
+---------------------------------------+----------+
| shoes_worn | COUNT(*) |
+---------------------------------------+----------+
| New Balance Trail Runners-All Terrain | 30 |
| Oboz Sawtooth Low | 47 |
| Keen Koven WP(keen-dry) | 6 |
| New Balance 510v2 | 1 |
+---------------------------------------+----------+
4 rows in set (0.00 sec)In order to provide a better, manageable on-screen demonstration, I will limit the remaining portion of query results to just those of the favorite shoes I wore 47 times.
I also have a trail_hiked column and since I was in ‘ultra exercise mode‘ during this almost 3 month period, I even counted calories while push mowing the yard:
mysql> SELECT DISTINCT trail_hiked, COUNT(*)
-> FROM hiking_stats
-> GROUP BY trail_hiked;
+------------------------+----------+
| trail_hiked | COUNT(*) |
+------------------------+----------+
| Yard Mowing | 14 |
| Sandy Trail-Drive | 20 |
| West Boundary | 29 |
| House-Power Line Route | 10 |
| Tree Trail-extended | 11 |
+------------------------+----------+
5 rows in set (0.01 sec)Yet, to even further limit the data set, I will filter out those rows as well:
mysql> SELECT COUNT(*)
-> FROM hiking_stats
-> WHERE shoes_worn = 'Oboz Sawtooth Low'
-> AND
-> trail_hiked <> 'Yard Mowing';
+----------+
| COUNT(*) |
+----------+
| 40 |
+----------+
1 row in set (0.01 sec)For the sake of simplicity and ease of use, I will create a VIEW of columns to work with:
mysql> CREATE VIEW vw_fav_shoe_stats AS
-> (SELECT day_walked, burned_calories, distance_walked, time_walking, pace, trail_hiked
-> FROM hiking_stats
-> WHERE shoes_worn = 'Oboz Sawtooth Low'
-> AND trail_hiked <> 'Yard Mowing');
Query OK, 0 rows affected (0.19 sec)Leaving me with this set of data:
mysql> SELECT * FROM vw_fav_shoe_stats;
+------------+-----------------+-----------------+--------------+------+------------------------+
| day_walked | burned_calories | distance_walked | time_walking | pace | trail_hiked |
+------------+-----------------+-----------------+--------------+------+------------------------+
| 2018-06-03 | 389.6 | 4.11 | 01:13:19 | 3.4 | Sandy Trail-Drive |
| 2018-06-04 | 394.6 | 4.26 | 01:14:15 | 3.4 | Sandy Trail-Drive |
| 2018-06-06 | 384.6 | 4.10 | 01:13:14 | 3.4 | Sandy Trail-Drive |
| 2018-06-07 | 382.7 | 4.12 | 01:12:52 | 3.4 | Sandy Trail-Drive |
| 2018-06-17 | 296.3 | 2.82 | 00:55:45 | 3.0 | West Boundary |
| 2018-06-18 | 314.7 | 3.08 | 00:59:13 | 3.1 | West Boundary |
| 2018-06-20 | 338.5 | 3.27 | 01:03:42 | 3.1 | West Boundary |
| 2018-06-21 | 339.5 | 3.40 | 01:03:54 | 3.2 | West Boundary |
| 2018-06-24 | 392.4 | 3.76 | 01:13:51 | 3.1 | House-Power Line Route |
| 2018-06-25 | 362.1 | 3.72 | 01:08:09 | 3.3 | West Boundary |
| 2018-06-26 | 380.5 | 3.94 | 01:11:36 | 3.3 | West Boundary |
| 2018-07-03 | 323.7 | 3.29 | 01:00:55 | 3.2 | West Boundary |
| 2018-07-04 | 342.8 | 3.47 | 01:04:31 | 3.2 | West Boundary |
| 2018-07-06 | 375.7 | 3.80 | 01:10:42 | 3.2 | West Boundary |
| 2018-07-07 | 347.6 | 3.40 | 01:05:25 | 3.1 | Sandy Trail-Drive |
| 2018-07-08 | 351.6 | 3.58 | 01:06:09 | 3.2 | West Boundary |
| 2018-07-09 | 336.0 | 3.28 | 01:03:13 | 3.1 | West Boundary |
| 2018-07-11 | 375.2 | 3.81 | 01:10:37 | 3.2 | West Boundary |
| 2018-07-12 | 325.9 | 3.28 | 01:01:20 | 3.2 | West Boundary |
| 2018-07-15 | 382.9 | 3.91 | 01:12:03 | 3.3 | House-Power Line Route |
| 2018-07-16 | 368.6 | 3.72 | 01:09:22 | 3.2 | West Boundary |
| 2018-07-17 | 339.4 | 3.46 | 01:03:52 | 3.3 | West Boundary |
| 2018-07-18 | 368.1 | 3.72 | 01:08:28 | 3.3 | West Boundary |
| 2018-07-19 | 339.2 | 3.44 | 01:03:06 | 3.3 | West Boundary |
| 2018-07-22 | 378.3 | 3.76 | 01:10:22 | 3.2 | West Boundary |
| 2018-07-23 | 322.9 | 3.28 | 01:00:03 | 3.3 | West Boundary |
| 2018-07-24 | 386.4 | 3.81 | 01:11:53 | 3.2 | West Boundary |
| 2018-07-25 | 379.9 | 3.83 | 01:10:39 | 3.3 | West Boundary |
| 2018-07-27 | 378.3 | 3.73 | 01:10:21 | 3.2 | West Boundary |
| 2018-07-28 | 337.4 | 3.39 | 01:02:45 | 3.2 | Sandy Trail-Drive |
| 2018-07-29 | 348.7 | 3.50 | 01:04:52 | 3.2 | West Boundary |
| 2018-07-30 | 361.6 | 3.69 | 01:07:15 | 3.3 | West Boundary |
| 2018-07-31 | 359.9 | 3.66 | 01:06:57 | 3.3 | West Boundary |
| 2018-08-01 | 336.1 | 3.37 | 01:01:48 | 3.3 | West Boundary |
| 2018-08-03 | 259.9 | 2.57 | 00:47:47 | 3.2 | West Boundary |
| 2018-08-05 | 341.2 | 3.37 | 01:02:44 | 3.2 | West Boundary |
| 2018-08-06 | 357.7 | 3.64 | 01:05:46 | 3.3 | West Boundary |
| 2018-08-17 | 184.2 | 1.89 | 00:39:00 | 2.9 | Tree Trail-extended |
| 2018-08-18 | 242.9 | 2.53 | 00:51:25 | 3.0 | Tree Trail-extended |
| 2018-08-30 | 204.4 | 1.95 | 00:37:35 | 3.1 | House-Power Line Route |
+------------+-----------------+-----------------+--------------+------+------------------------+
40 rows in set (0.00 sec)The first window function I will look at is ROW_NUMBER().
Suppose I want a result set ordered by the burned_calories column for the month of ‘July’.
Of course, I can retrieve that data with this query:
mysql> SELECT day_walked, burned_calories, trail_hiked
-> FROM vw_fav_shoe_stats
-> WHERE MONTHNAME(day_walked) = 'July'
-> ORDER BY burned_calories DESC;
+------------+-----------------+------------------------+
| day_walked | burned_calories | trail_hiked |
+------------+-----------------+------------------------+
| 2018-07-24 | 386.4 | West Boundary |
| 2018-07-15 | 382.9 | House-Power Line Route |
| 2018-07-25 | 379.9 | West Boundary |
| 2018-07-22 | 378.3 | West Boundary |
| 2018-07-27 | 378.3 | West Boundary |
| 2018-07-06 | 375.7 | West Boundary |
| 2018-07-11 | 375.2 | West Boundary |
| 2018-07-16 | 368.6 | West Boundary |
| 2018-07-18 | 368.1 | West Boundary |
| 2018-07-30 | 361.6 | West Boundary |
| 2018-07-31 | 359.9 | West Boundary |
| 2018-07-08 | 351.6 | West Boundary |
| 2018-07-29 | 348.7 | West Boundary |
| 2018-07-07 | 347.6 | Sandy Trail-Drive |
| 2018-07-04 | 342.8 | West Boundary |
| 2018-07-17 | 339.4 | West Boundary |
| 2018-07-19 | 339.2 | West Boundary |
| 2018-07-28 | 337.4 | Sandy Trail-Drive |
| 2018-07-09 | 336.0 | West Boundary |
| 2018-07-12 | 325.9 | West Boundary |
| 2018-07-03 | 323.7 | West Boundary |
| 2018-07-23 | 322.9 | West Boundary |
+------------+-----------------+------------------------+
22 rows in set (0.01 sec)Yet, for whatever reason (maybe personal satisfaction), I want to award a ranking among the returned rows beginning with 1 indicative of the highest burned_calories count, all the way to (n) rows in the result set.
ROW_NUMBER(), can handle this no problem at all:
mysql> SELECT day_walked, burned_calories,
-> ROW_NUMBER() OVER(ORDER BY burned_calories DESC)
-> AS position, trail_hiked
-> FROM vw_fav_shoe_stats
-> WHERE MONTHNAME(day_walked) = 'July';
+------------+-----------------+----------+------------------------+
| day_walked | burned_calories | position | trail_hiked |
+------------+-----------------+----------+------------------------+
| 2018-07-24 | 386.4 | 1 | West Boundary |
| 2018-07-15 | 382.9 | 2 | House-Power Line Route |
| 2018-07-25 | 379.9 | 3 | West Boundary |
| 2018-07-22 | 378.3 | 4 | West Boundary |
| 2018-07-27 | 378.3 | 5 | West Boundary |
| 2018-07-06 | 375.7 | 6 | West Boundary |
| 2018-07-11 | 375.2 | 7 | West Boundary |
| 2018-07-16 | 368.6 | 8 | West Boundary |
| 2018-07-18 | 368.1 | 9 | West Boundary |
| 2018-07-30 | 361.6 | 10 | West Boundary |
| 2018-07-31 | 359.9 | 11 | West Boundary |
| 2018-07-08 | 351.6 | 12 | West Boundary |
| 2018-07-29 | 348.7 | 13 | West Boundary |
| 2018-07-07 | 347.6 | 14 | Sandy Trail-Drive |
| 2018-07-04 | 342.8 | 15 | West Boundary |
| 2018-07-17 | 339.4 | 16 | West Boundary |
| 2018-07-19 | 339.2 | 17 | West Boundary |
| 2018-07-28 | 337.4 | 18 | Sandy Trail-Drive |
| 2018-07-09 | 336.0 | 19 | West Boundary |
| 2018-07-12 | 325.9 | 20 | West Boundary |
| 2018-07-03 | 323.7 | 21 | West Boundary |
| 2018-07-23 | 322.9 | 22 | West Boundary |
+------------+-----------------+----------+------------------------+
22 rows in set (0.00 sec)You can see the row with burned_calories amount of 386.4 has position 1, while the row with value 322.9 has 22, which is the least (or lowest) amount among the returned rows set.
I’ll use ROW_NUMBER() for something a bit more interesting as we progress. Only when I learned about it used in that context, did I truly realize some of its real power.
Up next, let’s visit the RANK() window function to provide a different sort of ‘ranking‘ among the rows. We will still target the burned_calories column value. And, while RANK() is similar to ROW_NUMBER() in that they somewhat rank rows, it does introduce a subtle difference in certain circumstances.
I will even further limit the number of rows as a whole by filtering any records not in the month of ‘July’ but targeting a specific trail:
mysql> SELECT day_walked, burned_calories,
-> RANK() OVER(ORDER BY burned_calories DESC) AS position,
-> trail_hiked
-> FROM vw_fav_shoe_stats
-> WHERE MONTHNAME(day_walked) = 'July'
-> AND trail_hiked = 'West Boundary';
+------------+-----------------+----------+---------------+
| day_walked | burned_calories | position | trail_hiked |
+------------+-----------------+----------+---------------+
| 2018-07-24 | 386.4 | 1 | West Boundary |
| 2018-07-25 | 379.9 | 2 | West Boundary |
| 2018-07-22 | 378.3 | 3 | West Boundary |
| 2018-07-27 | 378.3 | 3 | West Boundary |
| 2018-07-06 | 375.7 | 5 | West Boundary |
| 2018-07-11 | 375.2 | 6 | West Boundary |
| 2018-07-16 | 368.6 | 7 | West Boundary |
| 2018-07-18 | 368.1 | 8 | West Boundary |
| 2018-07-30 | 361.6 | 9 | West Boundary |
| 2018-07-31 | 359.9 | 10 | West Boundary |
| 2018-07-08 | 351.6 | 11 | West Boundary |
| 2018-07-29 | 348.7 | 12 | West Boundary |
| 2018-07-04 | 342.8 | 13 | West Boundary |
| 2018-07-17 | 339.4 | 14 | West Boundary |
| 2018-07-19 | 339.2 | 15 | West Boundary |
| 2018-07-09 | 336.0 | 16 | West Boundary |
| 2018-07-12 | 325.9 | 17 | West Boundary |
| 2018-07-03 | 323.7 | 18 | West Boundary |
| 2018-07-23 | 322.9 | 19 | West Boundary |
+------------+-----------------+----------+---------------+
19 rows in set (0.01 sec)Notice anything odd here? Different from ROW_NUMBER()?
Check out the position value for those rows of ‘2018-07-22’ and ‘2018-07-27’. They are in a tie at 3rd.
With good reason since the burned_calorie value of 378.3 is present in both rows.
How would ROW_NUMBER() rank them?
Let’s find out:
mysql> SELECT day_walked, burned_calories,
-> ROW_NUMBER() OVER(ORDER BY burned_calories DESC) AS position,
-> trail_hiked
-> FROM vw_fav_shoe_stats
-> WHERE MONTHNAME(day_walked) = 'July'
-> AND trail_hiked = 'West Boundary';
+------------+-----------------+----------+---------------+
| day_walked | burned_calories | position | trail_hiked |
+------------+-----------------+----------+---------------+
| 2018-07-24 | 386.4 | 1 | West Boundary |
| 2018-07-25 | 379.9 | 2 | West Boundary |
| 2018-07-22 | 378.3 | 3 | West Boundary |
| 2018-07-27 | 378.3 | 4 | West Boundary |
| 2018-07-06 | 375.7 | 5 | West Boundary |
| 2018-07-11 | 375.2 | 6 | West Boundary |
| 2018-07-16 | 368.6 | 7 | West Boundary |
| 2018-07-18 | 368.1 | 8 | West Boundary |
| 2018-07-30 | 361.6 | 9 | West Boundary |
| 2018-07-31 | 359.9 | 10 | West Boundary |
| 2018-07-08 | 351.6 | 11 | West Boundary |
| 2018-07-29 | 348.7 | 12 | West Boundary |
| 2018-07-04 | 342.8 | 13 | West Boundary |
| 2018-07-17 | 339.4 | 14 | West Boundary |
| 2018-07-19 | 339.2 | 15 | West Boundary |
| 2018-07-09 | 336.0 | 16 | West Boundary |
| 2018-07-12 | 325.9 | 17 | West Boundary |
| 2018-07-03 | 323.7 | 18 | West Boundary |
| 2018-07-23 | 322.9 | 19 | West Boundary |
+------------+-----------------+----------+---------------+
19 rows in set (0.06 sec)Hmmm…
No ties in the position column numbering this time.
But, who gets precedence?
To my knowledge, for a predictable ordering, you will likely have to determine it by some other additional means within the query (e.g. the time_walking column in this case?).
But we are not done yet with ranking options. Here is DENSE_RANK():
mysql> SELECT day_walked, burned_calories,
-> DENSE_RANK() OVER(ORDER BY burned_calories DESC) AS position,
-> trail_hiked
-> FROM vw_fav_shoe_stats
-> WHERE MONTHNAME(day_walked) = 'July'
-> AND trail_hiked = 'West Boundary';
+------------+-----------------+----------+---------------+
| day_walked | burned_calories | position | trail_hiked |
+------------+-----------------+----------+---------------+
| 2018-07-24 | 386.4 | 1 | West Boundary |
| 2018-07-25 | 379.9 | 2 | West Boundary |
| 2018-07-22 | 378.3 | 3 | West Boundary |
| 2018-07-27 | 378.3 | 3 | West Boundary |
| 2018-07-06 | 375.7 | 4 | West Boundary |
| 2018-07-11 | 375.2 | 5 | West Boundary |
| 2018-07-16 | 368.6 | 6 | West Boundary |
| 2018-07-18 | 368.1 | 7 | West Boundary |
| 2018-07-30 | 361.6 | 8 | West Boundary |
| 2018-07-31 | 359.9 | 9 | West Boundary |
| 2018-07-08 | 351.6 | 10 | West Boundary |
| 2018-07-29 | 348.7 | 11 | West Boundary |
| 2018-07-04 | 342.8 | 12 | West Boundary |
| 2018-07-17 | 339.4 | 13 | West Boundary |
| 2018-07-19 | 339.2 | 14 | West Boundary |
| 2018-07-09 | 336.0 | 15 | West Boundary |
| 2018-07-12 | 325.9 | 16 | West Boundary |
| 2018-07-03 | 323.7 | 17 | West Boundary |
| 2018-07-23 | 322.9 | 18 | West Boundary |
+------------+-----------------+----------+---------------+
19 rows in set (0.00 sec)The tie remains, however, the numbering is different in where rows are counted, continuing through the remaining results.
Where RANK() began the count with 5 after the ties, DENSE_RANK() picks up at the next number, which is 4 in this instance, since the tie happened at row 3.
I’ll be the first to admit, these various row ranking patterns are quite interesting, but, how can you use them for a meaningful result set?
A Bonus Thought
I have to give credit where credit is due. I learned so much about window functions from a wonderful series on YouTube and one video, in particular, inspired me for this next example. Please keep in mind although the examples in that series are demonstrated with a non-open-source database system (Don’t toss the digital rotten fruits and veggies at me), there is a ton to learn from the videos overall.
I see a pattern in most of the query results so far I want to explore. I will not filter by any month nor trail.
What I want to know, are the consecutive days that I burned more than 350 calories. Better yet, groups of those days.
Here is the base query I will start with and build off from:
mysql> SELECT day_walked, burned_calories,
-> ROW_NUMBER() OVER(ORDER BY day_walked ASC) AS positional_bound,
-> trail_hiked
-> FROM vw_fav_shoe_stats
-> WHERE burned_calories > 350;
+------------+-----------------+------------------+------------------------+
| day_walked | burned_calories | positional_bound | trail_hiked |
+------------+-----------------+------------------+------------------------+
| 2018-06-03 | 389.6 | 1 | Sandy Trail-Drive |
| 2018-06-04 | 394.6 | 2 | Sandy Trail-Drive |
| 2018-06-06 | 384.6 | 3 | Sandy Trail-Drive |
| 2018-06-07 | 382.7 | 4 | Sandy Trail-Drive |
| 2018-06-24 | 392.4 | 5 | House-Power Line Route |
| 2018-06-25 | 362.1 | 6 | West Boundary |
| 2018-06-26 | 380.5 | 7 | West Boundary |
| 2018-07-06 | 375.7 | 8 | West Boundary |
| 2018-07-08 | 351.6 | 9 | West Boundary |
| 2018-07-11 | 375.2 | 10 | West Boundary |
| 2018-07-15 | 382.9 | 11 | House-Power Line Route |
| 2018-07-16 | 368.6 | 12 | West Boundary |
| 2018-07-18 | 368.1 | 13 | West Boundary |
| 2018-07-22 | 378.3 | 14 | West Boundary |
| 2018-07-24 | 386.4 | 15 | West Boundary |
| 2018-07-25 | 379.9 | 16 | West Boundary |
| 2018-07-27 | 378.3 | 17 | West Boundary |
| 2018-07-30 | 361.6 | 18 | West Boundary |
| 2018-07-31 | 359.9 | 19 | West Boundary |
| 2018-08-06 | 357.7 | 20 | West Boundary |
+------------+-----------------+------------------+------------------------+
20 rows in set (0.00 sec)We’ve seen ROW_NUMBER() already, however now it really comes into play.
To make this work (in MySQL at least) I had to use the DATE_SUB() function since essentially, with this technique we are subtracting a number – the value provided by ROW_NUMBER() from the day_walked date column of the same row, which in turn, provides a date itself via the calculation:
mysql> SELECT day_walked AS day_of_walk,
-> DATE_SUB(day_walked, INTERVAL ROW_NUMBER() OVER(ORDER BY day_walked ASC) DAY) AS positional_bound,
-> burned_calories,
-> trail_hiked
-> FROM vw_fav_shoe_stats
-> WHERE burned_calories > 350;
+-------------+------------------+-----------------+------------------------+
| day_of_walk | positional_bound | burned_calories | trail_hiked |
+-------------+------------------+-----------------+------------------------+
| 2018-06-03 | 2018-06-02 | 389.6 | Sandy Trail-Drive |
| 2018-06-04 | 2018-06-02 | 394.6 | Sandy Trail-Drive |
| 2018-06-06 | 2018-06-03 | 384.6 | Sandy Trail-Drive |
| 2018-06-07 | 2018-06-03 | 382.7 | Sandy Trail-Drive |
| 2018-06-24 | 2018-06-19 | 392.4 | House-Power Line Route |
| 2018-06-25 | 2018-06-19 | 362.1 | West Boundary |
| 2018-06-26 | 2018-06-19 | 380.5 | West Boundary |
| 2018-07-06 | 2018-06-28 | 375.7 | West Boundary |
| 2018-07-08 | 2018-06-29 | 351.6 | West Boundary |
| 2018-07-11 | 2018-07-01 | 375.2 | West Boundary |
| 2018-07-15 | 2018-07-04 | 382.9 | House-Power Line Route |
| 2018-07-16 | 2018-07-04 | 368.6 | West Boundary |
| 2018-07-18 | 2018-07-05 | 368.1 | West Boundary |
| 2018-07-22 | 2018-07-08 | 378.3 | West Boundary |
| 2018-07-24 | 2018-07-09 | 386.4 | West Boundary |
| 2018-07-25 | 2018-07-09 | 379.9 | West Boundary |
| 2018-07-27 | 2018-07-10 | 378.3 | West Boundary |
| 2018-07-30 | 2018-07-12 | 361.6 | West Boundary |
| 2018-07-31 | 2018-07-12 | 359.9 | West Boundary |
| 2018-08-06 | 2018-07-17 | 357.7 | West Boundary |
+-------------+------------------+-----------------+------------------------+
20 rows in set (0.00 sec)However, without DATE_SUB(), you wind up with this (or at least I did):
mysql> SELECT day_walked AS day_of_walk,
-> day_walked - ROW_NUMBER() OVER(ORDER BY day_walked ASC) AS positional_bound,
-> burned_calories,
-> trail_hiked
-> FROM vw_fav_shoe_stats
-> WHERE burned_calories > 350;
+-------------+------------------+-----------------+------------------------+
| day_of_walk | positional_bound | burned_calories | trail_hiked |
+-------------+------------------+-----------------+------------------------+
| 2018-06-03 | 20180602 | 389.6 | Sandy Trail-Drive |
| 2018-06-04 | 20180602 | 394.6 | Sandy Trail-Drive |
| 2018-06-06 | 20180603 | 384.6 | Sandy Trail-Drive |
| 2018-06-07 | 20180603 | 382.7 | Sandy Trail-Drive |
| 2018-06-24 | 20180619 | 392.4 | House-Power Line Route |
| 2018-06-25 | 20180619 | 362.1 | West Boundary |
| 2018-06-26 | 20180619 | 380.5 | West Boundary |
| 2018-07-06 | 20180698 | 375.7 | West Boundary |
| 2018-07-08 | 20180699 | 351.6 | West Boundary |
| 2018-07-11 | 20180701 | 375.2 | West Boundary |
| 2018-07-15 | 20180704 | 382.9 | House-Power Line Route |
| 2018-07-16 | 20180704 | 368.6 | West Boundary |
| 2018-07-18 | 20180705 | 368.1 | West Boundary |
| 2018-07-22 | 20180708 | 378.3 | West Boundary |
| 2018-07-24 | 20180709 | 386.4 | West Boundary |
| 2018-07-25 | 20180709 | 379.9 | West Boundary |
| 2018-07-27 | 20180710 | 378.3 | West Boundary |
| 2018-07-30 | 20180712 | 361.6 | West Boundary |
| 2018-07-31 | 20180712 | 359.9 | West Boundary |
| 2018-08-06 | 20180786 | 357.7 | West Boundary |
+-------------+------------------+-----------------+------------------------+
20 rows in set (0.04 sec)Hey, that doesn’t look so bad really.
What gives?
Eh, the row with a positional_bound value of ‘20180698’…
Wait a minute, this is supposed to calculate a date value by subtracting the number ROW_NUMBER() provides from the day_of_walk column.
Correct.
I don’t know about you, but I am not aware of a month with 98 days!
But, if there is one, bring on the extra paychecks!
All fun aside, this obviously was incorrect and prompted me to (eventually) use DATE_SUB(), which provides a correct, results set then allowing me to run this query:
mysql> SELECT MIN(t.day_of_walk),
-> MAX(t.day_of_walk),
-> COUNT(*) AS num_of_hikes
-> FROM (SELECT day_walked AS day_of_walk,
-> DATE_SUB(day_walked, INTERVAL ROW_NUMBER() OVER(ORDER BY day_walked ASC) DAY) AS positional_bound
-> FROM vw_fav_shoe_stats
-> WHERE burned_calories > 350) AS t
-> GROUP BY t.positional_bound
-> ORDER BY 1;
+--------------------+--------------------+--------------+
| MIN(t.day_of_walk) | MAX(t.day_of_walk) | num_of_hikes |
+--------------------+--------------------+--------------+
| 2018-06-03 | 2018-06-04 | 2 |
| 2018-06-06 | 2018-06-07 | 2 |
| 2018-06-24 | 2018-06-26 | 3 |
| 2018-07-06 | 2018-07-06 | 1 |
| 2018-07-08 | 2018-07-08 | 1 |
| 2018-07-11 | 2018-07-11 | 1 |
| 2018-07-15 | 2018-07-16 | 2 |
| 2018-07-18 | 2018-07-18 | 1 |
| 2018-07-22 | 2018-07-22 | 1 |
| 2018-07-24 | 2018-07-25 | 2 |
| 2018-07-27 | 2018-07-27 | 1 |
| 2018-07-30 | 2018-07-31 | 2 |
| 2018-08-06 | 2018-08-06 | 1 |
+--------------------+--------------------+--------------+
13 rows in set (0.12 sec)Basically, I have wrapped the results set provided from that analytical query, in the form of a Derived Table, and queried it for: a start and end date, a count of what I have labeled num_of_hikes, then grouped on the positional_bound column, ultimately providing sets of groups of consecutive days where I burned more than 350 calories.
You can see in the date range of 2018-06-24 to 2018-06-26, resulted in 3 consecutive days meeting the calorie burned criteria of 350 in the WHERE clause.
Not too bad if I don’t say so myself, but definitely a record I want to try and best!
Conclusion
Window functions are in a world and league of their own. I have not even scratched the surface of them, having only covered 3 of them in a ‘high-level‘ introductory and perhaps, trivial sense. However, hopefully, through this post, you find that you can query for quite interesting and potentially insightful data with a ‘bare minimal‘ use of them.
Thank you for reading.
