SQL 聚合,开窗函数使用以及行转列操作
关于SQL操作聚合函数,常用的如 Max、Sum、Avg、Count 等等,搭配着Group by 在不考虑性能的情况下,加上一些 inner, where 之类的基本可满足大部分查询要求。从最开始实习到工作一年的时候,查询很少用过其他的函数,但随着接手的业务需求逐渐变多变杂。在考虑到性能的情况下,仅仅掌握这些是不够的,尤其面对某类业务的时,会充斥的大量的子查询,连接查询,不好维护,写的也费劲。
--班级最高分 select MAX(TempScore) MaxScore,ClassName from TestASD group by ClassName --班级平均分 select AVG(CONVERT(float,tempScore)) AvgScore,className from TestASD group by ClassName --班级总分 select SUM(CONVERT(float,TempScore)) SumScore,ClassName from TestASD group by ClassName --班级人数 select COUNT(1) ClassCount,className from TestASD group by ClassName
1. 聚合函数的死结在于查询结果往往会随着 Group by 后面列的增加而发生变化。当需要列出多个列的时候,除聚合函数包含的列以外,均要写在Group by 身后。如下SQL语句
--人数 班级 select COUNT(1) Num,ClassName from TestASD group by ClassName --人数 最高分 班级 select COUNT(1) Num,ClassName,MAX(TempScore) Score from TestASD group by ClassName --人数 最高分 学生 班级 --错误,数据错乱 select COUNT(1) Num,ClassName,MAX(TempScore) Score,UserCode from TestASD group by ClassName,UserCode
针对以上sql语句,对应的查询结果, 可以发现,当第三条sql 语句多增加了一个UserCode的字段时,查询的结果就开始错乱,并不是我们想要的结果,若要得到正确的结果,必然少不了 Inner 查询,我首先想到的是 查询班级,和最高分作为一个表,然后将这个表和第二条查询语句通过班级和分数进行关联,进而查询出需要的结果。一边查询一边进行调整。
2 .开窗函数则不受Group by 的束缚,可以针对当前行返回多笔数据。语句如下,结果如上。
--开窗函数 函数(列) over(列) Select COUNT(1) over(partition by ClassName) Num, ClassName, MAX(TempScore) over(partition by ClassName) Score,UserCode from dbo.TestASD
PARTITION BY 函数是独立于结果集创建自己的分区,以上sql语句使用了两个开窗函数,分别是 count() over() 、max() over() ,其各自创建的分区互不影响,一般而言,聚合函数可用于开窗函数,其格式为: 聚合函数(列) + over(列),看到这里,突然有一股似曾相识的感觉,没错,其实经常使用的分页函数,便是开窗函数
Select * from ( Select ROW_NUMBER() over(order by usercode desc) RowNum, * from TestASD )TT Where TT.RowNum between 11 and 20
说到分页,这里顺带记录相关的两个点,
①. 当需要记录总条数时,使用开窗函数 over() 后面不加列,则针对结果集所有行进行计算
②. 除了Row_Number() 可以排序以外,还有Rank(),Dense_Rank() 函数进行排序,当需要计算学生所在的班级排名,年级排名的时候用起来贼方便
Select COUNT(1) over() TolCount, ROW_NUMBER() over(order by Convert(float,TempScore) desc) RowNum, --按顺序,1,2,3,4,5.... RANK() over(order by Convert(float,TempScore) desc) RanNum, --并列排名,之后排名自动延后 1,2,3,3,3,6,6,8 Dense_rank() over(order by Convert(float,TempScore) desc) DRanNum, --并列排名,之后排名继续,不延后 1,2,3,3,3,4,4,5* from TestASD
3. 行转列,这样的需求也挺多的,最常见的,如某表记录了学生姓名,成绩,班级,此时需要查询每个班级 优秀(90+),良好(80+),及格(60+),不及格(60-) 的人数
With XX as( Select COUNT(1) over(PARTITION by ClassName) ClassCount,* from( Select distinct UserCode,UserName,ClassName, MAX(CONVERT(float,TempScore)) over(partition by Usercode) MaxScore from TestASD)T ) --SUM+Case 进行行转列 Select ClassName, ClassCount 班级总数, SUM(case when MaxScore >=90 then 1 else 0 end) 优秀, SUM(case when MaxScore>=80 and MaxScore<90 then 1 else 0 end) 良好, SUM(case when MaxScore>=60 and MaxScore<80 then 1 else 0 end) 及格, SUM(case when MaxScore<60 then 1 else 0 end) 不及格 From XX group by ClassName,ClassCount
切记,使用Group by 时,后面带的列,一定要考虑清楚,多想想,这里坑比较多,下面时查询结果,对比行专列前后
以上这些,会用之后,查询基本上不会像以前那么头疼....