1 / 81

Chapter 4

Chapter 4. Aggregating Data and Group Functions. Group Function Fundamentals (1/5).

kesler
Download Presentation

Chapter 4

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 4 Aggregating Data and Group Functions

  2. Group Function Fundamentals (1/5) • Group functions are sometimes called aggregate functions and return a value based on a number of inputs. The exact number of inputs is not determined until the query is executed and all rows are fetched. This differs from single-row functions, in which the number of inputs is known at parse time--before the query is executed. Because of this difference, group functions have slightly different requirements and behavior than single-row functions.

  3. Group Function Fundamentals (2/5) • Group functions do not process NULL values and do not return a NULL value, even when NULLs are the only values evaluated. For example, a COUNT or SUM of NULL values will result in 0.

  4. Group Function Fundamentals (3/5) • Most of the group functions can be applied either to ALL values or to only the DISTINCT values for the specified expression. When ALL is specified, all non-NULL values are applied to the group function. When DISTINCT is specified, only one of each non-NULL value is applied to the function.

  5. Group Function Fundamentals (4/5) • To better understand the difference of ALL versus DISTINCT, let’s look at the SCOTT.EMP table—specifically, at salaries of the employees in department 20: • SELECT empno,salFROM scott.empWHERE deptno=20ORDER BY sal; EMPNO SAL---------- ---------- 7369 800 7876 1100 7566 2975 7788 3000 7902 3000

  6. Group Function Fundamentals (5/5) • If we average all of the values, we get (800+1100+2975+3000+3000)/5, or 2175. If, on the other hand, we average only the distinct values, we don’t include the second 3000, and get (800+1100+2975+3000)/4, or 1968.75. • The DISTINCT keyword is often used with the COUNT function. • SELECT AVG(sal) avg ,AVG(ALL sal) avg_all ,AVG(DISTINCT sal) avg_dist ,COUNT(sal) cnt ,COUNT(DISTINCT sal) cnt_dist ,SUM(sal) sum_all ,SUM(DISTINCT sal) sum_distFROM scott.empWHERE deptno = 20 AVG AVG_ALL AVG_DIST COUNT COUNT_DIST SUM_AL SUM_DIST----- ------- -------- ----- ---------- ------- -------- 2175 2175 1968.75 5 4 10875 7875

  7. Using Group Functions:Group Function Descriptions (1/59) • AVG • This function has the syntax AVG([{DISTINCT | ALL}] <n>), where n is a numeric expression. The AVG function returns the mean of the expression n. If neither DISTINCT nor ALL is specified in the function call, the default is ALL. • SELECT job_id, AVG(salary)FROM hr.employeesWHERE job_id like ‘AC%’GROUP BY job_id;JOB_ID AVG(SALARY)---------- -----------AC_ACCOUNT 8300AC_MGR 12000

  8. Using Group Functions:Group Function Descriptions (2/59) • CORR • CORR(<y> , <x>) takes two arguments, where y and x are numeric expressions representing the dependent and independent variables, respectively. This function returns the coefficient of correlation of a set of number pairs. The coefficient of correlation is a measure of strength of the relationship between the two numbers. CORR can return a NULL. The coefficient of correlation is calculated from those x, y pairs that are both not NULL using the formula COVAR_POP(y,x) / (STDDEV_POP(y) * STDDEV_POP(x)). • SELECT CORR(list_price,min_price) correlation ,COVAR_POP(list_price,min_price) covariance ,STDDEV_POP(list_price) stddev_popy ,STDDEV_POP(min_price) stddev_popxFROM oe.product_informationWHERE list_price IS NOT NULL AND min_price IS NOT NULL;CORRELATION COVARIANCE STDDEV_POPY STDDEV_POPX----------- ------------ ----------- ----------- .99947495 206065.903 496.712198 415.077696

  9. Using Group Functions:Group Function Descriptions (3/59) • COUNT (1/3) • This function has the syntax COUNT({* | [DISTINCT | ALL] <x>}), where x is an expression. The COUNT function returns the number of rows in the query. If an expression is given and neither DISTINCT nor ALL is specified, the default is ALL. The asterisk (*) is a special quantity.it counts all rows in the result set, regardless of NULLs.

  10. Using Group Functions:Group Function Descriptions (4/59) • COUNT (2/3) • In the example that follows, we count the number of rows in the HR.EMPLOYEES table (the number of employees), the number of departments that have employees in them (DEPT_COUNT), and the number of employees that have a department (NON_NULL_DEPT_COUNT). We can see from the results that one employee is not assigned to a department, and the other 106 are assigned to one of 11 departments. • SELECT COUNT(*) emp_count ,COUNT(DISTINCT department_id) dept_count ,COUNT(ALL department_id) non_null_dept_countFROM hr.employees; EMP_COUNT DEPT_COUNT NON_NULL_DEPT_COUNT---------- ---------- ------------------- 107 11 106

  11. Using Group Functions:Group Function Descriptions (5/59) • COUNT (3/3) • This next example looks at the number of employees drawing a commission, as well as the distinct number of commissions drawn. We see that 35 out of 107 employees draw a commission, and there are seven different commission levels in use. • SELECT COUNT(*) ,COUNT(commission_pct) comm_count ,COUNT(DISTINCT commission_pct) distinct_commFROM hr.employees; COUNT(*) COMM_COUNT DISTINCT_COMM---------- ---------- ------------- 107 35 7

  12. Using Group Functions:Group Function Descriptions (6/59) • COVAR_POP (1/2) • COVAR_POP(<y> , <x>) takes two arguments, where y and x are numeric expressions. This function returns the population covariance of a set of number pairs, which can be NULL. • The covariance is a measure of how two sets of data vary in the same way. The population covariance is calculated from those y, x pairs that are both not NULL using the formula (SUM(y*x) - SUM(y) * SUM(x) / COUNT(x)) / COUNT(x).

  13. Using Group Functions:Group Function Descriptions (7/59) • COVAR_POP (2/2) • SELECT category_id ,COVAR_POP(list_price,min_price) population ,COVAR_SAMP(list_price,min_price) sampleFROM oe.product_informationGROUP BY category_id;CATEGORY_ID POPULATION SAMPLE----------- ---------- ---------- 11 92805.0 98992.0 12 26472.3 29781.4 13 25142.1 26465.4 14 17983.0 18800.4 15 7650.8 8160.9 16 431.4 479.3 17 5466.1 5739.5 19 417343.9 426038.6 21 21.5 25.1 22 45.0 67.5 24 109428.3 114639.2 25 27670.3 31623.1 29 3446.8 3574.4 31 1424679.2 1709615.0 32 4575.1 4815.9 33 945.0 1134.0 39 1035.1 1086.9

  14. Using Group Functions:Group Function Descriptions (8/59) • COVAR_SAMP • COVAR_SAMP(<y> , <x>) takes two arguments, where y and x are numeric expressions representing the dependent and independent variables, respectively. This function returns the sample covariance of a set of number pairs, which can be NULL. The covariance is a measure of how two sets of data vary in the same way. The sample covariance is calculated from those x, y pairs that are both not NULL using the formula (SUM(y*x) - SUM(y) * SUM(x) / COUNT(x)) / (COUNT(x)-1). • SELECT SUM(list_price*min_price) sum_xy ,SUM(list_price) sum_y ,SUM(min_price) sum_x ,REGR_COUNT(list_price,min_price) count_x ,COVAR_SAMP(list_price,min_price) COVARIANCEFROM oe.product_information; SUM_XY SUM_Y SUM_X COUNT_X COVARIANCE---------- ---------- ---------- ---------- ---------- 73803559 71407 60280 285 206791

  15. Using Group Functions:Group Function Descriptions (9/59) • CUME_DIST (1/3) • This function has the syntax: CUME_DIST(< val_list>) WITHIN GROUP (ORDER BY col_list[ASC|DESC] [NULLS {first|last}])where val_list is a comma-delimited list of expressions that evaluate to numeric constant values, and col_list is the comma-delimited list of column expressions. CUME_DIST returns the cumulative distribution of a value in val_list within a distribution in col_list. • The cumulative distribution is a measure of ranking within the ordered group and will be in the range 0< CUME_DIST <= 1. See also PERCENT_RANK.

  16. Using Group Functions:Group Function Descriptions (10/59) • CUME_DIST (2/3) • SELECT department_id ,COUNT(*) emp_count ,AVG(salary) mean ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC) Median ,PERCENT_RANK(10000) WITHIN GROUP (ORDER BY salary DESC)*100 Pct_Rank_10K ,CUME_DIST(10000) WITHIN GROUP (ORDER BY salary DESC) Cume_Dist_10KFROM hr.employeesGROUP BY department_id;

  17. Using Group Functions:Group Function Descriptions (11/59) • CUME_DIST (3/3) • DEPARTMENT_ID EMP_COUNT MEAN MEDIAN PCT_RANK_10K CUME_DIST_10K------------- ---------- ---- ------ ------------ ------------- 10 1 4400 4400 .00000 0.500000 20 2 9500 9500 50.00000 0.666667 30 6 4150 2850 16.66667 0.285714 40 1 6500 6500 .00000 0.500000 50 45 3476 3100 .00000 0.021739 60 5 5760 4800 .00000 0.166667 70 1 10000 10000 .00000 1.000000 80 34 8956 8900 23.52941 0.342857 90 3 19333 17000 100.00000 1.000000 100 6 8600 8000 16.66667 0.285714 110 2 10150 10150 50.00000 0.666667NULL 1 7000 7000 .00000 0.500000

  18. Using Group Functions:Group Function Descriptions (12/59) • DENSE_RANK (1/5) • This function has the syntax:DENSE_RANK(< val_list>) WITHIN GROUP (ORDER BY col_list[ASC|DESC] [NULLS {first|last}])where val_list is a comma-delimited list of numeric constant expressions (expressions that evaluate to numeric constant values), and col_list is the comma-delimited list of column expressions. DENSE_RANK returns the row’s rank within an ordered group. When there are ties, ranks are not skipped. For example, if there are three items tied for first, then second, and third will not be skipped. See also RANK.

  19. Using Group Functions:Group Function Descriptions (13/59) • DENSE_RANK (2/5) • SELECT department_id ,COUNT(*) emp_count ,AVG(salary) mean ,DENSE_RANK(10000) WITHIN GROUP (ORDER BY salary DESC) dense_rank_10KFROM hr.employeesGROUP BY department_id;

  20. Using Group Functions:Group Function Descriptions (14/59) • DENSE_RANK (3/5) • DEPARTMENT_ID EMP_COUNT MEAN DENSE_RANK_10K------------- ---------- ------ -------------- 10 1 4400 1 20 2 9500 2 30 6 4150 2 40 1 6500 1 50 45 3476 1 60 5 5760 1 70 1 10000 1 80 34 8956 7 90 3 19333 3 100 6 8600 2 110 2 10150 2NULL 1 7000 1

  21. Using Group Functions:Group Function Descriptions (15/59) • DENSE_RANK (4/5) • To understand this ranking, let’s look closer at department 80. We see that 10,000 is the seventh highest salary in department 80. Even though there are eight employees that make more than 10000. The duplicates are not counted for ranking purposes. • SELECT salary, COUNT(*)FROM hr.employeesWHERE department_id=80 AND salary > 9000GROUP BY salaryORDER BY salary DESC;

  22. Using Group Functions:Group Function Descriptions (16/59) • DENSE_RANK (5/5) • SALARY COUNT(*)---------- ---------- 14000 1 13500 1 12000 1 11500 1 11000 2 10500 2 10000 3 9600 1 9500 3

  23. Using Group Functions:Group Function Descriptions (17/59) • FIRST • See KEEP.

  24. Using Group Functions:Group Function Descriptions (18/59) • GROUP_ID • GROUP_ID( ) takes no arguments and requires a GROUP BY clause. GROUP_ID returns a numeric identifier that can be used to uniquely identify duplicate groups. For i duplicate groups, GROUP_ID will return values 0 through i-1.

  25. Using Group Functions:Group Function Descriptions (19/59) • GROUPING (1/3) • GROUPING(x) takes a single argument, where x is an expression in the GROUP BY clause of the query. The GROUPING function is applicable only for queries that have a GROUP BY clause and a ROLLUP or CUBE clause. The ROLLUP and CUBE clauses create summary rows (sometimes called superaggregates) containing NULL in the grouped expressions. The GROUPING function returns a 1 for these summary rows and a 0 for the nonsummary rows, and it is used to distinguish the summary rows from the nonsummary rows.

  26. Using Group Functions:Group Function Descriptions (20/59) • GROUPING (2/3) • This function becomes significant when the data values being aggregated may contain NULL values, such as the MARITAL_STATUS column of the SH.CUSTOMERS table in the following example. • SELECT cust_gender gender ,cust_marital_status marital_status ,GROUPING(cust_gender) gender_superagg ,GROUPING(cust_marital_status) marital_superagg ,COUNT(*)FROM sh.customeGROUP BY CUBE(cust_marital_status,cust_gender);

  27. Using Group Functions:Group Function Descriptions (21/59) • GROUPING (3/3) • GENDER MARITALGENDER MARITAL_STATUS COUNT(*) SUPERAGG SUPERAGG------ -------------- ---------- ---------- ----------F married 4701 0 0M married 9328 0 0NULL married 14029 1 0F single 5898 0 0M single 12868 0 0NULL single 18766 1 0F NULL 5716 0 0F NULL 16315 0 1M NULL 11489 0 0M NULL 33685 0 1NULL NULL 17205 1 0NULL NULL 50000 1 1 • In these results, the 0s for MARITAL_SUPERAGG show regular rows, and the 1s show superaggregates.

  28. Using Group Functions:Group Function Descriptions (22/59) • KEEP (1/4) • The KEEP function has the syntax:agg_function KEEP(DENSE_RANK {FIRST|LAST}ORDER BY col_list [ASC|DESC] [NULLS {first|last}]))where agg_function is an aggregate function, and col_list is a list of columns to be ordered for the grouping. • This function is sometimes referred to as either the FIRST or the LAST function, and it is actually a modifier for one of the other group functions, such as COUNT or MIN. The KEEP function returns the first or last row of a sorted group. It is used to avoid the need for a self-join, looking for the MIN or MAX.

  29. Using Group Functions:Group Function Descriptions (23/59) • KEEP (2/4) • SELECT department_id ,MIN(hire_date) earliest ,MAX(hire_date) latest ,COUNT(salary) KEEP (DENSE_RANK FIRST ORDER BY hire_date) FIRST ,COUNT(salary) KEEP (DENSE_RANK LAST ORDER BY hire_date) LASTFROM hr.employeesGROUP BY department_id;

  30. Using Group Functions:Group Function Descriptions (24/59) • KEEP (3/4) • DEPARTMENT_ID EARLIEST LATEST FIRST LAST------------- ----------- ----------- ------- ------ 10 17-Sep-1987 17-Sep-1987 1 1 20 17-Feb-1996 17-Aug-1997 1 1 30 07-Dec-1994 10-Aug-1999 1 1 40 07-Jun-1994 07-Jun-1994 1 1 50 01-May-1995 08-Mar-2000 1 1 60 03-Jan-1990 07-Feb-1999 1 1 70 07-Jun-1994 07-Jun-1994 1 180 30-Jan-1996 21-Apr-2000 1 290 17-Jun-1987 13-Jan-1993 1 1 100 16-Aug-1994 07-Dec-1999 1 1110 07-Jun-1994 07-Jun-1994 2 224-May-1999 24-May-1999 1 1

  31. Using Group Functions:Group Function Descriptions (25/59) • KEEP (4/4) • We can see from the query above that department 80’s earliest and latest anniversary dates are 30-Jan-1996 and 21-Apr-2000. The FIRST and LAST columns show us that there was one employee hired on the earliest anniversary date (30-Jun-1996) and two hired on the latest anniversary date (21-Apr-2000). Likewise, we can see that department 110 has two employees hired on the earliest anniversary date (07-Jun-1994) and two on the latest anniversary date (07-Jun-1994).

  32. Using Group Functions:Group Function Descriptions (26/59) • LAST • See KEEP.

  33. Using Group Functions:Group Function Descriptions (27/59) • MAX (1/2) • This function has the syntax MAX([{DISTINCT | ALL}] <x>), where x is an expression. This function returns the highest value in the expression x. • If the expression x is a date/time datatype, it returns a DATE. For dates, the maximum is the latest date. • If the expression x is a numeric datatype, it returns a NUMBER. For numbers, the maximum is the largest number. • If the expression is a character datatype, it returns a VARCHAR2. For character strings, the maximum is the one that sorts highest based on the database character set. • Although the inclusion of either DISTINCT or ALL is syntactically acceptable, their use does not affect the calculation of a MAX; the largest distinct value is the same as the largest of all values.

  34. Using Group Functions:Group Function Descriptions (28/59) • MAX (2/2) • SELECT MAX(hire_date) ,MAX(salary) ,MAX(last_name)FROM hr.employees;MAX(HIRE_DA MAX(SALARY) MAX(LAST_NAME)----------- ----------- -------------21-APR-2000 24000 Zlotkey

  35. Using Group Functions:Group Function Descriptions (29/59) • MIN (1/2) • This function has the syntax MIN([{DISTINCT | ALL}] <x>), where x is an expression. This function returns the lowest value in the expression x. • If the expression x is a date/time datatype, it returns a DATE. For dates, the minimum is the earliest date. • If the expression x is a numeric datatype, it returns a NUMBER. For numbers, the minimum is the smallest number. • If the expression is a character datatype, it returns a VARCHAR2. For character strings, the minimum is the one that sorts lowest based on the database character set. • Although the inclusion of either DISTINCT or ALL is syntactically acceptable, their use does not affect the calculation of a MIN: the smallest distinct value is the same as the smallest value.

  36. Using Group Functions:Group Function Descriptions (30/59) • MIN (2/2) • SELECT MIN(hire_date) ,MIN(salary) ,MIN(last_name)FROM hr.employees;MIN(HIRE_DA MIN(SALARY) MIN(LAST_NAME)----------- ----------- --------------17-JUN-1987 2100 Abel

  37. Using Group Functions:Group Function Descriptions (31/59) • PERCENT_RANK (1/3) • The PERCENT_RANK function has the syntax:PERCENT_RANK(< val_list>) WITHIN GROUP (ORDER BY col_list[ASC|DESC] [NULLS {first|last}])where val_list is a comma-delimited list of expressions that evaluate to numeric constant values, and col_list is the comma-delimited list of column expressions. PERCENT_RANK returns the percent ranking of a value in val_list within a distribution in col_list. The percent rank x will be in the range 0<= x <= 1.

  38. Using Group Functions:Group Function Descriptions (32/59) • PERCENT_RANK (2/3) • The main difference between PERCENT_RANK and CUME_DIST is that PERCENT_RANK will always return a 0 for the first row in any set, while the CUME_DIST function cannot return a 0. We can use the PERCENT_RANK and CUME_DIST functions to examine the rankings of employees with a salary over 10,000 in the HR.EMPLOYEES table. Notice the different results for departments 40 and 70. • SELECT DEPARTMENT_ID ,COUNT(*) emp_count ,AVG(salary) mean ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC) median ,PERCENT_RANK(10000) WITHIN GROUP (ORDER BY salary DESC)*100 pct_rank_10K ,CUME_DIST(10000) WITHIN GROUP (ORDER BY salary DESC)*100 cume_dist_10KFROM hr.emplGROUP BY department_id;

  39. Using Group Functions:Group Function Descriptions (33/59) • PERCENT_RANK (3/3) • DEPARTMENT_ID EMP_COUNT MEAN MEDIAN PCT_RANK_10K CUME_DIST_10K------------- --------- ---- ------ ------------ ------------- 10 1 4400 4400 0 50 20 2 9500 9500 50 67 30 6 4150 2850 17 29 40 1 6500 6500 0 50 50 45 3476 3100 0 2 60 5 5760 4800 0 17 70 1 10000 10000 0 100 80 34 8956 8900 24 34 90 3 19333 17000 100 100 100 6 8600 8000 17 29 110 2 10150 10150 50 67NULL 1 7000 7000 0 50

  40. Using Group Functions:Group Function Descriptions (34/59) • PERCENTILE_CONT (1/4) • PERCENTILE_CONT has the syntax:PERCENTILE_CONT(< x>) WITHIN GROUP (ORDER BY col_list[ASC|DESC])where x is a percentile value in the range 0<x<1, and col_list is the sort specification. PERCENTILE_CONT returns the interpolated value that would fall in percentile position x within the sorted group col_list. • This function assumes a continuous distribution and is most useful for obtaining the median value of an ordered group. The median value is defined to be the midpoint in a group of ordered numbers—half of the values are above the median, and half of the values are below the median.

  41. Using Group Functions:Group Function Descriptions (35/59) • PERCENTILE_CONT (2/4) • For our example, we will use the SCOTT.EMP table, ordered by department number. • SELECT ename ,deptno ,salFROM scott.empORDER BY deptno ,sal;ENAME DEPTNO SAL---------- ---------- ----------MILLER 10 1300CLARK 10 2450KING 10 5000SMITH 20 800ADAMS 20 1100JONES 20 2975SCOTT 20 3000FORD 20 3000JAMES 30 950WARD 30 1250MARTIN 30 1250TURNER 30 1500ALLEN 30 1600BLAKE 30 2850

  42. Using Group Functions:Group Function Descriptions (36/59) • PERCENTILE_CONT (3/4) • We can see that for department 10, there are three SAL values: 1200, 2450, and 5000. The median would be 2450, because there is one value above this number and one value below this number. The median for department 30 is not so straightforward, since there are six values and the middle value is actually between the two data points 1250 and 1500. To get the median for department 30, we need to interpolate the midpoint. There are two common techniques used to interpolate this median value: one technique uses a continuous model and one uses a discrete model. In the continuous model, the midpoint is assumed to be the value halfway between the 1250 and 1500, which is 1375. Using the discrete model, the median must be an actual data point and, depending on whether the data is ordered ascending or descending, the median would be 1250 or 1500.

  43. Using Group Functions:Group Function Descriptions (37/59) • PERCENTILE_CONT (4/4) • SELECT deptno ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sal DESC) "CONTINUOUS“ ,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY sal DESC) "DISCRETE DESC“ ,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY sal ASC) "DISCRETE ASC“ ,AVG(sal) meanFROM scott.empGROUP BY deptno; DEPTNO CONTINUOUS DISCRETE DESC DISCRETE ASC MEAN---------- ---------- ------------- ------------ ------ 10 2450 2450 2450 2917 20 2975 2975 2975 2175 30 1375 1500 1250 1567

  44. Using Group Functions:Group Function Descriptions (38/59) • PERCENTILE_DISC (1/2) • PERCENTILE_DISC has the syntax:PERCENTILE_DISC(< x>) WITHIN GROUP (ORDER BY col_list[ASC|DESC])where x is a percentile value in the range 0<x<1 and col_list is the sort specification. PERCENTILE_DISC returns the smallest cumulative distribution value from the col_list set that is greater than or equal to value x. • This function assumes a discrete distribution. Sometimes, data cannot be averaged in a meaningful way. Date data, for example, cannot be averaged, but you can calculate the median date in a group of dates. For example, to calculate the median hire date for employees in each department, we could run the following query: • SELECT department_id ,COUNT(*) emp_count ,MIN(HIRE_DATE) first ,MAX(HIRE_DATE) last ,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY HIRE_DATE) medianFROM hr.employeGROUP BY department_id;

  45. Using Group Functions:Group Function Descriptions (39/59) • PERCENTILE_DISC (2/2) • DEPARTMENT_ID EMP_COUNT FIRST LAST MEDIAN------------- ---------- --------- --------- --------- 10 1 17-SEP-87 17-SEP-87 17-SEP-87 20 2 17-FEB-96 17-AUG-97 17-FEB-96 30 6 07-DEC-94 10-AUG-99 24-JUL-97 40 1 07-JUN-94 07-JUN-94 07-JUN-94 50 45 01-MAY-95 08-MAR-00 15-MAR-98 60 5 03-JAN-90 07-FEB-99 25-JUN-97 70 1 07-JUN-94 07-JUN-94 07-JUN-94 80 34 30-JAN-96 21-APR-00 23-MAR-98 90 3 17-JUN-87 13-JAN-93 21-SEP-89 100 6 16-AUG-94 07-DEC-99 28-SEP-97 110 2 07-JUN-94 07-JUN-94 07-JUN-94 1 24-MAY-99 24-MAY-99 24-MAY-99

  46. Using Group Functions:Group Function Descriptions (40/59) • RANK (1/2) • RANK has the syntax:RANK(< val_list>) WITHIN GROUP (ORDER BY col_list[ASC|DESC] [NULLS {first|last}])where val_list is a comma-delimited list of numeric constant expressions (expressions that evaluate to numeric constant values), and col_list is the comma-delimited list of column expressions. RANK returns the row’s rank within an ordered group. • When there are ties, ranks of equal value are assigned equal rank, and the number of tied rows is skipped before the next rank is assigned. For example, if there are three items tied for first, the second and third items will be skipped, and the next will be fourth. • SELECT department_id ,COUNT(*) emp_count ,AVG(salary) mean ,DENSE_RANK(10000) WITHIN GROUP (ORDER BY salary DESC) dense_rank_10KFROM hr.employeeGROUP BY department_id;

  47. Using Group Functions:Group Function Descriptions (41/59) • RANK (2/2) • DEPARTMENT_ID EMP_COUNT MEAN DENSE_RANK_10K------------- ---------- ------ -------------- 10 1 4400 1 20 2 9500 2 30 6 4150 2 40 1 6500 1 50 45 3476 1 60 5 5760 1 70 1 10000 1 80 34 8956 9 90 3 19333 4 100 6 8600 2 110 2 10150 2 1 7000 1

  48. Using Group Functions:Group Function Descriptions (42/59) • REGR_AVGX • REGR_AVGX(<y>,<x>) takes two arguments, where y and x are numeric expressions representing the dependent and independent variables, respectively. This linear-regression function returns the numeric average x value after eliminating NULL x,y pairs. REGR_AVGX first removes y,x pairs that have a NULL in either y or x, then computes AVG(x). REGR_AVGX can return NULL. • SELECT REGR_AVGX(losal,hisal) avgx ,REGR_AVGY(losal,hisal) avgy ,REGR_COUNT(losal,hisal) r_count ,REGR_INTERCEPT(losal,hisal) intercept ,REGR_R2(losal,hisal) fit ,REGR_SLOPE(losal,hisal) slopeFROM scott.salgrade; AVGX AVGY R_COUNT INTERCEPT FIT SLOPE------ ------ ------- ---------- --------- ----------3519.8 1660.8 5 878.085277 .864454268 .222374772

  49. Using Group Functions:Group Function Descriptions (43/59) • REGR_AVGY • REGR_AVGY(<y>,<x>) takes two arguments, where y and x are numeric expressions representing the dependent and independent variables, respectively. This linear-regression function returns the numeric average y value after eliminating NULL x,y pairs. • REGR_AVGY first removes y,x pairs that have a NULL in either y or x, then computes AVG(y). REGR_AVGY can return NULL. See the description of REGR_AVGX for an example.

  50. Using Group Functions:Group Function Descriptions (44/59) • REGR_COUNT • REGR_COUNT(<y>,<x>) takes two arguments, where y and x are numeric expressions representing the dependent and independent variables, respectively. This linear-regression function returns the number of non-NULL x,y pairs. REGR_COUNT first removes y,x pairs that have a NULL in either y or x, then counts the remaining number of pairs. REGR_COUNT can return a 0, but not a NULL. See the description of REGR_AVGX for an example.

More Related