Oracle Analytic functions (2)

Common analytic functions:

1. First,last

--Suppose A: = min (bonus) Keep (Dense_rank  First Order by salary)--Assuming that the minimum wage is 1000,a for employees who are equal to 1000 of the wage, take the minimum bonus--Suppose B: = min (bonus) Keep (Dense_rank  Last order by salary)--assuming a maximum wage of 9999,b is the minimum bonus for employees who are equal to 9999 of the wage--as an aggregation functionSelecte.department_id,min(e.hire_date) Keep (Dense_rank firstOrder  byE.salary),--The first entry date for employees with the lowest wage level       Max(e.hire_date) Keep (Dense_rank firstOrder  byE.salary),--The latest entry date for employees with the lowest wage levels       min(e.hire_date) Keep (Dense_rank lastOrder  byE.salary),--The first entry date of the highest-paid employee       Max(e.hire_date) Keep (Dense_rank lastOrder  byE.salary)--The latest entry date for the highest-paid employee   fromEmployees EGroup  bye.department_id;--as an analytic functionSelectE.last_name, E.salary, e.department_id, e.manager_id,min(e.hire_date) Keep (Dense_rank firstOrder  byE.salary) Over(Partition byE.DEPARTMENT_ID),--The first entry date for each department with the lowest wage level       Max(e.hire_date) Keep (Dense_rank firstOrder  byE.salary) Over(Partition byE.DEPARTMENT_ID),--The latest entry dates for the lowest-paid employees in each department       min(e.hire_date) Keep (Dense_rank lastOrder  byE.salary) Over(Partition byE.DEPARTMENT_ID),--The first entry date for the highest-paid employees in each department       Max(e.hire_date) Keep (Dense_rank lastOrder  byE.salary) Over(Partition byE.DEPARTMENT_ID)--The latest entry date for the highest-paid employees in each department   fromEmployees E;

2.first_value,last_value can only be used as an analysis function

--First_value,last_valueSelecte.employee_id, E.last_name, E.salary, e.department_id, First_value (last_name) Over(Partition byE.DEPARTMENT_ID)--the first employee in the department (the sort method seems not stable, can be sorted before opening the window)   fromemployees E;Selecte.employee_id, E.last_name, E.salary, e.department_id, Last_value (last_name) Over(Partition byE.DEPARTMENT_ID)--the last employee in the department (the sort method seems to be not stable, can be sorted before opening the window)   fromemployees E;Selecte.employee_id, E.last_name, E.salary, e.department_id, First_value (last_name) Over(Partition bye.department_idOrder  byE.salary)--within the department, the first person (the name of the person with the lowest wage) is taken in ascending order of the price, since it is the price ascending so that the name of the first person within the department does not change.   fromemployees E;Selecte.employee_id, E.last_name, E.salary, e.department_id, Last_value (last_name) Over(Partition bye.department_idOrder  byE.salary)--within the department, the last person (the name of the person with the highest salary) is taken in ascending order of price, because it is the price ascending, so the name of the last person in the department may change .   fromEmployees E;

3.lag,lead can only be used as an analysis function

--Lag (ARG1,ARG2,ARG3)--The arg1 value of the arg2 row data before the current row is queried, if the default is ARG3 not found, if ARG3 is not declared, the default is empty--Lead (ARG1,ARG2,ARG3)--The arg1 value of the arg2 row data after the current row is queried, if the default is ARG3 not found, if ARG3 is not declared, the default is emptySelectE.last_name, E.hire_date, E.salary, Lag (e.salary,1,0) Over(Order  byE.hire_date)--the salary of the first employee who has entered the job before himself   fromEmployees E; SelectE.last_name, E.hire_date, E.salary, lead (E.salary,1,0) Over(Order  byE.hire_date)--the salary of the first employee who entered the job after himself  fromEmployees E;

4. List_agg

--as a single-group grouping functionSelectListagg (E.last_name,';') withinGroup(Order  byE.hire_date),--name of all employees whose department number is 30       min(e.hire_date),Max(e.hire_date) fromEmployees Ewheree.department_id=  -;--as an aggregation functionSelectListagg (E.last_name,';') withinGroup(Order  byE.hire_date),--name of all employees in each department       min(e.hire_date),Max(e.hire_date) fromEmployees EGroup  bye.department_id;--as an aggregation functionSelectE.last_name, e.department_id, Listagg (E.last_name,';') withinGroup(Order  byE.hire_date) Over(Partition byE.DEPARTMENT_ID)--cannot add order by after over   fromEmployees E;

Median of 5.median

Oracle Analytic functions (2)