Learn What A Window Function Can Do
In this lesson we’ll see how we can partition movies into the years they were released, and find the top 5 rated titles each year. The power of the window function is to partition data into groups and answer questions about those groups.
[00:00] We'll start learning window functions by taking a look at row number. Row number is a really simple window function. We'll take a look at partitioning by year. This says that the window will be each year. 1893 is a window, all these for 1894 are a window, all of these for 1895 are a window.
[00:21] We can see that we're just numbering each row here, but we haven't defined an order for these to be in. This numbering is arbitrary. Instead, we'll say, "Order by rating, descending." This will be our ranking. The lowest rated ones will be at the bottom, the highest rated ones will be at the top.
[00:44] The highest rated movie in 1894 gets first place, the lowest rated movie in 1894 gets ninth place. These are based on the size of the window. In 1895 there are only three movies, last place is actually three.
[00:58] What's really cool is that now we can perform arbitrary selects over these. We can say, "Select star from this sub-query," and we'll give it a name, "Ratings," where ratings.ranking equals one.
[01:24] Now, we have the best-rated movies of each year, 1893, 1894, etc. etc. We could say, "Less than or equal to five." Now, we have the top five movies each year. We probably want to make sure we order by year, and ranking, ascending, just to be specific.
Thanks for creating this course. This is just what I needed. By the way, what SQL tool are you using in your screencast?