问题滞后（）在sql server中有条件

我有一个这样的表：

Number   Price    Type       Date         Time
------   -----    ----    ----------    ---------
23456    0,665     SV     2014/02/02     08:00:02
23457    1,3       EC     2014/02/02     07:50:45
23460    0,668     SV     2014/02/02     07:36:34

对于每个EC，我需要上一个/下一个SV价格。在这种情况下，查询很简单。

Select Lag(price, 1, price) over (order by date desc, time desc),
Lead(price, 1, price) over (order by date desc, time desc)
from ITEMS

但是，有一些特殊情况，其中两行或更多行是EC类型：

Number   Price    Type       Date         Time
------   -----    ----    ----------    ---------
23456    0,665     SV     2014/02/02     08:00:02
23457    1,3       EC     2014/02/02     07:50:45
23658    2,4       EC     2014/02/02     07:50:45
23660    2,4       EC     2014/02/02     07:50:48
23465    0,668     SV     2014/02/02     07:36:34

在这种情况下我可以使用Lead / Lag吗？如果没有，我是否必须使用子查询？

7458

2018-02-14 16:14

起源

答案:

你的问题（和Anon的优秀答案）是其中的一部分岛屿和空白的SQL。在这个答案中，我将尝试详细检查“row_number（）magic”。

我根据球赛中的事件做了一个简单的例子。对于每个活动，我们希望打印上一季度和下一季度相关的消息：

create table TestTable (id int identity, event varchar(64));
insert TestTable values
    ('Start of Q1'),
    ('Free kick'),
    ('Goal'),
    ('End of Q1'),
    ('Start of Q2'),
    ('Penalty'),
    ('Miss'),
    ('Yellow card'),
    ('End of Q2');

这是一个显示“row_number（）magic”方法的查询：

; with  grouped as
        (
        select  *
        ,       row_number() over (order by id) as rn1
        ,       row_number() over (
                    partition by case when event like '%of Q[1-4]' then 1 end 
                    order by id) as rn2
        from    TestTable
        )
,       order_in_group as
        (
        select  *
        ,       rn1-rn2 as group_nr
        ,       row_number() over (partition by rn1-rn2 order by id) as rank_asc
        ,       row_number() over (partition by rn1-rn2 order by id desc)
                    as rank_desc
        from    grouped
        )
select  *
,       lag(event, rank_asc) over (order by id) as last_event_of_prev_group
,       lead(event, rank_desc) over (order by id) as first_event_of_next_group
from    order_in_group
order by
        id

称为“分组”的第一个CTE计算两个 row_number()秒。首先是 1 2 3 对于表中的每一行。第二 row_number() 将暂停公告放在一个列表中，将其他事件放在第二个列表中。两者之间的区别， rn1 - rn2，对于游戏的每个部分都是独一无二的。检查示例输出中的差异是有帮助的：它在 group_nr 柱。你会看到每个值对应游戏的一个部分。
称为“order_in_group”的第二个CTE确定当前行在其岛或间隙内的位置。对于有3排的岛屿，位置是 1 2 3 对于升序，和 3 2 1 为降序。
最后，我们知道足以说明问题 lag() 和 lead() 跳多远我们要滞后 rank_asc 行以查找上一节的最后一行。要找到下一部分的第一行，我们必须领导 rank_desc 行。

希望这有助于澄清峡谷和群岛的“魔力”。这是SQL Fiddle的一个工作示例。

2018-02-14 18:04

是的，您可以使用LEAD / LAG。你只需要用一点ROW_NUMBER（）魔法预先计算跳跃的距离。

DECLARE @a TABLE ( number int, price money, type varchar(2),
                   date date, time time)
INSERT @a VALUES
(23456,0.665,'SV','2014/02/02','08:00:02'),
(23457,1.3  ,'EC','2014/02/02','07:50:45'),
(23658,2.4  ,'EC','2014/02/02','07:50:45'),
(23660,2.4  ,'EC','2014/02/02','07:50:48'),
(23465,0.668,'SV','2014/02/02','07:36:34');

; WITH a AS (
     SELECT *,
            ROW_NUMBER() OVER(ORDER BY [date] DESC, [time] DESC) x, 
            ROW_NUMBER() OVER(PARTITION BY 
               CASE [type] WHEN 'SV' THEN 1 ELSE 0 END 
               ORDER BY [date] DESC, [time] DESC) y 
     FROM @a)
 , b AS (
     SELECT *,
            ROW_NUMBER() OVER(PARTITION BY x-y ORDER BY x ASC) z1,
            ROW_NUMBER() OVER(PARTITION BY x-y ORDER BY x DESC) z2 
     FROM a)
SELECT *,
       CASE [type] WHEN 'SV' THEN 
           LAG(price,z1,price) OVER(PARTITION BY [type] ORDER BY x) 
           ELSE LAG(price,z1,price) OVER(ORDER BY x) 
           END,
       CASE [type] WHEN 'SV' THEN 
           LEAD(price,z2,price) OVER(PARTITION BY [type] ORDER BY x) 
           ELSE LEAD(price,z2,price) OVER(ORDER BY x) 
           END
FROM b
ORDER BY x

2018-02-14 17:19

很好的解决方案，但我的RDBMS（Netezza）抱怨铅函数的偏移量必须是“正整数常量”。 - stevepastelan

答案: