WEBVTT 1 00:00:00.520 --> 00:00:01.920 Hi guys, and welcome back. 2 00:00:01.920 --> 00:00:04.980 In this video we're going to look at how to use PARTITION 3 00:00:04.980 --> 00:00:08.270 with window function so that we can further subdivide 4 00:00:08.270 --> 00:00:10.890 the data that the window function is working with. 5 00:00:10.890 --> 00:00:11.993 So let's get to it. 6 00:00:12.970 --> 00:00:15.690 Here, we've got the data for this lesson. 7 00:00:15.690 --> 00:00:18.290 Again, we're working with polls, options and votes, 8 00:00:18.290 --> 00:00:20.570 and we've got two polls in this case, 9 00:00:20.570 --> 00:00:23.460 five options, and a few votes down here. 10 00:00:23.460 --> 00:00:26.400 What we want to achieve is to get, 11 00:00:26.400 --> 00:00:29.850 for each option, how many votes they have 12 00:00:29.850 --> 00:00:34.310 and rank the options in order of their votes, 13 00:00:34.310 --> 00:00:38.610 but we want to make sure that they are subdivided by poll. 14 00:00:38.610 --> 00:00:43.240 So the top option in a poll will have rank one 15 00:00:43.240 --> 00:00:45.330 and the second option in a poll 16 00:00:45.330 --> 00:00:46.970 will have rank two, and so on. 17 00:00:46.970 --> 00:00:49.490 So here, for example, we've got Flask 18 00:00:49.490 --> 00:00:51.560 that has the most options in this poll, 19 00:00:51.560 --> 00:00:53.250 that would have rank one. 20 00:00:53.250 --> 00:00:55.430 And Python that has the most options 21 00:00:55.430 --> 00:00:57.690 in this poll would also have rank one 22 00:00:57.690 --> 00:01:00.780 because they are the top option in their poll. 23 00:01:00.780 --> 00:01:02.570 So that's what we want to achieve. 24 00:01:02.570 --> 00:01:04.710 What we've got down here is some code 25 00:01:04.710 --> 00:01:06.720 that is gonna give us the poll title, 26 00:01:06.720 --> 00:01:08.650 the option text for each poll, 27 00:01:08.650 --> 00:01:11.730 as well as the count of votes for each option. 28 00:01:11.730 --> 00:01:13.190 And so what I'm gonna do though, 29 00:01:13.190 --> 00:01:15.420 is make sure that this is correct. 30 00:01:15.420 --> 00:01:16.703 Like that. 31 00:01:17.870 --> 00:01:19.710 And press run. 32 00:01:19.710 --> 00:01:22.810 And now what we get here is Python vs. Java, 33 00:01:22.810 --> 00:01:26.330 option is Python and the votes are two for that option. 34 00:01:26.330 --> 00:01:27.910 And in Flask vs. Django we've got 35 00:01:27.910 --> 00:01:29.500 these different vote counts there. 36 00:01:29.500 --> 00:01:31.320 And finally we get back to Python vs. Java. 37 00:01:31.320 --> 00:01:32.760 You can see these votes are, 38 00:01:32.760 --> 00:01:35.120 or these options are not ordered by any way 39 00:01:35.120 --> 00:01:38.410 and you can see the vote counts in each. 40 00:01:38.410 --> 00:01:42.360 So what we want to do is use the rank function. 41 00:01:42.360 --> 00:01:44.430 But how are we going to order 42 00:01:44.430 --> 00:01:47.200 because if we order by vote count 43 00:01:48.120 --> 00:01:49.840 then you can see that we're gonna get 44 00:01:49.840 --> 00:01:52.360 some strange results, right? 45 00:01:52.360 --> 00:01:55.490 And so, let's try it and see what happens. 46 00:01:55.490 --> 00:01:56.940 And the reason why we're gonna get strange results is 47 00:01:56.940 --> 00:02:00.700 because these options are not subdivided 48 00:02:00.700 --> 00:02:03.670 by poll so when we rank, we're gonna rank 49 00:02:03.670 --> 00:02:05.230 all of the options together. 50 00:02:05.230 --> 00:02:08.550 So let me do rank, over and then we're gonna order 51 00:02:08.550 --> 00:02:11.740 by the count of votes in descending order, 52 00:02:11.740 --> 00:02:13.933 see what happens, so I'll run this. 53 00:02:13.933 --> 00:02:15.660 Just gonna come down here 54 00:02:15.660 --> 00:02:17.490 and so you can see a bit better 55 00:02:17.490 --> 00:02:21.590 and you can see that we get rank one for Flask 56 00:02:21.590 --> 00:02:24.300 because this is the top voted option 57 00:02:24.300 --> 00:02:27.660 across all of the votes. 58 00:02:27.660 --> 00:02:30.450 Rank two for Python and then Django 59 00:02:30.450 --> 00:02:32.690 and Java have both rank three 60 00:02:32.690 --> 00:02:35.460 because they have the same number of votes. 61 00:02:35.460 --> 00:02:37.920 Finally, we've got It Depends on rank five. 62 00:02:37.920 --> 00:02:40.170 The reason why it's got rank five and not rank four 63 00:02:40.170 --> 00:02:42.680 is because the rank function skips a rank 64 00:02:42.680 --> 00:02:45.470 if it has a duplicate rank. 65 00:02:45.470 --> 00:02:48.310 You can use RANK_DENSE, by the way, 66 00:02:48.310 --> 00:02:51.280 in order to, or sorry, it's DENSE_RANK, not RANK_DENSE. 67 00:02:51.280 --> 00:02:55.687 DENSE_RANK there in order to avoid that skip, 68 00:02:57.260 --> 00:02:58.370 so now you've got rank three, 69 00:02:58.370 --> 00:02:59.980 rank three, and then rank four. 70 00:02:59.980 --> 00:03:01.720 Just up to you which one you wanna do. 71 00:03:01.720 --> 00:03:04.200 I'm gonna stick with rank for now. 72 00:03:04.200 --> 00:03:07.090 So we've just ordered and we've told rank 73 00:03:07.090 --> 00:03:09.520 what column to use for ranking, 74 00:03:09.520 --> 00:03:11.730 but we haven't told it to treat each poll 75 00:03:11.730 --> 00:03:16.340 as a separate group of ranks, but we can do that, 76 00:03:16.340 --> 00:03:21.340 simply by using PARTITION BY and then polls.title. 77 00:03:21.770 --> 00:03:26.012 If we do this, then rank is going to take the first title 78 00:03:26.012 --> 00:03:29.810 and it's gonna rank the rows for that title, 79 00:03:29.810 --> 00:03:31.080 then it's gonna take the next title, 80 00:03:31.080 --> 00:03:31.913 and it's gonna rank the rows 81 00:03:31.913 --> 00:03:34.140 for that title separately and so on. 82 00:03:34.140 --> 00:03:37.120 So if we run this, then you'll see that we get 83 00:03:37.120 --> 00:03:40.400 what we expected, one two and three, and one and two. 84 00:03:40.400 --> 00:03:44.200 Notice that this also ends up as ordered in a way, 85 00:03:44.200 --> 00:03:48.123 because of how the rank function operates. 86 00:03:49.310 --> 00:03:51.620 I'm gonna leave a link in the resources section 87 00:03:51.620 --> 00:03:54.760 of this video for you to read a bit more 88 00:03:54.760 --> 00:03:56.480 about some of these functions that you can use, 89 00:03:56.480 --> 00:03:57.750 some of these window functions. 90 00:03:57.750 --> 00:04:00.860 There's a great post over at PostgreSQL Tutorial 91 00:04:00.860 --> 00:04:03.660 that covers some of these most used window functions 92 00:04:03.660 --> 00:04:04.493 and it's really good. 93 00:04:04.493 --> 00:04:06.240 So I recommend that you check it out 94 00:04:06.240 --> 00:04:08.350 if you want to learn more about window functions, 95 00:04:08.350 --> 00:04:10.860 partitioning, and ordering. 96 00:04:10.860 --> 00:04:12.210 Other than that, thank you guys 97 00:04:12.210 --> 00:04:13.550 for joining me in this video. 98 00:04:13.550 --> 00:04:14.530 I hope you've enjoyed it. 99 00:04:14.530 --> 00:04:15.470 Thanks for watching. 100 00:04:15.470 --> 00:04:17.120 And I'll see you in the next one.