ORACLE分析函數（1）

標籤：

1. oracle中日期轉換為yyyy年mm月dd日的形式

select to_char(sysdate,‘"年"mm"月"dd"日"‘) from dual;

 

2. oracle分析函數文法

　　2.1 ORDER BY 

select e.last_name,       e.manager_id,       e.salary,       avg(e.salary) over() as emp_count --等同於（select avg(*) from employees）  from employees e;

select e.last_name,       e.manager_id,       e.salary,       avg(e.salary) over(order by e.salary asc) as emp_count --按照階梯取平均數  from employees e;

select e.last_name,       e.manager_id,       e.salary,       e.job_id,       avg(e.salary) over(partition by e.job_id) as emp_count --取每個工作的工資平均數  from employees e;

select e.last_name,       e.manager_id,       e.salary,       e.job_id,       avg(e.salary) over(partition by e.job_id order by e.salary asc) as emp_count --部門內按照階梯取工資平均數  from employees e;

 

　　2.2 UNBOUNDED PRECEDING

　　視窗資料從第一行資料開始

 

　　2.3 UNBOUNDED FOLLOWING

　　視窗資料直到最後一行資料

 

　　2.4 RANGE

　　邏輯視窗

　　count(*) over(order by salary asc range between 1 preceding and 11 following)

　　假設當前行salary為1000，則當前行的count(*)為滿足salary在（1000-1）和（1000+11）之間的資料行

　　count(*) over(order by salary desc range between 1 preceding and 11 following)

　　假設當前行salary為1000，則當前行的count(*)為滿足salary在（1000-11）和（1000+1）之間的資料行

 

 

　　2.5 ROW

　　物理視窗

　　count(*) over(order by salary range between 1 preceding and 11 following)

　　假設當前行排名為N，則當前行的count(*)為滿足排名在（N-1）和（N+11）之間的資料行

 

　　2.6 CURRENT ROW

　　從當前行開始或者以當前行結束

 

3.常用分析函數

　　3.1 AVG 平均數

select e.employee_id,       e.last_name,       e.salary,       e.manager_id,       avg(e.salary) over(partition by e.manager_id) --相同主管的平均工資  from employees e;

　　3.2 CORR 求線性關係

select e.last_name,       e.hire_date,       (sysdate - e.hire_date) hire_days,       e.salary,       e.job_id,       corr(sysdate - e.hire_date, e.salary) over(partition by e.job_id) correlation  from employees e order by e.job_id asc;

如果存線上性關係的話correlation不為空白，且salary線性等於hire_days * (1 + correlation)

　　3.3 count

　　3.4 covar_pop，COVAR_SAMP 共變數

　　3.5 cume_dist 相對位置

　　

--假設有一個人工資為15500，如下SQL可以查詢15500比多少員工的工資高select cume_dist(15500) within group(order by salary)  from employees e; --作為分析函數使用--查詢每個人的工資在相同主管下的大概位置select e.last_name,       e.salary,       e.manager_id,       cume_dist() over(partition by e.manager_id order by e.salary)  from employees e;

　　3.6 dense_rank 排名可以重複，且不會跳躍。假設資料為10,9,9,8,8;從高到低排名為：1,2,2,3,3

--作為彙總函式使用--假設有一個人工資為15500，如下SQL可以查詢15500的工資排名select dense_rank(15500) within group(order by salary)  from employees e; --作為分析函數使用--查詢每個人的工資在相同主管下的工資排名select e.last_name,       e.salary,       e.manager_id,       dense_rank() over(partition by e.manager_id order by e.salary)  from employees e;

　　3.7 rank 排名可以重複，會跳躍排序。假設資料為10,9,9,8,8;從高到低排名為：1,2,2,4,4

--作為彙總函式使用--假設有一個人工資為15500，如下SQL可以查詢15500的工資排名select rank(15500) within group(order by salary)  from employees e; --作為分析函數使用--查詢每個人的工資以及工資排名select e.last_name,       e.salary,       e.manager_id,       rank() over(order by e.salary)  from employees e;

　　3.8 row_number 只能做為分析函數使用

--查詢每種工作排名前三的人的姓名及工資select * from (select e.last_name,       e.salary,       e.job_id,       row_number() over(partition by e.job_id order by e.salary desc) rn  from employees e)  v where v.rn < 4 

 

　

　　

ORACLE分析函數（1）