Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:00,460 --> 00:00:07,000
In the last few lectures, we have discussed rank functions and and title function.
2
00:00:07,810 --> 00:00:12,310
Now let's move on to discuss some aggregate windows function.
3
00:00:14,370 --> 00:00:19,350
So the first function we are going to discuss is average function.
4
00:00:20,100 --> 00:00:26,790
We have already seen average in our aggregate functions when we were using group by.
5
00:00:27,480 --> 00:00:32,790
We can use the average function with windows function as well.
6
00:00:34,080 --> 00:00:36,870
So, for example, we are using the same data.
7
00:00:36,870 --> 00:00:39,900
We have customer store and revenue.
8
00:00:41,040 --> 00:00:48,930
Now suppose in front of each one of these rows, I want the average revenue of that store.
9
00:00:49,230 --> 00:00:58,890
For example, there are three customers from a story that is C one, C five and C seven with revenue
10
00:00:58,920 --> 00:01:01,380
100, 203 hundred.
11
00:01:02,700 --> 00:01:11,280
So the average for a store A is 100 plus 200 plus 300 divided by three.
12
00:01:11,790 --> 00:01:14,160
That comes out to be 200.
13
00:01:14,520 --> 00:01:18,120
So the average of store A is 200.
14
00:01:18,150 --> 00:01:27,510
So I want the average value of 200 to be in front of each one of these customers, seven, C, five
15
00:01:27,510 --> 00:01:28,650
and C, seven.
16
00:01:29,160 --> 00:01:37,980
Similarly, I have on the average value of store B in front of each customer who are purchasing from
17
00:01:38,040 --> 00:01:39,000
store B.
18
00:01:40,370 --> 00:01:42,740
So I want a final table.
19
00:01:42,740 --> 00:01:44,150
Something like this.
20
00:01:44,880 --> 00:01:49,700
So for store A, the revenues are 100, 203 hundred.
21
00:01:49,710 --> 00:01:53,580
So the average revenue should be 200, 202 hundred.
22
00:01:53,940 --> 00:02:01,780
Similarly, for store B customers, the values are 300, 300, 204 hundred.
23
00:02:01,800 --> 00:02:07,660
If you add all this and divide it by four, the average comes out to be 300.
24
00:02:07,680 --> 00:02:13,140
So I want 300 in front of each customer from a store.
25
00:02:13,170 --> 00:02:15,780
B, this is the final table we want.
26
00:02:15,960 --> 00:02:20,370
We can get this table from using average Windows function.
27
00:02:21,570 --> 00:02:23,100
And how can we do that?
28
00:02:23,550 --> 00:02:26,230
The syntax is average.
29
00:02:26,250 --> 00:02:31,380
Then you have to mention the field for which you want the average.
30
00:02:31,980 --> 00:02:39,270
Then again, just like any other window function, we have to use partition by and order by here.
31
00:02:39,270 --> 00:02:43,980
Order by is not required because there is no use of order.
32
00:02:45,030 --> 00:02:51,990
So in partition by you have to select the groups on which you want the average to be calculated.
33
00:02:52,890 --> 00:02:55,950
So again, only two columns are required.
34
00:02:56,250 --> 00:03:02,760
First column is on which you want to calculate the average value and second column which we are going
35
00:03:02,760 --> 00:03:09,390
to use in partition, by which is the group in which you want to calculate the average value.
36
00:03:10,530 --> 00:03:16,830
So now let's calculate the average of revenue of each estate in our database.
37
00:03:26,020 --> 00:03:28,060
Let's look at our table.
38
00:03:29,710 --> 00:03:31,960
Customer underscore order.
39
00:03:33,290 --> 00:03:36,050
That we created in our previous lectures.
40
00:03:36,950 --> 00:03:38,280
So we have customer ID.
41
00:03:38,300 --> 00:03:39,260
Customer name.
42
00:03:40,370 --> 00:03:42,080
State and.
43
00:03:42,780 --> 00:03:45,540
We also have sales total.
44
00:03:46,500 --> 00:03:51,690
So now let's calculate the average sales of each state.
45
00:03:52,580 --> 00:03:54,760
Now, how can we do that?
46
00:03:54,840 --> 00:03:56,510
Feel right, Select.
47
00:03:58,310 --> 00:04:01,430
Let's set customer ID.
48
00:04:08,560 --> 00:04:09,730
Customer name.
49
00:04:14,120 --> 00:04:17,690
Then let's head straight.
50
00:04:21,710 --> 00:04:26,780
That said the total revenue or total sales.
51
00:04:26,780 --> 00:04:27,590
So.
52
00:04:29,490 --> 00:04:30,570
Sales.
53
00:04:32,180 --> 00:04:32,810
Total.
54
00:04:33,740 --> 00:04:35,900
We will name it as revenue.
55
00:04:37,850 --> 00:04:42,910
And then let's calculate the average in each state.
56
00:04:42,920 --> 00:04:44,630
So we'll write average.
57
00:04:45,820 --> 00:04:49,480
We want the average of this sales total.
58
00:04:49,510 --> 00:04:52,540
So we will just write average of total.
59
00:04:54,220 --> 00:04:54,430
Well.
60
00:04:54,460 --> 00:04:55,060
Right?
61
00:04:56,470 --> 00:04:56,950
What?
62
00:04:58,790 --> 00:05:02,960
And the bracket will write partition by.
63
00:05:04,720 --> 00:05:09,720
We want to partition it on the column of state.
64
00:05:09,730 --> 00:05:13,780
So state let's save it as.
65
00:05:15,240 --> 00:05:16,920
Average revenue.
66
00:05:19,530 --> 00:05:21,180
So we are getting.
67
00:05:22,340 --> 00:05:28,820
Five columns, customer ID, customer name, ID, state revenue of that particular customer, and the
68
00:05:28,820 --> 00:05:34,520
average revenue of the whole state in which that customer belongs.
69
00:05:35,480 --> 00:05:36,230
From.
70
00:05:37,630 --> 00:05:40,180
Customer underscore order.
71
00:05:44,520 --> 00:05:45,810
Let's run this.
72
00:05:49,150 --> 00:05:51,730
You can see that we are getting this result.
73
00:05:52,420 --> 00:05:55,270
We have summarised customer name state.
74
00:05:56,420 --> 00:06:02,700
Revenue of each customer, and then we have the average revenue of the entire state.
75
00:06:02,750 --> 00:06:07,250
So for the state of Alabama, the average is this.
76
00:06:09,530 --> 00:06:10,550
Similarly.
77
00:06:13,510 --> 00:06:15,610
For the state of Arizona.
78
00:06:15,700 --> 00:06:17,260
The revenue is this.
79
00:06:18,090 --> 00:06:21,730
So one use case of calculating average can be.
80
00:06:21,750 --> 00:06:28,500
So suppose if you want to create a list of customers whose revenue is less than the average revenue
81
00:06:28,500 --> 00:06:29,620
of their estate.
82
00:06:29,640 --> 00:06:35,590
So suppose if some stores want to target customers who are buying less from their store.
83
00:06:35,610 --> 00:06:42,060
So how can they calculate the customers who are purchasing less than the other customers?
84
00:06:46,070 --> 00:06:48,440
So we are commending it and.
85
00:06:49,200 --> 00:06:52,080
If we want to select, we can just.
86
00:06:54,230 --> 00:06:55,730
Put this in the bracket.
87
00:07:04,730 --> 00:07:06,020
We can select.
88
00:07:09,010 --> 00:07:12,190
Start from this table.
89
00:07:13,660 --> 00:07:14,560
There.
90
00:07:16,720 --> 00:07:17,620
Revenue.
91
00:07:20,240 --> 00:07:21,980
There's less than.
92
00:07:23,640 --> 00:07:24,540
Average.
93
00:07:27,330 --> 00:07:28,170
Revenue.
94
00:07:32,740 --> 00:07:33,220
Right.
95
00:07:33,220 --> 00:07:34,510
Write a dot.
96
00:07:34,960 --> 00:07:37,000
Average revenue is less than.
97
00:07:39,660 --> 00:07:42,990
Aid revenue is less than the average revenue.
98
00:07:43,020 --> 00:07:47,700
Since we are using a as alias for this entire table.
99
00:07:53,500 --> 00:07:55,210
Now if I run this.
100
00:07:57,170 --> 00:08:04,220
You can see that we are only getting the customers who are buying less than the other customers.
101
00:08:05,810 --> 00:08:09,410
So this is the use case of average window function.
102
00:08:11,020 --> 00:08:16,990
In the next lectures we will discuss count and some functions as well.
103
00:08:17,140 --> 00:08:17,830
Thank you.
8197
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.