Fundamental window functions

In previous articles, we have examined how window functions work and introduced the concept of a data window, which is passed to the window function. Now it's time to look at the types of window functions available.

Types of window functions

categories of window functions

Window functions can be divided into 3 groups:

  • Aggregate window functions
  • Ranking window functions
  • Offset window functions

Aggregate window functions

Aggregate functions are those that perform arithmetic calculations on a data set and return a total value.

  • SUM — calculates the total sum of values;
  • COUNT — counts the total number of records in a column (NULL values are not counted);
  • AVG — calculates the arithmetic mean;
  • MAX — finds the highest value;
  • MIN — determines the lowest value.
MySQL
SELECT id,
	home_type,
	price,
	SUM(price) OVER(PARTITION BY home_type) AS 'Sum',
	COUNT(price) OVER(PARTITION BY home_type) AS 'Count',
	AVG(price) OVER(PARTITION BY home_type) AS 'Avg',
	MAX(price) OVER(PARTITION BY home_type) AS 'Max',
	MIN(price) OVER(PARTITION BY home_type) AS 'Min'
FROM Rooms;
idhome_typepriceSumCountAvgMaxMin
2Entire home/apt225312221148.666729980
30Entire home/apt180312221148.666729980
28Entire home/apt150312221148.666729980
38Entire home/apt85312221148.666729980
25Entire home/apt120312221148.666729980
42Entire home/apt120312221148.666729980
21Entire home/apt299312221148.666729980
20Entire home/apt190312221148.666729980
19Entire home/apt99312221148.666729980
17Entire home/apt215312221148.666729980
16Entire home/apt140312221148.666729980
15Entire home/apt120312221148.666729980
46Entire home/apt150312221148.666729980
11Entire home/apt135312221148.666729980
10Entire home/apt150312221148.666729980
48Entire home/apt110312221148.666729980
49Entire home/apt115312221148.666729980
6Entire home/apt200312221148.666729980
45Entire home/apt150312221148.666729980
5Entire home/apt80312221148.666729980
4Entire home/apt89312221148.666729980
41Private room6825042889.428615035
34Private room5025042889.428615035
35Private room7025042889.428615035
50Private room8025042889.428615035
36Private room8925042889.428615035
1Private room14925042889.428615035
37Private room3525042889.428615035
39Private room15025042889.428615035
47Private room13025042889.428615035
43Private room12025042889.428615035
44Private room13525042889.428615035
22Private room13025042889.428615035
3Private room15025042889.428615035
7Private room6025042889.428615035
8Private room7925042889.428615035
9Private room7925042889.428615035
12Private room8525042889.428615035
13Private room8925042889.428615035
14Private room8525042889.428615035
18Private room14025042889.428615035
18Private room14025042889.428615035
33Private room5525042889.428615035
23Private room8025042889.428615035
24Private room11025042889.428615035
26Private room6025042889.428615035
27Private room8025042889.428615035
29Private room4425042889.428615035
31Private room5025042889.428615035
32Private room5225042889.428615035
40Shared room40401404040

Ranking window functions

Ranking window functions are those that rank a value for each row in the window.

In ranking functions, the OVER keyword is followed by the mandatory ORDER BY condition, which determines the sorting order for ranking.

  • ROW_NUMBER - returns the row number, used for numbering;
  • RANK - returns the rank of each row. Here's how it works:
    • Sorting: firstly, rows are sorted by one or more columns. These columns are specified in ORDER BY in the OVER clause.
    • Assigning ranks: each unique row or group of rows that have the same values in the sorting columns is assigned a rank. The rank starts from 1.
    • Identical values: if several rows have the same values in the sorting columns, they receive the same rank. For example, if two rows are in second place, both receive rank 2.
    • Skipping ranks: after a group of rows with the same rank, the next rank increases by the number of rows in that group. For example, if two rows have rank 2, the next row will get rank 4, not 3.
    • Continuing sorting: this process continues until ranks have been assigned to all rows in the result set.
  • DENSE_RANK - returns the rank of each row. Unlike RANK, it returns a rank for identical values without skipping the next rank;
MySQL
SELECT id,
	home_type,
	price,
	ROW_NUMBER() OVER(PARTITION BY home_type ORDER BY price) AS 'row_number',
	RANK() OVER(PARTITION BY home_type ORDER BY price) AS 'rank',
	DENSE_RANK() OVER(PARTITION BY home_type ORDER BY price) AS 'dense_rank'
FROM Rooms;
idhome_typepricerow_numberrankdense_rank
5Entire home/apt80111
38Entire home/apt85222
4Entire home/apt89333
19Entire home/apt99444
48Entire home/apt110555
49Entire home/apt115666
25Entire home/apt120777
15Entire home/apt120877
42Entire home/apt120977
11Entire home/apt13510108
16Entire home/apt14011119
28Entire home/apt150121210
10Entire home/apt150131210
45Entire home/apt150141210
46Entire home/apt150151210
30Entire home/apt180161611
20Entire home/apt190171712
6Entire home/apt200181813
17Entire home/apt215191914
2Entire home/apt225202015
21Entire home/apt299212116
37Private room35111
29Private room44222
34Private room50333
31Private room50433
32Private room52554
33Private room55665
26Private room60776
7Private room60876
41Private room68997
35Private room7010108
8Private room7911119
9Private room7912119
27Private room80131310
23Private room80141310
50Private room80151310
12Private room85161611
14Private room85171611
13Private room89181812
36Private room89191812
24Private room110202013
43Private room120212114
22Private room130222215
47Private room130232215
44Private room135242416
18Private room140252517
1Private room149262618
3Private room150272719
39Private room150282719
40Shared room40111

Offset window functions

Offset window functions are those that allow moving and accessing different rows in the window relative to the current row, as well as accessing values at the beginning or end of the window.

  • LAG - accesses data from previous rows of the window.

    It has three arguments: the column whose value needs to be returned, the number of rows to offset (default is 1), and the value to return if the offset returns a NULL value.

  • LEAD - accesses data from following rows. Similar to LAG, it has 3 arguments.

  • FIRST_VALUE - returns the first value in the window. Takes a column as an argument, the value of which needs to be returned.

  • LAST_VALUE - returns the last value in the window. Takes a column as an argument, the value of which needs to be returned.

MySQL
SELECT id,
	home_type,
	price,
	LAG(price) OVER(PARTITION BY home_type ORDER BY price) AS 'lag',
	LAG(price, 2) OVER(PARTITION BY home_type ORDER BY price) AS 'lag_2',
	LEAD(price) OVER(PARTITION BY home_type ORDER BY price) AS 'lead',
	FIRST_VALUE(price) OVER(PARTITION BY home_type ORDER BY price) AS 'first_value',
	LAST_VALUE(price) OVER(PARTITION BY home_type ORDER BY price) AS 'last_value'
FROM Rooms;
idhome_typepricelaglag_2leadfirst_valuelast_value
5Entire home/apt80<NULL><NULL>858080
38Entire home/apt8580<NULL>898085
4Entire home/apt898580998089
19Entire home/apt9989851108099
48Entire home/apt110998911580110
49Entire home/apt1151109912080115
25Entire home/apt12011511012080120
15Entire home/apt12012011512080120
42Entire home/apt12012012013580120
11Entire home/apt13512012014080135
16Entire home/apt14013512015080140
28Entire home/apt15014013515080150
10Entire home/apt15015014015080150
45Entire home/apt15015015015080150
46Entire home/apt15015015018080150
30Entire home/apt18015015019080180
20Entire home/apt19018015020080190
6Entire home/apt20019018021580200
17Entire home/apt21520019022580215
2Entire home/apt22521520029980225
21Entire home/apt299225215<NULL>80299
37Private room35<NULL><NULL>443535
29Private room4435<NULL>503544
34Private room504435503550
31Private room505044523550
32Private room525050553552
33Private room555250603555
26Private room605552603560
7Private room606055683560
41Private room686060703568
35Private room706860793570
8Private room797068793579
9Private room797970803579
27Private room807979803580
23Private room808079803580
50Private room808080853580
12Private room858080853585
14Private room858580893585
13Private room898585893589
36Private room8989851103589
24Private room110898912035110
43Private room1201108913035120
22Private room13012011013035130
47Private room13013012013535130
44Private room13513013014035135
18Private room14013513014935140
1Private room14914013515035149
3Private room15014914015035150
39Private room150150149<NULL>35150
40Shared room40<NULL><NULL><NULL>4040

We are in touch with you
English