Поиск Datediff между записями в одной таблице

#sql-server #tsql

#sql-сервер #tsql

Вопрос:

 IP             QID  ScanDate                Rank
101.110.32.80   6   2016-09-28 18:33:21.000 3
101.110.32.80   6   2016-08-28 18:33:21.000 2
101.110.32.80   6   2016-05-30 00:30:33.000 1

У меня есть таблица с определенными записями, сгруппированными по IP-адресу и QID.. Мое требование — выяснить, в какой записи пропущена последовательность в столбце даты или, другими словами, разница в дате составляет более 30 дней. В приведенной выше таблице разница в дате между рангом 1 и рангом 2 составляет более 30 дней.Итак, я должен отметить запись ранга 2.

Ответ №1:

Вы можете использовать LAG в Sql 2012

 declare @Tbl Table (Ip VARCHAR(50), QID INT, ScanDate DATETIME,[Rank] INT)

INSERT INTO @Tbl        
VALUES  
('101.110.32.80', 6,   '2016-09-28 18:33:21.000', 3),
('101.110.32.80', 6,   '2016-08-28 18:33:21.000', 2),
('101.110.32.80', 6,   '2016-05-30 00:30:33.000', 1)

;WITH Result
AS
(
    SELECT
        T.Ip ,
        T.QID ,
        T.ScanDate ,
        T.[Rank],
        LAG(T.[Rank]) OVER (ORDER BY T.[Rank]) PrivSRank,
        LAG(T.ScanDate) OVER (ORDER BY T.[Rank]) PrivScanDate
    FROM
        @Tbl T
) 

SELECT
    R.Ip ,
    R.QID ,
    R.ScanDate ,
    R.Rank ,
    R.PrivScanDate,
    IIF(DATEDIFF(DAY, R.PrivScanDate, R.ScanDate) > 30, 'This is greater than 30 day. Rank '   CAST(R.PrivSRank AS VARCHAR(10)), '') CFlag
FROM
    Result R

Результат:

 Ip                       QID         ScanDate                Rank        CFlag
------------------------ ----------- ----------------------- ----------- --------------------------------------------
101.110.32.80            6           2016-05-30 00:30:33.000 1           
101.110.32.80            6           2016-08-28 18:33:21.000 2           This is greater than 30 day. Rank 1
101.110.32.80            6           2016-09-28 18:33:21.000 3           This is greater than 30 day. Rank 2

Ответ №2:

Хотя здесь можно использовать оконные функции, я думаю, что самосоединение может быть более простым и понятным:

 SELECT
t1.IP,
    t1.QID,
    t1.Rank,
    t1.ScanDate as endScanDate,
    t2.ScanDate as beginScanDate,
    datediff(day, t2.scandate, t1.scandate) as scanDateDays     
FROM
    table as t1
    INNER JOIN table as t2 ON
        t1.ip = t2.ip
        t1.rank - 1 = t2.rank --get the record from t2 and is one less in rank
WHERE datediff(day, t2.scandate, t1.scandate) > 30 --only records greater than 30 days

Это довольно понятно. Мы объединяем таблицу с самой собой и объединяем ряды вместе, где ранг 2 присоединяется к рангу 1, ранг 3 присоединяется к рангу 2 и так далее. Затем мы просто проверяем записи, которые превышают 30 дней, используя функцию datediff .

1. Я не уверен, почему «ранг» здесь уместен

2. Подход, который не учитывает ранг, показал бы, что оба кортежа ранга (1,2) и (1,3) помечены как помеченные. Поскольку op не вызывает кортеж ранга (1,3), я сделал предположение, что OP заботится только о диапазонах дат между последовательными рангами. Поскольку соединения тогда четко определены, и, учитывая, что OP не обнаружил никакого решения своей проблемы, даже если их несколько, я решил, что хорошим вариантом будет самостоятельное объединение с использованием последовательных рангов для установления отношений.

Ответ №3:

Я бы использовал оконную функцию, чтобы избежать самосоединения, которое во многих случаях будет работать лучше.

 WITH    cte
          AS (
              SELECT
                t.IP
              , t.QID
              , LAG(t.ScanDate) OVER (PARTITION BY t.IP ORDER BY T.ScanDate) AS beginScanDate
              , t.ScanDate AS endScanDate
              , DATEDIFF(DAY,
                         LAG(t.ScanDate) OVER (PARTITION BY t.IP ORDER BY t.ScanDate),
                         t.ScanDate) AS Diff
              FROM
                MyTable AS t
             )
    SELECT
        *
    FROM
        cte c
    WHERE
        Diff > 30;