PostgreSQL Advanced Features : Window Functions

A window function performs a calculation across a set of table rows that are somehow related to the
current row. This is comparable to the type of calculation that can be done with an aggregate function.
But unlike regular aggregate functions, use of a window function does not cause rows to become
grouped into a single output row — the rows retain their separate identities. Behind the scenes, the
window function is able to access more than just the current row of the query result.
Here is an example that shows how to compare each employee’s salary with the average salary in his
or her department:

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

depname | empno | salary | avg
-----------+-------+--------+-----------------------
develop | 11 | 5200 | 5020.0000000000000000
develop | 7 | 4200 | 5020.0000000000000000
develop | 9 | 4500 | 5020.0000000000000000
develop | 8 | 6000 | 5020.0000000000000000
develop | 10 | 5200 | 5020.0000000000000000
personnel | 5 | 3500 | 3700.0000000000000000
personnel | 2 | 3900 | 3700.0000000000000000
sales | 3 | 4800 | 4866.6666666666666667
sales | 1 | 5000 | 4866.6666666666666667
sales | 4 | 4800 | 4866.6666666666666667
(10 rows)

The first three output columns come directly from the table empsalary, and there is one output row
for each row in the table. The fourth column represents an average taken across all the table rows that
have the same depname value as the current row. (This actually is the same function as the regular avg
aggregate function, but the OVER clause causes it to be treated as a window function and computed
across an appropriate set of rows.)
A window function call always contains an OVER clause directly following the window function’s
name and argument(s). 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. 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). For each row, the window
function is computed across the rows that fall into the same partition as the current row.
You can also control the order in which rows are processed by window functions using ORDER BY
within OVER. (The window ORDER BY does not even have to match the order in which the rows are
output.) Here is an example:

SELECT depname, empno, salary,
rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;

depname | empno | salary | rank
-----------+-------+--------+------
develop | 8 | 6000 | 1
develop | 10 | 5200 | 2
develop | 11 | 5200 | 2
develop | 9 | 4500 | 4
develop | 7 | 4200 | 5
personnel | 2 | 3900 | 1
personnel | 5 | 3500 | 2
sales | 1 | 5000 | 1
sales | 4 | 4800 | 2
sales | 3 | 4800 | 2
(10 rows)

As shown here, the rank function produces a numerical rank within the current row’s partition for
each distinct ORDER BY value, in the order defined by the ORDER BY clause. rank needs no explicit
parameter, because its behavior is entirely determined by the OVER clause.
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 functions that slice up the data in different ways by means of different
OVER clauses, but they all act on the same collection of rows defined by this virtual table.
We already saw that 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.
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.
Here is an example using sum:

SELECT salary, sum(salary) OVER () FROM empsalary;

salary | sum
--------+-------
5200 | 47100
5000 | 47100
3500 | 47100
4800 | 47100
3900 | 47100
4200 | 47100
4500 | 47100
4800 | 47100
6000 | 47100
5200 | 47100
(10 rows)

Above, since there is no ORDER BY in the OVER clause, the window frame is the same as the partition,
which for lack of PARTITION BY is the whole table; in other words each sum is taken over the whole
table and so we get the same result for each output row. But if we add an ORDER BY clause, we get
very different results:

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;

salary | sum
--------+-------
3500 | 3500
3900 | 7400
4200 | 11600
4500 | 16100
4800 | 25700
4800 | 25700
5000 | 30700
5200 | 41100
5200 | 41100
6000 | 47100
(10 rows)

Here the sum is taken from the first (lowest) salary up through the current one, including any duplicates
of the current one (notice the results for the duplicated salaries).
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. This is because they
logically execute after the processing of those clauses. Also, window functions execute after regular
aggregate functions. This means it is valid to include an aggregate function call in the arguments of a
window function, but not vice versa.
If there is a need to filter or group rows after the window calculations are performed, you can use a
sub-select. For example:

SELECT depname, empno, salary, enroll_date
FROM
(SELECT depname, empno, salary, enroll_date,
rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
FROM empsalary
) AS ss
WHERE pos < 3;

The above query only shows the rows from the inner query having rank less than 3.
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. For example:

SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

PostgreSQL Advanced Features : Transactions

We will now discuss some more advanced features of SQL that simplify management and prevent loss or corruption of your data.
Transactions are a fundamental concept of all database systems. The essential point of a transaction is
that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between
the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the
transaction from completing, then none of the steps affect the database at all.
For example, consider a bank database that contains balances for various customer accounts, as well
as total deposit balances for branches. Suppose that we want to record a payment of $100.00 from
Arthur’s account to Bob’s account. Simplifying outrageously, the SQL commands for this might look
like:

UPDATE accounts SET balance = balance - 100.00
WHERE name = ’Arthur’;
UPDATE branches SET balance = balance - 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = ’Arthur’);
UPDATE accounts SET balance = balance + 100.00
WHERE name = ’Bob’;
UPDATE branches SET balance = balance + 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = ’Bob’);

The details of these commands are not important here; the important point is that there are several
separate updates involved to accomplish this rather simple operation. Our bank’s officers will want to
be assured that either all these updates happen, or none of them happen. It would certainly not do for
a system failure to result in Bob receiving $100.00 that was not debited from Arthur. Nor would Arthur
long remain a happy customer if she was debited without Bob being credited. We need a guarantee
that if something goes wrong partway through the operation, none of the steps executed so far will
take effect. Grouping the updates into a transaction gives us this guarantee. A transaction is said to
be atomic: from the point of view of other transactions, it either happens completely or not at all.
We also want a guarantee that once a transaction is completed and acknowledged by the database
system, it has indeed been permanently recorded and won’t be lost even if a crash ensues shortly
thereafter. For example, if we are recording a cash withdrawal by Bob, we do not want any chance that
the debit to his account will disappear in a crash just after he walks out the bank door. A transactional
database guarantees that all the updates made by a transaction are logged in permanent storage (i.e.,
on disk) before the transaction is reported complete.
Another important property of transactional databases is closely related to the notion of atomic updates:
when multiple transactions are running concurrently, each one should not be able to see the
incomplete changes made by others. For example, if one transaction is busy totalling all the branch
balances, it would not do for it to include the debit from Arthur’s branch but not the credit to Bob’s
branch, nor vice versa. So transactions must be all-or-nothing not only in terms of their permanent
effect on the database, but also in terms of their visibility as they happen. The updates made so far by
an open transaction are invisible to other transactions until the transaction completes, whereupon all
the updates become visible simultaneously.
In PostgreSQL, a transaction is set up by surrounding the SQL commands of the transaction with
BEGIN and COMMIT commands. So our banking transaction would actually look like:

BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = ’Arthur’;
-- etc etc
COMMIT;

If, partway through the transaction, we decide we do not want to commit (perhaps we just noticed that
Arthur’s balance went negative), we can issue the command ROLLBACK instead of COMMIT, and all our
updates so far will be canceled.
PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not
issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful)
COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes
called a transaction block.
Note: Some client libraries issue BEGIN and COMMIT commands automatically, so that you might
get the effect of transaction blocks without asking. Check the documentation for the interface you
are using.
It’s possible to control the statements in a transaction in a more granular fashion through the use of
savepoints. Savepoints allow you to selectively discard parts of the transaction, while committing the
rest. After defining a savepoint with SAVEPOINT, you can if needed roll back to the savepoint with
ROLLBACK TO. All the transaction’s database changes between defining the savepoint and rolling
back to it are discarded, but changes earlier than the savepoint are kept.
After rolling back to a savepoint, it continues to be defined, so you can roll back to it several times.
Conversely, if you are sure you won’t need to roll back to a particular savepoint again, it can be
released, so the system can free some resources. Keep in mind that either releasing or rolling back to
a savepoint will automatically release all savepoints that were defined after it.
All this is happening within the transaction block, so none of it is visible to other database sessions.
When and if you commit the transaction block, the committed actions become visible as a unit to
other sessions, while the rolled-back actions never become visible at all.
Remembering the bank database, suppose we debit $100.00 from Arthur’s account, and credit Bob’s
account, only to find later that we should have credited Wally’s account. We could do it using savepoints
like this:

BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = ’Arthur’;
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = ’Bob’;
-- oops ... forget that and use Wally’s account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = ’Wally’;
COMMIT;

This example is, of course, oversimplified, but there’s a lot of control possible in a transaction block
through the use of savepoints. Moreover, ROLLBACK TO is the only way to regain control of a transaction
block that was put in aborted state by the system due to an error, short of rolling it back completely
and starting again.