Shulou(Shulou.com)06/01 Report--

What is the analysis function?

The analysis function is a powerful function specially used by Oracle to solve the statistical needs of complex reports. It can be grouped in the data and then calculate some group-based statistics, and each row of each group can return a statistical value.

What is the difference between an analytical function and an aggregate function?

Ordinary aggregate functions are grouped with group by, each packet returns a statistical value, while the analysis function uses partition by grouping, and each group can return a statistical value per row.

The form of analytical function

The analysis function has a window function over (), which contains three analysis clauses: grouping (partition by), sorting (order by), and rows (rows), which are used in the following form: over (partition by xxx order by yyy rows between zzz).

Note: window clause here I only talk about rows windows, not range windows and sliding windows.

Analyze function examples (simulated under scott users)

The purpose of the example is to display the wages of employees in each department, along with the maximum wage of that part.

-- displays the salary of the employees of each department, together with the maximum wage of that part. SELECT E.DEPTNO, E.EMPNO, E.ENAME, E.SAL, LAST_VALUE (E.SAL) OVER (PARTITION BY E.DEPTNO ORDER BY E.SAL ROWS-- unbounded preceding and unbouned following for the previous and last records of all current records, that is, all records in the table-- unbounded: uncontrolled Infinite-- preceding: in. Before-- following: in. After that BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL FROM EMP E

Running result:

Example purpose: group by deptno, and then calculate the sum of each set of values

SELECT EMPNO, ENAME, DEPTNO, SAL, SUM (SAL) OVER (PARTITION BY DEPTNO ORDER BY ENAME) max_sal FROM SCOTT.EMP

Running result:

Example purpose: to group departments and display the summary from the first line to the current line

SELECT EMPNO, ENAME, DEPTNO, SAL,-- Note that ROWS BETWEEN unbounded preceding AND current row refers to the summary SUM (SAL) OVER (PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_sal FROM SCOTT.EMP from the first line to the current line

Running result:

Example goal: summary of the current line to the last line

SELECT EMPNO, ENAME, DEPTNO, SAL,-- Note that ROWS BETWEEN current row AND unbounded following refers to the summary SUM (SAL) OVER (PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal FROM SCOTT.EMP from the current line to the last line.

Running result:

Example target: summary of the previous line (rownum-1) of the current line to the current line

SELECT EMPNO, ENAME, DEPTNO, SAL,-- Note that ROWS BETWEEN 1 preceding AND current row refers to the summary SUM (SAL) OVER (PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) max_sal FROM SCOTT.EMP from the previous line (rownum-1) to the current line.

Running result:

Example target: summary from the previous line of the current line (rownum-1) to the next line of the current line (rownum+2)

SELECT EMPNO, ENAME, DEPTNO, SAL,-- Note ROWS BETWEEN 1 preceding AND 1 following refers to the summary SUM (SAL) OVER (PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal FROM SCOTT.EMP from the previous line (rownum-1) of the current line to the next line (rownum+2) of the current line.

Running result:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.