In this module, you will look at a few different functions, including the over partition function and lag and lead, to see how to use the window…
In this module, you will look at a few different functions, including the over partition function and lag and lead, to see how to use the window function in PostgreSQL. The window function performs a calculation across a set of table rows that are somehow related to the current row. This is the final module in this advanced SQL course and will give you an improved understanding of how to create complete databases in PostgreSQL, as well as how to add, access, and process data stored in a computer database.
Learning Objectives:
- Purpose and necessity of window functions
- Range in PostgreSQL
Skills you’ll gain
Mysql++MySQLPostgreSQLStored FunctionsTable FunctionsWindow FunctionWhat You'll Learn
- Understand the purpose and necessity of window functions in PostgreSQL
- Use the OVER PARTITION clause to perform calculations across related sets of table rows
- Apply the LAG and LEAD window functions to reference data in other rows
- Work with the FIRST VALUE and RANK functions in PostgreSQL
- Explore the use of range in PostgreSQL window functions
- Create complete databases and add, access, and process data stored in PostgreSQL
Key Takeaways
- A window function performs a calculation across a set of table rows that are somehow related to the current row.
- This module covers several functions, including the OVER PARTITION function as well as LAG and LEAD.
- This is the final module in the advanced SQL course and reinforces how to create complete databases in PostgreSQL.
- The course covers how to add, access, and process data stored in a computer database.
- Learning objectives focus on the purpose and necessity of window functions and range in PostgreSQL.
Frequently Asked Questions
What does this module cover?
It covers several functions, including the OVER PARTITION function and LAG and LEAD, to show how to use the window function in PostgreSQL. Lessons include Windows Function, Over Partition, First Value, Rank Function, and Lag & Lead.
What is a window function?
A window function performs a calculation across a set of table rows that are somehow related to the current row.
Where does this module fit in the course?
This is the final module in the advanced SQL course, and it gives an improved understanding of how to create complete databases in PostgreSQL, as well as how to add, access, and process data stored in a computer database.
What skills does this course build?
The course builds skills in MySQL, Mysql++, PostgreSQL, stored functions, table functions, and window functions.
Transcript
Show transcript (free preview lesson)
Transcript of the free preview lesson. Remaining lessons unlock with the full course.
In this lesson of advanced SQL I will provide an overview of the window function. A window function performs a calculation across a set of table rows that are somehow related to the current row. It's similar to our aggregate functions, but unlike regular aggregate functions, the use of a window functions does not cause rows to become grouped into a single output row. They retain their separate identities. So behind the scenes, you're able to access more than just the current row of the query result. We will look at a few different functions including the underscore Partition, the first Over underscore value, rank, lag, and lead to see how to use the window function and PostgreSQL. In the next lesson I will show you how to use the Over by Partition.
Learn on the Go
Take your learning anywhere — the KnowledgeCity mobile app lets you watch lessons on the go.