（1.2）sql server窗口函数,sql server分析函数(以及top ties/tablesample)

目录

• 0.测试数据代码
• 1.row_number()  over( partition by column order by column)
• 2.rank() over( partition by column order by column)
• 3.dense_rank() over( partition by column order by column)
•  4.ntile(number) over( partition by column order by column)
• 5、top colomn 与 top  with ties
• 6.tablesample ：返回表部分样本

 

---

关键字：sql server窗口函数、分析函数、四大窗口函数

特别细的从零讲解参考：https://www.cnblogs.com/CareySon/p/3411176.html

更多常用窗口函数：https://www.cnblogs.com/gered/p/11088845.html

回到顶部

0.测试数据代码

CREATE TABLE [dbo].[tbl_Student](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [学生ID] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [学生姓名] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [课程ID] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
    [课程名称] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [成绩] [int] NULL,
    [教师ID] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [教师姓名] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

insert into tbl_Student values('S3','王五','K4 ','政治',53,'T4','赵老师');
insert into tbl_Student values('S1','张三 ','K1 ','数学',61,'T1','张老师');
insert into tbl_Student values('S2','李四','K3 ','英语',88,'T3','李老师');
insert into tbl_Student values('S1','张三 ','K4 ','政治',77,'T4','赵老师');
insert into tbl_Student values('S2','李四','K4 ','政治',67,'T5','周老师');
insert into tbl_Student values('S3','王五','K2 ','语文',90,'T2','王老师');
insert into tbl_Student values('S3','王五','K1 ','数学',55,'T1','张老师');
insert into tbl_Student values('S1','张三 ','K2 ','语文',81,'T2','王老师');
insert into tbl_Student values('S4','赵六','K2 ','语文',59,'T1','王老师');
insert into tbl_Student values('S1','张三 ','K3 ','英语',37,'T3','李老师');

 

 

回到顶部

1.row_number()  over( partition by column order by column)

（1）测试数据

　　

（2）根据程序使用row_number() over()排序

　　

（3）说明：即使相同分数，也会按排序（这里用到的是默认聚集索引(id字段)排序）

（4）至于row_number()  over(partition by column order by column) 加上分区的这种，这里就不演示了

（1-3）中演示的是以所有数据为整体排序，如果用上partition by 字段名分区的，即以分区字段为一个整体排序，分区字段有10个不同的值就有10个从1开始的排序。

 

 

回到顶部

2.rank() over( partition by column order by column)

　　与row_number 一样，其区别在于如果是相同分数，则会给出相同名次。如图1-4名，结果排名回事1，2，2，4，这里也同样不演示分区的情况了。

结果图如下：

 

 

回到顶部

3.dense_rank() over( partition by column order by column)

　　与rank() over()一样，其区别是，如果是1-4名中有相同名次的也会给同名次，但是其下一个名次会顺序承接上一个值+1.也就是会出现1,2,2,3这样的结果

回到顶部

 4.ntile(number) over( partition by column order by column)

 

　　其实根据 over里面的分组与排序，把所有数据分组，先看一下结果图

　　

在图中，一共14行数据，我把其按照n1列排序后，分为3组。平均分下来只有3*4=12啊，那么多余的2行怎么分的呢？

如果行数平均划分后还有余行，那么就把行分在最前面的几组上。
比如我们的结果有7行，要分为3组。
那么第一组3行，第二组2行，第三组2行。
如果我们结果有14行，平均分为3组。
那么第一组5行，第二组5行，第三组4行。
依此类推。

所以，我们的14行，平分每个组4行，还多2行会1行加在1组上，1行加在2组上。

 

回到顶部

5、top colomn 与 top  with ties

这个top就不说了，大家都知道什么意思，并且在CRUD里面都可以用到。

top ties的作用是，把所有数据集中order by 字段与top中的最后一行order by 字段数据值相同的数据也一起筛选出来。

top ties的效果给大家演示一下，如图：

（1）如果直接top(2)是这样的

（2）加上ties的效果

 

 

 

回到顶部

6.tablesample ：返回表部分样本