数据库初级培训(第2讲).ppt

数据库基础培训(第二讲),上海全成通信技术有限公司 2012-09 金刚(),培训大纲,一、分析函数 二、GROUP BY扩展 三、其它特性,Oracle 分析函数,简介OLAP的系统(即Online Aanalyse Process)，这些系统一般用于系统决策使用。通常和数据仓库、数据分析、数据挖掘等概念联系在一起。这些系统的特点是数据量大，对实时响应的要求不高或者根本不关注这方面的要求，以查询、统计操作为主。我们来看看下面的几个典型例子：查找上一年度各个销售区域排名前10的员工按区域查找上一年度订单总额占区域订单总额20%以上的客户查找上一年度销售最差的部门所在的区域查找上一年度销售最好和最差的产品 我们看看上面的几个例子就可以感觉到这几个查询和我们日常遇到的查询有些不同，具体有：需要对同样的数据进行不同级别的聚合操作需要在表内将多条数据和同一条数据进行多次的比较需要在排序完的结果集上进行额外的过滤操作,3,Oracle 分析函数,分析函数语法 FUNCTION_NAME(,.)OVER()例:sum(sal)over(partition by deptno order by ename)sum就是函数名sal是分析函数的参数,每个函数有03个参数,参数可以是表达式,例如:sum(sal+comm)over 是一个关键字,用于标识分析函数,否则查询分析器不能区别sum()聚集函数和sum()分析函数partition by deptno 是可选的分区子句,如果不存在任何分区子句,则全部的结果集可看作一个单一的分区order by ename 是可选的order by 子句,有些函数需要它,有些则不需要.依靠已排序数据的那些函数,如:用于访问结果集中前一行和后一行的LAG和LEAD,必须使用；其它函数,如AVG,则不需要.在使用了任何排序的开窗函数时,该子句是强制性的,它指定了在计算分析函数时一组内的数据是如何排序的.,4,Oracle 分析函数,1)FUNCTION子句 ORACLE提供了26个分析函数,按功能分5类:等级(ranking)函数:用于寻找前N个排名开窗(windowing)函数:用于计算不同的累计,如SUM,COUNT,AVG,MIN,MAX等,作用于数据的一个窗口上 例:sum(t.sal)over(order by t.deptno,t.ename)running_total,sum(t.sal)over(partition by t.deptno order by t.ename)department_total,5,Oracle 分析函数,1)FUNCTION子句 制表(reporting)函数:与开窗函数同名,作用于一个分区或一组上的所有列，例:sum(t.sal)over()running_total2,sum(t.sal)over(partition by t.deptno)department_total2制表函数与开窗函数的关键不同之处在于OVER语句上缺少一个ORDER BY子句!LAG,LEAD函数:这类函数允许在结果集中向前或向后检索值,为了避免数据的自连接,它们是非常有用的.VAR_POP,VAR_SAMP,STDEV_POP,STDDEV_SAMP,STDDEV及线性回归函数:计算任何未排序分区的统计值,6,功能描述：开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化例子：over（order by salary）按照salary排序进行累计，order by是个默认的开窗函数 over（order by salary range between 50 preceding and 150 following）每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150 over（order by salary rows between 50 preceding and 150 following）每行对应的数据窗口是之前50行(含)，之后150行（含）over（order by salary rows between unbounded preceding and unbounded following）每行对应的数据窗口是从第一行到最后一行，等效：over（order by salary range between unbounded preceding and unbounded following）,OVER,Oracle 分析函数,2)PARTITION子句 按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组 3)ORDER BY子句分析函数中ORDERBY的存在将添加一个默认的开窗子句,这意味着计算中所使用的行的集合是当前分区中当前行和前面所有行,没有ORDER BY时,默认的窗口是全部的分区在Order by 子句后可以添加nulls last,如:order by comm desc nulls last表示排序时空值放在最后 nulls first|nulls last允许指定NULLS是出现在开始还是最后。,8,SQL select ename,sal,avg(sal)over()from emp;ENAME SAL AVG(SAL)OVER()-SMITH 800.00 2077.083333333ALLEN 1600.00 2077.083333333WARD 1250.00 2077.083333333JONES 2975.00 2077.083333333MARTIN 1250.00 2077.083333333BLAKE 2850.00 2077.083333333CLARK 2450.00 2077.083333333KING 5000.00 2077.083333333TURNER 1500.00 2077.083333333JAMES 950.00 2077.083333333FORD 3000.00 2077.083333333MILLER 1300.00 2077.083333333,Order By 子句,在没有Order by子句时，在全部组上计算平均值，每一行给一个同样的值。在用带有Order by的AVG（）时，每一行的平均值是那一行与前面所有行的平均值（此处用作开窗函数）,select ename,deptno,sum(sal)over(order by ename,deptno)sum_ename_deptno,sum(sal)over(order by deptno,ename)sum_deptno_enamefrom emporder by ename,deptnoENAME DEPTNO SUM_ENAME_DEPTNO SUM_DEPTNO_ENAME-ADAMS 20 1100 7400ALLEN 30 2700 23739.5BLAKE 30 5550 26589.5CLARK 20 8514.5 10364.5FORD 20 12514.5 14364.5JAMES 30 13464.5 27539.5JONES 20 16439.5 17339.5KING 10 21439.5 5000MARTIN 30 22689.5 28789.5MILLER 10 23989.5 6300SCOTT 20 27989.5 21339.5SMITH 20 28789.5 22139.5TURNER 30 30289.5 30289.5WARD 30 31539.5 31539.5,Order By 子句,Oracle 分析函数,4)WINDOWING子句 用于定义分析函数将在其上操作的行的集合 Windowing子句给出了一个定义变化或固定的数据窗口的方法,分析函数将对这些数据进行操作 默认的窗口是一个固定的窗口,仅仅在一组的第一行开始,一直继续到当前行,要使用窗口,必须使用ORDER BY子句 根据两个标准可以建立窗口:数据值的范围(RANGE)或与当前行的行偏移量(ROWS).,11,Oracle 分析函数,RANGE窗口仅对NUMBER和DATE起作用,因为不可能从VARCHAR2中增加或减去N个单元另外的限制是ORDER BY中只能有一列,因而范围实际上是一维的,不能在N维空间中 例:统计前100天平均工资 avg(t.sal)over(order by t.hiredate asc range 100 preceding),12,Oracle 分析函数,Row窗口利用ROW特性,就没有RANGE那样的限制了,数据可以是任何类型,且ORDER BY 可以包括很多列 Specifying窗口UNBOUNDED PRECEDING:这个窗口从当前分区的第一行开始,并结束于正在处理的当前行CURRENT ROW:该窗口从当前行开始(或结束)Numeric Expression PRECEDING:对该窗口从当前行之前的数字表达式(Numeric Expression)的行开始。对ROWS来说,从行序值小于数字表达式的当前行的值开始.Numeric Expression FOLLOWING:该窗口在当前行Numeric Expression行之后的行终止(或开始),且从行序值大于当前行Numeric Expression行的范围开始(或终止)ROWS between 100 preceding and 5 preceding:当前行前5到100行,13,Oracle 分析函数,14,Oracle 分析函数,15,Oracle 分析函数,16,Oracle 分析函数,17,select deptno,ename,sal,sum(sal)over(partition by deptno order by ename rows 2 preceding)sliding_totalfrom emporder by deptno,ename,Windowing 子句,DEPTNO ENAME SAL SLIDING_TOTAL-10 KING 5000.00 5000 10 MILLER 1300.00 6300 20 ADAMS 1100.00 1100 20 CLARK 2964.50 4064.5 20 FORD 4000.00 8064.5 20 JONES 2975.00 9939.5 20 SCOTT 4000.00 10975 20 SMITH 800.00 7775 30 ALLEN 1600.00 1600 30 BLAKE 2850.00 4450 30 JAMES 950.00 5400 30 MARTIN 1250.00 5050 30 TURNER 1500.00 3700 30 WARD 1250.00 400014 rows selected,select ename,sal,hiredate,hiredate-100 windowtop,first_value(ename)over(order by hiredate asc range 100 preceding)ename_prec,first_value(hiredate)over(order by hiredate asc range 100 preceding)hiredate_prec from emp order by hiredate asc,Range 窗口,ENAME SAL HIREDATE WINDOWTOP ENAME_PREC HIREDATE_PREC-SMITH 800.00 1980-12-17 1980-9-8 SMITH 1980-12-17ALLEN 1600.00 1981-2-20 1980-11-12 SMITH 1980-12-17WARD 1250.00 1981-2-22 1980-11-14 SMITH 1980-12-17JONES 2975.00 1981-4-2 1980-12-23 ALLEN 1981-2-20BLAKE 2850.00 1981-5-1 1981-1-21 ALLEN 1981-2-20CLARK 2964.50 1981-6-9 1981-3-1 JONES 1981-4-2TURNER 1500.00 1981-9-8 1981-5-31 CLARK 1981-6-9MARTIN 1250.00 1981-9-28 1981-6-20 TURNER 1981-9-8KING 5000.00 1981-11-17 1981-8-9 TURNER 1981-9-8FORD 4000.00 1981-12-3 1981-8-25 TURNER 1981-9-8JAMES 950.00 1981-12-3 1981-8-25 TURNER 1981-9-8MILLER 1300.00 1982-1-23 1981-10-15 KING 1981-11-17SCOTT 4000.00 1987-4-19 1987-1-9 SCOTT 1987-4-19ADAMS 1100.00 1987-5-23 1987-2-12 SCOTT 1987-4-1914 rows selected,select ename,sal,hiredate,first_value(ename)over(order by hiredate asc rows 5 preceding)ename_prec,first_value(hiredate)over(order by hiredate asc rows 5 preceding)hiredate_precfrom emporder by hiredate asc,ROW 窗口,ENAME SAL HIREDATE ENAME_PREC HIREDATE_PREC-SMITH 800.00 1980-12-17 SMITH 1980-12-17ALLEN 1600.00 1981-2-20 SMITH 1980-12-17WARD 1250.00 1981-2-22 SMITH 1980-12-17JONES 2975.00 1981-4-2 SMITH 1980-12-17BLAKE 2850.00 1981-5-1 SMITH 1980-12-17CLARK 2964.50 1981-6-9 SMITH 1980-12-17TURNER 1500.00 1981-9-8 ALLEN 1981-2-20MARTIN 1250.00 1981-9-28 WARD 1981-2-22KING 5000.00 1981-11-17 JONES 1981-4-2JAMES 950.00 1981-12-3 BLAKE 1981-5-1FORD 4000.00 1981-12-3 CLARK 1981-6-9MILLER 1300.00 1982-1-23 TURNER 1981-9-8SCOTT 4000.00 1987-4-19 MARTIN 1981-9-28ADAMS 1100.00 1987-5-23 KING 1981-11-1714 rows selected,select ename,hiredate,sal,avg(sal)over(order by hiredate ascrows 5 preceding)avg_5_before,count(*)over(order by hiredate ascrows 5 preceding)obs_before,avg(sal)over(order by hiredate desc rows 5 preceding)avg_5_after,count(*)over(order by hiredate desc rows 5 preceding)obs_afterfrom emporder by hiredate asc,ROW 窗口,ENAME HIREDATE SAL AVG_5_BEFORE OBS_BEFORE AVG_5_AFTER OBS_AFTER-SMITH 1980-12-17 800.00 800 1 2073.25 6ALLEN 1981-2-20 1600.00 1200 2 2189.916666 6WARD 1981-2-22 1250.00 1216.6666666 3 2131.583333 6JONES 1981-4-2 2975.00 1656.25 4 2756.583333 6BLAKE 1981-5-1 2850.00 1895 5 2927.416666 6CLARK 1981-6-9 2964.50 2073.25 6 2610.75 6TURNER 1981-9-8 1500.00 2189.9166666 6 2333.333333 6MARTIN 1981-9-28 1250.00 2131.5833333 6 2750 6KING 1981-11-17 5000.00 2756.5833333 6 2725 6JAMES 1981-12-3 950.00 2419.0833333 6 1837.5 4FORD 1981-12-3 4000.00 2610.75 6 2270 5MILLER 1982-1-23 1300.00 2333.3333333 6 2133.333333 3SCOTT 1987-4-19 4000.00 2750 6 2550 2ADAMS 1987-5-23 1100.00 2725 6 1100 114 rows selected,select deptno,ename,hiredate,count(*)over(partition by deptno order by hiredate nulls first range 100 preceding)cnt_range,count(*)over(partition by deptno order by hiredate nulls first rows 2 preceding)cnt_rowsfrom empwhere deptno in(10,20)order by deptno,hiredate;,Specifying 窗口,DEPTNO ENAME HIREDATE CNT_RANGE CNT_ROWS-10 CLARK 1981-06-09 1 1 10 KING 1981-11-17 1 2 10 MILLER 1982-01-23 2 3 20 SMITH 1980-12-17 1 1 20 JONES 1981-04-02 1 2 20 FORD 1981-12-03 1 3,select deptno,ename,sal,dense_rank()over(partition by deptno order by sal desc)dr,rank()over(partition by deptno order by sal desc)r from emp order by deptno,sal desc,分析函数使用实例,若要使用RANK，并遇到有相同值的情况，看看会发生什么情况？,DEPTNO ENAME SAL DR R-10 KING 5000.00 1 1 10 MILLER 1300.00 2 2 20 SCOTT 4000.00 1 1 20 FORD 4000.00 1 1 20 JONES 2975.00 2 3 20 CLARK 2964.50 3 4 20 ADAMS 1100.00 4 5 20 SMITH 800.00 5 6 30 BLAKE 2850.00 1 1 30 ALLEN 1600.00 2 2 30 TURNER 1500.00 3 3 30 WARD 1250.00 4 4 30 MARTIN 1250.00 4 4 30 JAMES 950.00 5 614 rows selected,select*from(select deptno,ename,sal,count(*)over(partition by deptno order by sal desc range unbounded preceding)cnt,count(*)over(partition by deptno order by sal desc,ename range unbounded preceding)cnt2 from emp)where cnt=3 order by deptno,sal desc;,分析函数使用实例,DEPTNO ENAME SAL CNT CNT2-10 KING 5000 1 1 10 CLARK 2450 2 2 10 MILLER 1300 3 3 20 FORD 3000 2 1 20 SCOTT 3000 2 2 20 JONES 2975 3 3 30 BLAKE 2850 1 1 30 ALLEN 1600 2 2 30 TURNER 1500 3 39 rows selected.,select*from(select deptno,ename,sal,row_number()over(partition by deptno order by sal desc)cnt from emp)where cnt=3order by deptno,sal desc,分析函数使用实例,DEPTNO ENAME SAL CNT-10 KING 5000.00 1 10 MILLER 1300.00 2 20 SCOTT 4000.00 1 20 FORD 4000.00 2 20 JONES 2975.00 3 30 BLAKE 2850.00 1 30 ALLEN 1600.00 2 30 TURNER 1500.00 38 rows selected,select ename,hiredate,sal,rnfrom(select ename,hiredate,sal,row_number()over(order by ename)rn from emp)where rn between 5 and 10 order by rn,分析函数使用实例,ENAME HIREDATE SAL RN-JAMES 1981-12-03 950 5JONES 1981-04-02 2975 6KING 1981-11-17 5000 7MARTIN 1981-09-28 1250 8MILLER 1982-01-23 3000 9SMITH 1980-12-17 3000 106 rows selected.,分析函数使用实例,问题：行列转换问题。Pivot查询是这样的，当你需要取出一些如下的数据时：,分析函数使用实例,DEPTNO HIGHEST_PAID SECOND_HIGHEST THIRD_HIGHEST-10 KING MILLER 20 SCOTT FORD JONES 30 BLAKE ALLEN TURNER,select deptno,max(decode(seq,1,ename,null)highest_paid,max(decode(seq,2,ename,null)second_highest,max(decode(seq,3,ename,null)third_highestfrom(select deptno,ename,row_number()over(partition by deptno order by sal desc nulls last)seq from emp)where seq=3 group by deptno,功能描述：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号语法：ROW_NUMBER()OVER(query_partition_clause order_by_clause)例子：下例返回每个员工再在每个部门中按员工号排序后的顺序号SELECT department_id,last_name,employee_id,ROW_NUMBER()OVER(PARTITION BY department_id ORDER BY employee_id)AS emp_id FROM employees WHERE department_id 50;,ROW_NUMBER,功能描述：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置语法：RANK()OVER(query_partition_clause order_by_clause)例子：下例中计算每个员工按部门分区再按薪水排序，依次出现的序列号（注意与DENSE_RANK函数的区别）SELECT d.department_id,e.last_name,e.salary,RANK()OVER(PARTITION BY e.department_id ORDER BY e.salary)as drank FROM employees e,departments d WHERE e.department_id=d.department_id AND d.department_id IN(60,90);,RANK,功能描述：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置语法：RANK()OVER(query_partition_clause order_by_clause)例子：下例中计算每个员工按部门分区再按薪水排序，依次出现的序列号（注意与DENSE_RANK函数的区别）SELECT d.department_id,e.last_name,e.salary,DENSE_RANK()OVER(PARTITION BY e.department_id ORDER BY e.salary)as drank FROM employees e,departments d WHERE e.department_id=d.department_id AND d.department_id IN(60,90);,DENSE_RANK,功能描述：和CUME_DIST（累积分配）函数类似，对于一个组中给定的行来说，在计算那行的序号时，先减1，然后除以n-1（n为组中所有的行数）。该函数总是返回0,1之间的数 注意：对于重复行，计算时取重复行中的第一行的位置。例如：在一个5行的组中，返回结果分布值依次为：0,0.25.0.5,0.75,1语法：PERCENT_RANK()OVER(query_partition_clause order_by_clause)例子：下例中如果Khoo的salary为2900，则pr值为0.6，因为RANK函数对于等值的返回序列值是一样的SELECT department_id,last_name,salary,PERCENT_RANK()OVER(PARTITION BY department_id ORDER BY salary)AS pr FROM employees WHERE department_id 100 ORDER BY department_id,salary;,PERCENT_RANK,功能描述：返回组中数据窗口的第一个值语法：FIRST_VALUE(expr)OVER(analytic_clause)例子：下面例子计算按部门分区按薪水排序的数据窗口的第一个值对应的名字，如果薪水的第一个值有多个，则从多个对应的名字中取缺省排序的第一个名字SELECT department_id,last_name,salary,FIRST_VALUE(last_name)OVER(PARTITION BY department_id ORDER BY salary ASC)AS lowest_sal FROM employees WHERE department_id in(20,30);,FIRST_VALUE,功能描述：可以访问结果集中的其它行而不用进行自连接。它允许去处理游标，就好像游标是一个数组一样。在给定组中可参考当前行之前的行，这样就可以从组中与当前行一起选择以前的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行），其相反的函数是LEAD语法：LAG(value_expr,offset,default)OVER(query_partition_clause order_by_clause)例子：下面的例子中列prev_sal返回按hire_date排序的前1行的salary值SELECT last_name,hire_date,salary,LAG(salary,1,0)OVER(ORDER BY hire_date)AS prev_sal FROM employees WHERE job_id=PU_CLERK;,LAG,功能描述：用于计算一个组和数据窗口内表达式的平均值语法：AVG(DISTINCT|ALL expr)OVER(analytic_clause)例子：下面的例子中列c_mavg计算员工表中每个员工的平均薪水报告，该平均值由当前员工和与之具有相同经理的前一个和后一个三者的平均数得来；SELECT manager_id,last_name,hire_date,salary,AVG(salary)OVER(PARTITION BY manager_id ORDER BY hire_date rows BETWEEN 1 PRECEDING AND 3 FOLLOWING)AS c_mavg FROM employees;,AVG,功能描述：该函数计算expression/(sum(expression)的值，它给出相对于总数的百分比，即当前行对sum(expression)的贡献语法：RATIO_TO_REPORT(expr)OVER(query_partition_clause)例子：下例计算每个员工的工资占该类员工总工资的百分比SELECT last_name,salary,RATIO_TO_REPORT(salary)OVER()AS rr FROM employees WHERE job_id=PU_CLERK;LAST_NAME SALARY RR-Khoo 3100.223021583Baida 2900.208633094Tobias 2800.201438849Himuro 2600.18705036Colmenares 2500.179856115,RATIO_TO_REPORT,示例：公司部门中入厂时间最早的员工的薪水最小是多少SqlSELECT deptno,ename,empno,hiredate,sal,MIN(sal)KEEP(dense_rank FIRST ORDER BY hiredate)OVER(Partition BY deptno)min_sal FROM emp;DEPTNO ENAME EMPNO HIREDATE SAL MIN_SAL-10 KING 7839 1981-11-17 5000 2450 10 CLARK 7782 1981-06-09 2450 2450 10 MILLER 7934 1982-01-23 3000 2450 20 FORD 7902 1981-12-03 3000 3000 20 SMITH 7369 1980-12-17 3000 3000 20 JONES 7566 1981-04-02 2975 3000 30 JAMES 7900 1981-12-03 950 1600 30 TURNER 7844 1981-09-08 1500 1600 30 MARTIN 7654 1981-09-28 1250 1600 30 WARD 7521 1981-02-22 1250 1600 30 ALLEN 7499 1981-02-20 1600 1600 30 BLAKE 7698 1981-02-20 2850 160012 rows selected.,FIRST,功能：把有序数据集合平均分配到expr制定的数据量的桶中，将桶号分配给每一行。如果不能平均分配，则较小桶号的桶分配额外的行，并且各个桶中能放的行数最多相差1.示例：SELECT ename,sal,deptno,ntile(4)over(partition by deptno order by sal)nl FROM emp;ENAME SAL DEPTNO NL-CLARK 2450 10 1MILLER 3000 10 2KING 5000 10 3JONES 2975 20 1SMITH 3000 20 2FORD 3000 20 3JAMES 950 30 1WARD 1250 30 1MARTIN 1250 30 2TURNER 1500 30 2ALLEN 1600 30 3BLAKE 2850 30 412 rows selected.,NTILE,培训大纲,一、分析函数 二、GROUP BY扩展 三、其它特性,使用 ROLLUP 操作分组使用 CUBE 操作分组使用 GROUPING 函数处理 ROLLUP 或 CUBE操作所产生的空值使用 GROUPING SETS 创建分组集在 GROUP BY 子句中组合分组:复合列连接分组集,Group By 扩展,ROLLUP 和CUBE,使用带有ROLLUP 和 CUBE 操作的GROUP BY 子句产生多种分组结果ROLLUP 产生n+1种分组结果 rollup(a,b)=group by a,b union all group by a union all group by()CUBE 产生2的n次方种分组结果 cube(a,b)=group by a,b union all group by b union all group by a union all group by(),ROLLUP 操作符,ROLLUP 是对 GROUP BY 子句的扩展ROLLUP 产生n+1种分组结果，顺序是从右向左,SELECTcolumn,group_function(column).FROMtableWHEREconditionGROUP BYROLLUP group_by_expressionHAVING having_expression;ORDER BYcolumn;,ROLLUP 应用举例,1,2,3,CUBE 操作符,CUBE是对 GROUP BY 子句的扩展CUBE 会产生类似于笛卡尔集的分组结果,SELECTcolumn,group_function(column).FROMtableWHEREconditionGROUP BYCUBE group_by_expressionHAVING having_expressionORDER BYcolumn;,CUBE 应用举例,SELECT department_id,job_id,SUM(salary)FROM employees WHERE department_id 60GROUP BY CUBE(department_id,job_id);,1,2,3,4,GROUPING 函数,GROUPING 函数可以和 CUBE 或 ROLLUP 结合使用使用 GROUPING 函数，可以找到哪些列在该行中参加了分组使用 GROUPING 函数,可以区分空值产生的原因GROUPING 函数返回 0 或 1 GROUPING_ID可以接受多个列，例如：grouping_id(a,b,c)=4*grouping(a)+2*group