Analysis functions, oracle analysis functions

Analysis functions, oracle analysis functions

Analysis functions
Basic syntax
Function Name ([parameter]) VOER (
Partition by clause field ,....
[Order by clause field,... [ASC] [DESC] [nulls first] [nulls last]
[Wing clause]);
Use the partition by clause

SELECT deptno, ename, sal, job, SUM (sal) OVER (partition by deptno) sum Department salary sum from emp;

If partition by is not added

SELECT deptno, ename, sal, job, SUM (sal) OVER () sum total Company salary FROM emp;

Add multiple partitions using the partition by clause

SELECT deptno,ename,sal,job,SUM(sal) OVER(PARTITION BY deptno,job)sumFROM emp;

Order by clause
View the order by clause

SELECT deptno,ename,sal,hiredate,RANK()OVER (PARTITION BY deptno ORDER BY sal,hiredate DESC) rkFROM emp;

Sort by order by directly

SELECT deptno,ename,sal,hiredate,SUM(sal) OVER(ORDER BY ename)sumFROM emp;

Put data with NULL values in NULLS FIRST
Put data with NULL value in NULLS LAST

SELECT deptno,ename,sal,comm,RANK() OVER(ORDER BY comm DESC NULLS LAST) RK,SUM(sal) OVER(ORDER BY comm DESC NULLS LAST) sumFROM emp;

Wing wing clause
RNGGE clause
Set the offset on sal

SELECT deptno,ename,sal,SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE 300 PRECEDING) sumFROM emp;

Match up N
Below is the downward N matching

SELECT deptno,ename,sal,SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE BETWEEN 0 PRECEDING AND 300 FOLLOWING) sumFROM emp;

Match the data of the current row

SELECT deptno,ename,sal,SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE BETWEEN 0 PRECEDING AND CURRENT ROW) sumFROM emp;

The current row option is used to indicate that the CURRENT number of rows is the same. Therefore, only the same data is used to calculate the SUM.
UNBOUNDED

SELECT deptno,ename,sal,SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sumFROM emp;

ROWS clause
Set two line Offsets

SELECT deptno,ename,sal,SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS 2 PRECEDING) sumFROM emp;

Query row range

SELECT deptno,ename,sal,SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sumFROM emp;

Example of Analytic Functions
Statistical functions
SUM
MIN
MAX
AVG
COUNT
These are data statistics functions.
Query the name, position, basic salary, Department number, Department number, average salary, maximum salary, minimum wage, and total salary of an employee whose ID is 7369

SELECT * FROM(SELECT empno,ename,job,sal,deptno,COUNT(empno) OVER(PARTITION BY deptno) count,ROUND(AVG(sal) OVER(PARTITION BY deptno),2) avg,MAX(sal) OVER(PARTITION BY deptno) max,MIN(sal) OVER(PARTITION BY deptno) min,SUM(sal) OVER(PARTITION BY deptno) sumFROM emp) tempWHERE temp.empno=7369;

Query the number, name, basic salary, Department name, Department location, and average salary, maximum and minimum wage of each employee.
Emp and dept tables

SELECT e.empno,e.ename,e.sal,d.dname,d.loc,ROUND(AVG(sal) OVER (PARTITION BY e.deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),2) avg, MAX(sal) OVER(PARTITION BY e.deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) max,MIN(sal) OVER(PARTITION BY e.deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) min,SUM(sal) OVER(PARTITION BY e.deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sumFROM emp e,dept dWHERE e.deptno=d.deptno;

Level functions
Record marking Function
RANK () and DENSE_RANK ()
Observe the RANK () and DENSE_RANK () Functions

SELECT deptno,ename,sal,RANK() OVER(PARTITION BY deptno ORDER BY sal) rank_result,DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) dense_rank_resutFROM emp;

It can be seen that there is a repeat in RANK, but the current value has not changed, but the other one will jump, the next difference is + 2 and DENSE_RANK () each time only + 1, the repeat will not increase
Row marking Function
ROW_NUMBER () indicates the row Record Number automatically and can be connected no matter whether the content is repeated or not.

SELECT deptno, ename, sal, ROW_NUMBER () OVER (partition by deptno order by sal) row_result_deptno, ROW_NUMBER () OVER (order by sal) row_result_salFROM emp; Retain with the KEEP statement

Qualified Data

SELECT deptno,MAX(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) max_sal,MIN(sal) KEEP (DENSE_RANK LAST ORDER BY sal) min_salFROM empGROUP BY deptno;

Retrieve records of the first and last rows

FIRST_VALUE() LAST_VALUE()SELECT deptno,empno,ename,sal,FIRST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY salRANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) first_result,LAST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY salRANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last_resultFROM empWHERE deptno=10;

Compare adjacent records
LAG () is used to obtain the nth row record of the data rows listed earlier. If no record exists, the default value is used. If no record exists, NULL is returned if no default value is set.
The N-row record of the data listed after the LEAD () deduplication is displayed. If no record exists, the default value is used. If no record exists, NULL is returned if no record is set.

SELECT deptno,empno,ename,sal,LAG(sal,2,0) OVER (PARTITION BY deptno ORDER BY sal) lag_result,LEAD(sal,2,0) OVER (PARTITION BY deptno ORDER BY sal)lead_resultFROM empWHERE deptno=20;

Report Functions
CUME_DIST () calculates the relative position of a partition
NTILE (number) divides a partition into "expression" hash Representation
RATIO_TO_TEPORT (expression) This function calculates the value of expression/(sum (expression), which gives the percentage relative to the total data
Verify the CUME_DIST () function
Assume that the partitions are divided by 5 rows: 0.2, 0.4, 0.6, 0.8, and 1.0.

SELECT deptno,ename,sal, CUME_DIST() OVER(PARTITION BY deptno ORDER BY sal) cumeFROM empWHERE deptno IN(10,20);

 

NTILE Function
Divide the ordered results of a Data zone and assign a unique group number to each group.

SELECT deptno,sal,SUM(sal) OVER (PARTITION BY deptno ORDER BY sal) sum,NTILE(3) OVER (PARTITION BY deptno ORDER BY sal) ntile_a,NTILE(6) OVER (PARTITION BY deptno ORDER BY sal) ntile_bFROM emp;

The RATIO_TO_REPORT function displays the data to be collected as a percentage of the overall data.

SELECT deptno,SUM(sal),ROUND(RATIO_TO_REPORT(SUM(sal)) OVER(),5) rate,ROUND(RATIO_TO_REPORT(SUM(sal)) OVER(),5)*100 || '%'precentFROM empGROUP BY deptno;

Row and column Conversion

Query the total salary of each position in each department

SELECT deptno,job,SUM(sal)FROM empGROUP BY deptno,job;

2nd implementations
Display with DECODE

SELECT deptno, SUM(DECODE(job,'PRESIDENT',sal,0)) PRESIDENT_JOB,SUM(DECODE(job,'MANAGER',sal,0)) MANAGER_JOB,SUM(DECODE(job,'ANALYST',sal,0)) ANALYST_JOB, SUM(DECODE(job,'CLERK',sal,0)) CLERK_JOB, SUM(DECODE(job,'SALESMAN',sal,0)) SALESMAN_JOBFROM empGROUP BY deptno;

DECODE is not used for implementation.

SELECT temp.dno,SUM(PRESIDENT_JOB),SUM(MANAGER_JOB),SUM(ANALYST_JOB),SUM(CLERK_JOB),SUM(SALESMANJOB)FROM(SELECT deptno dno, (SELECT SUM(sal) FROM emp WHERE job='PRESIDENT'AND empno=e.empno) PRESIDENT_JOB,(SELECT SUM(sal) FROM emp WHERE job='MANAGER'AND empno=e.empno) MANAGER_JOB,(SELECT SUM(sal) FROM emp WHERE job='ANALYST'AND empno=e.empno) ANALYST_JOB,(SELECT SUM(sal) FROM emp WHERE job='CLERK'AND empno=e.empno) CLERK_JOB,(SELECT SUM(sal) FROM emp WHERE job='SALESMAN'AND empno=e.empno) SALESMANJOBFROM emp e) tempGROUP BY temp.dnoORDER BY temp.dno DESC;

Functions of callback and unregister

SELECT * FROM (SELECT deptno,job,sal FROM emp)PIVOT(SUM(sal)FOR job IN('PRESIDENT' AS PRESIDENT_JOB,'MANAGER' AS MANAGER_JOB,'ANALYST' AS ANALYST_JOB,'CLERK' AS CLERK_JOB,'SALESMAN' AS SALESMANJOB))ORDER BY deptno;

Use XML and ANY
If XML display is added to the operation, you can use ANY to set all the data to be operated.

SELECT * FROM (SELECT deptno,job,sal FROM emp)PIVOT XML(SUM(sal)FOR job IN(ANY))ORDER BY deptno;

Query more statistics

SELECT * FROM (SELECT deptno,job,sal,SUM(sal) OVER (PARTITION BY deptno) sumsal,MAX(sal) OVER (PARTITION BY deptno) maxsal,MIN(sal) OVER (PARTITION BY deptno) minsalFROM emp)PIVOT(SUM(sal)FOR job IN('PRESIDENT' AS PRESIDENT_JOB,'MANAGER' AS MANAGER_JOB,'ANALYST' AS ANALYST_JOB,'CLERK' AS CLERK_JOB,'SALESMAN' AS SALESMANJOB))ORDER BY deptno;

 

Set multiple statistical functions to query the total salary of different positions in each department and the highest salary of different positions in each department

SELECT * FROM (SELECT deptno, job, sal FROM emp) terminate (SUM (sal) AS sum, MAX (sal) AS maxFOR job IN ('President 'AS PRESIDENT_JOB, 'manager' AS MANAGER_JOB, 'analyst' AS ANALYST_JOB, 'cler' AS CLERK_JOB, 'salesman' as salesmanjob) order by deptno; use un‑twith temp AS (SELECT * FROM (SELECT deptno, job, sal FROM emp) values (SUM (sal) FOR job IN ('President 'AS PRESIDENT_JOB, 'manager' AS MANAGER_JOB, 'analyst' AS ANALYST_JOB, 'cler' AS CLERK_JOB, 'salesman' as salesmanjob) order by deptno) SELECT * FROM tempun1_include NULLS (sal_sum FOR job IN (PRESIDENT_JOB AS 'president ', MANAGER_JOB AS 'manager', ANALYST_JOB AS 'analyst', CLERK_JOB AS 'cler ', salesmanjob as 'salesman') order by deptno;