Lag() mit Bedingung in sql server

Deiner Frage (und Anon ' s ausgezeichnete Antwort) ist Teil die SQL-Inseln und Lücken. In dieser Antwort, werde ich versuchen zu untersuchen, die "row_number () - Magie" im detail.

Ich habe ein einfaches Beispiel, basierend auf Ereignissen in einem Ballspiel. Für jede Veranstaltung, die wir gerne drucken Sie die vorherigen und nächsten Quartal bei der damit verbundenen Nachricht:

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');

Hier ist eine Abfrage zeigen Sie das "row_number () - Magie" - Ansatz:

; 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

• Die erste CTE genannt "gruppiert" berechnet zwei row_number()s. Die erste ist 1 2 3 für jede Zeile in der Tabelle. Die zweite row_number() Orten pause Ansagen in einer Liste, und andere Ereignisse, die in einer zweiten Liste. Der Unterschied zwischen den beiden rn1 - rn2, ist einzigartig für jeden Abschnitt des Spiels. Es ist hilfreich, um zu überprüfen, Unterschied in der Beispiel-Ausgabe: es ist in der group_nr Spalte. Sie werden sehen, dass jeder Wert entspricht einem Abschnitt des Spiels.
• Der zweite CTE genannt "order_in_group" bestimmt die position der aktuellen Zeile innerhalb seiner Insel oder eine Lücke. Für eine Insel mit 3 Zeilen, die Positionen sind 1 2 3 für die aufsteigende Reihenfolge und 3 2 1 für die absteigender Reihenfolge.
• Schließlich, wir wissen genug, um sagen lag() und lead() wie weit Sie springen. Wir haben für die lag rank_asc Zeilen zu finden, die Letzte Zeile des vorherigen Abschnitts. Zu finden ist die erste Zeile der nächsten Rubrik haben wir für Sie zu führen rank_desc Zeilen.

Hoffe, das hilft zur Klärung der "Magie" der Lücken und Inseln. Hier ist ein Beispiel auf SQL Fiddle.

Hier ist noch ein weiterer Weg zu erreichen das gleiche Ergebnis, aber mit bedingten max/min-Funktionen das Fenster der über eine Ordinalzahl. Die Ordnungszahl kann eingestellt werden, basierend auf was auch immer Spalten den Zweck passt, aber in diesem Fall glaube ich, dass die OP will Sie Date und 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

Beachten Sie, dass das Beispiel des OP wurde erweitert, um zu zeigen, dass eingestreute Sequenzen von EC yeild zu dem beabsichtigten Ergebnis führen. Die Mehrdeutigkeiten eingeführt, die durch die frühesten zwei aufeinanderfolgende Zeilen mit Typ - SV wird in diesem Fall dazu führen, dass der maximale Wert abgeholt. Einrichten der Ordnungszahl enthalten die Price ist ein möglicher Weg, um dieses Verhalten zu ändern.

Einer SQLFiddle können hier gefunden werden: http://sqlfiddle.com/#!18/85117/1