This Lesson is for Members

Subscribe today and get access to all lessons! Plus direct HD download for offline use, enhances transcripts, member comment forums, and iTunes "podcast" RSS feed. Level up your skills now!

Unlock This Lesson

Already subscribed? Sign In

Autoplay

    Learn What A Window Function Can Do

    Brett CassetteBrett Cassette

    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.

    Code

    Code

    Become a Member to view code

    You must be a Member to view code

    Access all courses and lessons, track your progress, gain confidence and expertise.

    Become a Member
    and unlock code for this lesson
    Transcript

    Transcript

    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.

    Discuss

    Discuss