Advanced Features of PostgreSQL (Part 02)

Window Functions

A window function does a computation over a group of table rows that are connected to the current row in some way. This is akin to the kind of calculation that an aggregate function may perform. However, unlike non-window aggregate calls, window functions do not lead to the grouping of rows into a single output row. Instead, the rows continue to be distinct from one another. The window method has access to more rows of the query result than simply the current row in the background.
The OVER clause always follows the window function's name and any arguments in a window function call. This is what sets it apart from a typical function or non-window aggregate syntactically. The window function's exact division of the query's data into subsets for processing depends on the OVER clause. In the OVER clause, the PARTITION BY clause separates the rows into partitions based on the values of the PARTITION BY expression(s). The window function is calculated across the rows that are a part of the same partition as the current row for each row.
You can also control the order in which rows are processed by window functions using ORDER BY within
OVER.

The idea of window functions also includes the idea that each row has a group of rows within its partition known as its window frame. Some window functions don't affect the entire partition; instead, they simply affect the rows of the window frame. If ORDER BY is not specified, the frame will by default include all rows from the beginning of the partition to the current row, as well as any subsequent rows that are identical to the current row in accordance with the ORDER BY clause. The default frame, when ORDER BY is absent, contains every row in the partition.

Only the SELECT list and the ORDER BY clause of the query are allowed to employ window functions. Other places, like the GROUP BY, HAVING, and WHERE clauses, restrict them. This is due to the fact that they logically follow the processing of those phrases. In addition, aggregate functions that are not window functions run first. This means that a window function call may include an aggregate function call, but not the other way around.

Inheritance:

A notion from object-oriented databases is inheritance. It provides intriguing new opportunities for database design.
Example for Inheritance:

CREATE TABLE cities (
24
Advanced Features
 name text,
 population real,
 elevation int -- (in ft)
);

CREATE TABLE capitals (
 state char(2) UNIQUE NOT NULL
) INHERITS (cities);

In this example, a column of capitals inherits its parent cities' name, population, and elevation columns. The column name's type, text, is a built-in PostgreSQL type for character strings of varying length. The state column in the capitals table also displays the state's abbreviation. A table in PostgreSQL can derive from zero or more other tables.