Блог

Главная - Вопросы по программированию - Группировка T-SQL путем изменения элементов

Группировка T-SQL путем изменения элементов

#sql #sql-server #datetime #gaps-and-islands

#sql #sql-сервер #дата и время #пробелы и острова

Вопрос:

Надеюсь, у вас все хорошо и безопасно!

В моем первом сообщении мне нужна помощь с T-SQL.

Как я могу пометить (чтобы подсчитать, сколько произошло PICK_STATE_CHANGES) список событий от одного события к другому?

Например, я хочу пометить (создать виртуальные контейнеры) все строки в столбце oel_class между ‘LABM_PICK_SCAN_LOCATION’ и сначала ‘LABM_PICK_DECLARE_TROLLEYY_FULL’ как 1, а затем каждую следующую ‘LABM_PICK_SCAN_LOCATION’ в ‘LABM_PICK_DECLARE_TROLLEYY_FULL’ как предыдущую 1 1:

 user    event_time          oel_class
102480  2020-03-25 12:09:58 LABM_PICK_SCAN_LOCATION <-- this is the starting point of the first virtual container (1)
102480  2020-03-25 12:10:23 TM_TROLLEY_COLLECT -  (1)
102480  2020-03-25 12:12:16 PICK_STATE_CHANGE -  (1)
102480  2020-03-25 12:12:39 PICK_STATE_CHANGE -  (1)
102480  2020-03-25 12:13:44 PICK_STATE_CHANGE -  (1)
102480  2020-03-25 12:14:09 PICK_STATE_CHANGE - etc.
102480  2020-03-25 12:14:39 PICK_STATE_CHANGE
102480  2020-03-25 12:15:20 PICK_STATE_CHANGE
102480  2020-03-25 12:15:20 PICK_STATE_CHANGE
102480  2020-03-25 12:16:17 PICK_STATE_CHANGE
102480  2020-03-25 12:16:51 PICK_STATE_CHANGE
102480  2020-03-25 12:17:27 PICK_STATE_CHANGE
102480  2020-03-25 12:18:02 PICK_STATE_CHANGE
102480  2020-03-25 12:18:02 LABM_PICK_DECLARE_TROLLEY_FULL <-- this is the end of the virtual container
102480  2020-03-25 12:18:48 TM_LOC_CHANGE <-- ignore this
102480  2020-03-25 12:19:28 LABM_PICK_DECLARE_TROLLEY_FULL <-- ignore this
102480  2020-03-25 12:21:40 TM_TROLLEY_PARK <-- ignore this
102480  2020-03-25 12:21:40 LABM_PICK_SELECT_PACK_AREA <-- ignore this
102480  2020-03-25 12:21:48 LABM_PICK_SCAN_LOCATION <-- this is the start of the second virtual container (2)
102480  2020-03-25 12:21:55 TM_TROLLEY_COLLECT -  (2)
102480  2020-03-25 12:24:57 PICK_STATE_CHANGE -  (2)
102480  2020-03-25 12:25:55 PICK_STATE_CHANGE -  (2)
102480  2020-03-25 12:26:33 PICK_STATE_CHANGE -  (2)
102480  2020-03-25 12:27:15 PICK_STATE_CHANGE -  (2)
102480  2020-03-25 12:27:52 PICK_STATE_CHANGE -  (2)
102480  2020-03-25 12:28:47 PICK_STATE_CHANGE -  (2)
102480  2020-03-25 12:29:38 PICK_STATE_CHANGE -  (2)
102480  2020-03-25 12:29:38 PICK_STATE_CHANGE -  (2)
102480  2020-03-25 12:30:03 PICK_STATE_CHANGE -  (2)
102480  2020-03-25 12:30:53 PICK_STATE_CHANGE -  (2)
102480  2020-03-25 12:33:16 PICK_STATE_CHANGE -  (2)
102480  2020-03-25 12:35:52 PICK_STATE_CHANGE -  (2)
102480  2020-03-25 12:35:52 LABM_PICK_DECLARE_TROLLEY_FULL <-- this is the end of the virtual container
102480  2020-03-25 12:44:05 TM_TROLLEY_PARK <-- ignore this

etc. 3, 4, 5, ...

Есть идеи? Я хотел сделать СЛУЧАЙ, но как мне сказать, чтобы поставить 1 рядом с конкретными событиями, следующими за одним, а затем 1 к «виртуальному контейнеру»?

Я использую SQL Server 2017.

Ответ №1:

Если я правильно вас понял, вы можете использовать lead() и совокупный sum() :

 select t.*,
    case when oel_class in ('TM_TROLLEY_COLLECT', 'PICK_STATE_CHANGE')
        then sum(case when oel_class = 'TM_TROLLEY_COLLECT' and lead_oel_class = 'PICK_STATE_CHANGE' then 1 else 0 end) over(partition by user order by event_time)
    end as grp
from (
    select t.*, 
        lead(oel_class) over(partition by user order by event_time) lead_oel_class
    from mytable t
) t