60.Oracle数据库SQL开发之高级查询——使用分析函数之评级函数

最新推荐文章于 2023-07-24 15:02:28 发布

badman250

最新推荐文章于 2023-07-24 15:02:28 发布

阅读量3.1w

点赞数

本文链接：https://blog.csdn.net/notbaron/article/details/49847031

版权

60.Oracle数据库SQL开发之高级查询——使用分析函数之评级函数

欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/49847031

数据库中有很多内置的分析函数，能够执行复杂的计算。分析函数可以分为很多类。

评级函数，反百分比点函数，窗口函数，报表函数，延迟与领先函数，首函数与末函数，线性回归函数，假想评级及分布函数。

先执行如下：

store@PDB1> select * from all_sales where rownum<= 12;

YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT

---------- ---------- ----------- --------------------

2003 8 5 23

2003 9 5 23

2003 10 5 23

2003 11 5 23

2003 12 5 23

2003 1 5 24

2003 2 5 24

2003 3 5 24

2003 4 5 24

2003 5 5 24

2003 6 5 24

2003 7 5 24

12 rows selected.

如下图1：

1. 使用RANK和DENSE_RANK函数

可以计算数据项在分组中的排名。两个函数之间的区别在于处理相等数据项的方式。

两个函数之前的出别是在处理相等数据项的方式不同。

store@PDB1> select prd_type_id,sum(amount),rank()over (order by sum(amount) desc) as rank,

dense_rank() over (order by sum(amount) desc) as dense_rank fromall_sales where year=2003 and amount is not null group by prd_type_id order byprd_type_id;

PRD_TYPE_ID SUM(AMOUNT) RANK DENSE_RANK

----------- ----------- ---------- ----------

1 905081.84 1 1

2 186381.22 4 4

3 478270.91 2 2

4 402751.16 3 3

2. 使用NULLS FIRST和NULLS LAST子句控制控制的排名

可以使用NULLS FIRST和NULLS LAST显示地控制控制是一个分组中最高还是最低的排名。

store@PDB1> select prd_type_id,sum(amount),rank()over (order by sum(amount) desc nulls last) as rank,dense_rank() over (order bysum(amount) desc nulls last) as dense_rank from all_sales where year=2003 groupby prd_type_id order by prd_type_id;

PRD_TYPE_ID SUM(AMOUNT) RANK DENSE_RANK

----------- ----------- ---------- ----------

1 905081.84 1 1

2 186381.22 4 4

3 478270.91 2 2

4 402751.16 3 3

5 5 5

使用NULLS LAST将空值指定为最低。

3. 分析函数与PARTITION BY子句结合使用

要将分组划分为自分组时，可以将PARTITION BY子句和分析函数结合起来使用。

例如：

store@PDB1> selectprd_type_id,month,sum(amount),rank() over (partition by month order bysum(amount) desc) as rank

fromall_sales where year=2003 and amount is not null group by prd_type_id,monthorder by prd_type_id,month;

PRD_TYPE_ID MONTH SUM(AMOUNT) RANK

----------- ---------- ----------- ----------

1 1 38909.04 1

1 2 70567.9 1

1 3 91826.98 1

1 4 120344.7 1

1 5 97287.36 1

1 6 57387.84 1

1 7 60929.04 2

1 8 75608.92 1

1 9 85027.42 1

1 10 105305.22 1

1 11 55678.38 1

1 12 46209.04 2

2 1 14309.04 4

2 2 13367.9 4

2 3 16826.98 4

2 4 15664.7 4

2 5 18287.36 4

2 6 14587.84 4

2 7 15689.04 3

2 8 16308.92 4

2 9 19127.42 4

2 10 13525.14 4

2 11 16177.84 4

2 12 12509.04 4

3 1 24909.04 2

3 2 15467.9 3

3 3 20626.98 3

3 4 23844.7 2

3 5 18687.36 3

3 6 19887.84 3

3 7 81589.04 1

3 8 62408.92 2

3 9 46127.42 3

3 10 70325.29 3

3 11 46187.38 2

3 12 48209.04 1

4 1 17398.43 3

4 2 17267.9 2

4 3 31026.98 2

4 4 16144.7 3

4 5 20087.36 2

4 6 33087.84 2

4 7 12089.04 4

4 8 58408.92 3

4 9 49327.42 2

4 10 75325.14 2

4 11 42178.38 3

4 12 30409.05 3

48 rows selected.

4. 分析函数与ROLLUP,CUBE,GROUPINGSETS操作符结合使用

用ROLLUP和RANK获得不同产品类型ID的销售排名：

store@PDB1> select prd_type_id,sum(amount),rank()over (order by sum(amount) desc) as rank from all_sales

whereyear=2003 group by rollup(prd_type_id) order by prd_type_id;

PRD_TYPE_ID SUM(AMOUNT) RANK

----------- ----------- ----------

1 905081.84 3

2 186381.22 6

3 478270.91 4

4 402751.16 5

5 1

1972485.13 2

6 rows selected.

用CUBE和RANK根据产品类型ID和员工ID获得销售排名：

store@PDB1> select prd_type_id,sum(amount),rank()over (order by sum(amount) desc) as rank from all_sales where year = 2003 groupby cube(prd_type_id,emp_id) order by prd_type_id,emp_id;

PRD_TYPE_ID SUM(AMOUNT) RANK

----------- ----------- ----------

1 197916.96 19

1 214216.96 17

1 98896.96 26

1 207216.96 18

1 93416.96 28

1 93417.04 27

1 905081.84 9

2 20426.96 40

2 19826.96 41

2 19726.96 42

2 43866.96 34

2 32266.96 38

2 50266.42 31

2 186381.22 21

3 140326.96 22

3 116826.96 23

3 112026.96 24

3 34829.96 36

3 29129.96 39

3 45130.11 33

3 478270.91 10

4 108326.96 25

4 81426.96 30

4 92426.96 29

4 47456.96 32

4 33156.96 37

4 39956.36 35

4 402751.16 13

5 1

466997.84 11

432297.84 12

323077.84 15

333370.84 14

187970.84 20

228769.93 16

1972485.13 8

42 rows selected.

使用GROUPING SETS和RANK获得销量小计的排名：

store@PDB1> select prd_type_id,sum(amount),rank() over (order by sum(amount) desc)as rank from all_sales where year = 2003 group by groupingsets(prd_type_id,emp_id) order by prd_type_id,emp_id;

PRD_TYPE_ID SUM(AMOUNT) RANK

----------- ----------- ----------

1 905081.84 2

2 186381.22 11

3 478270.91 3

4 402751.16 6

5 1

466997.84 4

432297.84 5

323077.84 8

333370.84 7

187970.84 10

228769.93 9

11 rows selected.

5. 使用CUME_DIST和PERCENT_RANK函数

CUME_DIST函数可以计算某个特定值相对于一组值中的位置。PERCENT_RANK函数可以计算某个值相对于一组值的百分比排名。

store@PDB1> selectprd_type_id,sum(amount),cume_dist() over (order by sum(amount) desc) ascume_dist,percent_rank() over (order by sum(amount) desc) as percent_rank fromall_sales where year=2003 group by prd_type_id 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

6. 使用NTILE函数

NTILE(buckets)可以计算n分片的值。Bucket指定了分片的片数。

store@PDB1> selectprd_type_id,sum(amount),ntile(4) over (order by sum(amount) desc) as ntile fromall_sales where year=2003 and amount is not null group by prd_type_id order byprd_type_id;

PRD_TYPE_ID SUM(AMOUNT) NTILE

----------- ----------- ----------

1 905081.84 1

2 186381.22 4

3 478270.91 2

4 402751.16 3

7. 使用ROW_NUMBER函数

ROW_NUMBER从1开始，为每一条分组记录返回一个数字。

store@PDB1> selectprd_type_id,sum(amount),row_number() over (order by sum(amount) desc) asrow_number from all_sales where year=2003 group by prd_type_id order byprd_type_id;

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

badman250

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
60.Oracle数据库SQL开发之高级查询——使用分析函数之评级函数

60.Oracle数据库SQL开发之高级查询——使用分析函数之评级函数欢迎转载，转载请标明出处：数据库中有很多内置的分析函数，能够执行复杂的计算。分析函数可以分为很多类。评级函数，反百分比点函数，窗口函数，报表函数，延迟与领先函数，首函数与末函数，线性回归函数，假想评级及分布函数。先执行如下：store@PDB1> select * from all_sales wher
复制链接

扫一扫