1
00:00:00,120 --> 00:00:05,760
So next up, we're going to look at some of the window functions that are solely window functions,

2
00:00:06,090 --> 00:00:09,560
unlike things like sum and average min max.

3
00:00:09,570 --> 00:00:10,740
Those are aggregate functions.

4
00:00:10,740 --> 00:00:13,560
You can use them with groups, you can use them as a window function.

5
00:00:13,560 --> 00:00:19,380
The functions I'm going to show you in this video are uniquely window functions, so they include things

6
00:00:19,380 --> 00:00:20,640
like rank.

7
00:00:20,640 --> 00:00:26,700
Let's start with that one Rank is going to return the rank of the current row within its partition.

8
00:00:26,910 --> 00:00:29,370
So let's take a look at how this works.

9
00:00:29,850 --> 00:00:34,500
I will start with one of my previous queries and just duplicate that.

10
00:00:34,530 --> 00:00:36,780
We still want employee number department salary.

11
00:00:36,780 --> 00:00:43,590
And my goal here is to start by getting the overall rank for every employee based on their salary.

12
00:00:43,590 --> 00:00:45,660
Who is the first salary?

13
00:00:45,660 --> 00:00:50,010
Highest salary, second, highest, third, highest, and so on next to each row.

14
00:00:50,220 --> 00:00:53,490
And so I would use rank to do that rank.

15
00:00:53,490 --> 00:01:04,769
And then over we'll call this as overall salary rank and then instead of over, my SQL has no idea what

16
00:01:04,769 --> 00:01:06,060
I'm trying to rank here.

17
00:01:06,360 --> 00:01:08,100
It's just it doesn't know.

18
00:01:08,100 --> 00:01:16,950
I need to specify that I'm going to order by salary and I'll do descending and you'll see what that

19
00:01:16,950 --> 00:01:17,460
does.

20
00:01:18,150 --> 00:01:20,010
So why don't we just run it and see what happens?

21
00:01:21,240 --> 00:01:22,050
Here we are.

22
00:01:22,050 --> 00:01:29,520
We see employee number, department salary and then their overall salary rank one here is for this salary

23
00:01:29,520 --> 00:01:31,080
to this salary.

24
00:01:31,080 --> 00:01:31,740
And that makes sense.

25
00:01:31,740 --> 00:01:32,910
This is the highest salary.

26
00:01:32,910 --> 00:01:35,190
We have 159,000.

27
00:01:35,190 --> 00:01:36,270
This is the lowest salary.

28
00:01:36,270 --> 00:01:38,130
We have 31,000.

29
00:01:38,580 --> 00:01:39,990
A couple of things to note.

30
00:01:39,990 --> 00:01:46,920
First of all, rank is not the same as row number, which is actually another one of these functions

31
00:01:46,920 --> 00:01:48,270
that we have access to.

32
00:01:48,840 --> 00:01:52,170
Row number will just give us a consecutive number of rows.

33
00:01:52,170 --> 00:01:52,330
Right?

34
00:01:52,350 --> 00:01:54,360
This is the fourth row, fifth row, sixth row.

35
00:01:54,360 --> 00:01:57,450
But take a look at what happens here When we get to the seventh row.

36
00:01:57,450 --> 00:02:05,730
We have a tie, 70,070 thousand when we use rank, Any values that are the same within a window are

37
00:02:05,730 --> 00:02:07,080
considered the same rank.

38
00:02:07,080 --> 00:02:09,750
And so then they skip a number following that.

39
00:02:09,750 --> 00:02:11,820
So we have two sevens and then nine.

40
00:02:11,820 --> 00:02:18,570
So it's not the same as just counting the rows if you have duplicate data or ties in your information.

41
00:02:18,810 --> 00:02:23,010
So what we did right here is just calculate the rank for one massive window.

42
00:02:23,010 --> 00:02:28,320
We didn't partition it so all the rows order them by salary and rank by salary.

43
00:02:28,650 --> 00:02:36,900
But what we can also do is put a partition in there, so I'll say rank over and then partition by department.

44
00:02:36,900 --> 00:02:41,580
In this time I'll also order by salary because that's what I care about.

45
00:02:42,390 --> 00:02:44,280
Order by salary.

46
00:02:46,320 --> 00:02:49,170
As department salary rank.

47
00:02:50,950 --> 00:02:56,770
So what this will do is form up individual windows partitioned by department.

48
00:02:56,770 --> 00:02:58,480
So there's, I think, three departments.

49
00:02:58,480 --> 00:02:59,020
Right.

50
00:02:59,470 --> 00:03:04,930
And then within each of those, it's going to order them by salary and calculate the rank for each row.

51
00:03:06,420 --> 00:03:07,710
And this is what we get.

52
00:03:08,070 --> 00:03:08,850
Let's look.

53
00:03:09,000 --> 00:03:13,590
First of all, we have this person here in sales.

54
00:03:13,740 --> 00:03:14,820
Oh, you know what?

55
00:03:14,820 --> 00:03:16,680
I'm sorting the other way around.

56
00:03:16,710 --> 00:03:17,670
It still works.

57
00:03:17,670 --> 00:03:24,210
But you'll see that the highest paid person, hundred and 59,000 within sales is ranked seventh.

58
00:03:24,300 --> 00:03:26,850
But that's because we are in us sending orders.

59
00:03:26,850 --> 00:03:34,290
So let's also order by descending if we want the number one rank to be the highest paid, we could have

60
00:03:34,290 --> 00:03:35,490
number one, be the lowest paid.

61
00:03:35,490 --> 00:03:39,780
But I think most of the time you would want the first ranking to be the highest paid.

62
00:03:40,110 --> 00:03:42,830
Okay, so here we are within their department.

63
00:03:42,840 --> 00:03:48,870
This person here is ranked one within sales and also overall ranked one.

64
00:03:49,170 --> 00:03:54,750
This employee here in engineering 103,000 is first in their department.

65
00:03:54,750 --> 00:03:56,040
Second overall.

66
00:03:56,730 --> 00:03:59,130
This person and engineering 91,000.

67
00:03:59,700 --> 00:04:04,680
Their department rank is too, and their overall salary rank is three.

68
00:04:05,350 --> 00:04:11,220
So another thing I should mention here is the order that we get back is something we could change.

69
00:04:11,230 --> 00:04:13,810
We have more than one place we can order by.

70
00:04:13,810 --> 00:04:18,970
We can order within each window, which is very different from the overall order at the end.

71
00:04:18,970 --> 00:04:26,230
So if I wanted to order by employee number, I could do that order by department or order by the overall

72
00:04:26,230 --> 00:04:29,080
salary rank, which is what's kind of happening right now.

73
00:04:29,140 --> 00:04:33,190
But let's do an order by department, order by department.

74
00:04:33,190 --> 00:04:36,790
This will just chunk together people from the same department.

75
00:04:36,880 --> 00:04:42,670
As you can see here, all of our customer service people, now that department is up top and then all

76
00:04:42,670 --> 00:04:45,040
the engineering people, all the sales people.

77
00:04:45,220 --> 00:04:51,010
And I'm not suggesting this is better necessarily, but now we can see this person is first within customer

78
00:04:51,010 --> 00:04:54,520
service at 61,000, but they're 11th overall.

79
00:04:54,740 --> 00:04:59,620
This person is seventh within customer service in 21st overall.

80
00:05:00,070 --> 00:05:02,470
So just an example of using rank.

81
00:05:02,590 --> 00:05:05,890
This is a function that is only going to work with over.

82
00:05:05,920 --> 00:05:08,110
It is only a window function.

83
00:05:08,110 --> 00:05:13,480
We can't use it with group B and you don't have to partition just like these other functions we've seen.

84
00:05:13,480 --> 00:05:19,300
If you don't partition it, we get one window with all of the rows and this will calculate in our case

85
00:05:19,300 --> 00:05:24,010
based on salary the ranking for every row relative to all the other rows.

86
00:05:24,460 --> 00:05:30,280
Whereas here we created different windows by partitioning by department, and then within each of those

87
00:05:30,280 --> 00:05:33,820
small windows we order by salary and get the ranking.

88
00:05:33,820 --> 00:05:36,790
And remember, ranking is not always consecutive.

89
00:05:37,120 --> 00:05:40,120
We can have ties, in which case it will skip digits.

90
00:05:40,120 --> 00:05:45,160
It's going to show us who's the third highest salary, but there might not be a fourth highest.

91
00:05:45,160 --> 00:05:48,070
If there's people tied for third, it jumps to fifth.

92
00:05:48,070 --> 00:05:50,260
I think that's just confusing to say out loud, though.

93
00:05:50,260 --> 00:05:51,220
So that's rank.

94
00:05:51,220 --> 00:05:53,620
We'll take a look at some more examples in the next video.