Getting Started with Oracle analytics functions

I. Getting started with Oracle analytics functions

What is an analytic function?
Analytic functions are powerful functions that Oracle specifically uses to address the statistical requirements of complex reports, which can be grouped in data and then calculated based on some statistical values of groups, and each row of each group can return a statistic value.

What is the difference between an analytic function and an aggregate function?
The normal aggregation functions are grouped by group by, each grouping returns a statistic value, and the analysis functions are grouped by partition, and each group can return a statistic value.

form of the Analytic function
The analysis function has an open window function over (), which contains three analysis clauses: grouping (partition by), sorting (Order by), window (rows), they are used in the following form: Over (partition by xxx order by yyy rows is Tween zzz).
Note: The window clause here I only say rows window, range mode and sliding window do not mention

Analysis Function Example (simulated under Scott User)

Example Purpose: Displays the payroll for each department employee, accompanied by a maximum wage that shows the section.

-Displays the wages of the employees of each department, accompanied by a maximum wage showing that part.
SELECT E.deptno,
       E.empno,
       E.ename,
       E.sal,
       
       
            
            --unbounded preceding and unbouned following for the previous, next record of all current records, that is, all records in the table
            --unbounded: Uncontrolled, infinite
            --preceding: In ... Before
            --following: In ... After
            Between unbounded preceding and unbounded following) Max_sal
  From EMP E;

Operation Result:

Example purpose: Grouping by Deptno and then calculating 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;

Operation Result:

Example purpose: Grouping departments, with a summary showing the first row to the current row

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       -Note that rows between unbounded preceding  and current row refer to the total of the first line
       
                     
                     ROWS between unbounded preceding and current ROW) max_sal
  From SCOTT. EMP;

Operation Result:

Example target: Summary of the current row to the last row

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       -Note that the rows between current row and unbounded following refer to the totals for the last line
       
                     
                     ROWS between current ROW and unbounded following) Max_sal
  From SCOTT. EMP;

Operation Result:

Example target: The top row of the current row (rownum-1) to a summary of the current row

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       
       
                     
                     Between 1 preceding and current ROW) max_sal
  From SCOTT. EMP;

Operation Result:

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

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       -Note that rows between 1 preceding and 1 following refers to the summary of the previous row (rownum-1) of the current line to the next row (rownum+2) of the current row
       
                     
                     ROWS between 1 preceding and 2 following) Max_sal
  From SCOTT. EMP;

Operation Result:

Getting Started with Oracle analytics functions