sql开窗函数_位移函数lag与lead

第一部分：语法

位移函数语法如下：

lag(exp_str,offset,defval) over(partition by ..order by …)

lead(exp_str,offset,defval) over(partition by ..order by …)

其中exp_str是字段名

     Offset是偏移量，即是上1个或上N个的值，假设当前行在表中排在第5行，则offset 为3，则表示我们所要找的数据行就是表中的第2行（即5-3=2）。

     Defval默认值，当两个函数取上N/下N个值，当在表中从当前行位置向前数N行已经超出了表的范围时，lag（）函数将defval这个参数值作为函数的返回值，若没有指定默认值，则返回NULL，那么在数学运算中，总要给一个默认值才不会出错。

第二部分：实例

以下语句在以下环境均可以执行：sqlserver2014。

场景：几个朋友来到电影院的售票处，准备预约连续空余座位。你能利用表 T0527，帮他们写一个查询语句，获取所有空余座位，并将它们按照 seat_id 排序后返回吗？

示例数据：

对于如上样例，你的查询语句应该返回如下结果。 

注意：seat_id 字段是一个自增的整数，free 字段是布尔类型（'1' 表示空余， '0' 表示已被占据）。连续空余座位的定义是大于等于 2 个连续空余的座位。

示例数据sql： 

CREATE TABLE T0527
(
seat_id INT,
free INT
)

INSERT INTO T0527 VALUES 
(1,1),
(2,0),
(3,1),
(4,1),
(5,1)

查询sql： 

SELECT 
SEAT_ID
FROM 
    (SELECT *,    
    LAG(FREE,1,0)OVER(ORDER BY SEAT_ID) AS LAG_FREE,    
    LEAD(FREE,1,0)OVER(ORDER BY SEAT_ID) AS LEAD_FREE    
    FROM T0527    
    )T
WHERE FREE = 1 AND (FREE = LAG_FREE OR FREE = LEAD_FREE)

思路解析：

连续空余座位，那么就是这一行的数据为1并且等于上一行或下一行的数据。

使用位移函数先得到如下结果：

LAG_FREE表示前一行的free数据，LEAD_FREE表示下一行的free数据。此时可以在同一行判断free是否和LAG_FREE和LEAD_FREE相等，同时要过滤掉free为0的数据。