xml地图|网站地图|网站标签 [设为首页] [加入收藏]

热门视频

当前位置:美高梅游戏网站 > 热门视频 > T-SQL:开窗函数(十一卡塔尔

T-SQL:开窗函数(十一卡塔尔

来源:http://www.gd-chuangmei.com 作者:美高梅游戏网站 时间:2019-11-12 10:07

1.基本概念

1.创建测试表score

开窗函数分为两个部分分别是

create table score(
class_no varchar2(10),      --班级
student_name varchar2(20),  --姓名
score number                --分数
);

1.聚合,排名,偏移,分布函数 。

2.初始化数据

2.开窗分区,排序,框架。

insert into score(class_no,student_name,score) values('n001','park',99);
insert into score(class_no,student_name,score) values('n001','ning',99);
insert into score(class_no,student_name,score) values('n001','tom',79);
insert into score(class_no,student_name,score) values('n001','cat',87);
insert into score(class_no,student_name,score) values('n001','sandy',95);
insert into score(class_no,student_name,score) values('n002','cake',85);
insert into score(class_no,student_name,score) values('n002','mavom',69);
insert into score(class_no,student_name,score) values('n002','tony',90);
insert into score(class_no,student_name,score) values('n002','lisa',99);
insert into score(class_no,student_name,score) values('n002','linda',67);
insert into score(class_no,student_name,score) values('n003','versy',84);
insert into score(class_no,student_name,score) values('n003','peter',97);
insert into score(class_no,student_name,score) values('n003','train',83);
insert into score(class_no,student_name,score) values('n003','rain',80);

下面举个例子

3.将不同班级学生按分数降序排列

SELECT empid, ordermonth, val,
  SUM(val) OVER(PARTITION BY empid
                ORDER BY ordermonth
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW) AS runval
FROM Sales.EmpOrders;
select *
  from (select class_no,
               student_name,
               score,
               rank() over(partition by class_no order by score desc)
          from score) t;

sum(val)  就是集合函数

4.执行结果

over() 就是开窗     PARTITION BY empid  就是开窗分区(分组)   ORDER BY ordermonth 开窗排序  

图片 1

 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  开窗架构

5.其他分析函数

2.排名开窗函数

row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)

 SQL SERVER 支持4个排名函数 ROW_NUMBER,RANK,DENSE_RANK ,NTLE   来看看它们分别的作用

 

SELECT orderid, custid, val,
ROW_NUMBER() OVER(ORDER BY val) AS rownum,
RANK() OVER(ORDER BY val) AS rank,
DENSE_RANK() OVER(ORDER BY val) AS dense_rank,
NTILE(10) OVER(ORDER BY val) AS ntile
FROM Sales.OrderValues
ORDER BY val;

图片 2

可以看到 它们不同排序规则

ROW_NUMBER() 对排序字段行号进行排序  

RANK() 对数值进行排序 对相同数值有行号占用

本文由美高梅游戏网站发布于热门视频,转载请注明出处:T-SQL:开窗函数(十一卡塔尔

关键词: