學習目標 -瞭解分析函數作用和類型 -使用分析函數產生報告 分析函數 分析函數用於計算一些基於組的聚合值，它與聚合函數的區別在於，分析函數每組返回多行，聚合函數每組返回一行。 一般分析函數 ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) 按分區或返回 ...
學習目標 -瞭解分析函數作用和類型 -使用分析函數產生報告 分析函數 分析函數用於計算一些基於組的聚合值，它與聚合函數的區別在於，分析函數每組返回多行，聚合函數每組返回一行。 一般分析函數 ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) 按分區或返回記錄生成唯一編號 RANK() OVER(PARTITION BY ... ORDER BY ...) 按分區或返回記錄排序，會跳號 DENSE_RANK() OVER(PARTITION BY ... ORDER BY ...) 按分區或返回記錄排序，不跳號 COUNT() OVER(PARTITION BY ... ORDER BY ...) 按分區或返回記錄進行計數 MAX() OVER(PARTITION BY ... ORDER BY ...) 按分區或返回記錄計算最大值 MIN() OVER(PARTITION BY ... ORDER BY ...) 按分區或返回記錄計算最小值 SUM() OVER(PARTITION BY ... ORDER BY ...) 按分區或返回記錄進行求和 AVG() OVER(PARTITION BY ... ORDER BY ...) 按分區或返回記錄求出平均值 FIRST_VALUE() OVER(PARTITION BY ... ORDER BY ...) 按分區或返回記錄的第一個值 LAST_VALUE() OVER(PARTITION BY ... ORDER BY ...) 按分區或返回記錄的最後一個值 LAG() OVER(PARTITION BY ... ORDER BY ...) 按分區或返回記錄上偏移值 LEAD() OVER(PARTITION BY ... ORDER BY ...) 按分區或返回記錄下偏移值 函數語法說明 PARITION BY Function_name(...) Over(partition by col_name) 用於分區，按列進行分區 ORDER BY (預設視窗) Function_name(...) Over(Order by col_name [rows | range between n|unbounded preceding and n| unbounded following]) -rows：【行】前n行、後n行或當前行，用於計算 -range：【範圍】大於或小於當前值的n行，或使用前n行來計算 -unbounded：【無界限】所有行都使用計算 ROW_NUMBER() OVER(partition by ... order by ...) 功能與rownum偽列類似，order by子句中指定有序列，從1開始為分區中的每一行或查詢返回的每一行分配一個唯一的編號。 註意ROW_NUMBER(這裡不能限定列名)
查詢按部門代碼求出薪水排名： 13:49:52 SQL> set pagesize 500 13:50:09 SQL> col last_name format a20 13:50:09 SQL> select last_name,department_id,salary,row_number() over(partition by department_id order by salary ) row_num 13:50:09 2 from employees; LAST_NAME DEPARTMENT_ID SALARY ROW_NUM -------------------- ------------- ---------- ---------- Whalen 10 4390 1 Fay 20 5990 1 Hartstein 20 12990 2 Colmenares 30 2490 1 Himuro 30 2590 2 Tobias 30 2790 3 Baida 30 2890 4 Khoo 30 3090 5 Raphaely 30 10990 6 Mavris 40 6490 1
RANK() OVER(PARTITION BY ... ORDER BY ...) 為查詢返回的每一行併列排序，相同排名後的排名會出現跳號
查詢部門代碼50，工資在3000~6000之間的排名情況 13:56:59 SQL> set pagesize 500 13:57:30 SQL> col last_name format a20 13:57:30 SQL> select last_name,department_id,salary,rank() over(partition by department_id order by salary ) row_num 13:57:30 2 from employees 13:57:30 3 where department_id =50 13:57:30 4 and salary between 3000 and 6000; LAST_NAME DEPARTMENT_ID SALARY ROW_NUM -------------------- ------------- ---------- ---------- Fleaur 50 3090 1 Walsh 50 3090 1 Davies 50 3090 1 Nayer 50 3190 4--出現跳號 McCain 50 3190 4 Taylor 50 3190 4 Stiles 50 3190 4 Bissot 50 3290 8--出現跳號 Mallin 50 3290 8 Dellinger 50 3390 10--出現跳號 Rajs 50 3490 11 Dilly 50 3590 12 Ladwig 50 3590 12 Chung 50 3790 14--出現跳號 Everett 50 3890 15 Bell 50 3990 16 Bull 50 4090 17 Sarchand 50 4190 18 Mourgos 50 5790 19 已選取 19 個資料列.
DENSE_RANK() OVER(PARTITION BY ... ORDER BY ...) 為查詢返回的每一行併列排序，相同排名後的排名不會跳號
查詢部門代碼50，工資在3000~6000之間的排名情況 14:01:48 SQL> set pagesize 500 14:02:06 SQL> col last_name format a20 14:02:06 SQL> select last_name,department_id,salary,dense_rank() over(partition by department_id order by salary ) row_num 14:02:06 2 from employees 14:02:06 3 where department_id =50 14:02:06 4 and salary between 3000 and 6000; LAST_NAME DEPARTMENT_ID SALARY ROW_NUM -------------------- ------------- ---------- ---------- Fleaur 50 3090 1 Walsh 50 3090 1 Davies 50 3090 1 Nayer 50 3190 2 McCain 50 3190 2 Taylor 50 3190 2 Stiles 50 3190 2 Bissot 50 3290 3 Mallin 50 3290 3 Dellinger 50 3390 4 Rajs 50 3490 5 Dilly 50 3590 6 Ladwig 50 3590 6 Chung 50 3790 7 Everett 50 3890 8 Bell 50 3990 9 Bull 50 4090 10 Sarchand 50 4190 11 Mourgos 50 5790 12 已選取 19 個資料列.
COUNT() OVER(PARTITION BY ... ORDER BY ...) 返回查詢記錄或分區的計數值(次數)
14:11:48 SQL> set pagesize 500 14:12:54 SQL> col last_name format a20 14:12:54 SQL> select last_name,department_id,salary,count(salary) over( partition by department_id ) count_num 14:12:54 2 from employees; LAST_NAME DEPARTMENT_ID SALARY COUNT_NUM -------------------- ------------- ---------- ---------- Whalen 10 4390 1--部門10中出現了1次 Hartstein 20 12990 2--部門20中出現了2次 Fay 20 5990 2--部門20中出現了2次 Raphaely 30 10990 6--部門30中出現了6次 Khoo 30 3090 6 Baida 30 2890 6 Tobias 30 2790 6 Himuro 30 2590 6 Colmenares 30 2490 6 Mavris 40 6490 1 Weiss 50 7990 45 Fripp 50 8190 45 Kaufling 50 7890 45 Vollman 50 6490 45
MAX() OVER(PARTITION BY ... ORDER BY ...) 按分區返回最大的值
14:12:55 SQL> set pagesize 500 14:15:59 SQL> col last_name format a20 14:15:59 SQL> select last_name,department_id,salary,max(salary) over( partition by department_id ) max_sal 14:15:59 2 from employees 14:15:59 3 ; LAST_NAME DEPARTMENT_ID SALARY MAX_SAL -------------------- ------------- ---------- ---------- Whalen 10 4390 4390--部門10薪水最高的 Hartstein 20 12990 12990--部門20薪水最高的 Fay 20 5990 12990--部門20薪水最高的 Raphaely 30 10990 10990--部門30薪水最高的 Khoo 30 3090 10990 Baida 30 2890 10990 Tobias 30 2790 10990 Himuro 30 2590 10990 Colmenares 30 2490 10990 Mavris 40 6490 6490--部門40薪水最高的 Weiss 50 7990 8190--部門50薪水最高的 Fripp 50 8190 8190 Kaufling 50 7890 8190 Vollman 50 6490 8190 Mourgos 50 5790 8190
MIN() OVER(PARTITION BY ... ORDER BY ...) 按分區返回最小的值
14:16:00 SQL> set pagesize 500 14:18:10 SQL> col last_name format a20 14:18:10 SQL> select last_name,department_id,salary,min(salary) over( partition by department_id ) min_sal 14:18:10 2 from employees; LAST_NAME DEPARTMENT_ID SALARY MIN_SAL -------------------- ------------- ---------- ---------- Whalen 10 4390 4390 Hartstein 20 12990 5990 Fay 20 5990 5990 Raphaely 30 10990 2490 Khoo 30 3090 2490 Baida 30 2890 2490 Tobias 30 2790 2490 Himuro 30 2590 2490 Colmenares 30 2490 2490 Mavris 40 6490 6490 Weiss 50 7990 2090 Fripp 50 8190 2090 Kaufling 50 7890 2090 Vollman 50 6490 2090 Mourgos 50 5790 2090
SUM() OVER(PARTITION BY ... ORDER BY ...) 按分區彙總求和
查詢所有薪水累積相加 14:32:10 SQL> set pagesize 500 14:32:40 SQL> col last_name format a20 14:32:40 SQL> select last_name,department_id,salary,sum(salary) over( order by salary ) sum_sal 14:32:40 2 from employees 14:32:40 3 where department_id=30; LAST_NAME DEPARTMENT_ID SALARY SUM_SAL -------------------- ------------- ---------- ---------- Colmenares 30 2490 2490 Himuro 30 2590 5080 Tobias 30 2790 7870 Baida 30 2890 10760 Khoo 30 3090 13850 Raphaely 30 10990 24840 已選取 6 個資料列. 查詢按部門分區進行求和 14:19:28 SQL> set pagesize 500 14:20:06 SQL> col last_name format a20 14:20:06 SQL> select last_name,department_id,salary,sum(salary) over( partition by department_id ) sum_sal 14:20:06 2 from employees; LAST_NAME DEPARTMENT_ID SALARY SUM_SAL -------------------- ------------- ---------- ---------- Whalen 10 4390 4390 Hartstein 20 12990 18980 Fay 20 5990 18980 Raphaely 30 10990 24840 Khoo 30 3090 24840 Baida 30 2890 24840 Tobias 30 2790 24840 Himuro 30 2590 24840 Colmenares 30 2490 24840 Mavris 40 6490 6490 Weiss 50 7990 155950 Fripp 50 8190 155950 Kaufling 50 7890 155950 查詢按部門分區前後2筆進行求和 【視窗期】 14:24:33 SQL> set pagesize 500 14:24:59 SQL> col last_name format a20 14:24:59 SQL> select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary rows between 2 preceding and 2 following ) sum_sal 14:24:59 2 from employees; LAST_NAME DEPARTMENT_ID SALARY SUM_SAL -------------------- ------------- ---------- ---------- Whalen 10 4390 4390 --部門只有1行 Fay 20 5990 18980 Hartstein 20 12990 18980 Colmenares 30 2490 7870--部門30，前後2行求和，7870=2490+2590+2790 Himuro 30 2590 10760--部門30，前後2行求和，10760 =2490+2590+2790+2890 Tobias 30 2790 13850--部門30，前後2行求和，13850 =2490+2590+2790+2890+3090 Baida 30 2890 22350--部門30，前後2行求和，22350 =2590+2790+2890+3090+10990 Khoo 30 3090 19760 Raphaely 30 10990 16970 Mavris 40 6490 6490 Olson 50 2090 6470 Philtanker 50 2190 8860 Markle 50 2190 11250 Gee 50 2390 11650 Landry 50 2390 11950 Patel 50 2490 12250 Vargas 50 2490 12350 Marlow 50 2490 12450 Perkins 50 2490 12550 Sullivan 50 2490 12650 OConnell 50 2590 12750 範圍在200以內的【視窗期】 set pagesize 500 col last_name format a20 select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary range between 200 preceding and 200 following ) sum_sal from employees; 範圍無限制【視窗期】 set pagesize 500 col last_name format a20 select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary range between unbounded preceding and unbounded following ) sum_sal from employees; set pagesize 500 col last_name format a20 select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary rows between unbounded preceding and unbounded following ) sum_sal from employees;
AVG() OVER(PARTITION BY ... ORDER BY ...) 按分區求出平均值
14:39:56 SQL> set pagesize 500 14:39:56 SQL> col last_name format a20 14:39:56 SQL> select last_name,department_id,salary,avg(salary) over( partition by department_id ) avg_sal 14:39:56 2 from employees; LAST_NAME DEPARTMENT_ID SALARY AVG_SAL -------------------- ------------- ---------- ---------- Whalen 10 4390 4390 Hartstein 20 12990 9490 Fay 20 5990 9490 Raphaely 30 10990 4140 Khoo 30 3090 4140 Baida 30 2890 4140 Tobias 30 2790 4140 Himuro
