Блог

Я работаю над базой данных SQL Azure (T-SQL), где у меня есть таблица, выглядящая следующим образом :

 Device | MsgDate (type datetime2) | MsgType | Latitude | Longitude | other irrelevant columns
------- --------------------------- --------- ---------- ----------- -------------------------
1500   |  25/10/2020 14:01:05      |    1    |   40.5   |    -72.5  |  ...
1500   |  25/10/2020 14:01:45      |    1    |   40.5   |    -72.5  |  ...
1500   |  25/10/2020 14:31:23      |    2    |   40.6   |    -74.5  |  ...
1500   |  25/10/2020 15:02:14      |    2    |   43.5   |    -78.3  |  ...
1500   |  25/10/2020 15:24:55      |    3    |   48.5   |    -12.5  |  ...
1500   |  25/10/2020 15:25:03      |    3    |   48.5   |    -12.5  |  ...
1500   |  25/10/2020 18:36:31      |    1    |   48.5   |    -12.5  |  ...
1501   |  25/10/2020 15:17:44      |    1    |   39.5   |    -78.2  |  ...
1501   |  25/10/2020 15:18:21      |    1    |   39.5   |    -78.2  |  ...
1501   |  25/10/2020 15:39:02      |    3    |   48.5   |    -12.5  |  ...
 

Я пытаюсь дедуплицировать эту таблицу на основе следующего правила :

• каждый кортеж DeviceID / MsgDate / MsgType является ключом продукта, но MsgDate, которые отличаются друг от друга менее чем на 2 минуты, на самом деле идентичны и, следовательно, дублируются для одного устройства и одного типа сообщения. В случае дубликатов я сохраняю первое сообщение (но это не имеет большого значения, если сохраняется только одно сообщение).

На данный момент я фокусируюсь только на сообщениях типа 1 и 3.

Поэтому я пытаюсь получить таблицу :

 Device |  MsgDate (type datetime2) | MsgType | Latitude | Longitude | other irrelevant columns
------- --------------------------- --------- ---------- ----------- -------------------------
1500   |  25/10/2020 14:01:05      |    1    |   40.5   |    -72.5  |  ...
1500   |  25/10/2020 14:31:23      |    2    |   40.6   |    -74.5  |  ...
1500   |  25/10/2020 15:02:14      |    2    |   43.5   |    -78.3  |  ...
1500   |  25/10/2020 15:24:55      |    3    |   48.5   |    -12.5  |  ...
1500   |  25/10/2020 18:36:31      |    1    |   48.5   |    -12.5  |  ...
1501   |  25/10/2020 15:17:44      |    1    |   39.5   |    -78.2  |  ...
1501   |  25/10/2020 15:39:02      |    3    |   48.5   |    -12.5  |  ...
 

Я пробовал следующий метод, но, к сожалению, он усекает только минуты из части даты и времени, поэтому он неправильно обрабатывает дубликаты дат, для которых изменилась минута (например 24:55 -> 25:03).

 SELECT      POS1.DeviceID,
            POS1.MsgDate,
            POS1.MessageType,
            POS1.Latitude,
            POS1.Longitude
    FROM PositionTable POS1
    --WHERE MsgType = '1' or MsgType = '3'
    INNER JOIN (
            SELECT  DeviceID,
                    MIN(MsgDate) AS UniqueMsgDate
            FROM PositionTable
            WHERE MsgType = '1' OR MsgType = '3'
            GROUP BY DeviceID,
            DATEPART(YEAR, MsgDate),
            DATEPART(MONTH, MsgDate),
            DATEPART(DAY, MsgDate),
            DATEPART(HOUR, MsgDate),
            (DATEPART(MINUTE, MsgDate) / 2)
            ) POS2
            ON POS1.DeviceID = POS2.DeviceID AND POS1.MsgDate = POS2.UniqueMsgDate
 

Если я правильно понимаю, вы можете использовать lag() :

 with todelete as (
      select t.*,
             lag(msgdate) over (partition by device order by msgdate) as prev_msgdate
      from t
     )
delete from todelete
    where msgdate < dateadd(minute, 2, prev_msgdate);