(Les20 Analytics Functions)[20180108]

2018-01-08 19:26:14来源:cnblogs.com作者:请点..头像人点击

分享
    学习目标        -了解分析函数作用和类型        -使用分析函数产生报告     分析函数        分析函数用于计算一些基于组的聚合值,它与聚合函数的区别在于,分析函数每组返回多行,聚合函数每组返回一行。     一般分析函数        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 50013:50:09 SQL> col last_name format a2013:50:09 SQL> select last_name,department_id,salary,row_number() over(partition by department_id order by salary ) row_num13:50:09   2  from employees;LAST_NAME            DEPARTMENT_ID     SALARY    ROW_NUM-------------------- ------------- ---------- ----------Whalen                          10       4390          1Fay                             20       5990          1Hartstein                       20      12990          2Colmenares                      30       2490          1Himuro                          30       2590          2Tobias                          30       2790          3Baida                           30       2890          4Khoo                            30       3090          5Raphaely                        30      10990          6Mavris                          40       6490          1
            RANK()   OVER(PARTITION BY ... ORDER BY ...)                             为查询返回的每一行并列排序,相同排名后的排名会出现跳号 
查询部门代码50,工资在3000~6000之间的排名情况13:56:59 SQL> set pagesize 50013:57:30 SQL> col last_name format a2013:57:30 SQL> select last_name,department_id,salary,rank() over(partition by department_id order by salary ) row_num13:57:30   2  from employees13:57:30   3  where department_id =5013:57:30   4  and salary between 3000 and 6000;LAST_NAME            DEPARTMENT_ID     SALARY    ROW_NUM-------------------- ------------- ---------- ----------Fleaur                          50       3090          1Walsh                           50       3090          1Davies                          50       3090          1Nayer                           50       3190          4--出现跳号McCain                          50       3190          4Taylor                          50       3190          4Stiles                          50       3190          4Bissot                          50       3290          8--出现跳号Mallin                          50       3290          8Dellinger                       50       3390         10--出现跳号Rajs                            50       3490         11Dilly                           50       3590         12Ladwig                          50       3590         12Chung                           50       3790         14--出现跳号Everett                         50       3890         15Bell                            50       3990         16Bull                            50       4090         17Sarchand                        50       4190         18Mourgos                         50       5790         19已選取 19 個資料列.
            DENSE_RANK() OVER(PARTITION BY ... ORDER BY ...)                为查询返回的每一行并列排序,相同排名后的排名不会跳号 
查询部门代码50,工资在3000~6000之间的排名情况14:01:48 SQL> set pagesize 50014:02:06 SQL> col last_name format a2014:02:06 SQL> select last_name,department_id,salary,dense_rank() over(partition by department_id order by salary ) row_num14:02:06   2  from employees14:02:06   3  where department_id =5014:02:06   4  and salary between 3000 and 6000;LAST_NAME            DEPARTMENT_ID     SALARY    ROW_NUM-------------------- ------------- ---------- ----------Fleaur                          50       3090          1Walsh                           50       3090          1Davies                          50       3090          1Nayer                           50       3190          2McCain                          50       3190          2Taylor                          50       3190          2Stiles                          50       3190          2Bissot                          50       3290          3Mallin                          50       3290          3Dellinger                       50       3390          4Rajs                            50       3490          5Dilly                           50       3590          6Ladwig                          50       3590          6Chung                           50       3790          7Everett                         50       3890          8Bell                            50       3990          9Bull                            50       4090         10Sarchand                        50       4190         11Mourgos                         50       5790         12已選取 19 個資料列.
            COUNT() OVER(PARTITION BY ... ORDER BY ...)                返回查询记录或分区的计数值(次数) 
14:11:48 SQL> set pagesize 50014:12:54 SQL> col last_name format a2014:12:54 SQL> select last_name,department_id,salary,count(salary) over( partition by department_id ) count_num14: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          6Baida                           30       2890          6Tobias                          30       2790          6Himuro                          30       2590          6Colmenares                      30       2490          6Mavris                          40       6490          1Weiss                           50       7990         45Fripp                           50       8190         45Kaufling                        50       7890         45Vollman                         50       6490         45
            MAX() OVER(PARTITION BY ... ORDER BY ...)                按分区返回最大的值 
14:12:55 SQL> set pagesize 50014:15:59 SQL> col last_name format a2014:15:59 SQL> select last_name,department_id,salary,max(salary) over( partition by department_id ) max_sal14:15:59   2  from employees14: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      10990Baida                           30       2890      10990Tobias                          30       2790      10990Himuro                          30       2590      10990Colmenares                      30       2490      10990Mavris                          40       6490       6490--部门40薪水最高的Weiss                           50       7990       8190--部门50薪水最高的Fripp                           50       8190       8190Kaufling                        50       7890       8190Vollman                         50       6490       8190Mourgos                         50       5790       8190
            MIN() OVER(PARTITION BY ... ORDER BY ...)                按分区返回最小的值 
14:16:00 SQL> set pagesize 50014:18:10 SQL> col last_name format a2014:18:10 SQL> select last_name,department_id,salary,min(salary) over( partition by department_id ) min_sal14:18:10   2  from employees;LAST_NAME            DEPARTMENT_ID     SALARY    MIN_SAL-------------------- ------------- ---------- ----------Whalen                          10       4390       4390Hartstein                       20      12990       5990Fay                             20       5990       5990Raphaely                        30      10990       2490Khoo                            30       3090       2490Baida                           30       2890       2490Tobias                          30       2790       2490Himuro                          30       2590       2490Colmenares                      30       2490       2490Mavris                          40       6490       6490Weiss                           50       7990       2090Fripp                           50       8190       2090Kaufling                        50       7890       2090Vollman                         50       6490       2090Mourgos                         50       5790       2090
            SUM() OVER(PARTITION BY ... ORDER BY ...)                按分区汇总求和 
查询所有薪水累积相加14:32:10 SQL> set pagesize 50014:32:40 SQL> col last_name format a2014:32:40 SQL> select last_name,department_id,salary,sum(salary) over( order   by salary ) sum_sal14:32:40   2  from employees14:32:40   3  where department_id=30; LAST_NAME            DEPARTMENT_ID     SALARY    SUM_SAL-------------------- ------------- ---------- ----------Colmenares                      30       2490       2490Himuro                          30       2590       5080Tobias                          30       2790       7870Baida                           30       2890      10760Khoo                            30       3090      13850Raphaely                        30      10990      24840已選取 6 個資料列.                    查询按部门分区进行求和14:19:28 SQL> set pagesize 50014:20:06 SQL> col last_name format a2014:20:06 SQL> select last_name,department_id,salary,sum(salary) over( partition by department_id ) sum_sal14:20:06   2  from employees;LAST_NAME            DEPARTMENT_ID     SALARY    SUM_SAL-------------------- ------------- ---------- ----------Whalen                          10       4390       4390Hartstein                       20      12990      18980Fay                             20       5990      18980Raphaely                        30      10990      24840Khoo                            30       3090      24840Baida                           30       2890      24840Tobias                          30       2790      24840Himuro                          30       2590      24840Colmenares                      30       2490      24840Mavris                          40       6490       6490Weiss                           50       7990     155950Fripp                           50       8190     155950Kaufling                        50       7890     155950                查询按部门分区前后2笔进行求和 【窗口期】14:24:33 SQL> set pagesize 50014:24:59 SQL> col last_name format a2014: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_sal14:24:59   2  from employees;LAST_NAME            DEPARTMENT_ID     SALARY    SUM_SAL-------------------- ------------- ---------- ----------Whalen                          10       4390       4390 --部门只有1行Fay                             20       5990      18980 Hartstein                       20      12990      18980Colmenares                      30       2490       7870--部门30,前后2行求和,7870=2490+2590+2790Himuro                          30       2590      10760--部门30,前后2行求和,10760 =2490+2590+2790+2890Tobias                          30       2790      13850--部门30,前后2行求和,13850 =2490+2590+2790+2890+3090Baida                           30       2890      22350--部门30,前后2行求和,22350 =2590+2790+2890+3090+10990Khoo                            30       3090      19760Raphaely                        30      10990      16970Mavris                          40       6490       6490Olson                           50       2090       6470Philtanker                      50       2190       8860Markle                          50       2190      11250Gee                             50       2390      11650Landry                          50       2390      11950Patel                           50       2490      12250Vargas                          50       2490      12350Marlow                          50       2490      12450Perkins                         50       2490      12550Sullivan                        50       2490      12650OConnell                        50       2590      12750 范围在200以内的【窗口期】set pagesize 500col last_name format a20select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary range between 200 preceding and 200 following ) sum_salfrom employees; 范围无限制【窗口期】set pagesize 500col last_name format a20select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary range between unbounded preceding and unbounded following ) sum_salfrom employees; set pagesize 500col last_name format a20select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary rows between unbounded preceding and unbounded following ) sum_salfrom employees;
            AVG() OVER(PARTITION BY ... ORDER BY ...)                按分区求出平均值            
14:39:56 SQL> set pagesize 50014:39:56 SQL> col last_name format a2014:39:56 SQL> select last_name,department_id,salary,avg(salary) over( partition by department_id ) avg_sal14:39:56   2  from employees;LAST_NAME            DEPARTMENT_ID     SALARY    AVG_SAL-------------------- ------------- ---------- ----------Whalen                          10       4390       4390Hartstein                       20      12990       9490Fay                             20       5990       9490Raphaely                        30      10990       4140Khoo                            30       3090       4140Baida                           30       2890       4140Tobias                          30       2790       4140Himuro                          30       2590       4140Colmenares                      30       2490       4140Mavris                          40       6490       6490Weiss                           50       7990 3465.55556Fripp                           50       8190 3465.55556Kaufling                        50       7890 3465.55556Vollman                         50       6490 3465.55556Mourgos                         50       5790 3465.55556

             FIRST_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 50014:50:48 SQL> col last_name format a2014: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_sal14:50:48   3  from employees;LAST_NAME            DEPARTMENT_ID     SALARY  FIRST_SAL   LAST_SAL-------------------- ------------- ---------- ---------- ----------Whalen                          10       4390       4390       4390Hartstein                       20      12990      12990       5990Fay                             20       5990      12990       5990Raphaely                        30      10990      10990       2490Khoo                            30       3090      10990       2490Baida                           30       2890      10990       2490Tobias                          30       2790      10990       2490Himuro                          30       2590      10990       2490Colmenares                      30       2490      10990       2490Mavris                          40       6490       6490       6490Weiss                           50       7990       7990       2590Fripp                           50       8190       7990       2590Kaufling                        50       7890       7990       2590
             LAG(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 50014:58:33 SQL> col last_name format a2014: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_sal14:58:33   4  from employees;LAST_NAME            DEPARTMENT_ID    LAG_SAL     SALARY   LEAD_SAL-------------------- ------------- ---------- ---------- ----------Whalen                          10          0       4390Fay                             20          0       5990      12990Hartstein                       20       5990      12990Colmenares                      30          0       2490       2590Himuro                          30       2490       2590       2790Tobias                          30       2590       2790       2890Baida                           30       2790       2890       3090Khoo                            30       2890       3090      10990Raphaely                        30       3090      10990Mavris                          40          0       6490Olson                           50          0       2090       2190Philtanker                      50       2090       2190       2190Markle                          50       2190       2190       2390Gee                             50       2190       2390       2390

学习总结:        1.了解分析函数和聚合(组)函数之间的差异        2.一般分析函数大致分为4类            伪列类:row_number() over()、rank() over()和dense_rank() over()            计算类:sum() over()、count() over()、max() over()、min() over()和avg() over()            返回值类:first_value() over()和last_value()  over()            偏移类:lag() over()和lead() over

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台