分析函数,它与聚合函数的界别在于

    学习目的

    学习目的

        -理解分析函数成效和项目

        -领会分析函数功能和花色

        -使用分析函数爆发报告

        -使用分析函数暴发报告

 

 

    分析函数

    分析函数

       
分析函数用于计算一些依据组的聚合值,它与聚合函数的分别在于,分析函数每组再次回到多行,聚合函数每组重返一行。

       
分析函数用于统计一些根据组的聚合值,它与聚合函数的区分在于,分析函数每组重返多行,聚合函数每组再次回到一行。

 

 

    一般分析函数

    一般分析函数

        ROW_NUMBER() OVER(PARTITION BY … ORDER BY …) 
按分区或重返记录生成唯一编号

        ROW_NUMBER() OVER(PARTITION BY … ORDER BY …) 
按分区或回到记录生成唯一编号

        RANK() OVER(PARTITION BY … ORDER BY …) 
按分区或回到记录排序,会跳号

        RANK() OVER(PARTITION BY … ORDER BY …) 
按分区或重返记录排序,会跳号

        DENSE_RANK() OVER(PARTITION BY … ORDER BY
…)  按分区或回到记录排序,不跳号

        DENSE_RANK() OVER(PARTITION BY … ORDER BY
…)  按分区或回到记录排序,不跳号

        COUNT() OVER(PARTITION BY … ORDER BY
…) 按分区或再次来到记录进行计数

        COUNT() OVER(PARTITION BY … ORDER BY
…) 按分区或重回记录举办计数

        MAX() OVER(PARTITION BY … ORDER BY
…) 按分区或回到记录计算最大值 

        MAX() OVER(PARTITION BY … ORDER BY
…) 按分区或回到记录统计最大值 

        MIN() OVER(PARTITION BY … ORDER BY
…) 按分区或重回记录总计最小值  

        MIN() OVER(PARTITION BY … ORDER BY
…) 按分区或再次回到记录总计最小值  

        SUM() OVER(PARTITION BY … ORDER BY
…) 按分区或回到记录进行求和

        SUM() OVER(PARTITION BY … ORDER BY
…) 按分区或回到记录举行求和

        AVG() OVER(PARTITION BY … ORDER BY
…) 按分区或再次来到记录求出平均值

        AVG() OVER(PARTITION BY … ORDER BY
…) 按分区或回到记录求出平均值

        FIRST_VALUE() OVER(PARTITION BY … ORDER BY
…) 按分区或重临记录的首个值

        FIRST_VALUE() OVER(PARTITION BY … ORDER BY
…) 按分区或再次回到记录的第二个值

        LAST_VALUE() OVER(PARTITION BY … ORDER BY
…) 按分区或回到记录的最后一个值

        LAST_VALUE() OVER(PARTITION BY … ORDER BY
…) 按分区或回到记录的末梢一个值

        LAG() OVER(PARTITION BY … ORDER BY
…) 按分区或重临记录上偏移值

        LAG() OVER(PARTITION BY … ORDER BY
…) 按分区或重回记录上偏移值

        LEAD() OVER(PARTITION BY … ORDER BY
…) 按分区或回到记录下偏移值

        LEAD() OVER(PARTITION BY … ORDER BY
…) 按分区或重临记录下偏移值

 

 

        函数语法表达

        函数语法表明

            PARITION BY 

            PARITION BY 

                Function_name(…) Over(partition by col_name)

                Function_name(…) Over(partition by col_name)

                用于分区,按列进行分区

                用于分区,按列进行分区

            ORDER BY (暗许窗口)

            ORDER BY (默许窗口)

                Function_name(…) Over(Order by col_name [rows |
range between n|unbounded preceding and n| unbounded following])

                Function_name(…) Over(Order by col_name [rows |
range between n|unbounded preceding and n| unbounded following])

                -rows:【行】前n行、后n行或当前行,用于计算

                -rows:【行】前n行、后n行或当前行,用于总括

                -range:【范围】大于或小于当前值的n行,或接纳前n行来计量

                -range:【范围】大于或小于当前值的n行,或利用前n行来测算

                -unbounded:【无界限】所有行都使用总括

                -unbounded:【无界限】所有行都使用计算

 

 

        ROW_NUMBER() OVER(partition by … order by …)

        ROW_NUMBER() OVER(partition by … order by …)

                效率与rownum伪列类似,order
by子句中指定有连串,从1起头为分区中的每一行或询问重返的每一行分配一个唯一的编号。

                功效与rownum伪列类似,order
by子句中指定有连串,从1方始为分区中的每一行或询问重返的每一行分配一个唯一的数码。

                

                

                注意ROW_NUMBER(那里无法限定列名)

                注意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
查询按部门代码求出薪水排名:
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 …)             

            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 個資料列.
查询部门代码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 …)

            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 個資料列.
查询部门代码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 …)

            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
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 …)

            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
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 …)

            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
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 …)

            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;
查询所有薪水累积相加
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 …)

            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                          30       2590       4140
Colmenares                      30       2490       4140
Mavris                          40       6490       6490
Weiss                           50       7990 3465.55556
Fripp                           50       8190 3465.55556
Kaufling                        50       7890 3465.55556
Vollman                         50       6490 3465.55556
Mourgos                         50       5790 3465.55556
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                          30       2590       4140
Colmenares                      30       2490       4140
Mavris                          40       6490       6490
Weiss                           50       7990 3465.55556
Fripp                           50       8190 3465.55556
Kaufling                        50       7890 3465.55556
Vollman                         50       6490 3465.55556
Mourgos                         50       5790 3465.55556

             FIRST_VALUE() OVER(PARTITION BY … ORDER BY
….) 重回一组有序的值中的首个值。
若是集合中的第四个值为空,则函数将赶回null,除非指定IGNORE NULLS

             FIRST_VALUE() OVER(PARTITION BY … ORDER BY
….) 重回一组有序的值中的首个值。
借使集合中的第四个值为空,则函数将回来null,除非指定IGNORE NULLS

            LAST_VALUE() OVER(PARTITION BY … ORDER BY
….)  重回一组有序的值中的最终一个值。
假若集合中的最后一个值为空,则函数将回到null,除非指定IGNORE NULLS。

            LAST_VALUE() OVER(PARTITION BY … ORDER BY
….)  再次回到一组有序的值中的最终一个值。
尽管集合中的最后一个值为空,则函数将回到null,除非指定IGNORE NULLS。

 

 

14:49:16 SQL> set pagesize 500
14:50:48 SQL> col last_name format a20
14:50:48 SQL> select last_name,department_id,salary,first_value(salary) over( partition by department_id) first_sal,
14:50:48   2  last_value(salary) over( partition by department_id ) last_sal
14:50:48   3  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY  FIRST_SAL   LAST_SAL
-------------------- ------------- ---------- ---------- ----------
Whalen                          10       4390       4390       4390
Hartstein                       20      12990      12990       5990
Fay                             20       5990      12990       5990
Raphaely                        30      10990      10990       2490
Khoo                            30       3090      10990       2490
Baida                           30       2890      10990       2490
Tobias                          30       2790      10990       2490
Himuro                          30       2590      10990       2490
Colmenares                      30       2490      10990       2490
Mavris                          40       6490       6490       6490
Weiss                           50       7990       7990       2590
Fripp                           50       8190       7990       2590
Kaufling                        50       7890       7990       2590
14:49:16 SQL> set pagesize 500
14:50:48 SQL> col last_name format a20
14:50:48 SQL> select last_name,department_id,salary,first_value(salary) over( partition by department_id) first_sal,
14:50:48   2  last_value(salary) over( partition by department_id ) last_sal
14:50:48   3  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY  FIRST_SAL   LAST_SAL
-------------------- ------------- ---------- ---------- ----------
Whalen                          10       4390       4390       4390
Hartstein                       20      12990      12990       5990
Fay                             20       5990      12990       5990
Raphaely                        30      10990      10990       2490
Khoo                            30       3090      10990       2490
Baida                           30       2890      10990       2490
Tobias                          30       2790      10990       2490
Himuro                          30       2590      10990       2490
Colmenares                      30       2490      10990       2490
Mavris                          40       6490       6490       6490
Weiss                           50       7990       7990       2590
Fripp                           50       8190       7990       2590
Kaufling                        50       7890       7990       2590

 

 

            LAG(column,n,default) OVER(PARTITION BY … ORDER BY …) 
 上偏移地点的重返值,n表示偏移量(正整数),default指定暗许值

            LAG(column,n,default) OVER(PARTITION BY … ORDER BY …) 
 上偏移地方的再次来到值,n表示偏移量(正整数),default指定默认值

            LEAD(column,n,default) OVER(PARTITION BY … ORDER BY …) 
下偏移地方的再次来到值,n表示偏移量(正整数) ,default指定默许值

            LEAD(column,n,default) OVER(PARTITION BY … ORDER BY …) 
下偏移地点的再次来到值,n表示偏移量(正整数) ,default指定暗许值

            

            

14:58:21 SQL> set pagesize 500
14:58:33 SQL> col last_name format a20
14:58:33 SQL> select last_name,department_id,lag(salary,1,0) over( partition by department_id order by salary) lag_sal,
14:58:33   2  salary,
14:58:33   3  lead(salary,1,NULL) over( partition by department_id order by salary) lead_sal
14:58:33   4  from employees;
LAST_NAME            DEPARTMENT_ID    LAG_SAL     SALARY   LEAD_SAL
-------------------- ------------- ---------- ---------- ----------
Whalen                          10          0       4390
Fay                             20          0       5990      12990
Hartstein                       20       5990      12990
Colmenares                      30          0       2490       2590
Himuro                          30       2490       2590       2790
Tobias                          30       2590       2790       2890
Baida                           30       2790       2890       3090
Khoo                            30       2890       3090      10990
Raphaely                        30       3090      10990
Mavris                          40          0       6490
Olson                           50          0       2090       2190
Philtanker                      50       2090       2190       2190
Markle                          50       2190       2190       2390
Gee                             50       2190       2390       2390
14:58:21 SQL> set pagesize 500
14:58:33 SQL> col last_name format a20
14:58:33 SQL> select last_name,department_id,lag(salary,1,0) over( partition by department_id order by salary) lag_sal,
14:58:33   2  salary,
14:58:33   3  lead(salary,1,NULL) over( partition by department_id order by salary) lead_sal
14:58:33   4  from employees;
LAST_NAME            DEPARTMENT_ID    LAG_SAL     SALARY   LEAD_SAL
-------------------- ------------- ---------- ---------- ----------
Whalen                          10          0       4390
Fay                             20          0       5990      12990
Hartstein                       20       5990      12990
Colmenares                      30          0       2490       2590
Himuro                          30       2490       2590       2790
Tobias                          30       2590       2790       2890
Baida                           30       2790       2890       3090
Khoo                            30       2890       3090      10990
Raphaely                        30       3090      10990
Mavris                          40          0       6490
Olson                           50          0       2090       2190
Philtanker                      50       2090       2190       2190
Markle                          50       2190       2190       2390
Gee                             50       2190       2390       2390

图片 1

图片 2

上学总计:

学学总计:

        1.驾驭分析函数和集合(组)函数之间的差距

        1.叩问分析函数和聚集(组)函数之间的距离

        2.相似分析函数大约分成4类

        2.貌似分析函数大概分为4类

            伪列类:row_number() over()、rank() over()和dense_rank()
over()

            伪列类:row_number() over()、rank() over()和dense_rank()
over()

            计算类:sum() over()、count() over()、max() over()、min()
over()和avg() over()

            计算类:sum() over()、count() over()、max() over()、min()
over()和avg() over()

            重临值类:first_value() over()和last_value()  over()

            重临值类:first_value() over()和last_value()  over()

            偏移类:lag() over()和lead() over

            偏移类:lag() over()和lead() over

相关文章