Window Function in Postgres
Part I: Functionalities of window function
-> A window function performs a calculation across a set of table rows that are somehow related to the current row. Window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. That makes it difference from aggregate function.
-> Over Clause is required to identify window function. This is what syntactically distinguishes it from a regular function or aggregate function.The OVER clause determines exactly how the rows of the query are split up for processing by the window function.
-> Over Clause contains two list one PARTITION BY and ORDER BY.
PARTITION BY: The PARTITION BY list within OVER specifies dividing the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s).
ORDER BY: The ORDER BY list within OVER specifies ordering of rows.
Note: ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION BY, in which case there is just one partition containing all the rows.
-> The rows considered by a window function are those of the “virtual table” produced by the query’s FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function.
-> A query can contain multiple window function but they use same collection of rows defined by virtual table.
-> Window functions are permitted only in the SELECT list and the ORDER BY clause of the query. They are forbidden elsewhere, such as in GROUP BY, HAVING and WHERE clauses. The reason to this, window function executes after these clauses.
-> There is another important concept associated with window functions: for each row, there is a set of rows within its partition called its window frame. Many (but not all) window functions act only on the rows of the window frame, rather than of the whole partition. By default, if ORDER BY is supplied then the frame consists of all rows from the start of the partition up through the current row, plus any following rows that are equal to the current row according to the ORDER BY clause. When ORDER BY is omitted the default frame consists of all rows in the partition.
-> When a query involves multiple window functions, it is possible to write out each one with a separate OVER clause, but this is duplicative and error-prone if the same windowing behavior is wanted for several functions. Instead, each windowing behavior can be named in a WINDOW clause and then referenced in OVER.
Best Open Source Business Intelligence Software Helical Insight is Here
A Business Intelligence Framework
Thanks and Regards
Fahad Anjum