1
00:00:00,090 --> 00:00:03,380
The next of these window functions will look at is nn tile.

2
00:00:03,390 --> 00:00:06,360
So nn tile is a function we could use.

3
00:00:06,390 --> 00:00:11,550
Let's say we wanted to divide up salaries into quartiles and figure out which quartile each salary fit

4
00:00:11,550 --> 00:00:15,780
into or which decimal tile.

5
00:00:16,140 --> 00:00:18,150
Which percentage.

6
00:00:18,150 --> 00:00:21,660
If we divided it into 100, we could do it as a percentage basis.

7
00:00:22,350 --> 00:00:29,160
So the concept is we provide a number RN, and that's how many buckets that are values will be sorted

8
00:00:29,160 --> 00:00:29,730
into.

9
00:00:30,090 --> 00:00:35,700
And so within each partition, let's say we did four, we would have four buckets for salaries and then

10
00:00:35,700 --> 00:00:43,500
it will return to us a number ranging from one up to four up to RN to say which quartile that we a given

11
00:00:43,500 --> 00:00:44,460
value falls into.

12
00:00:44,490 --> 00:00:45,510
So let me just show it.

13
00:00:45,810 --> 00:00:50,190
Let's work on a new query that is a little less cluttered.

14
00:00:50,190 --> 00:00:51,470
So I'm going to get rid of all this.

15
00:00:51,480 --> 00:00:57,360
I'll keep employee number department in salary, but then what I'm going to do here is RN tile.

16
00:00:57,960 --> 00:00:59,190
Let's do quartile.

17
00:00:59,190 --> 00:00:59,790
Sure.

18
00:01:00,330 --> 00:01:01,110
And tile.

19
00:01:01,110 --> 00:01:07,410
And then we have to say over what are we going to do, the entire company or the entire table of employees?

20
00:01:07,410 --> 00:01:08,340
Let's start there.

21
00:01:08,610 --> 00:01:20,010
So we'll just say over and we'll order by salary descending and we'll call this one salary quartile.

22
00:01:20,700 --> 00:01:23,870
Let's just call it caught quartile OC.

23
00:01:24,000 --> 00:01:25,470
Let's run this.

24
00:01:28,010 --> 00:01:29,530
And overall rank.

25
00:01:29,540 --> 00:01:30,500
What is he complaining about?

26
00:01:30,500 --> 00:01:32,210
Oh, duh, I'm ordering by there.

27
00:01:32,210 --> 00:01:35,810
I can't do that because I got rid of that column I was ordering by.

28
00:01:36,170 --> 00:01:36,950
Try it again.

29
00:01:38,570 --> 00:01:44,990
So you'll see on the right side we have salary quartile that shows us if we're in the first quartile,

30
00:01:44,990 --> 00:01:48,560
second, third or fourth for each row for that salary.

31
00:01:48,560 --> 00:01:52,490
So down 31,000 up to 55,000.

32
00:01:52,790 --> 00:01:54,290
That's the fourth quartile.

33
00:01:54,320 --> 00:01:59,780
The first quartile in our example is 159,070 2000.

34
00:01:59,780 --> 00:02:01,970
That's not saying the quartile stops here.

35
00:02:01,970 --> 00:02:08,180
It's just with the rows that we currently have, that is the lowest number that is considered in that

36
00:02:08,180 --> 00:02:09,229
first quartile.

37
00:02:09,229 --> 00:02:13,040
So we could also do an example where we partition.

38
00:02:13,040 --> 00:02:17,090
So this is for the entire company overall or the entire table.

39
00:02:17,090 --> 00:02:20,600
At least here we'll do a partition by department.

40
00:02:22,080 --> 00:02:26,940
And then we'll order by salary descending within the department and we'll call this department salary

41
00:02:26,940 --> 00:02:27,720
quartile.

42
00:02:28,530 --> 00:02:31,560
And I need a comma and I'm using quartile.

43
00:02:31,560 --> 00:02:36,000
It's a common example we don't have to do for you could do whatever number of buckets you want.

44
00:02:36,810 --> 00:02:44,100
And here we are within each department now we see that it's a little hard because it's not grouped by

45
00:02:44,100 --> 00:02:45,480
or it's not ordered by department.

46
00:02:45,480 --> 00:02:53,070
But if we look at this person, they're in sales 159,000, their first overall and also quartile one

47
00:02:53,070 --> 00:02:57,240
overall and quartile one in their sales department.

48
00:02:57,240 --> 00:03:00,480
But let's look at this person right here.

49
00:03:00,630 --> 00:03:07,170
This person is in the second quartile overall for salary making 67,000.

50
00:03:07,740 --> 00:03:11,790
But within engineering, they're one of the lower paid engineers on the team.

51
00:03:12,060 --> 00:03:15,000
They are in the fourth bucket, the fourth quartile.

52
00:03:15,000 --> 00:03:18,840
So that's entail, of course, you could break it up two different buckets.

53
00:03:19,260 --> 00:03:24,060
You could work with different pieces of data and partition them using another column.

54
00:03:24,060 --> 00:03:26,970
We just don't have much with our department salary employee number.

55
00:03:26,970 --> 00:03:28,020
That's kind of all we got.

56
00:03:28,020 --> 00:03:30,390
But it still illustrates how this works.

57
00:03:30,390 --> 00:03:35,310
We break up a window into different buckets and then for each value, you tell me which of those buckets

58
00:03:35,310 --> 00:03:36,030
we landed.