开窗函数

over()

有时候数据库返回的数据每一行好比一幢高楼的一层, 开窗函数就是在每一层开一扇窗, 让每一层能看到整装楼的全貌或一部分.

比如一张学生成绩表(transcript)有两列: [name] & [grade], 要求写一段查询能得到每行数据, 同时又增加一列显示平均分,就可以用到开窗函数:

select *,avg(grade) over() from transcript

这里所有学生的平均分是要看到"整幢楼的全貌才能得到的", over 就是开了扇窗从而得到了这个结果.

在over的括号里还可以加一些关键词比如partition by或者order by:

over 和 order by 连用可以解决求月累计销量的问题 

over + partition by＋order by 可以实现按季度划分, 求每季度内累加销量的结果:

SELECT [month]
      ,[sales],sum(sales) over (partition by (month-1)/3 order by month)
  FROM [test].[dbo].[Sales]

2017/6/15:

这里再补充4个 sqlserver2012 中新增的开窗函数:

lead

lag

first_value

last_value

其实只要明白了lead函数, 后面3个就自然懂了

假设要对全班的同学的成绩进行一次排名, 现在要知道相邻排名的两个同学间分数差多少(排名高的分数减去排名低的分数)

可以这样理解这个问题, 其实就是求相邻排名的两个同学中排名高的那个同学领先(lead)他后面那个同学几分, 比如第一名领先第二名几分, 第二名领先第三名几分以此类推, 典型的用lead的场景.

假设一张学生成绩表(transcript)有两列: [name] & [grade]:

select [name],[grade]-lead([grade],1,null) over (order by [grade] desc) from transcript

lead的第一个参数就是要比较的列(确切的说也可以是一个标量型的表达式), 第二个是偏移量, 既然要比的是相邻的两个学生自然就是1啦, 第三个参数是默认值,可以想象最后一名同学是没有人比他成绩更差的, 这是lead就会返回第三个参数给的默认值.

lag类似lead, 但是它是按over指定的顺序取前一行或前n行

first_value, last_value就是这个顺序的第一行和最后一行