![Page 1: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/1.jpg)
1
FUNCTIONSWINDOW
By Willem Booysen
Version 1.5
![Page 2: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/2.jpg)
2
Willem
About meAccountant turned Accidental DBA
God loving, happily married man with 2 wonderful kids.
I’m an accountant by trade and an Accidental DBA by luck.
Spend most of my Postgres time in SQL scripting.
Let’s get on with it!
![Page 3: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/3.jpg)
3
Create the database and tables we’ll use in this Presentation and getting
to know our data
STEP 2
GETTING TO KNOW OUR DATA
What are Window Functions and why use them?
STEP 1
WHY WINDOW FUNCTIONS?
Content OverviewWhat you can expect in this presentation
![Page 4: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/4.jpg)
4
OVER()
PARTITION BY
STEP 4
BASIC SYNTAX
Because rows should know their place
STEP 5
ROWS AND RANKS
Understand how your base result set, windows and partitions interact.
STEP 3
WINDOWS VS PARTITIONS
![Page 5: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/5.jpg)
5
This has nothing to do with gaming…
STEP 6
LAG and LEAD
It’s not as simple as it sounds…
STEP 7
FIRST & LAST
Rows, Ranges, Unbounded, following and preceding… Your head will hurt
here.
STEP 8
LESS BASIC SYNTAX
![Page 6: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/6.jpg)
6
Things you should be aware of
STEP 10
WATCH OUT!
FINISH
Because I’m an accountant…
STEP 9
RUNNING TOTALS
![Page 7: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/7.jpg)
7
"A WINDOW FUNCTION performs a calculation acrossa set of table rows that are somehow related to thecurrent row. This is comparable to the type of calculationthat can be done with an aggregate function. But unlikeregular aggregate functions, use of a window functiondoes not cause rows to become grouped into a singleoutput row — the rows retain their separate identities.”
—PostgreSQL Manual
What is a Window Function?
![Page 8: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/8.jpg)
8
“What?”
—Me
![Page 9: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/9.jpg)
9
Basically, Window Functions were created to stop people from using Self Joins and generally
reduce the complexity of queries aroundanalytics, aggregate data
and extensive use of cursors.
(Purely my opinion based on my Google searches and reading Stackoverflow comments)
![Page 10: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/10.jpg)
10
I’ll illustrate this soon, but first you need to understand the underlying data used in the coming examples
![Page 11: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/11.jpg)
11O U R D A T A
3 Departments
Duplicate Salaries• Accounting (5)• Production (6)
• IT (7)
Messed up index
![Page 12: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/12.jpg)
12
D E M O
TIME FOR SOME FUN
*
![Page 13: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/13.jpg)
13Demo Recap
VS
Traditional Method Window Functions
![Page 14: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/14.jpg)
14Demo Recap
Window Functions
It all starts with anAggregate Function
![Page 15: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/15.jpg)
15
A visual guide to Windows and PartitionsKnowing WHERE it’s at is half the battle
Open a Window OVER the entire Base Data Set.
A window’s beauty is limited to the landscape beyond – the Base Data Set
OVER ()
Split the Base Data Set into PARTITIONs and
open a Window OVER each of them
OVER (PARTITION BY…)
Our Base Data Set is the result of limiting
expressions, like WHERE
SELECT … WHERE …
All the data within your table, before
any queries against it
YOUR TABLE
03
02
01
04
![Page 16: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/16.jpg)
16
OVER ()
OVER (PARTITION BY…)
Basic Syntax
![Page 17: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/17.jpg)
17
OVER ()
OVER (PARTITION BY…)
![Page 18: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/18.jpg)
18
OVER ()
OVER (PARTITION BY…)
![Page 19: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/19.jpg)
19
OVER ()
OVER (PARTITION BY…)
![Page 20: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/20.jpg)
20
OVER ()
OVER (PARTITION BY… ORDER BY …)
![Page 21: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/21.jpg)
21
OVER ()
OVER (PARTITION BY… ORDER BY …)
You can also control the order in which rows are processed by window functions using the
ORDER BY clause.
The window ORDER BY does not have to match the order in which the rows are output (the order of the Base Data Set)
![Page 22: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/22.jpg)
22
D E M O
TIME FOR SOME FUN
![Page 23: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/23.jpg)
23
RankingIt’s not as simple as first, second and third…
One cannot assign a rank without ORDER
![Page 24: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/24.jpg)
24
Row_Numbers
Allocates row numbers based
on the ORDER BY specified
within the Window.
Rank
Duplicate values are assigned
the same rank, SKIPPING the
next number in line.
Dense_Rank
Duplicate values are assigned
the same rank, no values are
skipped.
RankingIt’s not as simple as first, second and third…
1234
1224
1223
![Page 25: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/25.jpg)
25
D E M O
ORDER IN THE COURT!
![Page 26: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/26.jpg)
26
Advanced Ranking(For Data Scientists and Statisticians)
percent_rank()Relative rank of the current row… (rank -1) / (total rows – 1)
cume_dist()Relative rank of the current row… (no or rows preceding or peer with current row) / (total rows)
ntile(num_buckets integer)Returns integer ranging from 1 to the argument value, dividing the partition as equally as possible
![Page 27: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/27.jpg)
27
D E M O … A G A I N
GOOD LUCK WITH THIS ONE…
![Page 28: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/28.jpg)
28
LAG and LEADOffset from the current row
LEAD/LAG (column, offset, default_value) OVER (…)
Syntax
![Page 29: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/29.jpg)
29
FIRST and LASTOffset relative to beginning/end of the window frame
FIRST_VALUE (column) OVER (…)LAST_VALUE (column) OVER (…)
Syntax
![Page 30: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/30.jpg)
30
Offset relative to beginning/end of the window frameWindow Frames increase with each row inside your partition, from row 1. Think of it as analytics
step by
step,
row by
row
(based on your partition order)
![Page 31: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/31.jpg)
31
D E M O
LAG & LEAD
with some
FIRST & LAST
![Page 32: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/32.jpg)
32
Catchy PhrasesMostly hidden by default
Row Function / Position
1 First_Value / Min
2 …
3 …
4 …
5 Lag
6 Current Row
7 Lead
8 …
9 …
10 …
11 Last_Value / Max
ROWS BETWEEN
UNBOUNDED PRECEDING
AND
UNBOUNDED FOLLOWING
ROWS BETWEEN
2 PRECEDING
AND
3 FOLLOWING
![Page 33: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/33.jpg)
33Unless you have an ORDER BY…Then the default becomes:
Row Function / Position
1 First_Value / Min
2 …
3 …
4 …
5 Lag
6 Current Row
7 Lead
8 …
9 …
10 …
11 Last_Value / Max
RANGE BETWEEN
UNBOUNDED PRECEDING
AND
UNBOUNDED FOLLOWING
RANGE BETWEEN
2 PRECEDING
AND
3 FOLLOWING
![Page 34: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/34.jpg)
34
D E M O
TIME FOR SOME FUN
![Page 35: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/35.jpg)
35
Catchy PhrasesMostly hidden by default
What is the difference between ROWS betweenand RANGE between?
• "ROWS" is over "PARTITION BY“• "RANGE" is over "ORDER BY" (within the Partition of course)
![Page 36: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/36.jpg)
36
Running TotalsBecause I’m an accountant
![Page 37: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/37.jpg)
37
D E M O
Run Mr Totals . Run!
![Page 38: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/38.jpg)
38
W A R N I N G
1. Issues with Distinct()
2. You cannot use Window Functions in your WHERE clauses
3. Window Frames effect functions, e.g. MIN/MAX/FIRST/LAST
![Page 39: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/39.jpg)
39
D E M O
Crash and burn
![Page 40: WINDOW FUNCTIONS - Postgres Conf€¦ · regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row —the rows retain their](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0f215a7e708231d442a38d/html5/thumbnails/40.jpg)
40
T h e E n d