Группировка столбцов SQL по строкам

ProgramBox

Группировка столбцов SQL по строкам

Post author:admin
Запись опубликована:12 апреля, 2023
Post category:Вопросы по программированию

#sql-server #group-by

#sql-сервер #группировать по

Вопрос:

Пытаюсь с SQL-запросом преобразовать имеющиеся у меня данные в требуемый формат. У меня есть журнал событий для компьютеров, и я хотел бы связать время начала и окончания и результат события в одной строке. Я не могу использовать ЛАГ из-за версии SQLServer. Приветствуется любая помощь.

текущий набор данных:

  ---------- ---------- ------------ ------------------------------ --------------------- 
| MACHINE  | EVENT_ID | EVENT_CODE |           DATE_TIME          | EVENT_DESCRIPTOR    |
 ---------- ---------- ------------ ------------------------------ --------------------- 
| 1        | 1        | 1          | 2020-08-06 14:59:26          | SCAN : START : z1 : |
| 1        | 2        | 6          | 2020-08-06 15:00:18          | SCAN : END : z1 :   |
| 1        | 3        | 1          | 2020-08-06 15:00:45          | SCAN : START : z1 : |
| 1        | 4        | 5          | 2020-08-06 15:01:54          | SCAN : ABORT : z1 : |
| 2        | 5        | 1          | 2020-08-06 15:02:15          | SCAN : START : z1 : |
| 2        | 6        | 6          | 2020-08-06 15:05:07          | SCAN : END : z1 :   |
| 1        | 7        | 1          | 2020-08-06 15:05:13          | NEST : START : z1 : |
| 1        | 8        | 6          | 2020-08-06 15:05:22          | NEST : END : z1 :   |
| 1        | 9        | 1          | 2020-08-06 15:07:17          | CUT : START : z1 :  |
| 1        | 10       | 6          | 2020-08-06 15:10:40          | CUT : END : z1 :    |
 ---------- ---------- ------------ ------------------------------ ---------------------

Результат, которого я пытаюсь достичь:

  ---------- ------------------------------ ------------------------------ ---------- 
| Machine  | SCAN:START:Z1 _TIME          | SCAN:STOP_OR_ABORT:Z1 _TIME  | OUTCOME  |
 ---------- ------------------------------ ------------------------------ ---------- 
| 1        | Thu Aug 06 14:59:26 BST 2020 | 2020-08-06 15:00:18          | END      |
| 1        | Thu Aug 06 15:00:45 BST 2020 | 2020-08-06 15:01:54          | ABORT    |
| 1        | Thu Aug 06 15:02:15 BST 2020 | 2020-08-06 15:05:07          | END      |
 ---------- ------------------------------ ------------------------------ ----------

1. «Я не могу использовать ЛАГ из-за версии SQLServer». Это означает, что вы используете SQL Sever 2008 или более раннюю версию. Я настоятельно рекомендую вам запустить путь обновления, поскольку это означает, что вы используете полностью неподдерживаемую версию SQL Server.

2. Этот образец данных также не имеет никакого смысла. Действительно ли ваши данные представляют собой одно varchar значение, как кажется на первый взгляд? Если это так, первым шагом является исправление этого и нормализация. Кроме того, если вы не можете использовать LAG , используйте объединение с самостоятельной ссылкой с ROW_NUMBER

3. Что случилось с идентификаторами событий 7 и 8? Они не соответствуют ожидаемым результатам.

4. Я предполагаю, что ему нужны только события сканирования, но в этом случае он также не должен включать Event_ID 9 и 10.

5. Да, я никогда не хотел включать события 9 и 10. Я планирую запрашивать разные дескрипторы событий отдельно, поскольку их много, и это была всего лишь выборка данных. Обновленный результат, отражающий это

Ответ №1:

Вы можете выбрать начальные события и присоединить к ним конечные события в виде подзапросов, например, в форме внешнего apply.

 select L1.Machine, L1.date_time as Start, L2.datetime as Stop_Or_Abort,
       case L2.Event_Id when 5 then 'ABORT' when 6 then 'END' end as Outcome
from MyLogs L1 
     outer apply (select top 1 L2.date_time, L2.Event_Code
                  from MyLogs L2
                  where L2.Machine = L1.Machine and                         
                        L2.Event_ID > L1.Event_ID and
                        L2.Event_Code in (5, 6) 
                  order by L2.Event_ID) as L2
where L1.Event_Descriptor Like 'SCAN%' and
      L1.Event_Code = 1

Ответ №2:

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

 Select  Machine = L.MACHINE,
        [SCAN:START:Z1 _TIME] =  L.DATE_TIME,
        [SCAN:STOP_OR_ABORT:Z1 _TIME] = E.DATE_TIME,
        Outcome = Case  when E.EVENT_CODE = 5 then 'ABORT'
                            when E.EVENT_CODE = 6 then 'END'
                End
From Logs L
Outer Apply
(
    Select top 1    L1.DATE_TIME,
                    L1.EVENT_CODE
    From Logs L1
    where   L1.MACHINE = L.MACHINE and
            L1.EVENT_CODE in (5, 6) and
            L1.DATE_TIME > L.DATE_TIME
    order by EVENT_ID
) E
where L.EVENT_CODE = 1
and L.EVENT_DESCRIPTOR like 'SCAN%'

1. Благодаря iceblade удалось заставить это работать. Хотя запрос выполнялся медленно — около 9 секунд против 0,034 для Marcs.

2. @Storm2015 наши запросы в основном одинаковы, они будут использовать точно такой же план выполнения. Разница во времени, которую вы видели, вероятно, заключалась в более медленном выполнении с первой попытки и более быстром выполнении второго запроса из-за использования данных, уже находящихся в кэше.

3. @MarcGuillot Я пробовал многократные запуски, но всегда получал результаты через 6-10 секунд

4. @Storm2015 любопытно, что единственное реальное различие между нашими запросами заключается в том, как мы построили конструкцию ОБРАЩЕНИЯ (простые случаи по сравнению с искомыми). Я бы не ожидал, что это окажет такое влияние.

5. Это может быть из-за сравнения внутри внешнего приложения, В моем случае я получаю следующую запись на основе L1.DATE_TIME > L.DATE_TIME, в ответе Марка сравнение выполняется по L2.Event_ID > L1.Event_ID, этот столбец, вероятно, проиндексирован. Следовательно, у @MarcGuillot есть лучший ответ. 🙂