oracle分析函數（2）

標籤：

常用分析函數：

1. first,last

--假設a := min(獎金) keep(dense_rank first order by 工資)--假設工資最少為1000，a為在工資等於1000的員工取最小的獎金--假設b := min(獎金) keep(dense_rank last order by 工資)--假設工資最多為9999，b為在工資等於9999的員工取最小的獎金--作為彙總函式select e.department_id,       min(e.hire_date) keep(dense_rank first order by e.salary), --工資最低的員工中，最早入職的入職日期       max(e.hire_date) keep(dense_rank first order by e.salary), --工資最低的員工中，最晚入職的入職日期       min(e.hire_date) keep(dense_rank last order by e.salary), --工資最高的員工中，最早入職的入職日期       max(e.hire_date) keep(dense_rank last order by e.salary) --工資最高的員工中，最晚入職的入職日期  from employees e group by e.department_id; --作為分析函數select e.last_name,       e.salary,       e.department_id,       e.manager_id,       min(e.hire_date) keep(dense_rank first order by e.salary) over(partition by e.department_id), --每個部門工資最低的員工中，最早入職的入職日期       max(e.hire_date) keep(dense_rank first order by e.salary) over(partition by e.department_id), --每個部門工資最低的員工中，最晚入職的入職日期       min(e.hire_date) keep(dense_rank last order by e.salary) over(partition by e.department_id), --每個部門工資最高的員工中，最早入職的入職日期       max(e.hire_date) keep(dense_rank last order by e.salary) over(partition by e.department_id) --每個部門工資最高的員工中，最晚入職的入職日期  from employees e;

 

2.first_value,last_value只能作為分析函數使用

--first_value,last_valueselect e.employee_id,       e.last_name,       e.salary,       e.department_id,       first_value(last_name) over(partition by e.department_id) --部門內第一個員工（排序方式好像不是穩定的，可以先排序再開窗）  from employees e;select e.employee_id,       e.last_name,       e.salary,       e.department_id,       last_value(last_name) over(partition by e.department_id) --部門內最後一個員工（排序方式好像不是穩定的，可以先排序再開窗）  from employees e;select e.employee_id,       e.last_name,       e.salary,       e.department_id,       first_value(last_name) over(partition by e.department_id order by e.salary) --部門內部按照價格升序階梯取第一個人（工資最低的人的姓名），因為是價格升序所以部門內部第一個人的姓名不會變化  from employees e;select e.employee_id,       e.last_name,       e.salary,       e.department_id,       last_value(last_name) over(partition by e.department_id order by e.salary) --部門內部按照價格升序階梯取最後一個人（工資最高的人的姓名），因為是價格升序所以部門最後一個人的的姓名有可能變化  from employees e;

 

3.lag，lead只能作為分析函數使用

--lag(arg1，arg2，arg3)--查詢當前行之前的第arg2行資料的arg1的值，如果未找到預設為arg3,如果arg3不聲明，預設為空白--lead(arg1，arg2，arg3)--查詢當前行之後的第arg2行資料的arg1的值，如果未找到預設為arg3,如果arg3不聲明，預設為空白select e.last_name,       e.hire_date,       e.salary,       lag(e.salary, 1, 0) over(order by e.hire_date)--自己之前入職的第一個員工的工資  from employees e;  select e.last_name,      e.hire_date,      e.salary,      lead(e.salary, 1, 0) over(order by e.hire_date)--自己之後入職的第一個員工的工資 from employees e;

 

4. list_agg 

--作為單組分組函數select listagg(e.last_name， ‘;‘) within group(order by e.hire_date), --部門號為30的所有員工姓名       min(e.hire_date),       max(e.hire_date)  from employees e where e.department_id = 30;--作為彙總函式select listagg(e.last_name， ‘;‘) within group(order by e.hire_date), --各部門的所有員工姓名       min(e.hire_date),       max(e.hire_date)  from employees e group by e.department_id;--作為彙總函式select e.last_name,       e.department_id,       listagg(e.last_name， ‘;‘) within group(order by e.hire_date) over(partition by e.department_id) --over後不能加order by  from employees e;

 

5.median 中位元

 

oracle分析函數（2）