WEBVTT 1 00:00:00.570 --> 00:00:02.100 Hi guys and welcome back. 2 00:00:02.100 --> 00:00:03.960 In this video, we're going to continue talking 3 00:00:03.960 --> 00:00:06.770 about window functions with the example 4 00:00:06.770 --> 00:00:08.610 from our polling app. 5 00:00:08.610 --> 00:00:12.470 So here is the query that we had earlier on. 6 00:00:12.470 --> 00:00:14.380 You can see that everything is stuff 7 00:00:14.380 --> 00:00:15.440 that we're familiar with, 8 00:00:15.440 --> 00:00:17.210 except for the thing in the middle 9 00:00:17.210 --> 00:00:21.327 which is the count divided by the sum of the count over. 10 00:00:21.327 --> 00:00:23.050 So we're going to be focusing 11 00:00:23.050 --> 00:00:25.560 on that specific line as we go through, 12 00:00:25.560 --> 00:00:28.230 but first of all, let me just bring this up here 13 00:00:28.230 --> 00:00:32.390 and we're going to evaluate what data we're working with 14 00:00:32.390 --> 00:00:34.590 and whenever you're analysing one of these functions, 15 00:00:34.590 --> 00:00:36.950 that should be the first port of call. 16 00:00:36.950 --> 00:00:38.860 What is the data that you have? 17 00:00:38.860 --> 00:00:40.820 And really the data that we've got here 18 00:00:40.820 --> 00:00:44.650 is everything in the from clause downwards. 19 00:00:44.650 --> 00:00:47.310 And so we've got all of this stuff here. 20 00:00:47.310 --> 00:00:50.963 Now, from options gives us the options table. 21 00:00:51.820 --> 00:00:54.830 When we left join it on vote, we're going to get 22 00:00:54.830 --> 00:00:59.830 duplicate rows in the option table, one for each vote. 23 00:01:00.180 --> 00:01:04.010 Run this separately in a fiddle or on your database 24 00:01:04.010 --> 00:01:07.430 if you want and you're not exactly sure what I'm saying, 25 00:01:07.430 --> 00:01:09.900 but essentially we'd end up with a bunch of duplicate rows 26 00:01:09.900 --> 00:01:14.120 for options and each row would have a different vote data. 27 00:01:14.120 --> 00:01:16.000 But then, because we're grouping, 28 00:01:16.000 --> 00:01:19.570 we reduce the duplication in the options rows 29 00:01:19.570 --> 00:01:24.570 and we end up with some hidden groups for the votes. 30 00:01:24.860 --> 00:01:27.230 Those groups, as we know, can only be accessed 31 00:01:27.230 --> 00:01:28.530 with an aggregate function 32 00:01:28.530 --> 00:01:31.897 like sum, count, average, et cetera. 33 00:01:31.897 --> 00:01:33.810 Note that I'm ignoring the where clause here 34 00:01:33.810 --> 00:01:37.110 because it's not really important at this point in time. 35 00:01:37.110 --> 00:01:40.820 So, the question now is, is this data here, 36 00:01:40.820 --> 00:01:43.240 the rows and the hidden groups, 37 00:01:43.240 --> 00:01:46.040 what the window function has access to? 38 00:01:46.040 --> 00:01:47.563 And the answer to that is no 39 00:01:47.563 --> 00:01:49.590 because the aggregate functions, 40 00:01:49.590 --> 00:01:52.680 in this case it's count, run first. 41 00:01:52.680 --> 00:01:55.540 So we're going to get this data, 42 00:01:55.540 --> 00:01:58.560 but with the count already calculated. 43 00:01:58.560 --> 00:02:02.370 And it's going to look something like this. 44 00:02:02.370 --> 00:02:05.240 We're going to have all of the option data 45 00:02:05.240 --> 00:02:08.690 and then, instead of the hidden groups, 46 00:02:08.690 --> 00:02:12.360 we have the already calculated count. 47 00:02:12.360 --> 00:02:16.760 So that gives us three, two, and one for each of these rows. 48 00:02:16.760 --> 00:02:19.080 Note that the column name here is count of votes, 49 00:02:19.080 --> 00:02:21.620 the option ID, and I don't mean to imply 50 00:02:21.620 --> 00:02:25.550 that has to yet be calculated, that's already calculated, 51 00:02:25.550 --> 00:02:26.960 that's just the name of the column. 52 00:02:26.960 --> 00:02:30.720 So, now that we've got this, the count runs first 53 00:02:30.720 --> 00:02:33.150 and that is the data that the window function, 54 00:02:33.150 --> 00:02:36.590 the sum function in this case, has to work with. 55 00:02:36.590 --> 00:02:39.580 So let me just bring this up here to the middle 56 00:02:39.580 --> 00:02:42.800 and now you can see that we can very easily run sum 57 00:02:42.800 --> 00:02:44.580 on this stuff here. 58 00:02:44.580 --> 00:02:45.413 Why? 59 00:02:45.413 --> 00:02:46.880 Because we've just got four columns 60 00:02:46.880 --> 00:02:48.070 and one of them has a bunch of numbers 61 00:02:48.070 --> 00:02:50.220 so clearly we can add those. 62 00:02:50.220 --> 00:02:52.380 So what we're going to do is we're going to focus 63 00:02:52.380 --> 00:02:56.170 on this particular part of the select statement 64 00:02:56.170 --> 00:02:57.730 and we're going to focus 65 00:02:57.730 --> 00:02:59.893 on the first row of data specifically. 66 00:03:00.920 --> 00:03:04.210 By the time that this output is evaluated, 67 00:03:04.210 --> 00:03:07.400 that means that the count has already been calculated. 68 00:03:07.400 --> 00:03:09.740 That's why we've got the number three in there. 69 00:03:09.740 --> 00:03:11.420 So we're going to start 70 00:03:11.420 --> 00:03:14.450 with the calculated counts down there. 71 00:03:14.450 --> 00:03:16.750 Those rectangles there mean the value 72 00:03:16.750 --> 00:03:19.913 that these expressions represent. 73 00:03:21.000 --> 00:03:23.000 So you can see that on the left, 74 00:03:23.000 --> 00:03:26.230 the count is on its own, it's not in a window function, 75 00:03:26.230 --> 00:03:30.910 and what it evaluates to is the value that the current row 76 00:03:30.910 --> 00:03:33.150 is working with, so that is the count 77 00:03:33.150 --> 00:03:35.910 of the associated votes, that's three. 78 00:03:35.910 --> 00:03:40.570 But on the right, we don't quite get the three 79 00:03:40.570 --> 00:03:42.940 because we're going to run this in a window function 80 00:03:42.940 --> 00:03:45.340 and what that is going to represent 81 00:03:45.340 --> 00:03:48.840 is the counts throughout the entire window. 82 00:03:48.840 --> 00:03:50.793 That's what the over means. 83 00:03:51.760 --> 00:03:54.200 So we end up with three, two, and one. 84 00:03:54.200 --> 00:03:56.870 When we add them together, using the window function, 85 00:03:56.870 --> 00:03:59.260 that has access to this entire result set 86 00:03:59.260 --> 00:04:01.290 because the window function runs 87 00:04:01.290 --> 00:04:03.580 after the count has evaluated. 88 00:04:03.580 --> 00:04:06.730 So it has access to the calculated counts. 89 00:04:06.730 --> 00:04:10.510 Then we can divide and we can multiply by a hundred, 90 00:04:10.510 --> 00:04:12.590 giving us the fifty percent. 91 00:04:12.590 --> 00:04:16.700 So that's really the important thing to take into account. 92 00:04:16.700 --> 00:04:19.680 By the time the window function runs, 93 00:04:19.680 --> 00:04:22.480 the count is no longer a function. 94 00:04:22.480 --> 00:04:26.800 It is just the value and sum is operating 95 00:04:26.800 --> 00:04:29.780 over the entire results, 96 00:04:29.780 --> 00:04:31.930 whereas without the window function, 97 00:04:31.930 --> 00:04:35.453 you're just thinking about individual rows. 98 00:04:36.300 --> 00:04:38.880 That's what makes the window functions a little bit tricky 99 00:04:38.880 --> 00:04:40.690 because they are operating essentially 100 00:04:40.690 --> 00:04:41.890 at two different stages 101 00:04:41.890 --> 00:04:45.280 and have access to two different groups of data. 102 00:04:45.280 --> 00:04:46.620 So hopefully these diagrams have 103 00:04:46.620 --> 00:04:50.120 somewhat helped you understand what's going on. 104 00:04:50.120 --> 00:04:52.970 But in the next two videos we've got a few more examples, 105 00:04:52.970 --> 00:04:55.070 including a couple of things that you can do 106 00:04:55.070 --> 00:04:56.840 with window functions to, for example, 107 00:04:56.840 --> 00:05:00.820 sort columns and partition them before operating on them. 108 00:05:00.820 --> 00:05:02.420 So let's go over to the next video 109 00:05:02.420 --> 00:05:03.740 where we're going to look at that. 110 00:05:03.740 --> 00:05:05.890 Thanks for watching and I'll see you there.