在SQL Server中具有条件的Lag() [英] Lag() with condition in 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价格.在这种情况下，查询很简单.

For each EC I need previous/next SV price. In this case, the query is simple.

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类型:

But, there are some special cases where two or more rows are EC type:

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吗?如果没有，我是否必须使用子查询?

can I use Lead/Lag in this cases? If not, did I have to use a subquery?

推荐答案

您的问题(以及Anon的出色回答)是

Your question (and Anon's excellent answer) is part of the SQL of islands and gaps. In this answer, I will try to examine the "row_number() magic" in detail.

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

I've made a simple example based on events in a ballgame. For each event, we'd like to print the previous and next quarter related message:

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()魔术"方法的查询:

Here's a query showing off the "row_number() magic" approach:

; 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列中.您会看到每个值都对应于游戏的一个部分.
• 第二个CTE称为"order_in_group"，确定当前行在其孤岛或间隙内的位置.对于具有三行的岛，位置按升序排列为1 2 3，对于降序排列为3 2 1.
• 最后，我们知道足以告诉lag()和lead()跳多远.我们必须将rank_asc行滞后才能找到上一部分的最后一行.要找到下一部分的第一行，我们必须引导rank_desc行.
• The first CTE called "grouped" calculates two row_number()s. The first is 1 2 3 for each row in the table. The second row_number() places pause announcements in one list, and other events in a second list. The difference between the two, rn1 - rn2, is unique for each section of the game. It's helpful to check difference in the example output: it's in the group_nr column. You'll see that each value corresponds to one section of the game.
• The second CTE called "order_in_group" determines the position of the current row within its island or gap. For an island with 3 rows, the positions are 1 2 3 for the ascending order, and 3 2 1 for the descending order.
• Finally, we know enough to tell lag() and lead() how far to jump. We have to lag rank_asc rows to find the final row of the previous section. To find the first row of the next section, we have to lead rank_desc rows.

希望这有助于阐明差距和离岛的魔力". 这是SQL Fiddle中的一个有效示例.

Hope this helps clarifying the "magic" of Gaps and Islands. Here is a working example at SQL Fiddle.

这篇关于在SQL Server中具有条件的Lag()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！