More on Window Functions with PostgreSQL

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.

Check out this problem - Smallest Distinct Window .

Frequently Asked Questions

Does Postgres support window functions?

Yes, like SQL, you can use window functions in PostgreSQL.

Can we use window functions in every query?

The SELECT and ORDER BY clause of the query are the only places where window functions are allowed.

What is the difference between GROUP BY and window functions?

The GROUP BY only allows aggregate functions, whereas you can use aggregate, ranking, and value functions in windows functions.

Conclusion

In this article, we have discussed the details of the Window Functions with PostgreSQL.

We hope that the blog has helped you enhance your knowledge regarding Window Functions with PostgreSQL. You can refer to our guided paths on the Coding Ninjas Studio platform to learn more about DSA, DBMS, Competitive Programming, Python, Java, JavaScript, etc. To practice and improve yourself in the interview, you can also check out Top 100 SQL problems, Interview experience, Coding interview questions, and the Ultimate guide path for interviews. Do upvote our blogs to help other ninjas grow. Happy Coding!!