Xem mẫu
The Analytical Functions in Oracle (Analytical Functions I)
The value of nr here is 20 (20 rows).
By the row, the CUME_RANK calculation is:
CNAME TEMP RANK rownum cr calculation CD --------------- ---- ---------- ------ ------ ------------- ------
Binghamton 20 New Milford 24
Provo 44 Reston 47 Alexandria 47 Idaho Falls 47 Grass Valley 55 Baton Rouge 58 Starkville 58 Carrboro 58
Brewton 72 Gulf Breeze 77 Davenport 77
Orlando 79
1 1 1 (1/20) .050 2 2 2 (2/20) .100
6 6 6 (6/20) .300 7 7 9 (9/20) .450 7 8 9 (9/20) .450 7 9 9 (9/20) .450 10 10 10 (10/20) .500 11 11 13 (13/20) .650 11 12 13 (13/20) .650 11 13 13 (13/20) .650
17 17 17 (17/20) .850 18 19 19 (19/20) .950 18 19 19 (19/20) .950
20 20 20 (20/20) 1.000
The cr value of 9 for row 7 occurs because the rank of 7 was given to all rows up to the ninth row, and hence rows 7, 8, and 9 get the same value of 9 for cr, the numerator in the function calculation.
The PERCENT_RANK and CUME_RANK func-tions are very specialized and far less common than RANK or ROW_NUMBER. Also, in our examples we have depicted only one grouping — one partition. A PARTITION BY clause may be added to the analytic clause of the function, and sub-grouping and sub-PER-CENT_RANKs and CUME_DISTs may also be reported.
108
Chapter | 3
For example, using our Employee table with PERCENT_RANK and CUME_DIST:
SELECT empno, ename, region,
RANK() OVER(PARTITION BY region ORDER BY curr_salary) RANK,
PERCENT_RANK() OVER(PARTITION BY region ORDER BY curr_salary) PR,
CUME_DIST() OVER(PARTITION BY region ORDER BY curr_salary) CD
FROM employee
Gives:
EMPNO ENAME REGION RANK PR CD ---------- -------------------- ------ ---------- ---------- ----------
108 David
111 Katie
E 1 0 .333333333
E 2 .5 .666666667
122 Lindsey E 3 1 1 101 John W 1 0 .25
102 Stephanie W
106 Chloe W
2 .333333333 .75
2 .333333333 .75
104 Christina W 4 1 1
In this result, first note the partitioning by region: The result set acts like two different sets of data based on the partition. Within each region, we see the calculation of PERCENT_RANK and CUME_DIST as per the previous algorithms.
109
The Analytical Functions in Oracle (Analytical Functions I)
References
SQL for Analysis in Data Warehouses, Oracle Corpo-ration, Redwood Shores, CA, Oracle9i Data Warehousing Guide, Release 2 (9.2), Part Number A96520-01.
For an excellent discussion of how Oracle 10g has improved querying, see “DSS Performance in Oracle Database 10g,” an Oracle white paper, Sep-tember 2003. This article shows how the Optimizer has been improved in 10g.
110
Chapter | 4
Chapter 4
Aggregate Functions Used as Analytical
Functions Functions
(Analytical II)
The Use of Aggregate Functions in SQL
Many of the common aggregate functions can be used as analytical functions: SUM, AVG, COUNT, STDDEV, VARIANCE, MAX, and MIN. The aggre-gate functions used as analytical functions offer the advantage of partitioning and ordering as well. As an example, say you want to display each person’s employee number, name, original salary, and the aver-age salary of all employees. This cannot be done with a query like the following because you cannot mix aggre-gates and row-level results.
111
Aggregate Functions Used as Analytical Functions (Analytical Functions II)
SELECT empno, ename, orig_salary, AVG(orig_salary)
FROM employee ORDER BY ename
Gives:
SELECT empno, ename, orig_salary, *
ERROR at line 1:
ORA-00937: not a single-group group function
But we can use a Cartesian product/virtual table like this:
SELECT e.empno, e.ename, e.orig_salary, x.aos "Avg. salary"
FROM employee e,
(SELECT AVG(orig_salary) aos FROM employee) x ORDER BY ename
Which gives:
EMPNO ENAME ORIG_SALARY Avg. salary ------ ---------- ----------- -----------
101 John 106 Chloe
104 Christina 108 David
111 Kate 122 Lindsey
102 Stephanie
35000 38285.7143 33000 38285.7143 43000 38285.7143 37000 38285.7143 45000 38285.7143 40000 38285.7143
35000 38285.7143
This type of query is borderline cumbersome and may be done far more easily using AVG in an analytical function:
112
...
- tailieumienphi.vn
nguon tai.lieu . vn