SQL新手新手向入门修炼-分析函数

由于本人对 sql 理解有限，如果文章中存在问题，麻烦各位帮忙指出。

当然若是觉得写的还可以，麻烦最下面顶一下…

今天本文主要就分析函数来展开叙述，最近巨S 我在编写这方面代码的时候发现，分析函数可以很简洁明了的将好几段子查询集合为一段，极大的减轻了系统的消耗量，在数据量较大的情况下，可以提高效率,话不多说，正题开始：

1. 分析函数（排序）

(1)
select row_number( ) over( order by 类名 ) from table
(2)
select dense_rank( ) over( order by 类名 ) from table
(3)
select rank( ) over( order by 类名 ) from table
(4)
select ntile( 2 ) over( order by 类名 ) from table

（1）显示结果为

name	core	rank
tom	87	1
jim	83	2
kim	83	3
mini	80	4

（2）显示结果为

name	core	rank
tom	87	1
jim	83	2
kim	83	2
mini	80	3

（3）显示结果为

name	core	rank
tom	87	1
jim	83	2
kim	83	2
mini	80	4

（4）显示结果为

name	core	rank
tom	87	1
jim	83	1
kim	83	2
mini	80	2

这三种基本类型大家在各大博客上应该是很常见，也知道显示出来的是什么东西，但是怎么将这些显示出来的数据应用到我们日常的代码分析中呢?

首先，我们来对这四种函数进行分析

（1）row_number（）
这个是由上到下，直接按序排列，不存在并列，跳跃一说，如果当有数据相同的时候，则按默认给这两个成绩相同的排先后

（2）dense_rank（）
在这个语法中就存在并列一说，数据相同的序号相同，但后续排序是连续的，不存在序号的跳跃

（3）rank（）
这个语法中存在跳跃一说，即数据相同的序号相同，但后续排序是从前一个 序号 + 前一序号相同数量 开始的

（4）ntile （ number ）
这个是简单的分组函数，通过括号里的number ，将该表的数据等量分成 number 份若不能等分，则分的大的在前，分的小的在后，比如 25 分三份，即 9，8，8 ；若是 5 份，则还是5，5，5，5，5。

就使用来说，概括的说就是我们主要通过这个排序 rank 值，当作子查询的条件来使用

举个栗子：
我们需要对张成绩表进行处理，对前三的人按序排序，而其他人则任是默认排序。

分析：
（1）有题可知，我们需要把前三的人找出来，放在开头
（2）其他人的排序是不能变的

那我们先把这个前三的人找出来（以下两段代码运行结果是相同的）

SELECT * 
FROM (SELECT ROW_NUMBER() OVER(ORDER BY SC.CORE) AS ranks,
 SC.STUDENT_NO,
 SC.CORE,
 SC.COURSE_NO
FROM STUDENT_CORE SC)
WHERE ROWNUM <4 ;

或者

SELECT *
FROM (SELECT * FROM STUDENT_CORE SC ORDER BY SC.CORE)
WHERE ROWNUM < 4;

可能你会问了，明明下面的更简单为什么还需呀上面的那中写法，那请继续看下去
然后我们通过外连接，找到我们的student_core中对应的数字，然后通过rank来进行排序

SELECT COR.STUDENT_NO ST, COR.COURSE_NO,COR.CORE CO,
CASE
WHEN HS.RANKS < 4 THEN RANKS
ELSE RANKS * NULL
END RANKSS
FROM STUDENT_CORE COR,
 (SELECT *
FROM (SELECT ROWNUM RN,
 HSC.STUDENT_NO STUDENT_NO,
 HSC.COURSE_NO COURSE_NO,
 HSC.CORE,
 ROW_NUMBER() OVER(ORDER BY HSC.CORE DESC) RANKS
FROM STUDENT_CORE HSC)
WHERE ROWNUM < 4) HS
WHERE COR.STUDENT_NO = HS.STUDENT_NO(+)
AND COR.COURSE_NO = HS.COURSE_NO(+);

至此我们为什么使用前一种写法就显而易见了，因为需要增加一个序列，用着个序列来进行排序，通过指定那前三名的序列，同时使其他人的序列为null ，对这个含有null 的序列进行排序就能达到题目的需要。
如果说仍然使用 rownum 那么每条记录的 rownum 以我目前的水平还不知道怎么去除，从而会导致剩下的记录不能按照原来的顺序进行排序。

2. 分析函数(组函数)
在分析函数前还可使用组函数，使得数据在显示出来的同时，后还可以跟上如各个部门自己的最大，最小，从而数据的对比就显而易见了。

具体的格式：

select 组函数（目标数据） over（partition by 目标分组 order by 目标排序条件） from 目标表

举个栗子：

select max( salary ) over( partition by department_id order by salary desc) 
from employees;

这个理解起来并不是特别困难，接下来我就着将over列中重复的数据合并来这个例子来进行分析

举个栗子：
我们需要对部门进行分类，然后列出一条这个部门的平均工资，在将这个部门的下属员工以多条的形式列出来。

栗子分析：
（1）我们需要将部门，平均工资查出来，并且在表中只显示一条
（2）部门下属员工信息要以一对多的形式在数据表中显现出来
（3）我们可以对部门及平均工资进行操作，从而使得器数据变成一行，再通过指定部门号查出下属员工信息

SELECT DECODE(LAG(E1.DEPARTMENT_ID, 1, NULL)
 OVER(PARTITION BY E1.DEPARTMENT_ID ORDER BY E1.DEPARTMENT_ID),
NULL,
 E1.DEPARTMENT_ID) DEPARTMENT_IDD,
 DECODE(LAG(E1.AVG_SALARY, 1, NULL)
 OVER(PARTITION BY E1.DEPARTMENT_ID ORDER BY E1.DEPARTMENT_ID),
NULL,
 E1.AVG_SALARY) SALARYY,
 E.EMPLOYEE_ID,
 E.LAST_NAME
FROM EMPLOYEES E,
 (SELECT E.DEPARTMENT_ID, AVG(E.SALARY) AVG_SALARY
FROM EMPLOYEES E
GROUP BY E.DEPARTMENT_ID) E1
WHERE E1.DEPARTMENT_ID = E.DEPARTMENT_ID(+);

在这段代码中主要是对over中的组函数，decode（），lag（）这三个点进行理解
（1）
对于over中的组函数不能使用单组分组函数，可能听起来有点奇怪，按照我的理解就是说在这种情况下你在partition by 后面指定的需要时一组不带重复的数据( 就这点我希望如果有理解朋友麻烦在下面的评论帮我分析下)
（2）
decode（）的话主要是根据指定情况即满足第一个情况，即为null，else后的则是剩下的全为目标数据如：department_id，对于本题使用了偏移函数使第一条数据为null ，剩下的为目标数据如： department_id
然后通过decode 的判断使有数据存在的为null ，没有数据存在的设置为目标数据如：department_id
（3）
lag（）偏移函数，使目标函数按指定偏移量偏移
下面这位老哥的栗子举得很易懂的比较全面的需要详细理解lag（）可以去他的博客看看
http://blog.csdn.net/thinkscape/article/details/8290894

下面的表为我们此次运算途径
(1)原本链接后的样子

department_id	avg(salary)	last_name
01	1000	kim
01	1000	kii
02	1500	ill
02	1500	asd
02	1500	adf

(2)使用lag（）偏移后的样子

department_id	avg(salary)	last_name
		kim
01	1000	kii
		ill
02	1500	asd
02	1500	adf

(3)使用decode（）判断条件后的结果

department_id	avg(salary)	last_name
01	1000	kim
		kii
02	1500	ill
		asd
		adf

好了,到此我们的分析函数先告一段落，后续如果我碰到什么新颖的情况会在这里添加的，如果那个老哥对我写的有意见的，那还烦请帮我指出下，新手上路容易翻车！！！

秒客网

SQL新手新手向入门修炼-分析函数

SQL新手新手向入门修炼-分析函数

相关文章