WEBVTT 1 00:00:00.150 --> 00:00:01.770 Hi guys, and welcome back. 2 00:00:01.770 --> 00:00:03.210 In this video, we're going to learn 3 00:00:03.210 --> 00:00:06.040 about built-in functions in PostgreSQL 4 00:00:06.040 --> 00:00:08.720 and how we can use them to get a random voter, 5 00:00:08.720 --> 00:00:10.750 the voter for one of our auctions. 6 00:00:10.750 --> 00:00:13.480 So first of all, what are functions? 7 00:00:13.480 --> 00:00:15.200 Very much like Python functions, 8 00:00:15.200 --> 00:00:17.660 names for blocks of code that we can run. 9 00:00:17.660 --> 00:00:19.110 And just like Python functions, 10 00:00:19.110 --> 00:00:21.870 they can have arguments and parameters as well. 11 00:00:21.870 --> 00:00:23.450 There's a lot of built-in functions, 12 00:00:23.450 --> 00:00:26.680 including mathematical and aggregate functions. 13 00:00:26.680 --> 00:00:27.870 Here's just two examples. 14 00:00:27.870 --> 00:00:29.660 Here's a mathematical function. 15 00:00:29.660 --> 00:00:32.550 Select random, and then put the brackets at the end 16 00:00:32.550 --> 00:00:35.000 and that tells PostgreSQL that you wanna run this function. 17 00:00:35.000 --> 00:00:38.720 That's gonna give you a random number between zero and one. 18 00:00:38.720 --> 00:00:40.340 And you've got something, for example, 19 00:00:40.340 --> 00:00:43.990 select count and then put id, the id column, 20 00:00:43.990 --> 00:00:45.860 in the brackets from polls. 21 00:00:45.860 --> 00:00:48.170 That's gonna give you the number of values 22 00:00:48.170 --> 00:00:51.453 in the id column from the polls data sect. 23 00:00:52.310 --> 00:00:53.760 So, mathematical functions. 24 00:00:53.760 --> 00:00:55.080 We've got a few examples here. 25 00:00:55.080 --> 00:00:58.880 random() gives you a value between 0.0 and 1.0. 26 00:00:58.880 --> 00:01:01.210 So, floating point value. 27 00:01:01.210 --> 00:01:03.880 abs(x) accepts an argument 28 00:01:03.880 --> 00:01:05.720 and gives you it's absolute value. 29 00:01:05.720 --> 00:01:08.733 So for example, minus five would be converted to five. 30 00:01:09.680 --> 00:01:12.790 mod(y,x) gives you the remainder of Y and X, 31 00:01:12.790 --> 00:01:16.040 kind of like the Python modulus operator. 32 00:01:16.040 --> 00:01:18.310 And there's loads more, dozens more. 33 00:01:18.310 --> 00:01:20.030 So I've left a link to the documentation 34 00:01:20.030 --> 00:01:21.620 over in the resources section 35 00:01:21.620 --> 00:01:22.630 in case you wanna learn more 36 00:01:22.630 --> 00:01:25.110 about these mathematical functions. 37 00:01:25.110 --> 00:01:28.160 Really interesting are aggregate functions, though. 38 00:01:28.160 --> 00:01:30.160 We've got something like count(expr). 39 00:01:30.160 --> 00:01:32.940 And count(expr) doesn't take just a normal argument, 40 00:01:32.940 --> 00:01:34.820 it takes an expression. 41 00:01:34.820 --> 00:01:36.680 And it gives you the number of rows 42 00:01:36.680 --> 00:01:39.590 resulting from evaluating the expression. 43 00:01:39.590 --> 00:01:42.850 So for example, if you give it a column inside a select, 44 00:01:42.850 --> 00:01:47.020 it's gonna calculate how many values are in that column 45 00:01:47.020 --> 00:01:49.210 out of the entire result set. 46 00:01:49.210 --> 00:01:51.500 avg(expr), for example, gives you the average value 47 00:01:51.500 --> 00:01:52.690 of those rows. 48 00:01:52.690 --> 00:01:55.080 max(expr) gives you the maximum value of the rows. 49 00:01:55.080 --> 00:01:56.940 And again, there's loads more. 50 00:01:56.940 --> 00:01:59.720 And again, I've also left a link to the documentation 51 00:01:59.720 --> 00:02:00.950 in the resources section. 52 00:02:00.950 --> 00:02:02.960 I'd encourage you to at least know 53 00:02:02.960 --> 00:02:04.860 some of the aggregate functions, 54 00:02:04.860 --> 00:02:08.380 but really, if you can read through the documentation, 55 00:02:08.380 --> 00:02:11.800 you're then gonna know more or less what's there. 56 00:02:11.800 --> 00:02:14.000 And then later on, it may come to you 57 00:02:14.000 --> 00:02:15.370 when you're trying to write a query 58 00:02:15.370 --> 00:02:17.650 and you're not quite sure how to do it, you may remember, 59 00:02:17.650 --> 00:02:19.330 I remember looking at the documentation 60 00:02:19.330 --> 00:02:21.380 and there was a function that maybe could help here. 61 00:02:21.380 --> 00:02:23.800 That's really the aim of reading the documentation 62 00:02:23.800 --> 00:02:25.400 at this point. 63 00:02:25.400 --> 00:02:27.330 So how are we gonna get a random voter? 64 00:02:27.330 --> 00:02:29.630 Well, we're gonna use the random function. 65 00:02:29.630 --> 00:02:31.020 Select '*' from votes. 66 00:02:31.020 --> 00:02:32.630 It's gonna give us all the votes. 67 00:02:32.630 --> 00:02:35.100 Where option id equal value, 68 00:02:35.100 --> 00:02:38.563 is gonna give us all the votes for a particular option. 69 00:02:39.410 --> 00:02:43.180 Order by random is gonna shuffle the results. 70 00:02:43.180 --> 00:02:45.920 Limit one is gonna give us just the top one. 71 00:02:45.920 --> 00:02:49.820 So essentially, we are getting one random vote 72 00:02:49.820 --> 00:02:52.150 for a particular option. 73 00:02:52.150 --> 00:02:53.950 Then, our Python function is gonna look 74 00:02:53.950 --> 00:02:55.600 very much like our other ones. 75 00:02:55.600 --> 00:02:58.190 Just grab the transactions, stop the cursor, 76 00:02:58.190 --> 00:03:01.060 run the query with the option id as an argument, 77 00:03:01.060 --> 00:03:03.210 and return cursor.fetchone. 78 00:03:03.210 --> 00:03:05.163 Let's go and do this over in PyCharm. 79 00:03:06.900 --> 00:03:08.200 All right, so we're here in PyCharm 80 00:03:08.200 --> 00:03:10.200 and we've got the query created. 81 00:03:10.200 --> 00:03:12.320 Select random vote. 82 00:03:12.320 --> 00:03:14.230 It is equal to select '*' from votes 83 00:03:14.230 --> 00:03:17.780 where the option id is equal to the value passed in. 84 00:03:17.780 --> 00:03:20.210 Order by random, which is gonna shuffle the rows. 85 00:03:20.210 --> 00:03:23.030 Limit one, which is gonna give us only one row, 86 00:03:23.030 --> 00:03:24.060 essentially a random one. 87 00:03:24.060 --> 00:03:25.550 It doesn't matter what order it's in 88 00:03:25.550 --> 00:03:27.920 because we are shuffling them. 89 00:03:27.920 --> 00:03:30.920 Then, we're gonna copy that, go down to the bottom, 90 00:03:30.920 --> 00:03:33.120 and it's here, get random poll vote. 91 00:03:33.120 --> 00:03:34.610 All we have to do is run the query. 92 00:03:34.610 --> 00:03:37.490 So, we're gonna do cursor.execute. 93 00:03:37.490 --> 00:03:39.560 We're gonna pass in the query and the option id, 94 00:03:39.560 --> 00:03:41.880 making sure that it is a tophold 95 00:03:41.880 --> 00:03:43.220 by putting that comma there. 96 00:03:43.220 --> 00:03:46.013 And then return cursor.fetchone, 97 00:03:47.170 --> 00:03:48.130 if I can type. 98 00:03:48.130 --> 00:03:49.370 There we go. 99 00:03:49.370 --> 00:03:50.700 Return and cursor.fetchone, again, 100 00:03:50.700 --> 00:03:53.180 gives us that single row. 101 00:03:53.180 --> 00:03:55.610 All right, that's really everything for this video. 102 00:03:55.610 --> 00:03:58.600 As you can see, using the functions can be pretty simple, 103 00:03:58.600 --> 00:03:59.750 but there's a lot of them 104 00:03:59.750 --> 00:04:02.090 and you can use them in all sorts of places. 105 00:04:02.090 --> 00:04:04.770 You should know some of the main functions 106 00:04:04.770 --> 00:04:06.630 that are available in PostgreSQL, 107 00:04:06.630 --> 00:04:07.950 so that's why I've left you the links 108 00:04:07.950 --> 00:04:09.700 to the documentation in there 109 00:04:09.700 --> 00:04:11.000 so you can have a look at that. 110 00:04:11.000 --> 00:04:13.250 But I've also covered some of the main ones 111 00:04:13.250 --> 00:04:15.110 over in the presentation as well. 112 00:04:15.110 --> 00:04:16.050 Thank you guys for watching. 113 00:04:16.050 --> 00:04:17.290 I hope you've enjoyed this video. 114 00:04:17.290 --> 00:04:18.940 And I'll see you in the next one.