Analytical functies

http://www.orafaq.com/node/55

https://oracle-base.com/articles/misc/analytic-functions

Analytic Function Syntax

analytic_function([ arguments ]) OVER (analytic_clause)

The analytic_clause breaks down into the following optional elements.

[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]

Query_partition_clause

The query_partition_clause divides the result set into partitions, or groups, of data. The operation of the analytic function is restricted to the boundary imposed by these partitions



If the query_partition_clause is omitted, the whole result set is treated as a single partition.
SELECT empno, deptno, sal,

       AVG(sal) OVER () AS avg_sal FROM   emp;

If we change the OVER clause to include a query_partition_clause based on the department, the averages presented are specifically for the department the employee belongs too.

SELECT empno, deptno, sal,
       AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal
FROM   emp;

order_by_clause

The order_by_clause is used to order rows, or siblings, within a partition. So if an analytic function is sensitive to the order of the siblings in a partition you should include an order_by_clause. Bijv. als je laagste salaris binnen dept wil laten zien.

SELECT empno, deptno, sal,
       FIRST_VALUE(sal IGNORE NULLS) OVER (PARTITION BY deptno ORDER BY sal ASC NULLS LAST) AS first_val_in_dept
FROM   emp;

ASC is the default for an order_by_clause and NULLS LAST is the default for ASC orders. When ordering by DESC, the default is NULLS FIRST.

 It is important to understand how the order_by_clause affects display order. The order_by_clause is guaranteed to affect the order of the rows as they are processed by the analytic function, but it may not always affect the display order. As a result, you must always use a conventional ORDER BY clause in the query if display order is important. Do not rely on any implicit ordering done by the analytic function. Remember, the conventional ORDER BY clause is performed after the analytic processing, so it will always take precedence.