Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:00,960 --> 00:00:06,240
Now, the first window function we are going to discuss is rule number.
2
00:00:07,390 --> 00:00:13,870
As the name suggests, raw numbers gives the raw number in a separate column.
3
00:00:15,010 --> 00:00:23,710
You can also provide this raw number on the basis of groups and on the basis of your given order on
4
00:00:23,710 --> 00:00:25,240
which you want to sort.
5
00:00:26,980 --> 00:00:32,230
No, we will just take this example to understand this function.
6
00:00:33,750 --> 00:00:38,010
Now suppose I have three columns in my data set.
7
00:00:38,780 --> 00:00:46,250
The first column is customer, where I have the different customer IDs for each customer.
8
00:00:47,040 --> 00:00:49,560
The second column is state.
9
00:00:49,980 --> 00:00:54,560
Here I have a state corresponding to each customer.
10
00:00:54,570 --> 00:00:58,350
So as you can see, there are two kinds of state.
11
00:00:58,800 --> 00:01:03,360
And then the third column is orders.
12
00:01:03,450 --> 00:01:10,920
Here we have the total number of orders that each customer has placed in a given amount of time.
13
00:01:12,130 --> 00:01:20,950
Now, suppose in each state I want to give rule number to the customers according to their number of
14
00:01:20,950 --> 00:01:21,610
orders.
15
00:01:23,550 --> 00:01:33,780
For example, for State A I have one to give rule number one to the customer with maximum number of
16
00:01:33,780 --> 00:01:34,410
orders.
17
00:01:34,500 --> 00:01:38,850
So I want value as one in front of my customer.
18
00:01:38,880 --> 00:01:45,600
See five Since he or she has placed maximum number of orders in a state a.
19
00:01:47,350 --> 00:01:52,780
Seven has placed three orders and SI five has placed six orders.
20
00:01:53,680 --> 00:02:02,980
So I want to get one in front of see five two in front of C one and then three in front of C seven.
21
00:02:04,750 --> 00:02:13,900
Similarly, in State B, I have four customers with order five, four, two and four.
22
00:02:14,770 --> 00:02:25,540
Since C two has placed maximum number of orders in state B, I want to give c2a value of one, then
23
00:02:25,540 --> 00:02:32,680
C three and C six has placed four orders and then C four has placed two orders.
24
00:02:32,680 --> 00:02:38,800
So I want my ordering in this order inside the group of states.
25
00:02:40,180 --> 00:02:43,360
So this is the final table I have want.
26
00:02:45,820 --> 00:02:49,210
We can use rule number to get this column.
27
00:02:50,700 --> 00:02:51,960
Now here.
28
00:02:52,860 --> 00:02:58,830
This is the syntax of row number, which is the keyword is row number.
29
00:02:58,860 --> 00:03:06,300
Then we will write over and then partition by and order by and partition by.
30
00:03:06,330 --> 00:03:12,810
We have to mention the column on which we want to partition our data or group our data.
31
00:03:12,870 --> 00:03:20,160
For example, here we want our customer to have row numbers within a group of states.
32
00:03:20,220 --> 00:03:21,090
So.
33
00:03:22,290 --> 00:03:25,530
So I will write partition by store.
34
00:03:26,410 --> 00:03:28,060
And on what basis.
35
00:03:28,060 --> 00:03:30,280
We want to provide these raw numbers.
36
00:03:30,280 --> 00:03:35,360
We want to provide this raw numbers on the basis of their number of orders.
37
00:03:35,410 --> 00:03:39,220
So in order by I will write orders.
38
00:03:39,910 --> 00:03:45,520
And since we want this in descending order, we'll read the SC.
39
00:03:46,800 --> 00:03:51,150
So let's look at the output we get after running this code.
40
00:03:51,270 --> 00:03:53,580
So this is the output we will get.
41
00:03:54,370 --> 00:04:00,610
C five with maximum number of orders in state, one will get rule number as one.
42
00:04:01,120 --> 00:04:08,860
Then the second highest number of orders in state A is CE one with three orders.
43
00:04:08,860 --> 00:04:12,070
So C one should be second.
44
00:04:12,250 --> 00:04:13,450
Similarly.
45
00:04:14,380 --> 00:04:18,430
C7 seven has the lowest number of orders, so.
46
00:04:19,250 --> 00:04:22,250
The third row should be off C seven.
47
00:04:24,110 --> 00:04:24,970
Similarly.
48
00:04:24,980 --> 00:04:28,250
Now let's move to a state B.
49
00:04:28,490 --> 00:04:32,090
Here we are using partition by state.
50
00:04:32,870 --> 00:04:41,540
The row numbering will start with a state B, so the maximum number of orders in the state B is of C
51
00:04:41,540 --> 00:04:42,020
two.
52
00:04:42,050 --> 00:04:52,430
So we will get C two as one, then C three and C six have same number of orders, but since C three
53
00:04:52,460 --> 00:04:54,110
is appearing first.
54
00:04:54,850 --> 00:04:58,420
So C three should get second row number.
55
00:04:59,020 --> 00:05:06,850
Then comes C six with four orders, and at last we'll get C four with just two orders and row number
56
00:05:06,850 --> 00:05:07,570
of four.
57
00:05:09,100 --> 00:05:17,080
So if you look at the definition, it will also suggest the same thing unique number for each row within
58
00:05:17,080 --> 00:05:21,060
the partition, with different values for tied values.
59
00:05:21,070 --> 00:05:27,430
So here we had a tie, but we get unique values in the row number.
60
00:05:29,550 --> 00:05:32,940
So that's our first window function.
61
00:05:33,420 --> 00:05:37,080
Now let's use it in the data that we are using.
62
00:05:37,760 --> 00:05:46,760
So suppose we want to create a list of top three customers with maximum number of orders from each state?
63
00:05:47,680 --> 00:05:50,650
So we have customer table, we have sales table.
64
00:05:50,680 --> 00:05:57,850
Now we want to create a list of top three customers with maximum orders from each state like this.
65
00:05:58,480 --> 00:06:02,860
In the next video, we'll see how to do that.
66
00:06:03,190 --> 00:06:03,910
Thank you.
6192
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.