查询出来的结果

) as t
where t.mm=1

select id,name,max(score) from Student group by id,name order by name

常用分析函数:(最常用的应该是1.2.3 的排序)
1、row_number() over(partition by … order by …)
2、rank() over(partition by … order by …)
3、dense_rank() over(partition by … order by …)
4、count() over(partition by … order by …)
5、max() over(partition by … order by …)
6、min() over(partition by … order by …)
7、sum() over(partition by … order by …)
8、avg() over(partition by … order by …)
9、first_value() over(partition by … order by …)
10、last_value() over(partition by … order by …)
11、lag() over(partition by … order by …)
12、lead() over(partition by … order by …)
lag 和lead 可以
获取结果集中,按一定排序所排列的当前行的上下相邻若干offset
的某个行的某个列(不用结果集的自关联);
lag ,lead 分别是向前,向后;
lag 和lead
有三个参数,第一个参数是列名,第二个参数是偏移的offset,第三个参数是
超出记录窗口时的默认值)

心得:
rank()跳跃排序,有两个第二名时后边跟着的是第四名
dense_rank() 连续排序,有两个第二名时仍然跟着第三名

          查询出来的结果

        如果继续使用开始的方式,那么是不能满足要求的。

聚合函数,将多行数据合并成一行数据;而开窗函数则是将一行数据拆分成多行。

–每个班级的成绩第一的学生
–学生表中信息如下
a 1 80
b 1 78
c 1 95
d 2 74
e 2 92
f 3 99
g 3 99
h 3 45
i 3 55
j 3 78

常用开窗函数:
澳门新萄京,1.为每条数据显示聚合信息.(聚合函数() over())
2.为每条数据提供分组的聚合函数结果(聚合函数() over(partition by 字段) as
别名) –按照字段分组,分组后进行计算
3.与排名函数一起使用(row number() over(order by 字段) as 别名)

开窗函数适用于在每一行的最后一列添加聚合函数的结果。

开窗函数可以满足上述问题,同事也可以满足其他问题。例如:求每个班最高成绩学生的信息。

例如 : 1 张三 100

          两条信息都会输出。

 

查询结果如下:
c 1 95 1
e 2 92 1
f 3 99 1
g 3 99 1

这个问题应该还是相对简单,其实就用聚合函数就好了。

一个学习性任务:每个人有不同次数的成绩,统计出每个人的最高成绩。

避免这种情况,可以使用开窗函数。

上边这种情况只适用id 和name是一一对应的,否则查询出来的数据是不正确的。

分析:每个人学号一定是不同的,名字可能有重名,最大复杂的情况是,每个班最高成绩可能不止一个。

           2 张三 90

        使用开窗函数就能很好的解决这个问题。

over()开窗函数: 在使用聚合函数后,会将多行变成一行,
而开窗函数是将一行变成多行;
并且在使用聚合函数后,如果要显示其他的列必须将列加入到group by中,
而使用开窗函数后,可以不使用group by,直接将所有信息显示出来。

SQL查询语句如下:
select *
from
(
select name,class,s,rank()over(partition by class order by s desc) mm
from t2

个人理解就是,开窗函数和聚合函数功能是相反的。

相关文章