sql-server - sql server 中带有条件的 Lag()

Question

我有一个这样的表:

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 

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

Answer 1

您的问题(以及 Anon 的出色回答)是 the SQL of islands and gaps 的一部分。在这个答案中，我将尝试详细研究“row_number() 魔法”。

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

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() 魔法”方法:

; 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

• 第一个名为“grouped”的 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 行。

希望这有助于阐明间隙和岛屿的“魔力”。 Here is a working example at SQL Fiddle.