■Ranking functions enable you to calculate ranks, percentiles, and n-tiles (tertiles, quartiles, and so on).
■Inverse percentile functions enable you to calculate the value that corresponds to a percentile.
■Window functions enable you to calculate cumulative and moving aggregates.
■Reporting functions enable you to calculate things like market share.
■Lag and lead functions enable you to get a value in a row where that row is a certain number of rows away from the current row.
■First and last functions enable you to get the first and last values in an ordered group.
■Linear regression functions enable you to fit an ordinary-least-squares regression line to a set of number pairs.
■Hypothetical rank and distribution functions enable you to calculate the rank and percentile that a new row would have if you inserted it into a table.
■Ranking functions
・RANK()
Returns the rank of items in a group. RANK() leaves a gap in the sequence of rankings in the event of a tie.(eg:1,2,2,4)
・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.(eg:1,2,2,3)
・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.
■Using the RANK() and DENSE_RANK() Functions
①
SQL> select prd_type_id,sum(amount),
2 rank()over(order by sum(amount) desc) as rank,
3 dense_rank() over(order by sum(amount)desc) as dens_rank
4 from all_sales
5 where year=2003
6 and amount is not null
7 group by prd_type_id
8 order by prd_type_id;
PRD_TYPE_ID SUM(AMOUNT) RANK DENS_RANK
----------- ----------- ---------- ----------
1 905081.84 1 1
2 186381.22 4 4
3 478270.91 2 2
4 402751.16 3 3
②RANK() and DENSE_RANK()assign the highest rank of 1 to null values in descending rankings；
・order by
null is the highest in rows;
null Last is default for ASC
null first is default for desc
SQL> edit;
已写入 file afiedt.buf
1 select prd_type_id,sum(amount),
2 rank()over(order by sum(amount) desc) as rank,
3 dense_rank() over(order by sum(amount)desc) as dens_rank
4 from all_sales
5 where year=2003
6 group by prd_type_id
7* order by prd_type_id
SQL> /
PRD_TYPE_ID SUM(AMOUNT) RANK DENS_RANK
----------- ----------- ---------- ----------
1 905081.84 2 2
2 186381.22 5 5
3 478270.91 3 3
4 402751.16 4 4
5 1 1
③Controlling Ranking of Null Values Using the NULLS FIRST and NULLS LAST Clauses
SQL> edit;
已写入 file afiedt.buf
1 select prd_type_id,sum(amount),
2 rank() over (order by sum(amount) desc nulls last) as rank,
3 rank() over (order by sum(amount) desc) as rank2,
4 dense_rank() over (order by sum(amount) nulls first) as dense_rank,
5 dense_rank() over (order by sum(amount)) as dense_rank2
6 from all_sales
7 where year = 2003
8 group by prd_type_id
9* order by prd_type_id
SQL> /
PRD_TYPE_ID SUM(AMOUNT) RANK RANK2 DENSE_RANK DENSE_RANK2
----------- ----------- ---------- ---------- ---------- -----------
1 905081.84 1 2 5 4
2 186381.22 4 5 2 1
3 478270.91 2 3 4 3
4 402751.16 3 4 3 2
5 5 1 1 5
④Using the PARTITION BY Clause with Analytic Functions
SQL> edit
已写入 file afiedt.buf
1 select prd_type_id,month,sum(amount),
2 rank() over (partition by month order by sum(amount)desc) rank
3 ,rank() over (order by sum(amount)) rank2
4 from all_sales
5 where year=2003 and amount is not null and month in (1,2)
6 group by prd_type_id,month
7* order by prd_type_id,month
8 ;
PRD_TYPE_ID MONTH SUM(AMOUNT) RANK RANK2
----------- ---------- ----------- ---------- ----------
1 1 38909.04 1 7
1 2 70567.9 1 8
2 1 14309.04 4 2
2 2 13367.9 4 1
3 1 24909.04 2 6
3 2 15467.9 3 3
4 1 17398.43 3 5
4 2 17267.9 2 4
已选择8行。
⑤Using ROLLUP, CUBE, and GROUPING SETS Operators with Analytic Functions
SQL> edit;
已写入 file afiedt.buf
1 select prd_type_id,sum(amount),
2 rank() over (order by sum(amount)) rank,
3 rank() over (order by sum(amount)desc) rank2
4 from all_sales
5 where year=2003
6 group by rollup(prd_type_id)
7* order by prd_type_id
SQL> /
PRD_TYPE_ID SUM(AMOUNT) RANK RANK2
----------- ----------- ---------- ----------
1 905081.84 4 3
2 186381.22 1 6
3 478270.91 3 4
4 402751.16 2 5
5 6 1
1972485.13 5 2
已选择6行。
SQL> edit;
已写入 file afiedt.buf
1 SELECT prd_type_id, emp_id, SUM(amount),
2 RANK() OVER (ORDER BY SUM(amount) DESC) AS rank,
3 RANK() OVER (partition by emp_id ORDER BY SUM(amount) DESC) AS rank
4 FROM all_sales
5 WHERE year = 2003 and prd_type_id in(1,2)
6 GROUP BY CUBE(prd_type_id, emp_id)
7* ORDER BY prd_type_id, emp_id
SQL> /
PRD_TYPE_ID EMP_ID SUM(AMOUNT) RANK RANK
----------- ---------- ----------- ---------- ----------
1 21 197916.96 8 2
1 22 214216.96 6 2
1 23 98896.96 13 2
1 24 207216.96 7 2
1 25 93416.96 15 2
1 26 93417.04 14 2
1 905081.84 2 2
2 21 20426.96 19 3
2 22 19826.96 20 3
2 23 19726.96 21 3
2 24 43866.96 17 3
2 25 32266.96 18 3
2 26 50266.42 16 3
2 186381.22 9 3
21 218343.92 5 1
22 234043.92 4 1
23 118623.92 12 1
24 251083.92 3 1
25 125683.92 11 1
26 143683.46 10 1
1091463.06 1 1
已选择21行。
SQL> edit;
已写入 file afiedt.buf
1 SELECT prd_type_id, emp_id, SUM(amount),
2 RANK() OVER (ORDER BY SUM(amount) DESC) AS rank
3 FROM all_sales
4 WHERE year = 2003 and prd_type_id in(1,2)
5 GROUP BY GROUPING SETS(prd_type_id, emp_id)
6* ORDER BY prd_type_id, emp_id
SQL> /
PRD_TYPE_ID EMP_ID SUM(AMOUNT) RANK
----------- ---------- ----------- ----------
1 905081.84 1
2 186381.22 5
21 218343.92 4
22 234043.92 3
23 118623.92 8
24 251083.92 2
25 125683.92 7
26 143683.46 6
已选择8行。
■Using the CUME_DIST() and PERCENT_RANK() Functions
SQL> select prd_type_id,sum(amount),
2 cume_dist() over (order by sum(amount)desc) as cume_dist,
3 percent_rank() over (order by sum(amount)desc) as percent_rank
4 from all_sales
5 where year =2003
6 group by prd_type_id
7 order by prd_type_id;
PRD_TYPE_ID SUM(AMOUNT) CUME_DIST PERCENT_RANK
----------- ----------- ---------- ------------
1 905081.84 .4 .25
2 186381.22 1 1
3 478270.91 .6 .5
4 402751.16 .8 .75
5 .2 0
SQL> edit;
已写入 file afiedt.buf
1 select prd_type_id,sum(amount),
2 cume_dist() over (order by sum(amount)desc) as cume_dist,
3 percent_rank() over (order by sum(amount)desc) as percent_rank
4 from all_sales
5 where year =2003 and amount is not null
6 group by prd_type_id
7* order by prd_type_id
SQL> /
PRD_TYPE_ID SUM(AMOUNT) CUME_DIST PERCENT_RANK
----------- ----------- ---------- ------------
1 905081.84 .25 0
2 186381.22 1 1
3 478270.91 .5 .333333333
4 402751.16 .75 .666666667
■Using the NTILE() Function
You use NTILE(buckets) to calculate n-tiles (tertiles, quartiles, and so on); buckets specifies the number of “buckets” into which groups of rows are placed.
SQL> edit;
已写入 file afiedt.buf
1 SELECT
2 prd_type_id, SUM(amount),
3 NTILE(4) OVER (ORDER BY SUM(amount) DESC) AS ntile
4 FROM all_sales
5 WHERE year = 2003
6 AND amount IS NOT NULL
7 GROUP BY prd_type_id
8* ORDER BY prd_type_id
SQL> /
PRD_TYPE_ID SUM(AMOUNT) NTILE
----------- ----------- ----------
1 905081.84 1
2 186381.22 4
3 478270.91 2
4 402751.16 3
SQL> edit;
已写入 file afiedt.buf
1 SELECT
2 prd_type_id, SUM(amount),
3 NTILE(3) OVER (ORDER BY SUM(amount) DESC) AS ntile
4 FROM all_sales
5 WHERE year = 2003
6 AND amount IS NOT NULL
7 GROUP BY prd_type_id
8* ORDER BY prd_type_id
SQL> /
PRD_TYPE_ID SUM(AMOUNT) NTILE
----------- ----------- ----------
1 905081.84 1
2 186381.22 3
3 478270.91 1
4 402751.16 2
■Using the ROW_NUMBER() Function
You use ROW_NUMBER() to return a number with each row in a group, starting at 1.
SQL> edit;
已写入 file afiedt.buf
1 SELECT
2 prd_type_id, SUM(amount),
3 ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS row_number
4 FROM all_sales
5 WHERE year = 2003
6 GROUP BY prd_type_id
7* ORDER BY prd_type_id
SQL> /
PRD_TYPE_ID SUM(AMOUNT) ROW_NUMBER
----------- ----------- ----------
1 905081.84 2
2 186381.22 5
3 478270.91 3
4 402751.16 4
5 1
■Inverse Percentile Functions
254~