Probably the easiest way to understand analytic functions is to start
by looking at aggregate functions. An aggregate function, as the name
suggests, aggregates data from several rows into a single result row.
For example, we might use the
SELECT deptno, AVG(sal) FROM emp
GROUP BY deptno
ORDER BY deptno;
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
20 2175 30
1566.66667
SQL>
Analytic functions also operate on subsets of rows, similar to aggregate functions in
analytic_function([ arguments ]) OVER (analytic_clause) The
[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ] The sub-elements of the
BREAK ON deptno SKIP 1 DUPLICATES SELECT empno, deptno, sal, AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal FROM emp; EMPNO DEPTNO SAL AVG_DEPT_SAL ---------- ---------- ---------- ------------ 7782 10 2450 2916.66667 7839 10 5000 2916.66667 7934 10 1300 2916.66667 7566 20 2975 2175 7902 20 3000 2175 7876 20 1100 2175 7369 20 800 2175 7788 20 3000 2175 7521 30 1250 1566.66667 7844 30 1500 1566.66667 7499 30 1600 1566.66667 7900 30 950 1566.66667 7698 30 2850 1566.66667 7654 30 1250 1566.66667 SQL>
BREAK ON deptno SKIP 1 DUPLICATES SELECT empno, deptno, sal, FIRST_VALUE(sal IGNORE NULLS) OVER (PARTITION BY deptno) AS first_sal_in_dept FROM emp; EMPNO DEPTNO SAL FIRST_SAL_IN_DEPT ---------- ---------- ---------- ----------------- 7782 10 2450 2450 7839 10 5000 2450 7934 10 1300 2450 7566 20 2975 2975 7902 20 3000 2975 7876 20 1100 2975 7369 20 800 2975 7788 20 3000 2975 7521 30 1250 1250 7844 30 1500 1250 7499 30 1600 1250 7900 30 950 1250 7698 30 2850 1250 7654 30 1250 1250 SQL> Now compare the values of the
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; EMPNO DEPTNO SAL FIRST_VAL_IN_DEPT ---------- ---------- ---------- ----------------- 7934 10 1300 1300 7782 10 2450 1300 7839 10 5000 1300 7369 20 800 800 7876 20 1100 800 7566 20 2975 800 7788 20 3000 800 7902 20 3000 800 7900 30 950 950 7654 30 1250 950 7521 30 1250 950 7844 30 1500 950 7499 30 1600 950 7698 30 2850 950 SQL> In this case the "
It is important to understand how the
AVG
aggregate function to give us an average of all the employee salaries in the EMP table.SELECT AVG(sal) FROM emp; AVG(SAL) ---------- 2073.21429 SQL>
The
GROUP BY
clause allows us to apply aggregate
functions to subsets of rows. For example, we might want to display the
average salary for each department.SELECT deptno, AVG(sal) FROM emp
GROUP BY deptno
ORDER BY deptno;
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
20 2175 30
1566.66667
SQL>
In both cases, the aggregate function reduces the number of rows returned by the query.
Analytic functions also operate on subsets of rows, similar to aggregate functions in
GROUP BY
queries, but they do not reduce the number of rows returned by the
query. For example, the following query reports the salary for each
employee, along with the average salary of the employees within the
department.SET PAGESIZE 50 BREAK ON deptno SKIP 1 DUPLICATES SELECT empno, deptno, sal, AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal FROM emp; EMPNO DEPTNO SAL AVG_DEPT_SAL ---------- ---------- ---------- ------------ 7782 10 2450 2916.66667 7839 10 5000 2916.66667 7934 10 1300 2916.66667 7566 20 2975 2175 7902 20 3000 2175 7876 20 1100 2175 7369 20 800 2175 7788 20 3000 2175 7521 30 1250 1566.66667 7844 30 1500 1566.66667 7499 30 1600 1566.66667 7900 30 950 1566.66667 7698 30 2850 1566.66667 7654 30 1250 1566.66667 14 rows selected. SQL>
This timeAVG
is an analytic function, operating on the group of rows defined by the contents of theOVER
clause. This group of rows is known as a window, which is why analytic functions are sometimes referred to as window[ing] functions. Notice how theAVG
function is still reporting the departmental average, like it did in theGROUP BY
query, but the result is present in each row, rather than reducing the total number of rows returned. This is because analytic functions are performed on a result set after all join,WHERE
,GROUP BY
andHAVING
clauses are complete, but before the finalORDER BY
operation is performed.
Analytic Function Syntax
There are some variations in the syntax of the individual analytic functions, but the basic syntax for an analytic function is as follows.analytic_function([ arguments ]) OVER (analytic_clause) The
analytic_clause
breaks down into the following optional elements.[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ] The sub-elements of the
analytic_clause
each have their own syntax diagrams, shown here. Rather than repeat the syntax diagrams, the following sections describe what each section of the analytic_clause
is used for.query_partition_clause
Thequery_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, similar to
the way a GROUP BY
clause affects the action of an aggregate function. If the query_partition_clause
is omitted, the whole result set is treated as a single partition. The following query uses an empty OVER
clause, so the average presented is based on all the rows of the result set.CLEAR BREAKS SELECT empno, deptno, sal, AVG(sal) OVER () AS avg_sal FROM emp; EMPNO DEPTNO SAL AVG_SAL ---------- ---------- ---------- ---------- 7369 20 800 2073.21429 7499 30 1600 2073.21429 7521 30 1250 2073.21429 7566 20 2975 2073.21429 7654 30 1250 2073.21429 7698 30 2850 2073.21429 7782 10 2450 2073.21429 7788 20 3000 2073.21429 7839 10 5000 2073.21429 7844 30 1500 2073.21429 7876 20 1100 2073.21429 7900 30 950 2073.21429 7902 20 3000 2073.21429 7934 10 1300 2073.21429 SQL>
---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.BREAK ON deptno SKIP 1 DUPLICATES SELECT empno, deptno, sal, AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal FROM emp; EMPNO DEPTNO SAL AVG_DEPT_SAL ---------- ---------- ---------- ------------ 7782 10 2450 2916.66667 7839 10 5000 2916.66667 7934 10 1300 2916.66667 7566 20 2975 2175 7902 20 3000 2175 7876 20 1100 2175 7369 20 800 2175 7788 20 3000 2175 7521 30 1250 1566.66667 7844 30 1500 1566.66667 7499 30 1600 1566.66667 7900 30 950 1566.66667 7698 30 2850 1566.66667 7654 30 1250 1566.66667 SQL>
order_by_clause
Theorder_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
. The following query uses the FIRST_VALUE
function to return the first salary reported in each department. Notice
we have partitioned the result set by the department, but there is no order_by_clause
.BREAK ON deptno SKIP 1 DUPLICATES SELECT empno, deptno, sal, FIRST_VALUE(sal IGNORE NULLS) OVER (PARTITION BY deptno) AS first_sal_in_dept FROM emp; EMPNO DEPTNO SAL FIRST_SAL_IN_DEPT ---------- ---------- ---------- ----------------- 7782 10 2450 2450 7839 10 5000 2450 7934 10 1300 2450 7566 20 2975 2975 7902 20 3000 2975 7876 20 1100 2975 7369 20 800 2975 7788 20 3000 2975 7521 30 1250 1250 7844 30 1500 1250 7499 30 1600 1250 7900 30 950 1250 7698 30 2850 1250 7654 30 1250 1250 SQL> Now compare the values of the
FIRST_SAL_IN_DEPT
column when we include an order_by_clause
to order the siblings by ascending salary.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; EMPNO DEPTNO SAL FIRST_VAL_IN_DEPT ---------- ---------- ---------- ----------------- 7934 10 1300 1300 7782 10 2450 1300 7839 10 5000 1300 7369 20 800 800 7876 20 1100 800 7566 20 2975 800 7788 20 3000 800 7902 20 3000 800 7900 30 950 950 7654 30 1250 950 7521 30 1250 950 7844 30 1500 950 7499 30 1600 950 7698 30 2850 950 SQL> In this case the "
ASC NULLS LAST
" keywords are unnecessary as 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.windowing_clause
We have seen previously thequery_partition_clause
controls the window, or group of rows, the analytic operates on. The windowing_clause
gives some analytic functions a further degree of control over this window within the current partition. The windowing_clause
is an extension of the order_by_clause
and as such, it can only be used if an order_by_clause
is present. The windowing_clause
has two basic forms.RANGE BETWEEN start_point AND end_point ROWS BETWEEN start_point AND end_point
No comments:
Post a Comment