T-SQL:开窗函数（十二）

1.基本概念

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

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

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

下面举个例子

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;

sum(val) 就是集合函数

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

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 开窗架构

2.排名开窗函数

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;

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

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

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

DENSE_RANK() 也是对数值排序如果有相同数值依旧会按照原先行号加

NTILE 分区排序为每一行分配一个区号如果分10区会对所有数据进行分区总数据/分区数就是每多少数据为一区

ROW_NUMBER() 默认在 DISTINCT 筛选重复项之前计算

2.偏移开窗函数

sql server 中总共有4个偏移函数 LAG 和 LEAD, FIRST_VALUE 和 LAST_VALUE

LAG函数在当前行之前查找

LEAD函数在当前行之后查找

LAST_VALUE 返回查找结果的最后一行

FIRST_VALUE 返回查找结果的第一行

3.聚合开窗函数

SUM() OVER()

AVG() OVER()

COUNT() OVER()

MAX() OVER() 等等

T-SQL:开窗函数（十二）

相关推荐