Oracle PL/SQL Tutorial/Analytical Functions/Introduction
Содержание
- 1 Calculate with Analytical functions
- 2 More Than One Analytical Function May Be Used in a Single Statement
- 3 The Analytical Functions in Oracle (Analytical Functions I)
- 4 The Order in Which the Analytical Function Is Processed in the SQL Statement
- 5 Using the Analytic Functions
- 6 Using the Ranking Functions
Calculate with Analytical functions
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SELECT id, first_name, description, salary,
2 ROUND(AVG(salary) OVER(PARTITION BY description)) 3 "Avg-group", 4 ROUND(salary - AVG(salary) OVER(PARTITION 5 BY description)) "Diff." 6 FROM employee 7 ORDER BY description, first_name 8 /
ID FIRST_NAME DESCRIPTION SALARY Avg-group Diff.
---------- --------------- ---------- ---------- ----------
04 Celia Manager 2344.78 5121 -2777 07 David Manager 7897.78 5121 2777 01 Jason Programmer 1234.56 1235 0 02 Alison Tester 6661.78 4219 2442 03 James Tester 6544.78 4219 2325 08 James Tester 1232.78 4219 -2987 06 Linda Tester 4322.78 4219 103 05 Robert Tester 2334.78 4219 -1885 8 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL></source>
More Than One Analytical Function May Be Used in a Single Statement
<source lang="sql">
SQL> SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SELECT ID, first_name, salary,
2 RANK() OVER(ORDER BY salary desc) toprank_desc, 3 RANK() OVER(ORDER BY salary ASC) toprank_asc 4 FROM employee 5 ORDER BY first_name 6 /
ID FIRST_NAME SALARY TOPRANK_DESC TOPRANK_ASC
---------- ---------- ------------ -----------
02 Alison 6661.78 2 7 04 Celia 2344.78 5 4 07 David 7897.78 1 8 03 James 6544.78 3 6 08 James 1232.78 8 1 01 Jason 1234.56 7 2 06 Linda 4322.78 4 5 05 Robert 2334.78 6 3 8 rows selected. SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped.</source>
The Analytical Functions in Oracle (Analytical Functions I)
The analytical functions fall into categories:
- ranking,
- aggregate,
- row comparison,
- statistical.
The function has this syntax:
function(<arguments>) OVER(<analytic clause>)
For example, SELECT RANK() OVER(ORDER BY salary) FROM employee
The <arguments> part may be empty, as it is in the above example: "RANK()."
The <analytic clause> contains an ordering, partitioning, or windowing clause.
The ordering clause in the above example is "OVER(ORDER BY salary)."
An analytical function that uses an ordering may also partition the result set based on some attribute value.
The Row-numbering and Ranking Functions
- ROW_NUMBER
- RANK
- DENSE_RANK
- PERCENT_RANK
- CUME_DIST
- NTILE
16. 1. Introduction 16. 1. 1. The Analytical Functions in Oracle (Analytical Functions I) 16. 1. 2. <A href="/Tutorial/Oracle/0320__Analytical-Functions/TheOrderinWhichtheAnalyticalFunctionIsProcessedintheSQLStatement.htm">The Order in Which the Analytical Function Is Processed in the SQL Statement</a> 16. 1. 3. <A href="/Tutorial/Oracle/0320__Analytical-Functions/UsingtheAnalyticFunctions.htm">Using the Analytic Functions</a> 16. 1. 4. <A href="/Tutorial/Oracle/0320__Analytical-Functions/UsingtheRankingFunctions.htm">Using the Ranking Functions</a> 16. 1. 5. <A href="/Tutorial/Oracle/0320__Analytical-Functions/MoreThanOneAnalyticalFunctionMayBeUsedinaSingleStatement.htm">More Than One Analytical Function May Be Used in a Single Statement</a> 16. 1. 6. <A href="/Tutorial/Oracle/0320__Analytical-Functions/CalculatewithAnalyticalfunctions.htm">Calculate with Analytical functions</a>
The Order in Which the Analytical Function Is Processed in the SQL Statement
<source lang="sql">
SELECT FROM x WHERE</source>
is executed by the database engine by
scanning a table, x, and
retrieving rows when the WHERE clause is true.
WHERE is often called a "row filter."
The SELECT .. FROM .. WHERE may contain joins and GROUP BY as well as WHERE.
If there were GROUPING and HAVING clauses, then the criteria in HAVING would be applied after the result of the SELECT .. WHERE is completed.
HAVING is often called an "after filter".
HAVING is after the WHERE, and after the GROUP BY is executed.
The ordering is done last, after the result set has been established from SELECT .. FROM .. WHERE .. HAVING.
The analytical function is performed just before the ORDER BY.
All grouping, joins, WHERE clauses, and HAVING clauses will have already been applied.
Using the Analytic Functions
The analytic functions are organized into the following categories:
- Ranking functions calculate ranks, percentiles, and n-tiles (for example, tertiles, quartiles).
- Inverse percentile functions calculate the value that corresponds to a percentile.
- Window functions calculate cumulative and moving aggregates.
- Reporting functions calculate things like market shares.
- Lag and lead functions get a value in a row where that row is a certain number of rows away from the current row.
- First and last functions get the first and last values in an ordered group.
- Linear regression functions fit an ordinary-least-squares regression line to a set of number pairs.
- Hypothetical rank and distribution functions calculate the rank and percentile that a new row would have if you inserted it into a table.
Quote from:
Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)
# Paperback: 608 pages
# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)
# Language: English
# ISBN-10: 0072229810
# ISBN-13: 978-0072229813
16. 1. Introduction 16. 1. 1. <A href="/Tutorial/Oracle/0320__Analytical-Functions/TheAnalyticalFunctionsinOracleAnalyticalFunctionsI.htm">The Analytical Functions in Oracle (Analytical Functions I)</a> 16. 1. 2. <A href="/Tutorial/Oracle/0320__Analytical-Functions/TheOrderinWhichtheAnalyticalFunctionIsProcessedintheSQLStatement.htm">The Order in Which the Analytical Function Is Processed in the SQL Statement</a> 16. 1. 3. Using the Analytic Functions 16. 1. 4. <A href="/Tutorial/Oracle/0320__Analytical-Functions/UsingtheRankingFunctions.htm">Using the Ranking Functions</a> 16. 1. 5. <A href="/Tutorial/Oracle/0320__Analytical-Functions/MoreThanOneAnalyticalFunctionMayBeUsedinaSingleStatement.htm">More Than One Analytical Function May Be Used in a Single Statement</a> 16. 1. 6. <A href="/Tutorial/Oracle/0320__Analytical-Functions/CalculatewithAnalyticalfunctions.htm">Calculate with Analytical functions</a>
Using the Ranking Functions
You use the ranking functions to calculate ranks, percentiles, and n-tiles.
- RANK() returns the rank of items in a group.
- RANK() leaves a gap in the sequence of rankings in the event of a tie.
- DENSE_RANK() returns the rank of items in a group.
- DENSE_RANK() doesn"t leave a gap in the sequence of rankings in the event of a tie.
- CUME_DIST() returns the position of a specified value relative to a group of values.
- CUME_DIST() is short for cumulative distribution.
- PERCENT_RANK() returns the percent rank of a value relative to a group of values.
- NTILE() returns n-tiles: tertiles, quartiles, and so on.
- ROW_NUMBER() returns a number with each row in a group.
Quote from:
Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)
# Paperback: 608 pages
# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)
# Language: English
# ISBN-10: 0072229810
# ISBN-13: 978-0072229813
16. 1. Introduction 16. 1. 1. <A href="/Tutorial/Oracle/0320__Analytical-Functions/TheAnalyticalFunctionsinOracleAnalyticalFunctionsI.htm">The Analytical Functions in Oracle (Analytical Functions I)</a> 16. 1. 2. <A href="/Tutorial/Oracle/0320__Analytical-Functions/TheOrderinWhichtheAnalyticalFunctionIsProcessedintheSQLStatement.htm">The Order in Which the Analytical Function Is Processed in the SQL Statement</a> 16. 1. 3. <A href="/Tutorial/Oracle/0320__Analytical-Functions/UsingtheAnalyticFunctions.htm">Using the Analytic Functions</a> 16. 1. 4. Using the Ranking Functions 16. 1. 5. <A href="/Tutorial/Oracle/0320__Analytical-Functions/MoreThanOneAnalyticalFunctionMayBeUsedinaSingleStatement.htm">More Than One Analytical Function May Be Used in a Single Statement</a> 16. 1. 6. <A href="/Tutorial/Oracle/0320__Analytical-Functions/CalculatewithAnalyticalfunctions.htm">Calculate with Analytical functions</a>
