Oracle analytic functions (1)

1. The date in Oracle is converted to yyyy mm month DD Day form

Select to_char (sysdate,' "Year" MM "month" DD "Day")from dual;

2. Oracle Analytic function syntax

2.1 ORDER by

Select E.last_name,       e.manager_id,       e.salary,       avgover as--  equivalent to (select AVG (*) from employees) from   Employees E;

Select E.last_name,       e.manager_id,       e.salary,       avgover (order by ASC  as -- averaging  by ladder  from Employees e;

 select   E.last_name, e.manager_id, E.SA Lary, e.job_id,  avg  (e.salary) over
     (partition by  e.job_id) as  Emp_ Count --  Take the average wage per job  from  employees E; 

Select E.last_name,       e.manager_id,       e.salary,       e.job_id,       avg over by Order  by ASC  as -- Average wage per ladder in Department   from Employees e;

2.2 unbounded preceding

Window data starts with the first row of data

2.3 Unbounded following

Window data until the last row of data

2.4 RANGE

Logical window

COUNT (*) over (order by salary ASC range between 1 preceding and following)

Assuming that the current row salary is 1000, the count (*) of the current row is the data row that satisfies salary between (1000-1) and (1000+11)

COUNT (*) over (order by salary desc range between 1 preceding and one following)

Assuming that the current row salary is 1000, the count (*) of the current row is the data row that satisfies salary between (1000-11) and (1000+1)

2.5 ROW

Physical window

COUNT (*) over (order by salary range between 1 preceding and one following)

Assuming the current row is ranked N, the Count (*) of the current row is the row of data that satisfies the rank between (N-1) and (n+11)

2.6 Current ROW

Starts at the current line or ends at the current line

3. Common analysis functions

3.1 AVG Average

Select e.employee_id,       e.last_name,       e.salary,       e.manager_id,       avgover by  - average salary of the same supervisor from   Employees E;

3.2 CORR for linear relation

Select E.last_name,       e.hire_date,       -  e.hire_date) hire_days,       e.salary,       e.job_id,        -  Over  by e.job_id) Correlation  fromorderbyASC;

If there is a linear relationship, the correlation is not empty, and the salary linear equals hire_days * (1 + correlation)

3.3 Count

3.4 Covar_pop, COVAR_SAMP covariance

3.5 cume_dist Relative position

　　

--Suppose there is a person with a salary of 15500, the following SQL can query 15500 than how many employees pay highSelectCume_dist (15500) withinGroup(Order  bysalary) fromemployees E;--use as an analysis function--Check the approximate position of each person's salary under the same supervisorSelectE.last_name, E.salary, e.manager_id, Cume_dist () Over(Partition bye.manager_idOrder  bye.salary) fromEmployees E;

3.6 Dense_rank Rankings can be repeated without jumping. Assume that the data is 10,9,9,8,8; rank from highest to lowest: 1,2,2,3,3

--use as an aggregate function--Suppose a person has a salary of 15500, the following SQL can query 15500 of the payroll rankingsSelectDense_rank (15500) withinGroup(Order  bysalary) fromemployees E;--use as an analysis function--Check the wages of everyone under the same supervisor's salary rankingSelectE.last_name, E.salary, e.manager_id, Dense_rank () Over(Partition bye.manager_idOrder  bye.salary) fromEmployees E;

3.7 Rank can be repeated and will jump sort. Assume that the data is 10,9,9,8,8; rank from highest to lowest: 1,2,2,4,4

--use as an aggregate function--Suppose a person has a salary of 15500, the following SQL can query 15500 of the payroll rankingsSelectRank15500) withinGroup(Order  bysalary) fromemployees E;--use as an analysis function--check everyone's salary and salary rankingsSelectE.last_name, E.salary, e.manager_id, rank () Over(Order  bye.salary) fromEmployees E;

3.8 Row_number can only be used as an analytic function

-- Check the name and salary of the top three people in each job Select *  from (Select  e.last_name,       e.salary,       e.job_id,       over byOrder  bydesc) rn  from Employees e )  where  <4

　

　　

Oracle analytic functions (1)