WEBVTT 00:00:00.920 --> 00:00:02.370 Hi guys, and Welcome back. 00:00:02.370 --> 00:00:04.010 In this video we're going to talk 00:00:04.010 --> 00:00:07.410 about PostgreSQL window functions, window functions 00:00:07.410 --> 00:00:10.160 are a bit more advanced than anything we've seen so far. 00:00:10.160 --> 00:00:12.460 So please do bear with me while I explain 00:00:12.460 --> 00:00:14.850 this using some diagrams. 00:00:14.850 --> 00:00:17.110 First of all, let me show you what we're working with, 00:00:17.110 --> 00:00:20.660 we've got four different segments in this screen, 00:00:20.660 --> 00:00:22.070 the data that we're gonna be working 00:00:22.070 --> 00:00:24.910 with is on the top left, the query that we're gonna run 00:00:24.910 --> 00:00:27.730 is on the bottom left, the output that we get running, 00:00:27.730 --> 00:00:30.580 that query will be on the bottom right, and the explanation, 00:00:30.580 --> 00:00:31.413 or some bullet points about what's going on 00:00:31.413 --> 00:00:33.410 will be at the top right. 00:00:33.410 --> 00:00:36.130 So here's the data that we're gonna be working 00:00:36.130 --> 00:00:38.100 with just now for this example. 00:00:38.100 --> 00:00:41.850 We've got employee data, and these employees have a name, 00:00:41.850 --> 00:00:44.430 a salary and a department. 00:00:44.430 --> 00:00:48.980 And now let's say that we want to run a query like this one. 00:00:48.980 --> 00:00:51.090 Select salary and the average of salary 00:00:51.090 --> 00:00:52.550 from the employees table. 00:00:52.550 --> 00:00:57.550 Now, we know that we can't do this and because we are trying 00:00:57.630 --> 00:01:00.870 to select the average of something that hasn't been grouped. 00:01:00.870 --> 00:01:03.940 So this is ideally what we would want, 00:01:03.940 --> 00:01:05.820 but it's not really what we're gonna get. 00:01:05.820 --> 00:01:09.580 So we know that this doesn't work, it looks like the average 00:01:09.580 --> 00:01:13.120 for this query should be calculated in the whole column. 00:01:13.120 --> 00:01:15.803 But it doesn't work because we don't have a group by. 00:01:16.710 --> 00:01:21.020 So what can we do in order to make this work? 00:01:21.020 --> 00:01:22.910 Well, we have to use a window function. 00:01:22.910 --> 00:01:24.410 So here's the same data set, 00:01:24.410 --> 00:01:27.980 but now we're going to group by id. 00:01:27.980 --> 00:01:29.530 Now we know that when we calculate 00:01:29.530 --> 00:01:33.330 the average of a column that we have a group for, 00:01:33.330 --> 00:01:35.640 that's gonna work, but what it's gonna do 00:01:35.640 --> 00:01:39.830 is going to give us the average of each group as the result, 00:01:39.830 --> 00:01:44.570 which seems we only have one id per person with that salary 00:01:44.570 --> 00:01:47.610 is just giving us the same value that we started with. 00:01:47.610 --> 00:01:50.030 So this is almost completely useless. 00:01:50.030 --> 00:01:53.100 But it does work because we have a group by. 00:01:53.100 --> 00:01:55.500 Same thing again, but now we're going 00:01:55.500 --> 00:01:57.510 to use the window function, 00:01:57.510 --> 00:02:00.030 so what we've got here is the average of the salary 00:02:00.030 --> 00:02:02.240 and then we've got the keyword over, 00:02:02.240 --> 00:02:05.360 which is itself a function as well. 00:02:05.360 --> 00:02:09.020 Now, this is actually gonna give us what we want. 00:02:09.020 --> 00:02:11.480 We have the salary for each person, 00:02:11.480 --> 00:02:14.450 and then the average of all salaries on the right. 00:02:14.450 --> 00:02:17.130 Why might we want this, for example, if we want to compare 00:02:17.130 --> 00:02:19.740 whether each person salary is greater than 00:02:19.740 --> 00:02:22.160 or lower than the average for example. 00:02:22.160 --> 00:02:25.150 So average of salary over 00:02:25.150 --> 00:02:27.830 is what we call a window function. 00:02:27.830 --> 00:02:31.160 And it operates on the related rows 00:02:31.160 --> 00:02:33.550 of the row that we're calculating, 00:02:33.550 --> 00:02:36.563 rather than on the same road that it would without the over. 00:02:37.440 --> 00:02:38.660 So I'm gonna explain a bit more 00:02:38.660 --> 00:02:40.760 what that means in just a moment. 00:02:40.760 --> 00:02:42.860 The related rows in this case though, 00:02:42.860 --> 00:02:47.220 the thing that the average of salary is operating on, 00:02:47.220 --> 00:02:49.460 are all the rows in the table. 00:02:49.460 --> 00:02:52.960 So what we're doing here is we're selecting the salary 00:02:52.960 --> 00:02:56.120 as well as the average salary of the entire table, 00:02:56.120 --> 00:02:57.260 from the employees table, 00:02:57.260 --> 00:02:59.890 I'll show you a quick diagram on what I mean you 00:02:59.890 --> 00:03:01.950 here we've got that same query. 00:03:01.950 --> 00:03:05.640 And what the query does when you use a window function 00:03:05.640 --> 00:03:10.620 is essentially get the non window functions 00:03:10.620 --> 00:03:13.850 and get the window functions separately, 00:03:13.850 --> 00:03:18.040 because it's a window function, it runs in its own area 00:03:18.040 --> 00:03:20.420 if you like to think about it that way. 00:03:20.420 --> 00:03:23.690 So we've got the Select salary in one side, 00:03:23.690 --> 00:03:25.220 and select the average of salary 00:03:25.220 --> 00:03:27.350 from the employees table on the other side. 00:03:27.350 --> 00:03:29.120 Something to note is that the window function 00:03:29.120 --> 00:03:33.150 has access to everything that we have in our FROM clause. 00:03:33.150 --> 00:03:35.020 So here we're simply using that to select 00:03:35.020 --> 00:03:36.550 the average of the column. 00:03:36.550 --> 00:03:38.710 Then what happens at the end is these two results 00:03:38.710 --> 00:03:41.650 get merged together, and they give us the salary 00:03:41.650 --> 00:03:43.250 and the average for everything. 00:03:43.250 --> 00:03:46.240 So this is how the window function works 00:03:46.240 --> 00:03:50.710 when you have the over clause with nothing in the brackets, 00:03:50.710 --> 00:03:54.810 so, you can use the brackets in the over function to define 00:03:54.810 --> 00:03:56.580 the window more specifically. 00:03:56.580 --> 00:03:58.830 Inside the brackets we can add code that tells PostgreSQL 00:03:58.830 --> 00:04:01.760 what the related rows are. 00:04:01.760 --> 00:04:03.930 And if you don't put anything in the brackets, 00:04:03.930 --> 00:04:06.380 then PostgreSQL assumes the related rows 00:04:06.380 --> 00:04:09.630 are the entire thing that you've tried to get. 00:04:09.630 --> 00:04:10.950 PostgreSQL comes with a bunch of functions 00:04:10.950 --> 00:04:15.090 that must use over and they are called window functions. 00:04:15.090 --> 00:04:17.380 And other functions like average count, 00:04:17.380 --> 00:04:20.683 or sum can also be used as window functions. 00:04:21.520 --> 00:04:23.990 Something important to note, and we're gonna elaborate 00:04:23.990 --> 00:04:25.520 on this in the next few videos, 00:04:25.520 --> 00:04:28.660 is when our window functions evaluated? 00:04:28.660 --> 00:04:31.330 So here's a quote from the official documentation 00:04:31.330 --> 00:04:33.990 that says that if the query that you're running 00:04:33.990 --> 00:04:37.190 contains window functions, then PostgreSQL 00:04:37.190 --> 00:04:41.030 is gonna evaluate those after grouping aggregation 00:04:41.030 --> 00:04:43.810 and having filtering is performed. 00:04:43.810 --> 00:04:46.610 So the query users and the aggregates group by 00:04:46.610 --> 00:04:49.510 or having, then the rows that the window function 00:04:49.510 --> 00:04:54.440 sees are the group rows instead of the original table rows. 00:04:54.440 --> 00:04:58.120 So in our example just now where we use the average over, 00:04:58.120 --> 00:05:01.410 we didn't have any of these and grouping aggregation 00:05:01.410 --> 00:05:04.910 or having filtering, so the window function saw everything. 00:05:04.910 --> 00:05:07.320 But that changes when you have aggregates 00:05:07.320 --> 00:05:08.763 group by or having. 00:05:09.740 --> 00:05:11.850 Window functions also run after normal 00:05:11.850 --> 00:05:13.730 where filtering is applied. 00:05:13.730 --> 00:05:16.110 So the window function will only run on data 00:05:16.110 --> 00:05:18.640 that has been filtered out by where, 00:05:18.640 --> 00:05:21.240 and window functions have access to all the columns 00:05:21.240 --> 00:05:22.980 that you have in your FROM clause 00:05:22.980 --> 00:05:24.720 including joins and all that stuff, 00:05:24.720 --> 00:05:27.050 as we're gonna see with another example in the next video, 00:05:27.050 --> 00:05:30.670 not just the selected columns. 00:05:30.670 --> 00:05:32.440 Alright, thank you guys for joining me for this video, 00:05:32.440 --> 00:05:35.320 I hope this starts to make a little bit of sense, 00:05:35.320 --> 00:05:37.030 but over the next few videos, we're gonna look 00:05:37.030 --> 00:05:39.470 into more detail as to what window functions 00:05:39.470 --> 00:05:41.640 are and how we can use them, as well as a bunch 00:05:41.640 --> 00:05:43.910 of different options that we can give them. 00:05:43.910 --> 00:05:45.460 I'll see you in the next video.