Do you think IIT Guwahati certified course can help you in your career?
No
Introduction
Hi Ninjas!! Welcome to another blog on Window Functions. Today we will learn more about Window Functions with PostgreSQL. PostgreSQL is a powerful, open-source object-relational database management system (ORDBMS). It is used to store data securely.
What are Window Functions?
Window functions allow users to run calculations against subgroups or parts of a result set. A window function runs a calculation across a group of relevant table rows to the current row. This is calculated similarly to an aggregate function. The rows are distinct from one another. The window method has access to more rows of the query result than simply the current row.
Window Functions in Postgresql
Window Functions return a single aggregated value for each row. The SELECT and ORDER BY clause of the query are the only places where window functions are allowed. The GROUP BY, HAVING, and WHERE clauses restrict them.
We had a table Student containing the following data.
Let's execute queries using Window Functions in Postgresql.
OVER clause and PARTITION BY clause
The OVER clause determines the exact division of the query's data into subsets. The PARTITION BY clause separates the rows into partitions that have the same values as the PARTITION BY expression(s). The window function is calculated over all rows that are a part of the same partition.
Command:
Select Roll, Name, Department, AVG(Marks) over (PARTITION BY Department) from student;
Output:
The above query divides the table Department wise and computes the average marks for each department.
WINDOW clause
If your query requires multiple window functions, you can separate them using different over clauses. In a WINDOW clause, each behavior can be identified before being referred to in an OVER clause.
Let's have a look at the example.
Command:
Select Roll, Name, Sum(Marks) over (PARTITION BY Department) , AVG(Marks) over (PARTITION BY Department) from student;
Output:
The above query divides the table Department wise and computes the sum of the Marks and average marks.
You can use the window clause to execute the query.
Command:
Select Roll, Name, Sum(Marks) over w, AVG(Marks) over w from student WINDOW w AS (PARTITION BY Department);
Output:
The above query uses the window clause to create aliases for multiple window functions. It divides the table Department wise and computes the sum of the Marks and average marks.