Lag() с условием в SQL Server

Ответ 1

Ваш вопрос (и отличный ответ Анона) является частью SQL островов и пробелов. В этом ответе я попытаюсь подробно изучить "row_number() magic".

Я сделал простой пример, основанный на событиях в ballgame. Для каждого события мы хотели бы напечатать предыдущее и следующее сообщение:

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() s. Первая строка 1 2 3 для каждой строки таблицы. Второй row_number() помещает объявления паузы в один список и другие события во втором списке. Разница между двумя, rn1 - rn2, уникальна для каждого раздела игры. Полезно проверить разницу в примере вывода: в столбце group_nr. Вы увидите, что каждое значение соответствует одному разделу игры.
• Второй CTE, называемый "order_in_group", определяет положение текущей строки на своем острове или пробел. Для острова с 3 строками позиции 1 2 3 для возрастающего порядка и 3 2 1 для убывающего порядка.
• Наконец, мы достаточно знаем, чтобы сказать lag() и lead(), как далеко прыгать. Нам нужно запаздывать строки rank_asc, чтобы найти последнюю строку предыдущего раздела. Чтобы найти первую строку следующего раздела, нам нужно провести строки rank_desc.

Надеюсь, это поможет прояснить "магию" Гапсов и островов. Вот рабочий пример в SQL Fiddle.

Ответ 2

Да, вы можете использовать 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

Ответ 3

Вот еще один способ достижения того же результата, но с использованием условных функций max/min с окном по порядку. Порядковый номер может быть установлен в зависимости от того, какие столбцы соответствуют цели, но в этом случае я считаю, что OP подразумевает, что они будут Date и Time.

DROP TABLE IF EXISTS #t;
CREATE TABLE #t (
    Number INT, 
    Price MONEY, 
    Type CHAR(2),
    Date DATE,
    Time TIME(0)
);
INSERT INTO #t VALUES 
(23456, 0.666, 'SV', '2014/02/02', '10:00:02'),
(23457, 1.4  , 'EC', '2014/02/02', '09:50:45'),
(23658, 2.5  , 'EC', '2014/02/02', '09:50:45'),
(23660, 2.5  , 'EC', '2014/02/02', '09:50:48'),
(23465, 0.669, 'SV', '2014/02/02', '09:36:34'),
(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'), -- which one of these?
(23465, 0.670, 'SV', '2014/02/02', '07:36:34'); -- 

WITH time_ordered AS (
    SELECT *, DENSE_RANK() OVER (ORDER BY Date, Time) AS ordinal FROM #t
)
SELECT
    *,
    CASE WHEN Type = 'EC' 
    THEN MAX(CASE WHEN ordinal = preceding_non_EC_ordinal THEN Price END) 
    OVER (PARTITION BY preceding_non_EC_ordinal ORDER BY ordinal ASC) END AS preceding_price,
    CASE WHEN Type = 'EC'
    THEN MIN(CASE WHEN ordinal = following_non_EC_ordinal THEN Price END) 
    OVER (PARTITION BY following_non_EC_ordinal ORDER BY ordinal DESC) END AS following_price
FROM (
    SELECT
        *,
        MAX(CASE WHEN Type <> 'EC' THEN ordinal END) 
        OVER (ORDER BY ordinal ASC) AS preceding_non_EC_ordinal,
        MIN(CASE WHEN Type <> 'EC' THEN ordinal END) 
        OVER (ORDER BY ordinal DESC) AS following_non_EC_ordinal
    FROM time_ordered
) t
ORDER BY Date, Time

Обратите внимание, что пример, приведенный OP, был расширен, чтобы показать, что вкрапленные последовательности EC дают ожидаемый результат. Неоднозначность, введенная двумя самыми ранними последовательными строками с типом SV, в этом случае приведет к выбору максимального значения. Установка порядкового номера для включения Price - это возможный способ изменить это поведение.

SQLFiddle можно найти здесь: http://sqlfiddle.com/#!18/85117/1